Table Valued Parameter in SqlServer 2008
3.14/5 (3 votes)
How to create common procedure to insert the data in any table.
Introduction
Table Values Parameter (TVP) is one of the most useful features of SqlServer. We can create a user defined TVP and can use this as parameter in any procedure. When we worked on the large projects we need to create number of procedures to insert the data means one procedure for each table some time more than one. Here I am trying to create a single procedure to insert the data in any table with n number of columns.
Background
Have a look on collections to use and understand this article.
Using the code
To use this we need to follow the following steps.
1- We need to create a table to insert the data.
/* Create table Emp */
create table Emp(
EmpCode int,
EmpName varchar(20)
)
Go
2- Use following script to create a user defined Table Valued Parameter.
/* Create the User defined Table type */
CREATE TYPE [dbo].[pTableData] AS TABLE(
[ColName] [varchar](50) NULL,
[ColValue] [varchar](100) NULL
)
GO
After execution the above script a new user defined object should be displayed in Programmability>Types>User-Defined Table Type as in the following screen.
3- Now we are creating a common procedure to insert the data using the User-Defined Table Types which we have created in the second point.
/* Create procedure to insert data in any table with n number of columns */
Create procedure LB_SP_InsertData
(
@pTableName varchar(100), --Table Name
@pData pTableData readonly --Table data
)
as
Declare @SqlText as varchar(5000)
SET @SqlText='insert into '+@pTableName+'('
select @SqlText += ColName+',' from @pData
SET @SqlText=SUBSTRING(@SqlText,0,LEN(@SqlText))
SET @SqlText+=')values('
select @SqlText +=''''+ColValue+''''+',' from @pData
SET @SqlText=SUBSTRING(@SqlText,0,LEN(@SqlText))
SET @SqlText+=')'
PRINT (@SqlText)
Exec(@SqlText)
4- To test the procedure at back end we need to use the following script-
/*To test the procedure at back end use following script*/
declare @par as pTableData
insert into @par values('EmpCode','50')
insert into @par values('EmpName','Rahul Pratap Singh')
--select * from @par
Exec LB_SP_InsertData 'Emp', @par
Ok we have done with database, its time to understand how to call this procedure from dotnet client application, we need to complete the following steps to call this procedure-
1- First of all we need to design a web page with two text box and a button as in the following screen-
2 - the code file of the above web page should contains the following code -
using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using LoopBreak;
using System.Data;
public partial class UserRegistration : System.Web.UI.Page
{
Emp objUser = new Emp();
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
objUser.EmpCode = Convert.ToInt32(txtEmpCode.Text);
objUser.EmpName = txtEmpName.Text;
objUser.RegisterUser();
Response.Redirect("Success.aspx");
}
}
2- In business logic file, i am creating a class name Emp which contains all the properties and business logic regarding the Emp class.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Collections;
/// <summary>
/// Summary description for CategoryMaster
/// </summary>
///
///
namespace LoopBreak
{
public class Emp
{
//Object declaration section
DataCollection objUserDataCollection = new DataCollection();
DataDetail objDataDetails = new DataDetail();
#region "Property Declaration"
public int EmpCode { get; set; }
public string EmpName { get; set; }
#endregion
public void RegisterUser()
{
objDataDetails._DataCollection = this.objUserDataCollection;
objDataDetails._strTableName = clsConstant.cnsttblUser_Master;
objDataDetails.AddDataToDataCollection("EmpCode", this.EmpCode.ToString());
objDataDetails.AddDataToDataCollection("EmpName", this.EmpName);
DBInsert.InsertData(objDataDetails);
}
}
}
We are creating the object of DataCollection class which is the collection of user data just like as DataTable in Ado.Net, we can also use DataTable here for the same task to store all the rows to insert. We have implemented the List and IEnumerable type in the DataCollection class to adding extra functionality to iterate the data from the class.
/// <summary>
/// DataCollection class stores the data to insert.
/// </summary>
public class DataCollection : List<DataRow>, IEnumerable<SqlDataRecord>
{
IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
{
var sdr = new SqlDataRecord(
new SqlMetaData("ColName", SqlDbType.VarChar, 50),
new SqlMetaData("ColValue", SqlDbType.VarChar, 5000)
);
foreach (DataRow objUserData in this)
{
sdr.SetString(0, objUserData.StrColName);
sdr.SetString(1, objUserData.StrColValue);
yield return sdr;
}
}
}
The DataDetail class used to store the information about inserted data.Table name data collection and a method to add the data in data collection.
/// <summary>
/// DataDetails class contains the inserted data details.
/// </summary>
public class DataDetail
{
string strTableName;
public string _strTableName
{
get { return strTableName; }
set { strTableName = value; }
}
private DataCollection DataCollection;
public DataCollection _DataCollection
{
get { return DataCollection; }
set { DataCollection = value; }
}
public void AddDataToDataCollection(string strColName, string strColValue)
{
DataRow objDataRow = new DataRow();
objDataRow.StrColName = strColName;
objDataRow.StrColValue = strColValue;
DataCollection.Add(objDataRow);
}
}
The Data Access Layer contains the following code to insert the data using the procedure-
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using Microsoft.SqlServer.Server;
/// <summary>
/// Summary description for DAL
/// </summary>
///
namespace LoopBreak
{
public class DAL
{
public DAL() { }
/// <summary>
/// Returns the type of data provider that the application is using. The application will read from it's config file in the Provider element
/// of the AppSettings node.
/// </summary>
/// <returns></returns>
public static string GetProviders()
{
string provider = ConfigurationSettings.AppSettings["conStr"];
return provider;
}
}
#region "Procedure to insert the data"
public class DBInsert
{
public static void InsertData(DataDetail pUserDetails)
{
SqlConnection objSqlConnection = null;
SqlCommand objSQLComman=null;
try
{
objSQLComman = new SqlCommand(clsConstant.cnstLB_SP_InsertData);
objSQLComman.CommandType = CommandType.StoredProcedure;
objSqlConnection = new SqlConnection(DAL.GetProviders());
objSQLComman.Connection = objSqlConnection;
SqlParameter parTableName = new SqlParameter("@pTableName", SqlDbType.VarChar, 100);
parTableName.Value = pUserDetails._strTableName;
objSQLComman.Parameters.Add(parTableName);
SqlParameter parData = new SqlParameter("@pData", SqlDbType.Structured);
parData.TypeName = "dbo.pTableData";
parData.Value = pUserDetails._DataCollection;
objSQLComman.Parameters.Add(parData);
objSqlConnection.Open();
objSQLComman.ExecuteNonQuery();
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
if (objSQLComman != null)
{
objSQLComman.Dispose();
}
if (objSqlConnection .State == ConnectionState.Open)
{
objSqlConnection.Close();
}
objSqlConnection.Close();
}
}
}
/// <summary>
/// DataDetails class contains the inserted data details.
/// </summary>
public class DataDetail
{
string strTableName;
public string _strTableName
{
get { return strTableName; }
set { strTableName = value; }
}
private DataCollection DataCollection;
public DataCollection _DataCollection
{
get { return DataCollection; }
set { DataCollection = value; }
}
public void AddDataToDataCollection(string strColName, string strColValue)
{
DataRow objDataRow = new DataRow();
objDataRow.StrColName = strColName;
objDataRow.StrColValue = strColValue;
DataCollection.Add(objDataRow);
}
}
/// <summary>
/// DataRow class used to add a new row in DataCollection class.
/// </summary>
public class DataRow
{
string strColName;
public string StrColName
{
get { return strColName; }
set { strColName = value; }
}
string strColValue;
public string StrColValue
{
get { return strColValue; }
set { strColValue = value; }
}
}
/// <summary>
/// DataCollection class stores the data to insert.
/// </summary>
public class DataCollection : List<DataRow>, IEnumerable<SqlDataRecord>
{
IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
{
var sdr = new SqlDataRecord(
new SqlMetaData("ColName", SqlDbType.VarChar, 50),
new SqlMetaData("ColValue", SqlDbType.VarChar, 5000)
);
foreach (DataRow objUserData in this)
{
sdr.SetString(0, objUserData.StrColName);
sdr.SetString(1, objUserData.StrColValue);
yield return sdr;
}
}
}
#endregion
}
We are using the InsertData methos of DBInsert class to insert the data in any table. this will insert the data in any table of n numer of columns.
Hope this will help to reduce the code and will give the more flexiblity to modify the code.
Thanks.
Rahul
Points of Interest
Did you learn anything interesting/fun/annoying while writing the code? Did you do anything particularly clever or wild or zany?
History