Calling SQL Server Stored Procedure With Table-Valued Parameter in C# – Andrey Zavadskiy

In one of my previous post I described how to use table-value parameter in T-SQL code. Today I will show how to use it in your .NET application.

This is a sample C# console application. The most important things you should pay attention to are the following:

create DataTable with structure equivalent to table type defined in SQL database (in my case string for varchar(20), and Int16 for smallint)
fill your DataTable with data
add parameter to SqlCommand object with type SqlDbType.Structured
assign DataTable to parameter’s Value property

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
  class Program
  {
    static void Main(string[] args)
    {
      DataTable dtTVParameter = new DataTable();
      dtTVParameter.Columns.Add("PhoneNumber", typeof(string));
      dtTVParameter.Columns.Add("PhoneTypeID", typeof(Int16));

      DataRow row1 = dtTVParameter.NewRow();
      row1["PhoneNumber"] = "88005555555";
      row1["PhoneTypeID"] = 1;
      dtTVParameter.Rows.Add(row1);

      DataRow row2 = dtTVParameter.NewRow();
      row2["PhoneNumber"] = "88007777777";
      row2["PhoneTypeID"] = 2;
      dtTVParameter.Rows.Add(row2);

      using (SqlConnection conn1 = new SqlConnection("Data Source=(local);Initial Catalog=ClientsDB;Integrated Security=True"))
      {
        using (SqlCommand cmd1 = new SqlCommand("[dbo].[SaveClientPhoneNumbers]", conn1))
        {
          cmd1.CommandType = CommandType.StoredProcedure;
          cmd1.Parameters.AddWithValue("@ClientGuid", "14a579d2-3689-45e1-af6f-7dfbb17d57cf");
          cmd1.Parameters.Add("@PhoneNumbers", SqlDbType.Structured);
          cmd1.Parameters["@PhoneNumbers"].Value = dtTVParameter;

          conn1.Open();
          cmd1.ExecuteNonQuery();
        }
      }
    }
  }
}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data;

using System.Data.SqlClient;

namespace ConsoleApplication1

{

class Program

{

static void Main(string[] args)

{

DataTable dtTVParameter = new DataTable();

dtTVParameter.Columns.Add("PhoneNumber", typeof(string));

dtTVParameter.Columns.Add("PhoneTypeID", typeof(Int16));

DataRow row1 = dtTVParameter.NewRow();

row1["PhoneNumber"] = "88005555555";

row1["PhoneTypeID"] = 1;

dtTVParameter.Rows.Add(row1);

DataRow row2 = dtTVParameter.NewRow();

row2["PhoneNumber"] = "88007777777";

row2["PhoneTypeID"] = 2;

dtTVParameter.Rows.Add(row2);

using (SqlConnection conn1 = new SqlConnection("Data Source=(local);Initial Catalog=ClientsDB;Integrated Security=True"))

{

using (SqlCommand cmd1 = new SqlCommand("[dbo].[SaveClientPhoneNumbers]", conn1))

{

cmd1.CommandType = CommandType.StoredProcedure;

cmd1.Parameters.AddWithValue("@ClientGuid", "14a579d2-3689-45e1-af6f-7dfbb17d57cf");

cmd1.Parameters.Add("@PhoneNumbers", SqlDbType.Structured);

cmd1.Parameters["@PhoneNumbers"].Value = dtTVParameter;

conn1.Open();

cmd1.ExecuteNonQuery();

}

Leave a Reply Cancel reply