How to insert bulk data to a SQL Server database using Entity Framework in ASP.NET.

Introduction

In this post, I explain how to insert bulk data (multiple rows) to an SQL Server database using Entity Framework in ASP.NET.
One of the common problems I have seen is to bulk data insert to an SQL Server database at a time using ASP.Net Application. Here in this post, I have done this easily following these simple steps.

Here I have created a page containing a Gridview with no of blank rows(on demand) for taking inputs in application end and used entity framework code for save bulk data at a time in SQL Server end. Please follow steps for complete tutorial...

Previous Post: How to insert bulk data into SQL Server Database without Entity Framework.

Steps :

Step - 1: Create New Project.

Go to File > New > Project > Select asp.net web forms application > Entry Application Name > Click OK.

Step-2: Add a Database.

Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.

Step-3: Create a table for Save Records.

Open Database > Right Click on Table > Add New Table > Add Columns > Save > Enter table name > Ok.
In this example, I have used table as below

Step-4: Add Entity Data Model.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select ADO.net Entity Data Model under data > Enter model name > Add.
A popup window will come (Entity Data Model Wizard) > Select Generate from database > Next >
Chose your data connection > select your database > next > Select tables > enter Model Namespace > Finish.

Step-5: Add a Webpage and Design for Save/Show Multiple Records to/from SQL Server Database.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select web form/ web form using master page under Web > Enter page name > Add.

HTML Code
  
   <h3>Insert Multiple Record in a SQL Database using ASP.Net application.</h3>
    <div style="padding:10px 0px">
        No of record you want to insert : 
        <asp:TextBox ID="txtNoOfRecord" runat="server"></asp:TextBox>
        &nbsp;
        <asp:Button ID="btnAddRow" runat="server" Text="Add Rows" OnClick="btnAddRow_Click" />

    </div>
    <asp:GridView ID="gvContacts" runat="server" AutoGenerateColumns="false" CellPadding="5">
        <Columns>
            <asp:TemplateField HeaderText="SL No.">
                <ItemTemplate>
                    <%#Container.DataItemIndex +1 %>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="First Name">
                <ItemTemplate>
                    <asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Last Name">
                <ItemTemplate>
                    <asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Contact No">
                <ItemTemplate>
                    <asp:TextBox ID="txtContactNo" runat="server"></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
    <div style="padding:10px 0px;">
        <asp:Panel ID="Panel1" runat="server" Visible="false">
            <asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" />
            &nbsp;<asp:Label ID="lblMsg" runat="server" ></asp:Label>
        </asp:Panel>
    </div>
    <div>
        <b>Database Records</b>
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" CellPadding="5">
                <Columns>
                    <asp:TemplateField HeaderText="SL No.">
                        <ItemTemplate>
                            <%#Eval("ID") %>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="First Name">
                        <ItemTemplate>
                            <%#Eval("FirstName") %>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Last Name">
                        <ItemTemplate>
                           <%#Eval("LastName") %>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Contact No">
                        <ItemTemplate>
                            <%#Eval("ContactNo") %>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
        </div>
    </div>  

Step-6: Write code in page_load event for fetch and show existing data from database.


            
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    PopulateData();
                }
            }
        
and here is the function
            
        private void PopulateData()
        {
            using (MyDatabaseEntities dc = new MyDatabaseEntities())
            {
                var v = dc.Contacts.ToList();
                GridView1.DataSource = v;
                GridView1.DataBind();
            }
        }
        

Step-7: Write code in button (btnAddRow) click event for add blank row in gridview for take inputs.


            
        protected void btnAddRow_Click(object sender, EventArgs e)
        {
            AddRowsToGrid();
        }          
and here is the function
            
        private void AddRowsToGrid()
        {
            List<int> noofRows = new List<int>();
            int rows = 0;
            int.TryParse(txtNoOfRecord.Text.Trim(), out rows);

            for (int i = 0; i < rows; i++)
            {
                noofRows.Add(i);
            }

            gvContacts.DataSource = noofRows;
            gvContacts.DataBind();
            if (gvContacts.Rows.Count > 0)
            {
                Panel1.Visible = true;
            }
            else
            {
                Panel1.Visible = false;
            }
        }

Step-8: Write code in button (btnSave) click event for Save multiple records to SQL Server database.


            
        protected void btnSave_Click(object sender, EventArgs e)
        {
            // Save here

            // Check Velidity
            TextBox txtFirstName = null;
            TextBox txtLastName = null;
            TextBox txtContactNo = null;
            List allContacts = new List();

            foreach (GridViewRow row in gvContacts.Rows)
            {
                txtFirstName = (TextBox)row.FindControl("txtFirstName");
                txtLastName = (TextBox)row.FindControl("txtLastName");
                txtContactNo = (TextBox)row.FindControl("txtContactNo");

                if (txtFirstName == null || txtLastName == null || txtContactNo == null)
                {
                    return;
                }
                if (String.IsNullOrEmpty(txtFirstName.Text.Trim()) ||
                    String.IsNullOrEmpty(txtLastName.Text.Trim()) ||
                    String.IsNullOrEmpty(txtContactNo.Text.Trim()))
                {
                    lblMsg.Text = "All fields are required!";
                    return;
                }
                else
                {
                    allContacts.Add(new Contact { ID = 0, FirstName = txtFirstName.Text.Trim(), LastName = txtLastName.Text.Trim(),
                     ContactNo = txtContactNo.Text.Trim()});
                }
            }

            using (MyDatabaseEntities dc = new MyDatabaseEntities())
            {
                foreach (var i in allContacts)
                {
                    dc.Contacts.Add(i);
                }
                dc.SaveChanges();
                PopulateData();
                AddRowsToGrid();
                lblMsg.Text = "Successfully " + allContacts.Count +" record inserted!";
            }
        }

Step-9: Run Application.



Related Post : 



Hello ! My name is Sourav Mondal. I am a software developer working in Microsoft .NET technologies since 2010.

I like to share my working experience, research and knowledge through my site.

I love developing applications in Microsoft Technologies including Asp.Net webforms, mvc, winforms, c#.net, sql server, entity framework, Ajax, Jquery, web api, web service and more.