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. This abstraction eliminates the
object-relational impedance mismatch that is otherwise common in conventional
database-oriented programs. For example, in a conventional database-oriented
system, entries about a customer and their information can be stored in a
Customers table, their orders in an Orders table and their contact information
in yet another Contacts table. The application that deals with this database
must "know" which information is in which table, i.e., the relational
schema of the data is hard-coded 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. This
model of traversing relationships between items is very different from the
model used in object-oriented programming languages, where the relationships of
an object's features are exposed as Properties of the object and accessing the
property traverses the relationship. Also, using SQL queries expressed as
strings, only to have it processed by the database, keeps the programming
language from making any guarantees about the operation and from providing
compile time type information. The mapping of 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.
History: version 1
of Entity Framework (EFv1) was included with .NET Framework 3.5 Service Pack 1
and Visual Studio 2008 Service Pack 1, released on 11 August 2008. This version
has been widely criticized, even attracting a 'vote of no confidence' signed by
several hundred developers. The second version of Entity Framework, named
Entity Framework 4.0 (EFv4), was released as part of .NET 4.0 on 12 April 2010
and has addressed many of the criticisms made of version 1. A third version of
Entity Framework, yet unnamed, is planned for release in first quarter of 2011.
There have been several betas (called Community Technology Preview) of it and
the last CTP5 was released in December 2010.
Entity
Data Model: the Entity data model (EDM) specifies the conceptual model
(CSDL) of the data via the Entity-Relationship data model, which deals
primarily with Entities and the Associations they participate in. The EDM
schema is expressed in the Schema Definition Language (SDL), which is an
application of XML. In addition, the mapping (MSL) of the elements of the
conceptual schema (CSDL) to the storage schema (SSDL) must also be specified.
The mapping specification is also expressed in XML. Visual Studio also provides
Entity Designer, for visual creation of the EDM and the mapping specification.
The output of the tool is the XML file (*.edmx) specifying the schema and the
mapping. Edmx file contains EF metadata artifacts (CSDL/MSL/SSDL content).
These 3 files (csdl, msl, ssdl) can also be created or edited by hand.
Open a new project of type windows name it as EDM, open the add new
item window, select “ADO.NET Entity Data Model”, name it as “Sample.edmx”, Click
Add button, which opens a wizard select
“Generate from database”, click Next button, Choose Microsoft SQL Server
database and click on “Ok” button, provide the connection details, choose your
“CSharpDB” database, click on “Ok”
button, select “Yes, include the sensitive data in the connection string”, click
on “Next” button, expand Tables node, choose the tables Emp & Dept, click
Finish which displays the tables that are selected on a new window.
Note:
Once the tables are selected and clicked on the finish button internally all
the required classes representing the database, selected tables as well as
properties representing all the columns of tables selected as well as methods
which are required to perform the database operations gets generated. You can
check this by expanding the .edmx file in solution explorer and view it in the
Designer.cs file.
Place a
DataGridView control on the form and write the following code in its form load
event:
CSharpDBEntities
db = new CSharpDBEntities ();
dataGridView1.DataSource
= db.Emps.Select("it.Empno, it.Ename, it.Job, it.Sal, it.Deptno");
Or
dataGridView1.DataSource
=
db.Emps.Select("it.Empno,
it.Ename, it.Job, it.Sal, it.Deptno" ).Where("it.Deptno=30");
or
dataGridView1.DataSource
=
db.Emps.Select("it.Empno,
it.Ename, it.Job, it.Sal, it.Deptno").OrderBy("it.Deptno");
Note:
CSharpDBEntities is the class which is generated that represents your database
CSharp.
Performing Select & DML Operations:
Create 2 forms as following
In the
second Form change the modifier as Internal for all the 5 TextBox’s and Insert
button also so that they can be accessed from first form & in the first
form change the DataGridView name as dgView.
Code under First
Form
Declarations: CSharpDBEntities
db;
Under Form Load: db = new
CSharpDBEntities();
dgView.DataSource
= db.Emps.Select("it.Empno, it.Ename, it.Job, it.Sal, it.Deptno");
Under Insert Button: Form3 f =
new Form3(); f.btnSave.Text = "Insert"; f.ShowDialog();
dgView.DataSource
= db.Emps.Select("it.Empno, it.Ename, it.Job, it.Sal, it.Deptno");
Under Update Button: if
(dgView.SelectedRows.Count > 0) {
Form3 f = new Form3();
f.textBox1.Text = dgView.SelectedRows[0].Cells[0].Value.ToString();
f.textBox2.Text =
dgView.SelectedRows[0].Cells[1].Value.ToString();
f.textBox3.Text =
dgView.SelectedRows[0].Cells[2].Value.ToString();
f.textBox4.Text =
dgView.SelectedRows[0].Cells[3].Value.ToString();
f.textBox5.Text =
dgView.SelectedRows[0].Cells[4].Value.ToString();
f.btnSave.Text = "Update";
f.ShowDialog();
dgView.DataSource =
db.Emps.Select("it.Empno, it.Ename, it.Job, it.Sal, it.Deptno");
}
else
MessageBox.Show("Select a record from
GridView to update", "Warning",
MessageBoxButtons.OK,
MessageBoxIcon.Warning);
Under Delete Button: if
(dgView.SelectedRows.Count > 0) {
if (MessageBox.Show("Do you wish to
delete the record?", "Confirmation", MessageBoxButtons.YesNo,
MessageBoxIcon.Question) ==
DialogResult.Yes) {
int empno =
Convert.ToInt32(dgView.SelectedRows[0].Cells[0].Value);
Emp obj = db.Emps.SingleOrDefault(E =>
E.Empno == empno); db.DeleteObject(obj);
db.SaveChanges(); dgView.DataSource=db.Emps.Select("it.Empno,
it.Ename, it.Job, it.Sal, it.Deptno");
}
}
else
MessageBox.Show("Select a record from
GridView to delete", "Warning", MessageBoxButtons.OK,
MessageBoxIcon.Warning);
Code under Second
Form
Under Insert Button: CSharpDBEntities
db = new CSharpDBEntities();
if
(btnSave.Text == "Insert") {
Emp obj = new Emp(); obj.Empno =
int.Parse(textBox1.Text); obj.Ename = textBox2.Text;
obj.Job = textBox3.Text; obj.Sal =
decimal.Parse(textBox4.Text); obj.Deptno = int.Parse(textBox5.Text);
db.Emps.AddObject(obj); db.SaveChanges();
}
else {
int empno = int.Parse(textBox1.Text); Emp obj
= db.Emps.SingleOrDefault(E => E.Empno == empno);
obj.Ename = textBox2.Text; obj.Job =
textBox3.Text; obj.Sal = decimal.Parse(textBox4.Text);
obj.Deptno = int.Parse(textBox5.Text);
db.SaveChanges(); }
Calling Stored Procedures: we
can call stored procedures also using EDM, to call it first define a SP in sql
server database as following:
Create Procedure Select_Emp(@Deptno Int =
Null)
As
Begin
If
@Deptno Is Null
Select Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno From Emp
Else
Select Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno From Emp
Where Deptno = @Deptno
End
Invoking the above Procedure:
To invoke the above SP go to Sample.edmx file and in
the RHS we find a window “Model Browser”,
open it, right click on it and select “Update Model from Database” which opens
a “Update Wizard”, in it expand the node “Stored Procedures”, select the SP
“Select_Emp” we have defined above & click Finish, which adds the SP under
Stored Procedures node of Model Browser, right click on the SP “Select_Emp”,
select “Add Function Import” which opens a window, choose the Radio Button
“Entities”, select “Emp” from the Combo Box and click on “Ok” button.
Note: once you click
on the ok button it generates a new method “Select_Emp” under the class
“CSharpDBEntities”, check the Designer.cs file for it.
Create a new Form as following, change the name of DataGridView as
dgView and write the code:
Declarations:
CSharpDBEntities db; bool flag = false;
Under Form Load:
db = new CSharpDBEntities(); comboBox1.DataSource =
db.Emps.Select("it.Deptno").Distinct();
comboBox1.DisplayMember =
"Deptno"; dgView.DataSource = db.Select_Emp(null); flag = true;
Under ComboBox
SelectedIndexChanged: if (flag) {
int deptno =
int.Parse(comboBox1.Text); dgView.DataSource = db.Select_Emp(deptno);
}
Retrieving data from single
or multiple tables: we can retrieve data from 1 or more table(s) also
at a time using “Linq to Entities” query language which will be same as we have
used in case of querying data in Linq, with some minor changes.
Take a new windows form and design it as following and write the code:
Declarations:
CSharpDBEntities db;
Under Form Load:
db = new CSharpDBEntities();
comboBox1.DataSource = db.Emps.Select("it.Job").Distinct(); comboBox1.DisplayMember
= "Job";
dataGridView1.DataSource = from E in db.Emps
select new { E.Empno, E.Ename, E.Job, E.Mgr, E.HireDate, E.Sal, E.Comm,
E.Deptno };
Under ComboBox
SelectedIndexChanged:
dataGridView1.DataSource = from E in db.Emps
where E.Job == comboBox1.Text select new { E.Empno, E.Ename, E.Job, E.Mgr,
E.HireDate, E.Sal, E.Comm, E.Deptno };
Under Group By Clause
Button1:
dataGridView1.DataSource = from E in db.Emps group E by E.Deptno into
G select new { Deptno = G.Key, EmpCount = G.Count() };
Under Group By Clause Button2:
dataGridView1.DataSource
= from E in db.Emps group E by E.Deptno into G select new { Deptno = G.Key,
MaxSal = G.Max(i => i.Sal) };
Under
Group By Clause Button3:
dataGridView1.DataSource = from E in db.Emps
group E by E.Job into G select new { Job = G.Key, JobCount = G.Count() };
Under Group By Clause
Button4:
dataGridView1.DataSource = from E in db.Emps
group E by E.Job into G select new { Job = G.Key, MinSal = G.Min(i => i.Sal)
};
Under Order By Clause Button:
dataGridView1.DataSource = from E in db.Emps
orderby E.Sal select new { E.Empno, E.Ename, E.Job, E.Mgr, E.HireDate, E.Sal,
E.Comm, E.Deptno };
Under Multiple Tables Button:
dataGridView1.DataSource = from E in db.Emps
select new { E.Empno, E.Ename, E.Job, E.Mgr, E.HireDate, E.Sal, E.Comm,
E.Dept.Deptno, E.Dept.DName, E.Dept.Loc };
LINQ Vs EDM:
-
LINQ to SQL was developed for rapid application
development (RAD), whereas Entity Framework was developed for enterprise
application development.
-
LINQ to SQL works with the objects in a database
whereas Entity Framework works with the conceptual model of a database. As
explained earlier, these are two different things which further mean that the
Entity Framework allows you to perform queries against relationships between
entities, mapping single entities to multiple tables, and so on.
-
LINQ to SQL has the ability to call Stored
Procedures, but not map them with the results where as Entity Framework is all
about mapping results to entities.
No comments:
Post a Comment