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.

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