Pass table as parameter to SQLCLR

Question

Tell me answer

Srinivas Arevarapu · Answer

public static void SalesSqlStoredPro(DataTable mytable) { } i want like this

Hima Bindu · Answer

This is how you pass TVP as input parameter this ex is using Data Reader DataTable _dt; // 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();

Srinivas Arevarapu · Answer

Hi , I want In Sql CLR Procedure how to do in c# code i nedd. Suppose if take Sqlstring it will covert varchar like this i need to convert Table parameter in c#

Amit Singh · Answer

Hey Srinivas Check this Link 1.http://www.c-sharpcorner.com/UploadFile/ff2f08/pass-the-table-type-data-to-sql-clr-object/ 2.http://stackoverflow.com/questions/1356955/table-valued-parameters-to-clr-procedures-in-sql-server-2008-possible 3.http://stackoverflow.com/questions/3008830/pass-table-as-parameter-to-sqlclr-tv-udf

Rajeev Punhani · Answer

Hi Srinivas, I think you are trying to insert multiple rows through procedure.You can use table valued parameter in procedure.Please have a look at the link below. http://www.c-sharpcorner.com/UploadFile/dbb82e/passing-a-table-to-a-stored-procedure-in-sql-server-using-ta/

Pass table as parameter to SQLCLR

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (5)