Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 966
Anya Hope Avatar asked Sep 16 '14 14:09

Anya Hope


People also ask

What is a user-defined table type?

User-defined tables represent tabular information. They are used as parameters when you pass tabular data into stored procedures or user-defined functions. User-defined tables cannot be used to represent columns in a database table.

How do you declare a table type?

To declare variables of type table, use DECLARE @local_variable.

What is user-defined table function?

In SQL, a user-defined function (UDF) enables you to build complex logic into a single database object that you can call from a SELECT statement. You can use a table user-defined function (UDF) to create a parameterized, fixed view of the data in the underlying tables.


2 Answers

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.

like image 90
Habib Avatar answered Oct 26 '22 10:10

Habib


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

like image 31
sushil.agarwal Avatar answered Oct 26 '22 10:10

sushil.agarwal