CRUD Operations using ADO.net Entity Framework

INTRODUCTION

I had earlier blogged about LINQ as how it has changed the face of querying the data. Microsoft has added one more jewel in their Data Access Technology , called as ADO .net Entity Framework. ADO.NET Entity Framework abstracts the relational (logical) schema of the data that is stored in a database and presents its conceptual schema to the application. For example, in the database, entries about a customer and their information can be stored in the Customers table, their orders in the Orders table and their contact information in yet another Contacts table. For an application to deal with this database, it has to know which information is in which table, i.e., the relational schema of the data is hardcoded into the application. The disadvantage of this approach is that if this schema is changed the application is not shielded from the change. Also, the application has to perform SQL joins to traverse the relationships of the data elements in order to find related data. For example, to find the orders of a certain customer, the customer needs to be selected from the Customers table, the Customers table needs to be joined with the Orders table, and the joined tables need to be queried for the orders that are linked to the customer. The mapping of the logical schema into the physical schema that defines how the data is structured and stored on the disk is the job of the database system and client side data access mechanisms are shielded from it as the database exposes the data in the way specified by its logical schema.This technology does the same by eliminating the impedance mismatch between data models and programming languages.The logical schema and its mapping with the physical schema is represented as an Entity Data Model (EDM).The Entity data model (EDM) specifies the conceptual model of the data via the Entity-Relationship data model, which deals primarily with Entities and the Relationships they participate in. In addition, the mapping of the elements of the conceptual schema to the logical schema is also needed to be specified. The EDM schema is expressed in the Schema Definition Language (SDL), which is an application of XML. The mapping specification is also expressed in XML. ADO.NET also provides Entity Designer, for visual creation of the EDM and the mapping specification. The output of the tool is the XML file specifying the schema and the mapping. Visual Studio generates this file by the extension of .edmx

Crud Functionality

Whenever a new Data Access Technology is introduced , all developers ask the first question, How can I perform CRUD (Create , Retrieve , Update and Delete) operations with technology. If same question is right now going in your mind, then welcome to this post.I will show you the complete example and working WPF application , source code attached with this post. In this example I have super simple and classical Employee table in my database. To generate the .edmx file based on this table , follow this simple steps

  • Right click your project name in Visual Studio 2008 and select Add / New Item from the context menu
  • From Data Categories in left pane select ADO.net Entity Model as shown in the figure
  • Give the valid name to your file
  • Follow simple wizard steps and viola , you are done.
  • Visual Studio has created new Entity Data Model file for you.
Adding new edmx file in visual studio 2008

Adding new edmx file in visual studio 2008

Please note that you can only add the edmx file in Visual Studio provided you have installed  .net Framework 3.5 SP1 along with Visual Studio 2008 with SP1

Now let us dive into some code as how you can perform these operations

CREATE


Private Sub CreateEmployee(ByVal Emp As Employee)

 Dim key As EntityKey = Nothing
Try
Db.AddObject("Employee", Emp)
Db.SaveChanges() key = Db.CreateEntityKey("Employee", Emp)
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK)
Finally
MessageBox.Show(String.Format("New Employee Created with Employee Id {0}", key.EntityKeyValues(0).Value), "Employee Created", MessageBoxButton.OK)
End Try
End Sub

The above code will create a new employee record in Employee table. It accepts the parameter Emp , which is the Employee entity , based in edmx file. Visual Studio automatically generates this Partial class. Then we declare the EntityKey object , which will be used to get the Employee Id column value , which is an Identity Column in the table. The Db object variable is of Entities Type. The AddObject method add the new entity of Employee type in the logical schema, but still the changes are not committed in the database until the SaveChanges method is not executed. To get the identity column value of the last record inserted we used CreateEntityKey method which retrives the array of Entity keys and with their corresponding values.

RETRIEVE

Dim ObjList
Using db As New EFDemoEntities
  ObjList = (From Emp In db.Employee _
            Select Emp).ToList
End Using
The Retrieve code is self explanatory which is Linq To Entities Queries which select all the columns from the Employee Entity

UPDATE


Private Sub UpdateEmployee(ByVal Emp As Employee)
Dim key As EntityKey
key = Nothing
Dim OrignialEmplyee As Object
OrignialEmplyee = Nothing
Try
key = Db.CreateEntityKey("Employee", Emp)
    If Db.TryGetObjectByKey(key, OrignialEmplyee) Then
       Db.ApplyPropertyChanges(key.EntitySetName, Emp)
      Db.SaveChanges()
   End If

Catch ex As Exception
    MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK)
Finally
    MessageBox.Show("Updated")
End Try
End Sub



In the UpdateEmployee method again we accept the Emp parameter which is of Employee Entity Type.
We try to retrieve the orignal Employee entity object using TryGetObjectByKey method and passes that retrieved object by reference to the OrigninalEmployee variable.
Further then by calling ApplyPropertyChanges method , we overwrite the properties of original object with the updated object’s properties. SaveChanges method then commits these changes to the underlying database.

DELETE



Private Sub DeleteEmployee(ByVal Emp As Employee)
Try
  Db.DeleteObject(Emp)
  Db.SaveChanges()
Catch ex As Exception
    MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK)
Finally
    MessageBox.Show("Deleted")
End Try
End Sub

This method executes the DeleteObject method with Employee entity as its paramater. The SaveChanges method commits those changes to the database.

CONCLUSION

The Entity objects are front end interface to EDM Types which enables Object Oriented Programming to use them and access the data from the database in object fashioned rather than in releational way.This technology holds lots of promise as new Data Services (Astoria) , is also based on Entity Framework.
Download the code from here. Please rename the file extension from .doc to .rar. The zip file also have the Db.rar file in the Database folder. Attach this file to your SQL Server Database and change the connection string in the app.config file to suit to your development needs.
I hope you would have enjoy reading this post. Do let me know your thoughts on it.

Happy coding.
Follow me on twitter

Advertisements

6 thoughts on “CRUD Operations using ADO.net Entity Framework

  1. Hi Utpal,
    Thanks for your kind words. Yes ineed it looks lot of promising to me also.
    A complete paradigm shift from procedural (T-SQL) to object oriented (.Net) data access.

    Kudos to Team at MS.

    Cheers

  2. Yes great post thanks!

    I am using it in a very limited fashion right now. There are some definite problems in the current version of Linq to Entities. The biggest is the inability to do some simple functions, ie. Contains(). Seems they neglected to put that in, but will be released with the next version. I am waiting until that comes out before jumping into Entities any more.

    Linq to Sql is awesome though!!

    Thanks again for the post.

    • Hello Kelly
      Thanks for your appreciation.
      Yes indeed that infamous error “LINQ to Entities does not recognize the method ‘Boolean Contains(System.String)’ method, and this method cannot be translated into a store expression.”
      What impressed me about EF was the ease by which i can do data manipulation with master details tables. I shall be blogging on this topic soon.

      Cheers!

  3. Pingback: Master Details CRUD Operations with ADO.net Entity Framework « Getting Deep into .net

  4. I converted it to c# works perfectly fine. thanks man

    public static void HD_TicketUpdate(DI_Model.Ticket ticket)
    {
    EntityKey key = default(EntityKey);
    key = null;
    object OriginalTicket = null;
    DI_Entities Db = new DI_Entities();

    try
    {
    key = Db.CreateEntityKey(“DI_Entities.Ticket”, ticket);
    if (Db.TryGetObjectByKey(key, out OriginalTicket))
    {
    Db.ApplyPropertyChanges(key.EntitySetName, ticket);
    Db.SaveChanges();

    }
    }
    catch (Exception ex)
    {

    }

    }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s