Insert 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.

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);

Objective

Using the code

<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>

    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;
            }
        }   
    }

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
       fill_employee_details_grid();
    }
}

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;
            }
        }
}