Table valued parameter is a way to pass multiple rows to a SQL stored procedure from the client application. At the client side, this is known as structured data and can be in the form of data table or data reader. At the server side, this can be accessed similar to a @ temp table variable, with limitations like TVP is read-only i.e. no updates, inserts nor deletes, these can be passed as inputs to any other stored procedures or functions.
Performance:
Considering client – server connectivity and traversing while updating a length records – child record combination, it’s a great performance boost. While in comparison with other temp table variables it’s not that efficient as TVP cannot have indexes defined except primary key and constraints.
Let’s demonstrate how to use a TVP from a .Net client.
TVPs are available only from SQL server 2008 onwards and .Net 3.5 onwards supports the TVP (structured) client data types. What we have here is a SQL server 2008 R2 Database TestDB with a table MyTable defined with few columns and three stored procedures for fetching data, deleting and restoring (not database restore) data back to the table. The client application fetches the data from the table, displays it onto the datagrid, then deletes data from table and then inserts the same data back into the table by passing the cached data-table as a TVP to the restoring SP.
Let’s start by creating table and type for TVP
-- create main table
CREATE TABLE [dbo].[MyTable](
[C1] [uniqueidentifier] ROWGUIDCOL default newid(),
[C2] [datetime] default GetDate(),
[C3] [char](150) default 'AAAAAAAAAAAAAAAAAAAAA',
[C4] [int] default 10
) ON [PRIMARY];
GO
-- create TYPE for TVP
CREATE TYPE MyTableType AS TABLE(
C1 UNIQUEIDENTIFIER,
C2 DATETIME,
C3 CHAR(150),
C4 INT
);
|
Now let’s define the three stored procedures to support TVP
-- create procedures to use TVP
CREATE Procedure usp_GetMyTableData
AS
Begin
Select C1,C2,C3,C4 from MyTable;
End;
go
Create Procedure usp_DeleteMyTableData
AS
Begin
Delete from MyTable;
End;
go
Create procedure usp_RestoreMyTableData
@tvp MyTableType READONLY
AS
Begin
insert into MyTable
Select C1,C2,C3,C4 from @tvp;
End;
go
|
More realistic approach would be to replace the insert statement in “usp_RestoreMyTableData” with a Merge statement. Let’s alter our restore procedure with merge statement. For a detailed description on Merge could be found at http://sqlnfr.blogspot.com/2012/01/tsql-merge.html
ALTER PROCEDURE usp_RestoreMyTableData
@tvp MyTableType READONLY
AS
BEGIN
MERGE MyTable AS Target
USING (SELECT C1,C2,C3,C4 FROM @tvp) AS Source
ON Target.C1 = Source.C1
WHEN MATCHED THEN
UPDATE SET
Target.C2 = Source.C2,
Target.C3 = Source.C3,
Target.C4 = Source.C4
WHEN NOT MATCHED BY TARGET THEN
INSERT
(C1,C2,C3,C4)
VALUES
(Source.C1,Source.C2,Source.C3,Source.C4 )
WHEN NOT MATCHED BY Source THEN
Delete ;
END;
GO
|
Now let’s insert some data into our table, say a 10,000 rows, let’s do it in one go J
-- inserting data into MyTable
-- 10,000 rows
SET NOCOUNT ON;
INSERT INTO MyTable
DEFAULT VALUES;
GO 10000
SET NOCOUNT OFF;
|
select count(1) from MyTable;
go
|
Now the UI or the client:
Client is a .Net UI build using VSTS 2010. It has a data-grid and 3 command buttons one for retrieving data, deleting records and the third one for restoring records.
Following pictures shows the initial blank screen and the screen filled with data.
 |
 |
UI code is as given below, only difference in the btnRestore click event, where it passes data-table dt as a structured parameter.
SqlParameter param = cmd.Parameters.AddWithValue("@tvp", dt);
param.SqlDbType = SqlDbType.Structured;
Complete code is given below
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace TableTypeUI
{
public partial class frmMain : Form
{
SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=TestDB;User Id=sa;Password=sa@hydhtc76704l;");
DataTable dt;
public frmMain()
{
InitializeComponent();
}
private void frmMain_Load(object sender, EventArgs e)
{
}
private void btnFill_Click(object sender, EventArgs e)
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "usp_GetMyTableData";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
SqlDataAdapter da = new SqlDataAdapter(cmd);
try
{
conn.Open();
dt = new DataTable();
da.Fill(dt);
}
catch(Exception ex)
{
throw ex;
}
finally
{
if (conn.State != ConnectionState.Closed)
conn.Close();
}
dGrd.DataSource = dt;
}
private void btnRestore_Click(object sender, EventArgs e)
{
DataTable dt2 = new DataTable();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "usp_RestoreMyTableData";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
SqlParameter param = cmd.Parameters.AddWithValue("@tvp", dt);
param.SqlDbType = SqlDbType.Structured;
SqlDataAdapter da = new SqlDataAdapter(cmd);
try
{
conn.Open();
int ret = cmd.ExecuteNonQuery();
MessageBox.Show(ret.ToString() + " Row(S) affected ");
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State != ConnectionState.Closed)
conn.Close();
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "usp_DeleteMyTableData";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
try
{
conn.Open();
int ret = cmd.ExecuteNonQuery();
MessageBox.Show(ret.ToString() + " Row(S) deleted ");
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State != ConnectionState.Closed)
conn.Close();
}
}
}
}
|
Now when we run this code, we will have the UI displaying the initial blank screen and when we push the “Fill Data” button, we will have the grid filled up with records. The “Delete” button will delete all the records from MyTable. Let’s push the “Delete” button and delete all the data in MyTable (go to database to verify this). Now again push the “Restore” button to insert data back into MyTable. Now modify dome of the data on the grid and then again click “Restore” and see what happens when you reload the data by hitting “Fill data” button.
Enjoy!!!
No comments:
Post a Comment