Tuesday, 30 September 2014

Insert Bulk row at a time in sql table from C#

(1) Initially create Type in sql server...its interesting.

Detail about Type in sql Follow this link:
http://technet.microsoft.com/en-us/library/ms175007.aspx
http://technet.microsoft.com/en-us/library/bb510489.aspx#Restrictions

----Create type-----
create type _table as table
(
E_Name varchar(50),
Salary varchar(200)
)

(2)After that create Procedure in sql:=>here i have to use _table type as table-valued parameter in Procedure.

The table-valued parameter "@tablevariable" must be declared with the READONLY option.

------Create procedure with table-valued parameter---
create proc uspInsertBulkRow
@tablevariable _table readonly
as
begin
insert into mytable(Name,salary) select E_Name,Salary from @tablevariable
end

(3)After that i have to pass datatable as parameter value in procedure and This procedure insert multiple row in sql table.
private DataTable _datatableData(){
               DataTable _dt = new DataTable();
            _dt.Columns.Add("id");
            _dt.Columns.Add("Name");
            _dt.Columns.Add("Salary");
            _dt.Rows.Add("1","Anu", "12000");
            _dt.Rows.Add("3","Anamika", "15000");
            _dt.Rows.Add("4", "Ram", "17000");
            _dt.Rows.Add("5","Aman", "4000");
            _dt.Rows.Add("6","Tarun", "18000");
            _dt.Rows.Add("7","Akash", "5000");
            return _dt;}
    
 using (SqlCommand cmd = new SqlCommand("uspInsertBulkRow", con))
            {
         // ------_datatableData() Is a  method and its return datatable-----------
                cmd.Parameters.AddWithValue("@tablevariable", _datatableData());
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
Output : It's a simple example.you can use this concept as per requirement



No comments:

Post a Comment