Table Value Parameter Use With C#

Introduction

Table Valued Parameter is a new feature introduced with SQL Server 2008. Table Valued Parameters help to pass multiple rows of data from a client application to SQL Server without multiple round trips. Using a Table Valued Parameter we can pass multiple rows to a stored procedure.

Table Valued Parameter is a mechanism to pass bulk data from ADO.NET to SQL Server. In the following example we learn how to pass a Table Valued Parameter to a stored procedure. Using a Table Valued Parameter, we can pass a table as a single object instead of row by row.

Step 1: Create a table and a User Defined table type:

CREATE TABLE CUSTOMER
(
      CustomerId INT NOT NULL,
      CustomerName VARCHAR(MAX),
      Isdeleted BIT,
      PRIMARY KEY (CustomerId)
)

CREATE TYPE dbo.TableValuedTypeExample AS TABLE
(
      CustomerId INT NOT NULL,
      CustomerName VARCHAR(MAX),
      PRIMARY KEY (CustomerId)
)

Now create a procedure to receive data for the Table Valued Parameter and insert it into our original table:

CREATE PROC InsertValue
(@TempTable AS dbo.TableValuedTypeExample READONLY)
AS
BEGIN
      INSERT INTO CUSTOMER (CustomerId,CustomerName ,Isdeleted )
      SELECT CustomerId, CustomerName, 0 AS Isdeleted FROM @TempTable
END

Please refer Table-Valued Parameter in SQL Server 2008.

Step 2: Create a DataTable or structure the same as your Table Valued Parameter. Remember that all columns of the DataTable are parallel to the Table Data type:

static DataTable CreateTable()
{
    DataTable dt = new DataTable();
    dt.Columns.Add("CustomerId", typeof(Int32));
    dt.Columns.Add("CustomerName", typeof(string));
    return dt;
}

Step 3: To pass the data to the stored procedure, it must be represented as a SqlParameter and the type of this parameter must be structured:

//Create Table
DataTable myTable = CreateTable(); 

// Add New Rowto table
myTable.Rows.Add(1, "Jignesh Trivedi");
myTable.Rows.Add(2, "Tejas Trivedi");
myTable.Rows.Add(3, "Rakesh Trivedi");
 
SqlConnection connection = new SqlConnection("Data Source= DatabaseName;Initial Catalog=AdventureWorks;User
Id=sa;Password=password;");
connection.Open();
SqlCommand cmd = new SqlCommand("InsertValue", connection);
cmd.CommandType = CommandType.StoredProcedure;

//Pass table Valued parameter to Store Procedure
SqlParameter sqlParam = cmd.Parameters.AddWithValue("@TempTable", myTable);
sqlParam.SqlDbType = SqlDbType.Structured; 
cmd.ExecuteNonQuery();
connection.Close();
Console.Write("Data Save Successfully.");

Step 4: You can also do this using a generic list. Here the myDataCollection class is inherited from the List<myData> class and
the IEnumerable<SqlDataRecord> interface. The implementation IEnumerable <SqlDataRecord> will be used to convert our List data to our user
defined table:

public class myData
{
    public int CustomerId { get; set; }
    public string CustomerName { get; set; }
}

public class myDataCollection : List<myData>, IEnumerable<SqlDataRecord>
{
    IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
    {
        SqlDataRecord ret = new SqlDataRecord(
            new SqlMetaData("CustomerId", SqlDbType.Int),
            new SqlMetaData("CustomerName", SqlDbType.VarChar, 20)
            );
           
        foreach (myData data in this)
        {
            ret.SetInt32(0, data.CustomerId);
            ret.SetString(1, data.CustomerName);
            yield return ret;
        }
    }
}
myDataCollection myTable = new myDataCollection();
myTable.Add(new myData { CustomerId = 4, CustomerName = "Jignesh" });
myTable.Add(new myData { CustomerId = 5, CustomerName = "Tejas" });
myTable.Add(new myData { CustomerId = 6, CustomerName = "Rakesh" });
 
SqlConnection connection = new SqlConnection("Data Source= DatabaseName;Initial Catalog=AdventureWorks;User
Id=sa;Password=password;");
connection.Open();
SqlCommand cmd = new SqlCommand("InsertValue", connection);
cmd.CommandType = CommandType.StoredProcedure;

//Pass table Valued parameter to Store Procedure
SqlParameter sqlParam = cmd.Parameters.AddWithValue("@TempTable", myTable);
sqlParam.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery();
connection.Close();
Console.Write("Data Save Successfully.");

Conclusion

The table-valued parameter feature introduced with SQL Server 2008 has been truly needed. This will help developers to write code that provides better performance to applications by decreasing round-trips to the server.