Insert Multiple Records in One Shot to avoid Database Hitting.

vishalneeraj-24503
Posted by in ASP.NET category on for Intermediate level | Points: 250 | Views : 10629 red flag
Rating: 2.5 out of 5  
 2 vote(s)

In this article,we will learn about Inserting Multiple Records in One Shot to avoid Database Hitting.

Introduction

It's sometimes required not to Hit Database every time,when there are multiple records and we have to delete or update or insert records one-by-one based on some conditions.

So,database hitting should not be performed again and again.
If there are 1000 of records in DB,and we bind those 1000 records in a Gridview.
and we are inserting records one-by-one,so there may be Database hitting 1000 times.
So,it slow-downs performance,because ii will pick records and inserts into Database,then again comes back and takes records and again inserts data into Database.So sometimes database or our application hangs-up.

So,idea is that to remove this overhead on Database server,we will insert re cords in one shot.

It's required mostly about Database performances as well as Application performance.Avoid Database hitting when there are lots of records.So the idea is that,We will make an Insert Query Separated by Semi-Colon(;),and send this insert queries to database,so there will be only 1 hitting to Database.

For Example:-
Insert into tbl_name(column_name1,column_name2,column_name3) Values(some_data,some_data,some_data);

Insert into tbl_name(column_name1,column_name2,column_name3) Values(some_data,some_data,some_data);
As we can see that at the last of insert query Semi-colon is appended.so these Insert Queries will be sent to Sql-Server.

Objective

Insert Multiple Records in One Shot to avoid Database Hitting.

Using the code

We can understand it by an example.
Suppose we have a Gridview.

Our Aspx looks like below:-
<div>
<table>
<tr>
<td>
<asp:Label ID="lbl_message" runat="server" Text=""></asp:Label>
</td>
</tr>
<tr>
<td>
<asp:GridView ID="grid_view_employee_details" runat="server" AutoGenerateColumns="false"
AllowPaging="true" OnRowCommand="grid_view_employee_details_RowCommand">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="lbl_employee_id" runat="server" Text='<%#Eval("employee_id") %>' Visible="false"></asp:Label>
<asp:CheckBox ID="chk_employee_id" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Employee Code">
<ItemTemplate>
<asp:TextBox runat="server" Text='<%#Eval("employee_code") %>' ID="txt_employee_code">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Employee Name">
<ItemTemplate>
<asp:TextBox runat="server" Text='<%#Eval("employee_name") %>' ID="txt_employee_name">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Employee Address">
<ItemTemplate>
<asp:TextBox runat="server" Text='<%#Eval("address") %>' ID="txt_employee_address">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:Button ID="btn_remove" CommandName="Remove_Rows" runat="server" Text="Remove Rows"
CommandArgument='<%#Container.DataItemIndex %>' />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
<tr>
<td align="right">
<asp:Button ID="btn_save" runat="server" Text="Save" OnClick="btn_save_click" />
</td>
</tr>
</table>
</div>
I have taken Textbox Control inside ItemTemplate.

In Code-Behind,

1st create a function to Fill Gridview as shown:-

    private void fill_employee_details_grid()
{
SqlConnection con = null;
try
{
con = new SqlConnection(Convert.ToString(ConfigurationManager.ConnectionStrings["DataBaseConnection"].ConnectionString));
con.Open();
string query = "Select employee_id,employee_code,employee_name,address From Employee_Master Where Status = 'ÁA'";
SqlDataAdapter da = new SqlDataAdapter(query, con);
DataSet ds = new DataSet();
da.Fill(ds, "Employee Master");
grid_view_employee_details.DataSource = ds;
grid_view_employee_details.DataBind();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
{
if (con.State == ConnectionState.Open)
con.Close();
con = null;
}
}
}
//Now,Call Gridview bind function in Page-Load as shown

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
fill_employee_details_grid();
}
}
//Below is the Gridview filled with Employee Data


Now in Button save click events,write:-

protected void btn_save_click(object sender, EventArgs e)
{
SqlConnection con = null;

try
{
CheckBox chk_employee_id = new CheckBox();
TextBox txt_employee_code = new TextBox();
TextBox txt_employee_name = new TextBox();
TextBox txt_employee_address = new TextBox();
System.Text.StringBuilder sb_query = new System.Text.StringBuilder();
string query = string.Empty;
int row_affected = 0;
lbl_message.Text = string.Empty;

if (grid_view_employee_details.Rows.Count > 0)
{
foreach (GridViewRow gvr in grid_view_employee_details.Rows)
{
chk_employee_id = gvr.FindControl("chk_employee_id") as CheckBox;
txt_employee_code = gvr.FindControl("txt_employee_code") as TextBox;
txt_employee_name = gvr.FindControl("txt_employee_name") as TextBox;
txt_employee_address = gvr.FindControl("txt_employee_address") as TextBox;

if (chk_employee_id.Checked)
{
//string sql_query1 = @"Insert Into Employee_Master(employee_code,employee_name,address) Values('" + txt_employee_code.Text.Trim() + "' " +
// ",'" + txt_employee_name.Text.Trim() + "','" + txt_employee_address.Text.Trim() + "');";

string sql_query1 = "Insert Into Employee_Master(employee_code,employee_name,address) Values(@employee_code,@employee_name,@employee_address);";

sb_query.AppendLine(sql_query1);

}
}
query = sb_query.ToString();

if (!string.IsNullOrEmpty(query))
{
con = new SqlConnection(Convert.ToString(ConfigurationManager.ConnectionStrings["DataBaseConnection"].ConnectionString));
con.Open();
SqlCommand cmd = new SqlCommand(query, con);

cmd.Parameters.Add("@employee_code", SqlDbType.VarChar, 10);
cmd.Parameters["@employee_code"].Value = txt_employee_code.Text;

cmd.Parameters.Add("@employee_name", SqlDbType.VarChar, 50);
cmd.Parameters["@employee_name"].Value = txt_employee_name.Text;

cmd.Parameters.Add("@employee_address", SqlDbType.VarChar, 1000);
cmd.Parameters["@employee_address"].Value = txt_employee_address.Text;

row_affected = cmd.ExecuteNonQuery();

if (row_affected > 0)
{
lbl_message.Text = "Employee Record Successfully Saved";
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
{
if (con.State == ConnectionState.Open)
con.Close();

con = null;
}
}
}


As we can see,I have appended Semi-colon(;) in each Insert Queries,means in for each loop,whatever Check-boxes are checked,it will read Text-box values and make a Semi-colon separated Insert Statement.and after for each loop,it will save multiple records into DB.

So,as semicolon separated,we can also use Update statement.
and if we wanna delete multiple records,then Fetch Ids append them with Comma separated and then pass in delete statement in IN clause.

Conclusion

In this article we have learned about how to Insert multiple records without hitting Database again and again.

Reference

Write reference, if any

Page copy protected against web site content infringement by Copyscape

About the Author

vishalneeraj-24503
Full Name: vishal kumar
Member Level: Platinum
Member Status: Member,MVP
Member Since: 11/5/2013 5:58:17 AM
Country: India

http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Posted by: Saranpselvam on: 1/9/2014 | Points: 25
I tested this . it is not working . when i insert multiple records i added semicolon at end of each statement. but it hits DB multiple time


Posted by: vishalneeraj-24503 on: 1/10/2014 | Points: 25
Please see my code carefully.
I have only appended Insert statement inside for each loop. But outside of for each loop,i have written db connection stuffs.So it will hit Database only once.
You have done something wrong. Please check again your code.

Login to post response

Comment using Facebook(Author doesn't get notification)