Archive

Posts Tagged ‘Table-Valued Parameter’

Creating and Using Table-Valued Parameter in C# and T-SQL

June 21, 2009 2 comments

With the growing complexity of database applications, we often come across a problem when we need to send multiple rows to database table(s). When SqlBulkCopy class was introduced in .Net 2.0, it was a great relief for all. But if the same requirement arises at back-end processing and mostly in Stored Procedures, programmers often feel something lacking in stored procedure parameters. Workaround solutions are temporary tables, cursors, etc. to overcome.
SQL Server 2008 has a new feature called Table-Valued parameter where we can pass a table valued parameter to stored procedure just like we pass Entity or DTO class object as parameter to Data Access Layer in C# or VB.Net.
Now we can directly pass table like object instead of processing row by row ranging from few to many rows. We used SqlBulkCopy class to post many rows, but still it was not able to pass a table as a parameter.
In this example, we will see how to create a table-value type object, pass it to a stored procedure as a parameter in Sql Server 2008. Also, how to use it programmatically in C# 3.5 as well.

–Step 1: Create a User-defined Table Type
USE AdventureWorks
GO
CREATE TYPE PublisherTableType AS TABLE
(
PublisherID bigint,
PublisherName varchar(50),
PublisherAddress varchar(50),
PublisherContactNo varchar(15)
);
GO

–Step 2: Create a destination table
CREATE TABLE [dbo].[BookPublisher](
[PublisherId] [bigint] NULL,
[PublisherName] [varchar](50) NULL,
[PublisherAddress] [varchar](50) NULL,
[PublisherContactNo] [varchar](15) NULL
) ON [PRIMARY]

–Step 3: Create a procedure to receive data for the table-valued parameter
CREATE PROCEDURE usp_InsertPublisherInfo
@PublisherInfo dbo.PublisherTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO dbo.BookPublisher(PublisherID,PublisherName,PublisherAddress,PublisherContactNo)
SELECT * FROM @PublisherInfo;
GO

–Step 4: Declare a variable of type PublisherTableType
Declare @PubInfo dbo.PublisherTableType;
Insert into @PubInfo
values      (1, ‘BPB’, ‘Delhi, India’, ‘1234567890’   ),
(2, ‘PHI’, ‘Mumbai, India’, ‘7892345678’),
(3, ‘Apress’, ‘Chennai, India’, ‘467890034’),
(4, ‘Wrox’, ‘Kolkata, India’, ‘7463643847’);

–Step 5: Execute SP by passing table value type
Exec usp_InsertPublisherInfo @PublisherInfo = @PubInfo;

–Step 6: Select the newly inserted records
Select * from dbo.BookPublisher;
GO

I think steps mentioned in the above example are quite clear. One can see this newly created table value type object under “User-Defined Table Types”. Now, let’s look into another example to exploit the newly created PublisherTableType.

private DataTable PublisherList()
{
//
DataTable TblPub = new DataTable();
TblPub.Columns.Add(“PublisherId”, typeof(Int64));
TblPub.Columns.Add(“PublisherName”, typeof(String));
TblPub.Columns.Add(“PublisherAddress”, typeof(String));
TblPub.Columns.Add(“PublisherContactNo”, typeof(String));
DataRow PubRow;
//Fill the collection values
for (Int16 RwCnt = 0; RwCnt < 10; RwCnt++)
{
//
PubRow = TblPub.NewRow();
PubRow[0] = 100 + RwCnt;
PubRow[1] = “BookPub” + ” – ” + RwCnt.ToString();
PubRow[2] = “PubAddress” + ” – ” + RwCnt.ToString();
PubRow[3] = “0091” + ” – ” + “4567” + RwCnt.ToString();
TblPub.Rows.Add(PubRow);
}
return TblPub;
}
private void SaveDataUsingTableValueType(DataTable PubList)
{
//
String ConString = @”Data Source=DINESH_PC\DINESHPC;Initial Catalog=AdventureWorks;Integrated Security=True”;
SqlConnection SqlCon = new SqlConnection(ConString);
String SpName = “usp_InsertPublisherInfo”;
SqlCommand SqlCmd = new SqlCommand(SpName, SqlCon);
SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
//Parameter type is SqlDbType.Structured for table valued type parameter
SqlParameter SqlParam = SqlCmd.Parameters.Add(“@PublisherInfo”, System.Data.SqlDbType.Structured);
SqlParam.Value = PubList;
SqlCon.Open();
//Execute the command
SqlCmd.ExecuteNonQuery();
SqlCon.Close();
}

Next, call the save method as: SaveDataUsingTableValueType(PublisherList());
This way we see how this new feature is a bliss.
We have to remember some points with table value type. The READONLY qualifier after table value parameter in stored procedure is required here to mark parameter object as a pointer to the original table value. This way the original content passed cannot be modified inside the stored procedure. Once a table type object is created, it cannot be altered other than dropping and recreating it again with modified definition. Performance wise it is well suited for table rows less than 1000.

Have a nice coding day!