How to pass table valued parameter to a stored procedure in ASP.NET
by GetCodeSnippet.com • May 29, 2014 • Microsoft .NET C# (C-Sharp), Microsoft ASP.NET, Microsoft VB.NET • 0 Comments
In my previous article, I have shown that how to create a table valued parameter store procedure and how we can pass table valued parameter in SQL Server.Here, in this article, I will show you that how you can pass table valued parameter and call a stored procedure in ASP.NET. You have to add SqldbType as structured. It describes the special data type for specifying structured data contained in a table valued parameter. Now let’s see the code.
C#
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DataTable dt = new DataTable (); dt.Columns.Add("FirstName", typeof(string)); dt.Columns.Add("LastName", typeof(string)); dt.Columns.Add("JoiningDate", typeof(DateTime)); dt.Rows.Add("Andrew", "Fuller", DateTime.Now); dt.Rows.Add("Steven", "Buchanan", DateTime.Now); SqlConnection con = new SqlConnection("Your Connection String here"); con.Open(); SqlCommand cmd = new SqlCommand("dbo.InsertEmployeeRecords", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@myTable", SqlDbType.Structured); cmd.Parameters["@myTable"].Value = dt; cmd.ExecuteNonQuery(); |
VB.NET
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Dim dt As New DataTable() dt.Columns.Add("FirstName", GetType(String)) dt.Columns.Add("LastName", GetType(String)) dt.Columns.Add("JoiningDate", GetType(DateTime)) dt.Rows.Add("Andrew", "Fuller", Date.Now) dt.Rows.Add("Steven", "Buchanan", Date.Now) Dim con As New SqlConnection("Your Connection String here") con.Open() Dim cmd As New SqlCommand("dbo.InsertEmployeeRecords", con) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@myTable", SqlDbType.Structured) cmd.Parameters("@myTable").Value = dt cmd.ExecuteNonQuery() |
