在C＃中创建用户定义的表类型以在SQL Server存储过程中使用 [英] Create a user defined table type in c# to use in sql server stored procedure

问题描述

我试图编写一个C＃程序，该程序创建一个完整的表以发送回SQL Server存储过程。

I'm trying to write a C# program which creates a whole table to send back to a SQL Server stored procedure.

我碰到了msdn指南，但变成了令人难以置信的困惑：
http://msdn.microsoft.com/en -us / library / cc879253.aspx

I came across the msdn guide but became incredibly confused: http://msdn.microsoft.com/en-us/library/cc879253.aspx

我尝试使用msdn指南，但是尽管添加了对microsoft.sqlserver.smo的引用，但还是出现了错误。编译器建议的sqlserver.connectioninfo，microsoft.sqlserver.management.sdk.sfc。错误是：

I tried to use the msdn guide but get an error despite adding references to microsoft.sqlserver.smo, microsoft.sqlserver.connectioninfo, microsoft.sqlserver.management.sdk.sfc as suggested by the compiler. The error is:

为userdefinedtabletype设置父项失败。

Set parent failed for userdefinedtabletype.

基于msdn指南的代码段：

Code snippet based on msdn guide:

Server srv = new Server();
Database db = srv.Databases["MyDatabase"];

//fails at this line
UserDefinedTableType udtt = new UserDefinedTableType(db, "TestTable");
udtt.Columns.Add(new Column(udtt, "Col1", DataType.Int));
udtt.Create();

我只想能够建立用户定义的数据表，这是我唯一的相关问题我发现这里只处理在SQL中创建用户定义的表，而不是C＃。

I would simply like to be able to build a user defined data table, the only related questions I've found here deal only with creating a user defined table in SQL, not C#.

我的SQL Server连接代码是这样的：

My SQL Server connection code is this:

 DataSet ds = new DataSet("SQLDatabase");

       using (SqlConnection conn = new SqlConnection(Settings.Default.SQLConnectionString))
       {
           SqlCommand sqlComm = new SqlCommand("StoredProcedure", conn);

           sqlComm.Parameters.AddWithValue("@param", paramValue);

           sqlComm.CommandType = CommandType.StoredProcedure;

           SqlDataAdapter da = new SqlDataAdapter();
           da.SelectCommand = sqlComm;
           da.Fill(ds);
       }

请有人用简单的语言向我展示如何创建用户定义的表

Please could someone show me in simple language, how to create a user defined table type in my C# program?

推荐答案

最简单的选择是创建 DataTable 在C＃代码中，并将其作为参数传递给您的过程。假设您已将用户定义的表类型创建为：

Simplest option is to create a DataTable in C# code and pass it as a parameter to your procedure. Assuming that you have created a User Defined Table Type as:

CREATE TYPE [dbo].[userdefinedtabletype] AS TABLE(
    [ID] [varchar](255) NULL,
    [Name] [varchar](255) NULL
)

然后在您的C＃代码中执行以下操作：

then in your C# code you would do:

DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof (string));
dt.Columns.Add("Name", typeof (string));
//populate your Datatable

SqlParameter param = new SqlParameter("@userdefinedtabletypeparameter", SqlDbType.Structured)
{
    TypeName = "dbo.userdefinedtabletype",
    Value = dt
};
sqlComm.Parameters.Add(param);

请记住将 SqlDbType.Structured 指定为参数类型，并指定创建UDT时使用的名称。

Remember to specify SqlDbType.Structured as the type of parameter and specify the name you have used in creating your UDT.

这篇关于在C＃中创建用户定义的表类型以在SQL Server存储过程中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！