User defined table types, stored procedure parameters and c#

Capturing references to creating user defined table types in SQL server and then using them to write data out of API calls in c# (via SSIS custom components):

“In SQL Server 2008, a user-defined table type is a user-defined type that represents the definition of a table structure. You can use a user-defined table type to declare table-valued parameters for stored procedures or functions, or to declare table variables that you want to use in a batch or in the body of a stored procedure or function.”

https://technet.microsoft.com/en-us/library/bb522526%28v=sql.105%29.aspx

I’ve been using it to pass variables as parameters into stored procedures – there are some queries in forums about getting these to work, so here is my experience:

Firstly create the table type in SQL (use own tabletypename and appropriate fields and variable types

CREATE TYPE dbo.TableTypeName AS TABLE

([field1] int, [field2] nvarchar(500), [field3] datetime)

And create a stored procedure that processes the data as required, using this table type as a parameter:

CREATE PROCEDURE <stored proc name>

@ctttype dbo.TableTypeName READONLY

AS

INSERT INTO Table_on_db ([field1],[field2],[field3])

select [field1],[field2],[field3] from @ctttype

The parameter must be set to READONLY

Then create the datatable in the c# code:

DataTable dt = new DataTable();

dt.Clear();

dt.Columns.Add(“field1”, typeof(int));

dt.Columns.Add(“field2”, typeof(string));

dt.Columns.Add(“field3”, typeof(DateTime));

after trial and error it was easier to create the columns with typeof(variable type) as datetimes proved problematic – unless you explicitly suggested they were datetimes it would error trying to convert datetimes into string later. It obviously defaults to string or similar if you do not state otherwise.

I’d then use a foreach loop to run through the object returned from my API call and write the responses into the datatable:

foreach (APIResponseObject response in responses)

{

DataRow row = dt.NewRow();

row[“field1”] = response.field1;

row[“field2”] = response.field2;

row[“field3”] = response.field3;

dt.Rows.Add(row);

}

And then you can pass the data table through a parameterised SQL command:

SqlConnection destination = new SqlConnection(connectionStringDest);

destination.Open();

SqlCommand insertCommand = new SqlCommand(<stored proc name>, destination);

insertCommand.CommandType = CommandType.StoredProcedure;

SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(<tabletype variable name from stored proc>, dt);

tvpParam.SqlDbType = SqlDbType.Structured;

tvpParam.TypeName = <table type name from db>;

insertCommand.ExecuteNonQuery();

destination.Close();

I realise the wording for a number of these aspects is wrong, as they are more for my notes when I need to revisit this again, but hopefully it demonstrates this process moderately well.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s