GET ENTITY FRAMEWORK
Add the Entity Framework NuGet package to your projector
install the Entity Framework Tools for Visual Studio.
INTRODUCTION
ADO.NET is a very strong framework for data access. ADO.NET has been around since many years and there are a lot of systems running over ADO.NET. Developers who are totally oblivious to the concept of ORMs will probably be asking "What is Entity Framework? What are the benefits of using it and is it an alternative to ADO.NET?"
Well, to answer the first question about what is Entity Framework, Entity Framework is an Object Relational Mapper (ORM). It basically generates business objects and entities according to the database tables and provides the mechanism for:
- Performing basic CRUD (Create, Read, Update, Delete) operations.
- Easily managing "1 to 1", "1 to many", and "many to many" relationships.
- Ability to have inheritance relationships between entities.
and to answer the second question, the benefits are:
- We can have all data access logic written in higher level languages.
- The conceptual model can be represented in a better way by using relationships among entities.
- The underlying data store can be replaced without much overhead since all data access logic is present at a higher level.
and finally, the last question that whether it is an alternative to ADO.NET, the answer would be "yes and no". Yes because the developer will not be writing ADO.NET methods and classes for performing data operations and no because this model is actually written on top of ADO.NET, meaning under this framework, we are still using ADO.NET. So let us look at the architecture of Entity Framework (diagram taken from MSDN):
WHY BULK UPLOAD ?
The behind bulk upload, as the name suggest insert all records at once , but in general EF insert records one by one as shown below ==>
<asp:Content runat="server" ID="BodyContent" ContentPlaceHolderID="MainContent">
<h3>BULK INSERT USING EF</h3>
<div style="padding:10px 0px;">
No of records you want to insert:<asp:TextBox ID="txt_No_of_Records" runat="server"></asp:TextBox>
 <asp:Button ID="btn_add" runat="server" Text="ADD" OnClick="btn_add_Click" />
</div>
<p>
<br />
</p>
<asp:GridView ID="gv_customers" runat="server" AutoGenerateColumns="false" CellPadding="0">
<Columns>
<asp:TemplateField HeaderText="Sl No.">
<ItemTemplate>
<%#Container.DataItemIndex+1 %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="FirstName">
<ItemTemplate>
<asp:TextBox ID="txt_firstnm" runat="server"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="LastName">
<ItemTemplate>
<asp:TextBox ID="txt_lastnm" runat="server"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="ContactNo">
<ItemTemplate>
<asp:TextBox ID="txt_cont" runat="server"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<div style="padding:10px 0px;">
<asp:Panel ID="Panel1" runat="server">
<asp:Button ID="btn_Save" runat="server" Text="SAVE" OnClick="btn_Save_Click" />
<asp:Label ID="lblmsg" runat="server" ></asp:Label>
</asp:Panel>
</div>
</asp:Content>
Paste the above code in your UI i.e. web_application aspx page.
Then in the code behind
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace WebApplication1
{
public partial class _Default : Page
{
MyDataContext dcx;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
PopulateData();
}
}
private void PopulateData()
{
using (MyDatabaseEntities dc = new MyDatabaseEntities())
{
var v = dc.USER_DETAILS_TABLE.ToList();
}
}
protected void btn_add_Click(object sender, EventArgs e)
{
AddrowtoGrid();
}
private void AddrowtoGrid()
{
List<int> allRows = new List<int>();
int NoOfRows = 0;
int.TryParse(txt_No_of_Records.Text.Trim(), out NoOfRows);
for(int i=0;i<NoOfRows;i++)
{
allRows.Add(i);
}
gv_customers.DataSource = allRows;
gv_customers.DataBind();
if (gv_customers.Rows.Count > 0)
{ Panel1.Visible = true; }
else
{ Panel1.Visible = false; }
}
protected void btn_Save_Click(object sender, EventArgs e)
{
string startTime = DateTime.Now.ToLongTimeString();
DateTime startT = DateTime.Now;
TextBox txtFirstName = null;
TextBox txtLastName = null;
TextBox txtContactNo = null;
List<USER_DETAILS_TABLE> contact = new List<USER_DETAILS_TABLE>();
foreach (GridViewRow gvr in gv_customers.Rows)
{
txtFirstName = (TextBox)gvr.FindControl("txt_firstnm");
txtLastName = (TextBox)gvr.FindControl("txt_lastnm");
txtContactNo = (TextBox)gvr.FindControl("txt_cont");
// for (int j = 0; j < 10000; j++)
// {
contact.Add(new USER_DETAILS_TABLE { Id = 0, FirstName = txtFirstName.Text.Trim(), LastName = txtLastName.Text.Trim(), ContactNo = txtContactNo.Text.Trim() });
// }
}
using (MyDatabaseEntities dc = new MyDatabaseEntities())
{
string cs = dc.Database.Connection.ConnectionString;
var conn = new SqlConnection(cs);
// dcx = new MyDataContext();
// dcx.BulkInsertAll(contact);
foreach (var i in contact)
{
dc.USER_DETAILS_TABLE.Add(i);
}
dc.SaveChanges();
PopulateData();
AddrowtoGrid();
string endTime = DateTime.Now.ToLongTimeString();
TimeSpan span = DateTime.Now.Subtract(startT);
lblmsg.Text = "Successfully " + contact.Count + " Records inserted. Process Started at: " + startTime + " ,Ended at : " + endTime + " Span: " + span.Hours + " : " + span.Minutes + " : " + span.Seconds+" :: " +span.Milliseconds;
}
}
}
}
results
Successfully 10000 Records inserted. Process Started at: 09:56:30 ,Ended at : 09:57:11 Span: 0 : 0 : 41 :: 253Successfully 50000 Records inserted. Process Started at: 10:06:01 ,Ended at : 10:17:39 Span: 0 : 11 : 38 :: 143Which is very large time takenusing System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; using System.Data; using System.Data.Entity; using System.Data.Entity.Infrastructure; using System.Data.EntityClient; using System.Data.Objects; using System.Data.SqlClient; using System.Linq; using System.Text.RegularExpressions; using System.Web; namespace WebApplication1 { public class MyDataContext { public void BulkInsertAll<T>(IEnumerable<T> entities) { entities = entities.ToArray(); MyDatabaseEntities ctx = new MyDatabaseEntities(); //create your entity object here // EntityConnection ec = (EntityConnection)ctx.Database.Connection; //SqlConnection sc = (SqlConnection)ec.StoreConnection; //get the SQLConnection that your entity object would use string cs = ctx.Database.Connection.ConnectionString; var conn = new SqlConnection(cs); conn.Open(); Type t = typeof(T); TableAttribute tableAttr = t.GetType().GetCustomAttributes(typeof(TableAttribute), true).SingleOrDefault() as TableAttribute; // Get table name (if it has a Table attribute, use that, otherwise get the pluralized name) string tableName = tableAttr != null ? tableAttr.Name : t.GetType().Name; var bulkCopy = new SqlBulkCopy(conn) { DestinationTableName = ctx.GetTableName<USER_DETAILS_TABLE>() }; var properties = t.GetProperties().Where(EventTypeFilter).ToArray(); var table = new DataTable(); foreach (var property in properties) { Type propertyType = property.PropertyType; if (propertyType.IsGenericType && propertyType.GetGenericTypeDefinition() == typeof(Nullable<>)) { propertyType = Nullable.GetUnderlyingType(propertyType); } table.Columns.Add(new DataColumn(property.Name, propertyType)); } foreach (var entity in entities) { table.Rows.Add(properties.Select( property => GetPropertyValue( property.GetValue(entity, null))).ToArray()); } bulkCopy.WriteToServer(table); conn.Close(); } private bool EventTypeFilter(System.Reflection.PropertyInfo p) { var attribute = Attribute.GetCustomAttribute(p, typeof(AssociationAttribute)) as AssociationAttribute; if (attribute == null) return true; if (attribute.IsForeignKey == false) return true; return false; } private object GetPropertyValue(object o) { if (o == null) return DBNull.Value; return o; } } public static class ContextExtensions { public static string GetTableName<T>(this DbContext context) where T : class { ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext; return objectContext.GetTableName<T>(); } public static string GetTableName<T>(this ObjectContext context) where T : class { string sql = context.CreateObjectSet<T>().ToTraceString(); Regex regex = new Regex("FROM (?<table>.*) AS"); Match match = regex.Match(sql); string table = match.Groups["table"].Value; return table; } } }And now change the Button Save codes as belowprotected void btn_Save_Click(object sender, EventArgs e) { string startTime = DateTime.Now.ToLongTimeString(); DateTime startT = DateTime.Now; TextBox txtFirstName = null; TextBox txtLastName = null; TextBox txtContactNo = null; List<USER_DETAILS_TABLE> contact = new List<USER_DETAILS_TABLE>(); foreach (GridViewRow gvr in gv_customers.Rows) { txtFirstName = (TextBox)gvr.FindControl("txt_firstnm"); txtLastName = (TextBox)gvr.FindControl("txt_lastnm"); txtContactNo = (TextBox)gvr.FindControl("txt_cont"); // for (int j = 0; j < 10000; j++) //To loop 10000 time so that 5 records willbecome 50,000 record ie. for testing purpose // { contact.Add(new USER_DETAILS_TABLE { Id = 0, FirstName = txtFirstName.Text.Trim(), LastName = txtLastName.Text.Trim(), ContactNo = txtContactNo.Text.Trim() }); // } } using (MyDatabaseEntities dc = new MyDatabaseEntities()) { string cs = dc.Database.Connection.ConnectionString; var conn = new SqlConnection(cs); dcx = new MyDataContext(); dcx.BulkInsertAll(contact); //foreach (var i in contact) //{ // dc.USER_DETAILS_TABLE.Add(i); //} dc.SaveChanges(); PopulateData(); AddrowtoGrid(); string endTime = DateTime.Now.ToLongTimeString(); TimeSpan span = DateTime.Now.Subtract(startT); lblmsg.Text = "Successfully " + contact.Count + " Records inserted. Process Started at: " + startTime + " ,Ended at : " + endTime + " Span: " + span.Hours + " : " + span.Minutes + " : " + span.Seconds+" :: " +span.Milliseconds; } }Successfully 50000 Records inserted. Process Started at: 09:52:32 ,Ended at : 09:52:34 Span: 0 : 0 : 01 :: 885Successfully 200000 Records inserted. Process Started at: 10:01:15 ,Ended at : 10:01:21 Span: 0 : 0 : 06 :: 411Only 7 secondto insert 200000 records !!! Great
No comments:
Post a Comment