如果您对如何将用户定义的表类型传递给Java的MSSQL请求和用户定义的数据类型可以用感兴趣，那么这篇文章一定是您不可错过的。我们将详细讲解如何将用户定义的表类型传递给Java的MSSQL请求的各种细
如果您对如何将用户定义的表类型传递给Java的MSSQL请求和用户定义的数据类型可以用感兴趣，那么这篇文章一定是您不可错过的。我们将详细讲解如何将用户定义的表类型传递给Java的MSSQL请求的各种细节，并对用户定义的数据类型可以用进行深入的分析，此外还有关于asp.net – SQL Server用户定义的表类型和.NET、asp.net – 如何将SQL用户定义的函数添加到实体框架？、c# – 使用用户定义的表类型在SQL中插入和更新、java – Ksoap：将用户定义的类作为参数传递给web方法时无法序列化异常的实用技巧。
本文目录一览：- 如何将用户定义的表类型传递给Java的MSSQL请求（用户定义的数据类型可以用）
- asp.net – SQL Server用户定义的表类型和.NET
- asp.net – 如何将SQL用户定义的函数添加到实体框架？
- c# – 使用用户定义的表类型在SQL中插入和更新
- java – Ksoap：将用户定义的类作为参数传递给web方法时无法序列化异常
如何将用户定义的表类型传递给Java的MSSQL请求（用户定义的数据类型可以用）
给定一个简单的存储过程，该存储过程具有一个以整数列作为参数的表：
CREATE PROCEDURE [dbo].[table_sel]@tbl INT32Table READONLYASBEGINSET NOCOUNT ON;SELECT value FROM @tblEND如何从Java执行此存储过程？从C＃，这就是我想要的：
SqlConnection sqlconn;System.Data.DataTable tbl = new System.Data.DataTable("INT32Table", "dbo");tbl.Columns.Add("value", typeof(int));tbl.Rows.Add(2);tbl.Rows.Add(3);tbl.Rows.Add(5);tbl.Rows.Add(7);tbl.Rows.Add(11);using (SqlCommand command = new SqlCommand("table_sel")){ command.Connection = sqlconn; command.Parameters.AddWithValue("@tbl", tbl); command.CommandType = System.Data.CommandType.StoredProcedure; SqlDataReader reader = command.ExecuteReader(); //do stuff}答案1
小编典典您不能使用JDBC传递TVP。正如他们两年前提到的那样，Microsoft JDBC团队仍在为此进行工作。参考链接：
访问：[http](http://blogs.msdn.com/b/jdbcteam/archive/2012/04/03/how-would-
you-use-table-valued-parameters-tvp.aspx) :
//blogs.msdn.com/b/jdbcteam/archive/2012/04/03/how-would-you-use-table-
valued-parameters-
tvp.aspx
通过在过程主体中传递XML参数和OPENXML（）来尝试一些替代解决方案，而不是使用TVP。
asp.net – SQL Server用户定义的表类型和.NET
我需要将一个整数数组从.NET传递给一个存储过程,所以我搜索了这个主题并最终跑过了
Arrays and Lists in SQL Server 2008,由Erland Sommarskog编写并且据称被认为是进行此过程的标准方式.
我已经尝试了两种不同的方式将用户定义的表类型传递给存储过程,但我每个都得到例外.这两种方式都与Erland Sommarskog在上面的链接中使用的方式类似.
方式#1 – 使用DataTable作为sqlParameter
DataTable dt = new DataTable();
dt.Columns.Add("n",typeof(int));
// Just adding 3 test rows to the DataTable
DaTarow dr = dt.NewRow();
dr["n"] = 1;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["n"] = 2;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["n"] = 3;
dt.Rows.Add(dr);
// Creation of the sqlParameter
sqlParameter p = new sqlParameter();
p.ParameterName = "@ids";
p.Direction = ParameterDirection.Input;
p.sqlDbType = sqlDbType.Structured;
p.TypeName = "lstInt_TblType";
p.Value = dt;
// Blows up here
DataSet ds = DAWrapper.GetDataSet(
Common.GetDB(),"usp_Test",new sqlParameter[] { p });
我得到的例外是：
传入的表格数据流(TDS)远程过程调用(RPC)协议流不正确.参数1(“@ids”)：数据类型0x62(sql_variant)具有类型特定元数据的无效类型.
方式2 – 使用List作为sqlParameter
List<sqlDataRecord> lstSDR = new List<sqlDataRecord>();
sqlMetaData[] tvp_deFinition = { new sqlMetaData("n",sqlDbType.Int) };
// Just adding 3 test rows
sqlDataRecord rec = new sqlDataRecord(tvp_deFinition);
rec.SetInt32(0,50);
lstSDR.Add(rec);
rec = new sqlDataRecord(tvp_deFinition);
rec.SetInt32(0,51);
lstSDR.Add(rec);
rec = new sqlDataRecord(tvp_deFinition);
rec.SetInt32(0,52);
lstSDR.Add(rec);
// Creation of the sqlParameter
sqlParameter p = new sqlParameter();
p.ParameterName = "@ids";
p.Direction = ParameterDirection.Input;
p.sqlDbType = sqlDbType.Structured;
p.TypeName = "lstInt_TblType";
p.Value = lstSDR;
// Blows up here
DataSet ds = DAWrapper.GetDataSet(
Common.GetDB(),new sqlParameter[] { p });
我得到的例外情况表明：
从对象类型System.Collections.Generic.List`1 [[Microsoft.sqlServer.Server.sqlDataRecord,System.Data,Version = 4.0.0.0,Culture = neutral,PublicKeyToken = b77a5c561934e089]]到目标托管提供程序本机不存在映射类型.
其他信息
lstInt_TblType是我的sql Server 2008中用户定义的表类型.它确实存在(我对此进行了三重检查！).它有一个名为“n”的列,类型为int,主键,不允许空值.我复制了Erland如何设置他的.
我还验证了存储过程usp_Test可以在sql Server Manager Studio中运行,因此我非常确定异常不会从该方向发出.这是我用来验证存储过程是否有效的t-sql：
DECLARE @ids lstInt_TblType INSERT @ids(n) VALUES(1),(2),(3) EXEC usp_Test ids
任何有关如何使用此建议的建议将不胜感激.谢谢！
*编辑：*
存储过程usp_Test：
ALTER PROCEDURE [dbo].[usp_Test]
(
@ids lstInt_TblType READONLY
)
AS
BEGIN
SET NOCOUNT ON;
select *
from dbo.dat_MetaData
where MetaDataTypeID in (select n from @ids)
END
GO
解决方法
找到了另一种方法去做.这种方式使用System.Data.sqlClient库创建与数据库的连接,指定存储过程名称,然后将参数作为DataTable传递,作为sql Server用户定义的表类型.
using (sqlConnection conn = new sqlConnection(connStr)) {
sqlCommand cmd = conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "dbo.usp_Test";
cmd.Parameters.AddWithValue("@ids",dt);
conn.open();
using (sqlDataReader sqlReader = cmd.ExecuteReader()) {
DataTable retTbl = new DataTable();
retTbl.Load(sqlReader);
}
}
asp.net – 如何将SQL用户定义的函数添加到实体框架？
我可以像在.dbml中一样在我的.edmx文件中添加sql函数吗？如果可以的话,怎么做？如果我不能,是否有任何解决方法？
我试图谷歌,但没有找到任何具体的答案,如何做到这一点.
基于给定的答案,我创建了一个存储过程,并尝试添加“导入函数”,但它表示“存储过程不返回列”.我哪里做错了？
功能：
ALTER FUNCTION [dbo].[fn_locationSearch](@keyword varchar(10)) RETURNS TABLE AS RETURN ( SELECT CustomerBranch.ID,CustomerBranch.BranchName,CustomerBranch.Longitude,CustomerBranch.Latitue,CustomerBranch.Telephone,CustomerBranch.CategoryID,CustomerBranch.Description FROM freetexttable (CustomerOffer,*,@keyword) abc INNER JOIN OffersInBranch ON abc.[key]=OffersInBranch.OfferID INNER JOIN CustomerBranch ON OffersInBranch.BranchID=CustomerBranch.ID UNION SELECT CustomerBranch.ID,CustomerBranch.Latitude,CustomerBranch.Description FROM CustomerBranch WHERE FREETEXT(*,@keyword) )
存储过程：
ALTER PROCEDURE USP_locationSearch (@keyword varchar(10)) AS BEGIN SELECT * from dbo.fn_locationSearch(@keyword) END
解决方法
在Entity Framework中没有对sql用户定义函数的内置支持,最好的方法是创建一个包装函数调用并返回其输出的存储过程,然后将该过程添加到EF模型中.
c# – 使用用户定义的表类型在SQL中插入和更新
以下是我创建的新数据类型.
CREATE TYPE [dbo].[UpdateHotelTableType] AS TABLE(
[ID] [int] NULL,[HotelID] [int] NULL,[FromDate] [datetime] NULL,)
以下是我使用上述数据类型的存储过程.
ALTER PROCEDURE [dbo].[SP_Hotel_Info_Update]
-- Add the parameters for the stored procedure here
@XHotelInfoDetails UpdateHotelTableType READONLY,AS
BEGIN
Update dbo.HotelInfo
SET
FromDate = r.FromDate,from @XHotelInfoDetails r
Where HotelInfo.ID = r.ID
END
这适用于数据库中的更新结果.但我想检查id是否存在,如果id不存在,请将行插入表中.否则更新当前记录.在这里,我发送更新数据列表.
任何人都可以通过检查ID的存在来帮助我重新创建存储过程以插入数据.
解决方法
使用
MERGE：
Performs insert,update,or delete operations on a target table based on the results of a join with a source table. For example,you can synchronize two tables by inserting,updating,or deleting rows in one table based on differences found in the other table.
ALTER PROCEDURE [dbo].[SP_Hotel_Info_Update]
-- Add the parameters for the stored procedure here
@XHotelInfoDetails UpdateHotelTableType READONLY,AS
BEGIN
MERGE dbo.HotelInfo AS trg
USING @XHotelInfoDetails AS src
ON src.ID = trg.ID
WHEN MATCHED THEN
UPDATE SET FromDate = src.FromDate
WHEN NOT MATCHED BY TARGET THEN
INSERT (col1,col2,...)
VALUES (src.col1,src.col2,...);
END
编辑：
In my datatable,there can be newly added rows as well as deleted rows. So how can I compare the id and delete rows from hotelinfo table?
你可以添加新条款：
WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
THEN DELETE;
具体条件从目标中删除数据.
java – Ksoap：将用户定义的类作为参数传递给web方法时无法序列化异常
我花了几天时间试图找出如何使我的用户定义的java类可序列化,以便我可以将它作为参数发送到android ksoap调用c#web方法.
下面是我的代码和调用webservice时在logcat中抛出的异常,如果我得到即时答复或帮助,我将感激不尽.
我的java类XY.java：
import org.ksoap2.serialization.KvmSerializable;
import org.ksoap2.serialization.PropertyInfo;
public class XY implements KvmSerializable {
public static Class XY_CLASS = XY.class;
private String MyNum;
private String OppPhoneNum;
private String Name;
public XY()
{
}
public XY(String MyNum, String Name, String oppNum)
{
this.MyNum = MyNum;
this.Name = Name;
this.OppPhoneNum = oppNum;
}
public String getPhoneNum() {
return MyNum;
}
public void setPhoneNum(String MyNum) {
this.MyNum = MyNum;
}
public String getName() {
return Name;
}
public void setName(String Name) {
this.Name = Name;
}
public String getopponentPhoneNum() {
return OppPhoneNum;
}
public void setopponentPhoneNum(String OppPhoneNum) {
this.OppPhoneNum = OppPhoneNum;
}
@Override
public Object getProperty(int arg0) {
switch(arg0)
{
case 0:
return MyNum;
case 1:
return OppPhoneNum;
case 2:
return Name;
}
return null;
}
@Override
public int getPropertyCount() {
// Todo Auto-generated method stub
return 3;
}
@Override
public void getPropertyInfo(int index, Hashtable arg1, PropertyInfo info) {
switch(index)
{
case 0:
info.type = PropertyInfo.STRING_CLASS;
info.name = "MyNum";
break;
case 1:
info.type = PropertyInfo.STRING_CLASS;
info.name = "OppPhoneNum";
break;
case 2:
info.type = PropertyInfo.STRING_CLASS;
info.name = "Name";
break;
default:break;
}
}
@Override
public void setProperty(int index, Object value) {
switch(index)
{
case 0:
MyNum = value.toString();
break;
case 1:
OppPhoneNum = value.toString();
break;
case 2:
Name = value.toString();
break;
default:
break;
}
}
}
C#等价类：
[Serializable]
public class XY
{
public System.String Name
{
get;
set;
}
public System.String MyNum
{
get;
set;
}
public System.String OppPhoneNum
{
get;
set;
}
}
这就是我从我的活动中使用ksoap调用服务的方式：
private void unKNown(List<XY> entries)
{
//Initialize soap request + add parameters
SoapObject request = new SoapObject(NAMESPACE, METHOD_NAME);
PropertyInfo entriesProp =new PropertyInfo();
entriesProp.setName("entries");
entriesProp.setValue(entries);
entriesProp.setType(ArrayList.class);
//Use this to add parameters
request.addProperty(entriesProp);
//Declare the version of the SOAP request
SoapSerializationEnvelope envelope = new SoapSerializationEnvelope(SoapEnvelope.VER11);
envelope.setoutputSoapObject(request);
envelope.addMapping(NAMESPACE, "XY", XY.XY_CLASS);
envelope.dotNet = true;
try {
HttpTransportSE androidHttpTransport = new HttpTransportSE(URL);
//this is the actual part that will call the webservice
androidHttpTransport.call(SOAP_ADDCONTACTS, envelope);
// Get the SoapResult from the envelope body.
if (envelope.bodyIn instanceof SoapFault)
{
String str= ((SoapFault) envelope.bodyIn).faultstring;
Log.i("", str);
}
else
{
SoapObject resultsRequestSOAP = (SoapObject) envelope.bodyIn;
if(resultsRequestSOAP != null)
{
Log.i("AddContacts", "Adding Contacts succeeded");
}
}
}
catch (Exception e)
{
e.printstacktrace();
}
}**
Logcat例外：
java.lang.RuntimeException: Cannot Serialize: [XY .....**
注意：我正在尝试将XY对象列表作为参数传递给Web服务方法.
我将不胜感激任何帮助 .
解决方法:
我编辑了我的示例并为您添加了新的完整示例,我认为它可以帮助您.在这个例子中,我在服务器端数据库中有一个customer表,我想通过KvmSerializable用户定义的类将android中的数据插入到该表中.
以下是客户的KvmSerializable用户定义类：
public class Customer implements KvmSerializable {
public int Customer_ID;
public String Customer_Name;
public String Customer_Family;
public Customer() {
}
public Customer(int customer_id,
String customer_name,
String customer_family) {
Customer_ID = customer_id;
Customer_Name = customer_name;
Customer_Family = customer_family;
}
public Object getProperty(int arg0) {
// Todo Auto-generated method stub
switch (arg0) {
case 0:
return Customer_ID;
case 1:
return Customer_Name;
case 2:
return Customer_Family;
}
return null;
}
public int getPropertyCount() {
// Todo Auto-generated method stub
return 25;
}
public void getPropertyInfo(int index, Hashtable arg1, PropertyInfo info) {
// Todo Auto-generated method stub
switch (index) {
case 0:
info.type = PropertyInfo.INTEGER_CLASS;
info.name = "Customer_ID";
break;
case 1:
info.type = PropertyInfo.STRING_CLASS;
info.name = "Customer_Name";
break;
case 2:
info.type = PropertyInfo.STRING_CLASS;
info.name = "Customer_Family";
break;
default:
break;
}
}
public void setProperty(int index, Object value) {
// Todo Auto-generated method stub
switch (index) {
case 0:
Customer_ID = Integer.parseInt(value.toString());
break;
case 1:
Customer_Name = value.toString();
break;
case 2:
Customer_Family = value.toString();
break;
default:
break;
}
}
}
现在c#用户定义的客户类：
public class Customer
{
public int Customer_ID;
public string Customer_Name;
public string Customer_Family;
}
这是我为通过以下方式发送KvmSerializable对象而定义的CallSoap类：
public class CallSoap {
public static String NAMESPACE = "http://127.0.0.1:80/";
public static String URL = "http://127.0.0.1:80/service.asmx?WSDL";
public static Customer[] customers;
public static int AddCustomer(Customer[] customers) {
String MethodName = "AddCustomer";
SoapObject soapAddCustomer = new SoapObject(NAMESPACE, MethodName);
//customers Parameter
SoapObject soapDetails = new SoapObject(NAMESPACE, "customers");
SoapObject soapDetail[] = new SoapObject[customers.length];
for (int i=0;i<customers.length;i++){
soapDetail[i]= new SoapObject(NAMESPACE, "Customer");
soapDetail[i].addProperty("Customer_ID", customers[i].Customer_ID);
soapDetail[i].addProperty("Customer_Name", customers[i].Customer_Name);
soapDetail[i].addProperty("Customer_Family", customers[i].Customer_Family);
}
soapAddRequest.addSoapObject(soapDetails);
SoapSerializationEnvelope envelope = new SoapSerializationEnvelope( SoapEnvelope.VER11);
envelope.dotNet = true;
envelope.setoutputSoapObject(soapAddRequest);
envelope.addMapping(NAMESPACE, "Customer", new Customer().getClass());
HttpTransportSE HttpTransportSE = new HttpTransportSE(URL);
try {
HttpTransportSE.call(NAMESPACE + MethodName, envelope);
String result = envelope.getResponse().toString();
return Integer.parseInt(result);
} catch (Exception e) {
e.printstacktrace();
return 0;
}
}
最后是服务器端的AddCustomer方法：
[WebMethod]
public int AddCustomer(Customer[] customers)
{
for(int i=0;i<customers.Length;i++){
//Access to customer fields for allrows via
int id = customers[i].Customer_ID;
String name = customers[i].Customer_Name;
String = customers[i].Customer_Family;
}
return customers.Length;
}
今天关于如何将用户定义的表类型传递给Java的MSSQL请求和用户定义的数据类型可以用的介绍到此结束，谢谢您的阅读，有关asp.net – SQL Server用户定义的表类型和.NET、asp.net – 如何将SQL用户定义的函数添加到实体框架？、c# – 使用用户定义的表类型在SQL中插入和更新、java – Ksoap：将用户定义的类作为参数传递给web方法时无法序列化异常等更多相关知识的信息可以在本站进行查询。
本文标签：
![[转帖]Ubuntu 安装 Wine方法（ubuntu如何安装wine）](https://www.gvkun.com/zb_users/cache/thumbs/4c83df0e2303284d68480d1b1378581d-180-120-1.jpg)
