Work With Entity Data Model
1. What is Entity Framework ? The
Architecture of EF.
2. Sample Implementations(CRUD) of EDM in VS 2010.[Only With SP in Sql
Server 2k8]
Code Snippet for Fill Grid &
Dropdown List.
S.N Sample Code Snippets are in Italic Style.
1.What is Entity Framework ? The Architecture of EF.
The most simplest way to describe Entity Frame Work is , it’s an ORM
Tool.
An ORM Tool is one which is storing the data from Domain Objects to
Relational Database objects.It can be consisting of Three sub-parts , Domain
Class Objects , Relational DB objects & Mapping Information that lies
between Domain Class Object & Relational DB Objects.
That is , we build a Conceptual Schema Layer that consisting DN entities
like Tables , Views 7 SP’s as per our requirement.
LINQ almost do the same thing but EF has better advantages than LINQ ,
such as ,
A..It supports Oracle & other DB tools apart from Sql Server.
B. It maintain Reference among the Enitites based on Navigation
Properties and having enough intelligence to automatically adding mapping Or
rereferencing information among the tables in Relational database.
From a layman point of view, it’s an enhancement over the traditional
ADO.NET way to focus the Developer much more on Business logic rather than Db
transactional code snippets.
Ref: MSDN
As the Above Framework Defined it clearly shown that EF actually wrapping
the ADO.Net mechanism.
2. Sample Implementations(CRUD) of
EDM in VS 2010.[With SP in Sql Server 2k8]
We will use EDM in VS 2010 with Sql 2k8 for CRUD (Create, Read, Update, Delete)
operations where we are simply skipped any Code level Query by using EntitySql Or LinqToEntity , rather ,
we are perform all our business logic in SP & consume thoses SP’s in code
by using EDM.
Initially , we must create the EDM as steps mentioned below:
a.
Click
at “Add new Item” in your VS Project within Solution Explorer.
b.
Add
an ADO.Net Entity Data Model & rename it as SampleModel.edmx.
c.
Choose
“Generate From Database” option.
d.
Provide
the Database details , connection properties & a name for Entity Connection
Setting. In our Sample Below it’s defined as “CheckDBEntities”
e.
Select
Database objects as Tables, Views, SP’s.
f.
Put
Model Namespace.
Once EDM has been build ,
The Entities can be associated with each others as per defined relationship for
getting Navigation Properties.
Now Your EDM has been
ready to consume.
READ Data:
We can get Records from Db by using SP with the help of EDM in any one of
Two Ways as below:
i.By Mapping Function
With SP in EDM
ii. By Using “ExecuteStoreQuery”.
There are two Tables “tblUser “ & “tblFacility” as below in DB
end.
|
UserID
|
Name
|
Contact
|
FacilityID
|
|
PBS
|
Sumanta
|
992233554
|
F-01
|
|
FacilityID
|
FacilityName
|
|
F-01
|
Noida
|
Now , there are Two SP’s in DB to retrieve different set of data.
create proc getuser
as
begin
select * from tbluser
end
AND
Create proc getdata
@userid varchar(50)
As
Begin
Select t1.userid,t1.name,t2.facilityname from tbluser t1 left join
tblfacility t2 on t1.facilityid = t2.facilityid where t1.userid = @userid
End
Now , we add a different class “BL.cs” as Business Object that directly handles EDM rather in
code behind page.
i.By
Mapping Function With SP in EDM
To call SP’s by using function ,
we have to import a
Function -> specify Function Name -> Specify SP from List ->
Specify Return Types.
A return Type can discussed later in below , please find the details.
Once Function is created & Mapped with SP , then we call the Function
from our code that actually internally invoke SP Entity & get the records.
“FuncGetUser”
is name of the function that call “getuser” sp internally.
In Code Behind:
BL obj =
new BL();
IList<tbluser> lstUsers = obj.GetUser();
Gridview1.DataSource = lstUsers;
Gridview1.DataBind();
In Business Object
public
CheckDBEntities myentity = new CheckDBEntities();
public IList<tbluser> lstUsers;
public IList<FuncGetData_Result> lstMixedType;
public
IList<tbluser> GetUser()
{
lstUsers = myentity.FuncGetUser().ToList<tbluser>();
return lstUsers;
}
Here , the “tbluser” is Type of Entity , that
is actually returned by SP.
**IMP:
here “tbluser” is a Entity Type that is returned by Sp , but in case of
“getdata” SP , it returns mixed entity
types from “tbluser” & “tblfacility” i.e the Column Information’s belong to both “tbluser”
& “tblfacility” rather from any Particular ENTITY Type. Therefore we
have to create A Complex Type while Creating & Mapping Function with
respective SP Through Wizard:.
Complex Type:
To Create A Complex Type we must
Declare the Return Type of SP as
“Complex Type” , then we Click @ “Get Column Information” that actually retrieve
every column information’s that are returned by SP AND create a Virtual Entity
Type that doesn’t has any Physical Existence in DB Storage.
Here
in our sample application The newly created Complex Type must has a name
as “FuncGetData_Result”.
In Code Behind:
BL obj = new BL();
IList<FuncGetData_Result> lstMixedType
= obj.GetComplexType(101);
Gridview1.DataSource = lstMixedType;
Gridview1.DataBind();
In Business Object:
public IList<FuncGetData_Result>
GetComplexType(int UserID)
{
lstMixedType =
myentity.FuncGetData(UserID).ToList<FuncGetData_Result>();
return lstMixedType;
}
ii. By Using “StoreQuery”.
In this way
, we are not going to Create Or Import any Function , rather we will use “ExecuteStoreQuery” to retrieve
record sets from DB.
In Code
Behind:
BL obj =
new BL();
IList<tbluser> DT =
obj.GetUserByStoreQuery();
Gridview1.DataSource = DT;
Gridview1.DataBind();
In
Business Object:
public IList<tbluser> GetUserByStoreQuery()
{
IList<tbluser> dt =
myentity.ExecuteStoreQuery<tbluser>("getuser").ToList();
return dt;
}
In
The Same We Can Use ExecuteStoreQuery For Complex Type:
In Code Behind:
ietsParameterID.Value = “pbs”;
IList< FuncGetData_Result > DT =
myEntity.ExecuteStoreQuery<FuncGetData_Result>("getdata
@userid", ietsParameterID).ToList();
Gridview1.DataSource = DT;
Gridview1.DataBind();
Hence
, IMP is we are using “FuncGetData_Result” Complex Type that has been created when
We creating a new Function & mapped with a SP.
But
,What if we are not going to use Mapping
approach and we directly call SP by “ExecuteStoreQuery” ??
Then we must Define The Complex Type manually and add it’s
properties based on the SP for which it’s Created. , i.e We must Provide the
Column Information’s to Newly added Complex Type based on Return Column Types
of SP.
To Create Complex Type manually ,
1.Add a Complex Type in EDM Model.
2.Add Scaler Properties in this
Complex Type.
Let Suppose , SP “getdata” returns 3 columns - userid (string) named as userid ,
name(string) as name And Facilityid(int) as facilityid.
Then we must Add 3 properties in
Newly Created Complex Type , as
userid(string),name(string) and facilityid(int).
Now the Schema returned from SP
completely matched with Complex Type schema & we can use it in Code to
Obtain the Record Set of A Complex Type.
Lets Assumes The Name of Newly
Created Complex Type is “ComplexType1”.
In Code Behind :
ietsParameterID.Value = 101;
var DT =
myEntity.ExecuteStoreQuery<ComplexType1>("getdata @userid",
ietsParameterID).ToList();
Gridview1.DataSource =
DT;
Gridview1.DataBind();
Create/Update/Delete
Data :
As much Like READ Operation we can
do Insert/Update/Delete using one of two following ways:
a.Using
Mapping of Function With SP
b.
Using “ExecuteStoreCommand”
To Perform Insert / Update / Delete operation we have
following SP’s:
Create
Proc InsertData
@faciltiyid
int,
@facilityname
varchar(50)
As
Begin
Insert into facility
values(@facilityid,@facilityname)-
If @@error = 0
Begin
Commit
transaction
Select 1
as ‘result’
End
Else
Begin
Rollback transaction
Select 0 as ‘Result’
End
End
Create
Proc UpdateData
@facilityid
int,
@facilityname
varchar(50)
As
Begin
Update facility
Set facilityname = @facilityname
Where facilityid = @facilityid
End
Create
proc DeleteData
Facilityid
int
As
Begin
Delete from facility where
facilityid = @facilityid
End
a.Using
Mapping of Function With SP
public CheckDBEntities myEntity = new CheckDBEntities();
Create a Function “Func_insertdata”
that internally Mapped with SP “InsertData”.
int? result = myEntity. Func_insertdata
(102, “Colombo").FirstOrDefault();
Create a Function “Func_updateData”
that internally Mapped with SP “UpdateData”.
myEntity.
Func_ updateData (102, “Manila");
Create a Function “Func_deleteData”
that internally Mapped with SP “DeleteData”.
myEntity.
Func_ deleteData (10);
b.
Using “ExecuteStoreCommand”
For
Insertion of New Record in Facility Table,
var ietsParameterID = new
SqlParameter("@faciltiyid”, System.Data.SqlDbType.int);
ietsParameterID.Value =”102”;
var ietsParametername = new SqlParameter("@facilityname",”Colombo”);
int?result=
myEntity.ExecuteStoreCommand("InsertData
@ faciltiyid,@ facilityname", ietsParameterID, ietsParametername);
For
Updation of Existing Record in Facility Table,
var ietsParameterID = new SqlParameter("@faciltiyid”,
System.Data.SqlDbType.int);
ietsParameterID.Value =”102”;
var ietsParametername = new
SqlParameter("@facilityname",”Manila”);
myEntity.ExecuteStoreCommand("UpdateData @ faciltiyid,@ facilityname", ietsParameterID, ietsParametername);
For
Deletion of Existing Record in Facility Table,
var ietsParameterID = new SqlParameter("@faciltiyid”,
System.Data.SqlDbType.int);
ietsParameterID.Value =”102”;
myEntity.ExecuteStoreCommand("DeleteData
@ faciltiyid ", ietsParameterID);
4.Bind A DropdownList with Facility
Details
Let Suppose , there is a
DropdownList in ASPX page namely , ddlFacility.
We
can fill this Dropdown by using below code snippet.
public void bindDDL()
{
var display = from e in myEntity.Facility
select new { e.Facilityid };
ddlFacility.DataSource = display.ToList();
ddlFacility.DataTextField =
"FacilityName";
ddlFacility.DataValueField =
"FaciliyID";
ddlFacility.DataBind();
}
Reference:
