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:
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
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.
HTML Code
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>
<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" />
<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.
