using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Configuration; using System.Data.Common; using System.Data; namespace TableValueParameters { public class Program { public static void Main(string[] args) { //To represent the table parameter in C# we need to either have a set of entities which are IEnumreable //or a data reader or a Data table //in this example we create a data table with same name as the type we have in the DB DataTable dataTable = new DataTable("SampleDataType"); //we create column names as per the type in DB dataTable.Columns.Add("SampleString", typeof(string)); dataTable.Columns.Add("SampleInt", typeof(Int32)); //and fill in some values dataTable.Rows.Add("99", 99); dataTable.Rows.Add("98", null); dataTable.Rows.Add("97", 99); SqlConnection sqlConnection = new SqlConnection( ConfigurationManager.ConnectionStrings["SampleDBConnectionString"].ConnectionString); SqlCommand command = sqlConnection.CreateCommand(); command.CommandType = System.Data.CommandType.StoredProcedure; command.CommandText = "[dbo].[SampleProcedure]"; SqlParameter parameter = new SqlParameter(); //The parameter for the SP must be of SqlDbType.Structured parameter.ParameterName="@Sample"; parameter.SqlDbType = System.Data.SqlDbType.Structured; parameter.Value = dataTable; command.Parameters.Add(parameter); sqlConnection.Open(); int numberOfRowsUpdated = command.ExecuteNonQuery(); Console.WriteLine("Number of rows updated = " + numberOfRowsUpdated); Console.ReadKey(); } } }
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.
This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)