HomeASP.NET Introduction to the ADO.NET Entity Framewo...
Introduction to the ADO.NET Entity Framework using ASP.NET
This is an introductory article focusing on developing ASP.NET applications using the ADO.NET Entity Framework. In this article, we will make a detailed step-by-step examination of how to develop applications using the ADO.NET Entity Framework together with ASP.NET.
This article is based on the following configuration:
Windows Server 2003 with SP2
IIS
SQL Server 2008
Visual Studio 2008 (comes with .NET 3.5) with SP1
To make this article simple, I created a table structure which you can examine by clicking on the link.
The entire source code for this article is available in the form of two free downloadable zip files, which you can download here and here. The solution was developed using Microsoft Visual Studio 2008 Team Edition (with SP1), with Microsoft SQL Server 2008 Developer Edition on Microsoft Windows Server 2003 Standard Edition (with SP2). I didn't really test it in any other environment. I request that you post in the discussion area if you have any problems in execution.
Creating a simple application with ADO.NET Entity Framework
Let us start with a new solution:
Open Start || Programs || Microsoft Visual Studio 2008
Go to File || New Project
In the "New Project" dialog, select "Web" in "Project Types" and select "ASP.NET Web Application" in templates.
Provide "Sample01" as the name and press "OK."
Right click on project and click on Add || New Item
In the "Add New Item" dialog, select "ADO.NET Entity Data Model" as the template, provide "NWModel.edmx" as the name, and finally click on "OK."
Immediately, you will be provided with the "Entity Data Model Wizard." Select "Generate from database" to create a model and click "Next."
Provide database connectivity information and hit "Next."
Select "Emp," "Dept" in the list of tables, provide "NorthwindModel" as the namespace, hit "Finish."
Once the wizard completes, the Entity Designer looks like the following:
To make all the three entity mapping files (.csdl, .msl, .ssdl) copied to "bin" folder, configure as shown below:
Drag a button (btnEmpList) and GridView on to "Default.aspx"
Modify the code behind so that it looks like the following:
Partial Public Class _Default
Inherits System.Web.UI.Page
Protected Sub btnEmpList_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnEmpList.Click
Dim ctxt As New NorthwindEntities
Me.GridView1.DataSource = From oEmp In ctxt.Emp _
Select New With {.ID = oEmp.Empno, .Name = oEmp.Ename, .Salary = oEmp.Sal, .Dept = oEmp.Dept.Dname}
Let us add a new form (CRUD.aspx) to our project so that we can have a demonstration of CRUD operations using ADO.NET Entity Framework, using ASP.NET as client.
In the previous section, navigational properties are fetched using the "Include" method as follows:
Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSearch.Click
Dim ctxt As New NorthwindEntities
Dim oEmp As Emp = (From o In ctxt.Emp.Include("Dept") _
Where o.Empno = Me.txtEmpno.Text _
Select o).FirstOrDefault
If oEmp Is Nothing Then
btnClear_Click(Nothing, Nothing)
Me.lblMsg.Text = "Employee not found."
Exit Sub
End If
Me.txtEname.Text = oEmp.Ename
Me.txtSal.Text = oEmp.Sal
Me.txtDeptno.Text = oEmp.Dept.Deptno
End Sub
When we use the "Include" option, we call it "Eager Loading." The above code can be rewritten as follows:
Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSearch.Click
Dim ctxt As New NorthwindEntities
Dim oEmp As Emp = (From o In ctxt.Emp _
Where o.Empno = Me.txtEmpno.Text _
Select o).FirstOrDefault
If oEmp Is Nothing Then
Me.lblMsg.Text = String.Format("Employee with Empno: {0} not found.", Me.txtEmpno.Text)
btnClear_Click(Nothing, Nothing)
Exit Sub
End If
Me.txtEname.Text = oEmp.Ename
Me.txtSal.Text = oEmp.Sal
If Not oEmp.DeptReference.IsLoaded Then oEmp.DeptReference.Load()
Me.txtDeptno.Text = oEmp.Dept.Deptno
End Sub
In the above code, we are explicitly executing the "Load" method to retrieve the relationship details. This is called "Explicit Loading."
Similarly, the add method can be rewritten as follows:
Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click
Dim ctxt As New NorthwindEntities
Dim oDept As Dept = ctxt.Dept.Where(Function(p) p.Deptno = Me.txtDeptno.Text).FirstOrDefault
If oDept Is Nothing Then
Me.lblMsg.Text = "Invalid Deptno"
Exit Sub
End If
Dim oEmp As New Emp With {.Empno = Me.txtEmpno.Text, .Ename = Me.txtEname.Text, .Sal = Me.txtSal.Text, .Dept = oDept}
ctxt.AddToEmp(oEmp)
ctxt.SaveChanges()
Me.lblMsg.Text = "Added Successfully!"
End Sub
The above is bit different from the previously-provided code. In the above code, there is performance overhead as it retrieves department details from the database (another round trip to the database) even before adding the row to the table.
Another important operation when dealing with database interactions is the transaction. The ADO.NET Entity Framework supports transactions in a very simple manner. The following is the code which executes a transaction:
Protected Sub btnDoTrans_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnDoTrans.Click
Try
Dim ctxt As New NorthwindEntities
Dim oDept As New Dept With {.Deptno = 90, .Dname = "Marketing"}
Dim oEmp1 As New Emp With {.Empno = 9001, .Ename = "Jagadish", .Sal = 7000, .Dept = oDept}
Dim oEmp2 As New Emp With {.Empno = 9002, .Ename = "Pulakhandam", .Sal = 5900, .Dept = oDept}
oDept.Emp.Add(oEmp1)
oDept.Emp.Add(oEmp2)
ctxt.AddToDept(oDept)
ctxt.SaveChanges()
Me.lblMsg.Text = "Saved successfully"
Catch ex As Exception
If ex.Message.ToLower.Contains("innerexception") Then
Me.lblMsg.Text = ex.InnerException.Message
Else
Me.lblMsg.Text = ex.Message
End If
End Try
End Sub
You can also observe the Events in SQL Profiler during transaction execution as follows:
I did not introduce Entity SQL queries (which are totally different from normal SQL) in this article. But, I will at some point in the future. For now, to put it simply, Entity SQL queries are very similar to classic SQL queries, except that these queries work on Entities designed using the Entity Model designer.
For now, let us see few simple Entity SQL queries.
To retrieve all "Emp" objects, we can write the following code:
Dim ctxt As New NorthwindEntities
Dim queryString As String = "SELECT VALUE Emp FROM NorthwindEntities.Emp AS Emp"
Dim query As New ObjectQuery(Of Emp)(queryString, ctxt, MergeOption.NoTracking)
Me.GridView1.DataSource = query
Me.GridView1.DataBind()
We can also add filter conditions as follows:
Dim ctxt As New NorthwindEntities
Dim queryString As String = "SELECT VALUE Emp FROM NorthwindEntities.Emp AS Emp WHERE Emp.Dept.Deptno = 20"
Dim query As New ObjectQuery(Of Emp)(queryString, ctxt, MergeOption.NoTracking)
Me.GridView1.DataSource = query
Me.GridView1.DataBind()
Similarly, a particular value in an object can be retrieved using Entity SQL as follows:
Dim queryString As String = "SELECT Emp.Ename FROM NorthwindEntities.Emp AS Emp WHERE Emp.Empno = 1006"
Dim query As New ObjectQuery(Of String)(queryString, ctxt, MergeOption.NoTracking)
In my upcoming articles, we will see more and more examples of the ADO.NET Entity Framework together with other up-to-date Microsoft technologies. I hope you enjoyed the article and any suggestions, bugs, errors, enhancements etc. are highly appreciated at http://jagchat.spaces.live.com
ASP.NET 
LINQ to XML Programming Using Visual Basic.NET 2008
