使用企业库将数据表从ASP.NET传递到SQL Server [英] Pass (Data Table) to SQL Server From ASP.NET using Enterprise Library
问题描述
public void updateSkills(DataTable candidateSkillSets)
{
string sqlCommand = "Sp_Candidate";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "candidateSkillSets",DbType.Object, candidateSkillSets);
db.ExecuteNonQuery(dbCommand);
}
我有类似上面的方法,这里我将数据表传递给通过添加参数存储过程。 DbType.Object不采用数据表类型。我知道在ADO中我们可以使用 SqlDbType.Structured,但是对于企业libray来说,它是行不通的。我必须改用什么?
I have a method like the above, here i am passing the datatable to the stored procedure by adding the parameter."DbType.Object" is not taking the datatable type. I know in ADO we can use "SqlDbType.Structured", but for enterprise libray it is not working. What i have to use instead?
执行命令时出现以下错误
I am getting the following error while executing the command
传入的表格格式数据流(TDS)远程过程调用(RPC)
协议流不正确。参数1( @candidateSkillSets):
数据类型0x62(sql_variant)无效类型特定的
元数据的类型。
"The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 ("@candidateSkillSets"): Data type 0x62 (sql_variant) has an invalid type for type-specific metadata."
推荐答案
我必须修改@ eduardo的代码,可让我在企业库中工作:
I have to modify @eduardo's code to make me work in my case with Enterprise Library:
public int Insert(int id, DTO mnemonics)
{
DatabaseProviderFactory factory = new DatabaseProviderFactory();
Database Db = factory.CreateDefault();
using (var dbCommand = Db.GetStoredProcCommand("spINSERT"))
{
using (var table = new DataTable())
{
table.Columns.Add("Key", typeof(string));
table.Columns.Add("Value", typeof(string));
foreach (KeyValuePair<string, string> item in mnemonics.data)
{
var row = table.NewRow();
row["Key"] = item.Key;
row["Value"] = item.Value;
table.Rows.Add(row);
}
Db.AddInParameter(dbCommand, "id", DbType.int, id);
SqlParameter p = new SqlParameter("MNEMONICS", table);
p.SqlDbType = SqlDbType.Structured;
dbCommand.Parameters.Add(p);
Db.ExecuteNonQuery(dbCommand);
}
}
}
我在SQL Server中的类型:
My type in SQL Server:
CREATE TYPE [dbo].[StringDict] AS TABLE(
[Key] [varchar](max) NULL,
[Value] [varchar](max) NULL
)
I希望能帮助到某人
这篇关于使用企业库将数据表从ASP.NET传递到SQL Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!