Blogger news

Blogger templates

Wednesday, June 29, 2011

CRUD Operation using ADO.Net Entity Framework and LINQ to entities

ADO.Net entity frame work is Microsoft’s new addition to ADO.Net. It allows to create data access applications by programing to conceptual application model (set of classes and entities) instead to programing directly to relational storage schema.

LINQ to Entities

LINQ to Entities provides Language Integrated Query (LINQ) support that enables developers to write queries against Entity Framework conceptual model using Visual Basic or Visual C#. LINQ to Entities converts Language Integrated Queries (LINQ) queries to command-tree queries, executes the queries

LINQ to Entities also supports many other mainstream RDBMS databases such as Oracle, DB2, and MySQL, in addition to Microsoft SQL Server.

Here I will demonstrate a sample CRUD operation (Create, Read, Update and Delete) using entity frame work and LINQ to entities. I will explain in step by step.

1. Open Visual studio 2010 and create a new windows Form application as shown below,


2. Design the form as below,

3. To use LINQ to entities we want to add an Entity data model (EDM).

Add new item -->ADO.Net Entity Data Model and name it TestDB as shown below,

Click add button. Now you will get a window as shown below.

Click next button after selecting “Generate from database”

4. When click next button we will get a window as below,

If want to create a new connection click “New Connection” button and provide required credentials and select the database you want to access.

5. Click the radio button “Yes, include the sensitive data in the connection string” and click next button. Now you will get a window as shown below,

Select the tables you want to access and click finish button.

After click finish two files will be added to the project, “TestDB.edmx” and “TestDB.Designer.cs”. The first file holds the model of entities including entity sets, entity types, conceptual models and mappings.
Second file holds the code for model.

TestDB.Designer.cs is generated LINQ to entity class. When we open that file we can see two classes

a. TestEntities

b. Associate

TestEntities inherit from ObjectContext class which represents the main entry point of LINQ to entity framework.

Associate class are for table we selected. This class inherits EntityObject class which defines all of the related property changing and property changed event methods which we can extend to validate properties before and after the change.

Create Record (Insert)

To insert new record we can write the following code.

private void btnSave_Click(object sender, EventArgs e)

{

using (TestEntities obj = new TestEntities())

{

Associate associate = new Associate

{

FirstName = txtLastName.Text.Trim(),

LastName = txtLastName.Text.Trim(),

Email = txtEmail.Text.Trim()

};

obj.Associates.AddObject(associate);

obj.SaveChanges();

LoadGrid();

}

}

Here we first create object of “TestEntities” class which is inherited from “ObjectContext” class. Next we initialize associate entity with textbox values and add that entity to TestEntities object. SaveChanges method will insert the records to data store.

Read Records

Now we are going to query database using LINQ to entities and will bind records to grid. To query first we need to create object of ObjectContext class or it’s sub class.

In the page load of form give the code below,

private void Form1_Load(object sender, EventArgs e)

{

LoadGrid();

}

private void LoadGrid()

{

using (TestEntities obj = new TestEntities())

{

IEnumerable<Associate> associates = obj.Associates;

dgAssociate.DataSource =

obj.Associates.Select(a => new { a.AssociateID, a.FirstName, a.LastName, a.Email });

}

}

First we created object of TestEntities class which inherit from ObjectContext class. TestEntities class has a property “Associates” which holds the associate details. When page load executed you can see that your grid is binded as shown below.

Update Record

For convenience we are updating a record by hardcode an associate id as shown below.

private void btnUpdate_Click(object sender, EventArgs e)

{

using (TestEntities obj = new TestEntities())

{

Associate associate = obj.Associates.Where(a => a.AssociateID == 9).FirstOrDefault();

associate.FirstName = "Rahul";

associate.LastName = "Khan";

associate.Email = "rahul@abc.com";

obj.SaveChanges();

LoadGrid();

}

}

First we load the row we want to update, here we hardcode it as “9”. Then given the column value we want to update then call “SaveChanges” method of ObjectContext class. Yor can see the record with AssociateId 9 is updated as shown below,

Delete Record

To delete record/records write the method as below,

private void btnDelete_Click(object sender, EventArgs e)

{

using (TestEntities obj = new TestEntities())

{

IEnumerable<Associate> associatesToDelete = obj.Associates.Where(a => a.AssociateID == 9);

if (associatesToDelete.Count() > 0)

{

foreach (Associate associate in associatesToDelete)

{

obj.DeleteObject(associate);

}

obj.SaveChanges();

}

}

LoadGrid();

}

Here also we first queried the record we want to delete and delete each object from that collect and finally called “SaveChanges” method. You can see the record with AssociateId 9 is removed as show below grid,

Here all the sql queries generated by the system. We can see the generated SQL using sql server profiler or using ToTraceString method .

No comments:

Post a Comment