Tuesday, May 12, 2009

Insert Multiple Values in Database Simultaneously.

How to utilize table valued parameter in SQl server 2008.

we can use tablevalued Parameter feature of SQl server to pass multiple values into sql server. we can insert multiple values in database Using Sql server 2008 User Define types. In Sql server 2008 we can pass Datatabel as parameter in Sqlcommand. in backend that parameter will be declared as type of that User Define Type.

(1)this is the SQL Script for Creating User Define Type.

CREATE Type TypeNameas Table(ID int,Name varchar(20))

(2)Procedure Showing How to Utilize input table.

CREATE procedure haresh_InsertUser( @InputTable as PrameterTable readonly)asBEGINinsert into Users_mst (Name)
select [Name] from @InputTable

you can also update multiple values in database using syntex ..

update Users_mst set Name=T.Name from
SELECT ID,[Name] FRom @InputTable
) AS T
WHERE T.ID= Users_mst.ID

(3)How to pass table from C# to SQl.

SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = Conn;
cmd.CommandText = "haresh_InsertUser";
DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Rows.Add(1, "Haresh");
dt.Rows.Add(2, "Kinjal");
cmd.Parameters.Add(new SqlParameter("@InputTable", dt)); Response.Write(cmd.ExecuteNonQuery());