Pass table as parameter to SQLCLR
Tell me answer
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Guest UserPosted Jul 19, 2016, 3:05 AM
Hima BinduPosted Jul 19, 2016, 2:25 AM
This is how you pass TVP as input parameter this ex is using Data Reader
// create data table to insert items
_dt = new DataTable("Items");
_dt.Columns.Add("ItemID", typeof(string));
_dt.Columns.Add("Name", typeof(string));
_dt.Rows.Add(4, "SuperBowl 9 Hat");
_dt.Rows.Add(5, "SuperBowl 10 T-Shirt");
_dt.Rows.Add(6, "SuperBowl 13 Towel");
_dt.Rows.Add(7, "SuperBowl 14 Helmet");
SqlConnection con; // modify connection string to connect to your database string conStr = "Server=localhost;Database=MSSQLTIPS;Trusted_Connection=True;"; con = new SqlConnection(conStr); con.Open(); using (con) { // Configure the SqlCommand and SqlParameter. SqlCommand sqlCmd = new SqlCommand("dbo.InsertItemsTVP", con); sqlCmd.CommandType = CommandType.StoredProcedure; SqlParameter tvpParam = sqlCmd.Parameters.AddWithValue("@ItemTVP", _dt); //Needed TVP tvpParam.SqlDbType = SqlDbType.Structured; //tells ADO.NET we are passing TVP sqlCmd.ExecuteNonQuery(); } con.Close();Guest UserPosted Feb 6, 2016, 7:10 AM
Amit Kumar SinghPosted Feb 6, 2016, 7:03 AM
Rajeev PunhaniPosted Feb 6, 2016, 6:25 AM