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?
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.
To declare variables of type table, use DECLARE @local_variable.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With