Create a user defined table type in c# to use in sql server stored procedure

Question

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

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

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:

Set parent failed for userdefinedtabletype.

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();

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#.

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?

Habib · Accepted Answer · 2014-09-16 14:09:16Z

33

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
)

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);

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

answered Sep 16, 2014 at 14:09

Habib

224k30 gold badges420 silver badges446 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anya Hope Over a year ago

Yes, I hadn't realised that I needed to create the user defined table type within the SQL Server database first. That's helped a lot!

sushil.agarwal · Accepted Answer · 2020-11-15 18:42:54Z

1

I have faced problem using user defined table types is that being passed as structured type the table defination needs be same sequence of field names as it's in user defined type. Hence a procedure is required to create table within application from database user defined table type to exactly match and avoid mishap, today my 4 hours got wasted in knowing what wrong happening with my stored procedure. Only mistake was 1st two fields sequence was swapped. Please guide if some one can help how best this could be handled

answered Nov 15, 2020 at 18:42

sushil.agarwal

1611 silver badge9 bronze badges

Collectives™ on Stack Overflow

Create a user defined table type in c# to use in sql server stored procedure

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related