Another one of the Bulk sending Method is SQL Type Method. In this Method we will create the User-Defined Table Type in SQL Server 2008 and send it as a single parameter.
The following example will describe,
BulkCopyUsingType.cs
protected void Page_Load(object sender, EventArgs e)
{
DataTable dtDataTable = new DataTable("UserDetailes");
DataRow drDataRow = null;
dtDataTable.Columns.Add("System", typeof(string));
dtDataTable.Columns.Add("NoOfEmployess", typeof(int));
drDataRow = dtDataTable.NewRow();
drDataRow["System"] = "FireFly";
drDataRow["NoOfEmployess"] = "1000";
dtDataTable.Rows.Add(drDataRow);
drDataRow = dtDataTable.NewRow();
drDataRow["System"] = "Viking";
drDataRow["NoOfEmployess"] = "2421";
dtDataTable.Rows.Add(drDataRow);
using (SqlConnection objCon = new SqlConnection("pwd=pfp123;UID=pfp;database=usrdb;server=10.20.36.61"))
{
objCon.Open();
using (SqlCommand objCmd = new SqlCommand())
{
objCmd.CommandText = "usp_TypedParamInsert";
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Connection = objCon;
SqlParameter param1 = new SqlParameter("@LocationID", SqlDbType.Int);
param1.Value = 1;
objCmd.Parameters.Add(param1);
SqlParameter param = new SqlParameter("@UsersCollection", SqlDbType.Structured);
param.Value = dtDataTable; //Directly send the Bulk data as a table.
objCmd.Parameters.Add(param);
objCmd.ExecuteNonQuery();
}
}
}
In DB, we need to do the followings,
--Create Destination Table
CREATE TABLE [dbo].[tblUserDefinedTypedData](
[LocationID] [tinyint] NOT NULL,
[System] [varchar](20) NOT NULL,
[NoOfEmployess] [int] NOT NULL
) ON [PRIMARY]
-- Create the Type of User-Defined Table structure
CREATE TYPE [dbo].tblPFPSystem AS TABLE
(
[System] [varchar](20) NOT NULL,
[NoOfEmployess] [int] NOT NULL
)
GO
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Now we create the SP for insert operation
CREATE PROCEDURE [dbo].[usp_TypedParamInsert]
(
@LocationID INT,
@UsersCollection AS [dbo].[tblPFPSystem] READONLY
)
AS
BEGIN
SET NOCOUNT ON;
--This row performs bulk copy of object DataTable to Table in SQL
INSERT INTO [tblUserDefinedTypedData] SELECT @LocationID,* FROM @UsersCollection
END
Now, we will run the application, the DataTable Values are getting inserted into DB. In this example I have send a DataTable as hard coded one, instead of this you may use your data tables according to your needs. Now the Results shows like below,
03:36 |
Category:
Concepts
|
1 comments
Comments (1)
nice link
it's very very help full link