Entity Framework (.edmx) is one of the most popular frameworks today to querying database and there are many reasons for this. It has many qualities that make it suitable for development for users at most all levels. Let’s discuss how efficiently connect the SQL Server Database in a C# .NET Application using Entity Framework.
An .edmx file is an XML file that defines a conceptual model, a storage model, and the mapping between these models. An .edmx file also contains information that is used by the ADO.NET Entity Data Model Designer (Entity Designer) to render a model graphically. Entity Framework is perhaps the most important Database Connectivity Framework used in the workplace today.
Let us discuss how we have to create EDMX in Visual Studio using Entity Data Model Wizard. Before proceeding to the concept, first create a prerequisite tables and Stored Procedure to perform our task.
Create Table: Person
SQL Server Syntax:
|
1 2 3 4 5 6 7 |
CREATE TABLE [dbo].[Person]( [id] [int] IDENTITY(1,1) NOT NULL, [firstName] [varchar](50) NOT NULL, [lastName] [varchar](50) NOT NULL, [dob] [date] NOT NULL, [gender] [bit] NOT NULL, ) |
Insert Data:
Insert the data into the Person table.
|
1 2 3 4 5 6 7 8 9 10 11 |
INSERT INTO dbo.Person([firstName], [lastName], [dob], [gender]) VALUES ('Bala', 'Manigandan', '1985-12-06', 1) GO INSERT INTO dbo.Person([firstName], [lastName], [dob], [gender]) VALUES ('Prayag', 'Pavithran', '1985-09-15', 1) GO INSERT INTO dbo.Person([firstName], [lastName], [dob], [gender]) VALUES ('Sakthivel', 'G', '1986-12-11', 1) GO |
Select Data:
Select the data from the Person table.
|
1 |
SELECT * FROM dbo.Person |
| Id | firstName | lastName | dob | gender |
| 1 | Bala | Manigandan | 1993-07-03 | 1 |
| 2 | Mohan | Raj | 1975-05-01 | 1 |
| 3 | Raj | Kumar | 1980-10-10 | 1 |
Create Table: Person_Address
SQL Server Syntax:
|
1 2 3 4 5 6 7 |
CREATE TABLE [dbo].[Person_Address]( [id] [int] IDENTITY(1,1) NOT NULL, [pid] [int] NOT NULL, [address] [varchar](50) NOT NULL, [city] [varchar](50) NOT NULL, [ispreferred] [bit] NOT NULL ) |
Insert Data:
Insert the data into the Person_Address table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
INSERT INTO dbo.Person_Address([pid], [address], [city], [ispreferred]) VALUES ('1', '15, Car Street', 'Mumbai', 1) GO INSERT INTO dbo.Person_Address([pid], [address], [city], [ispreferred]) VALUES ('1', '24, Paramount Street', 'Delhi', 0) GO INSERT INTO dbo.Person_Address([pid], [address], [city], [ispreferred]) VALUES ('1', '6, Gandhi Road', 'Delhi', 0) GO INSERT INTO dbo.Person_Address([pid], [address], [city], [ispreferred]) VALUES ('2', '24, Nehru Road', 'Delhi', 1) GO INSERT INTO dbo.Person_Address([pid], [address], [city], [ispreferred]) VALUES ('2', '6, Gandhi Road', 'Delhi', 0) GO INSERT INTO dbo.Person_Address([pid], [address], [city], [ispreferred]) VALUES ('3', '6, Gandhi Road', 'Delhi', 1) GO |
Select Data:
Select the data from the Person_Address table.
|
1 |
SELECT * FROM dbo.Person_Address |
| Id | pid | address | City | Ispreferred |
| 1 | 1 | 15, Car Street | Mumbai | 1 |
| 2 | 1 | 24, Paramount Street | Delhi | 0 |
| 3 | 1 | 6, Gandhi Road | Delhi | 0 |
| 4 | 2 | 24, Nehru Road | Delhi | 1 |
| 5 | 2 | 6, Gandhi Road | Delhi | 0 |
| 6 | 3 | 6, Gandhi Road | Delhi | 1 |
Now, we write logic to fetch a Person Information along with Addresses associated with that particular person using Stored Procedure.
Create Stored Procedure: GetPersonInfo
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
CREATE PROCEDURE GetPersonInfo @PersonID int AS BEGIN SET NOCOUNT ON; DECLARE @PersonInfo TABLE( [Mode] [char], [id] [int], [firstName] [varchar](50), [lastName] [varchar](50), [dob] [date], [gender] [bit], [aid] [int], [pid] [int], [address] [varchar](50), [city] [varchar](50), [ispreferred] [bit] ); INSERT INTO @PersonInfo ([Mode], [id], [firstName], [lastName], [dob], [gender]) SELECT 'P', p.* FROM Person p WHERE p.id = @PersonID; INSERT INTO @PersonInfo ([Mode], [aid], [pid], [address], [city], [ispreferred]) SELECT 'A', pa.* FROM Person_Address pa WHERE pa.pid = @PersonID; SELECT pi.* FROM @PersonInfo pi; END GO |
Execute Stored Procedure:
Execute the Stored Procedure GetPersonInfo.
|
1 |
EXEC [dbo].[GetPersonInfo] @PersonID = 1 |
| Mode | Id | Firstname | lastName | Dob | Gender | aid | pid | address | City | Ispreferred |
| P | 1 | Bala | Manigandan | 1993-07-03 | 1 | NULL | NULL | NULL | NULL | NULL |
| A | NULL | NULL | NULL | NULL | NULL | 1 | 1 | 15, Car Street | Mumbai | 1 |
| A | NULL | NULL | NULL | NULL | NULL | 2 | 1 | 24, Paramount Street | Delhi | 0 |
| A | NULL | NULL | NULL | NULL | NULL | 3 | 1 | 6, Gandhi Road | Delhi | 0 |
Now, we are having two tables namely Peson and Person_Address and a Stored Procedure namely GetPersonInfo. Let us see how to connect this database tables and strored procdures with C# .NET application using Entity Framework.
Step 1:
Right Click into the Project File -> Add -> New Items
Step 2:
Select ADO.NET Entity Data Model under Visual C# Items -> Data. Give the Name as PersonModel
Step 3:
Choose Model Contents as EF Designer from database
Step 4:
Choose your Data Connection by clicking New Connection Button and then give the name as BalaEntities
Step 5:
Choose the latest Version (Here I’m choosing Entity Framework 6.x)
Step 6:
Choose the required Database Objects i.e., Tables, Views, Stored Procedures and Functions.
Step 7:
Here I’m Selecting the Table Person and Person_Address as well as Stored Proc finally click Finish
Step 8:
EDMX Files Created namely PersonModel.edmx
Step 9:
Navigate to Model Brower to check the PersonModel.edmx
Step 10:
Right Click into the Stored Procedure GetPersonInfo -> Add Function Import
Step 11:
Give the Function name to GetPersonInfo_Complex and Select the Returns a Collection of to Complex
Step 12:
Generate the Complex Return Collection by clicking the Get Column Information
Step 13:
Now Create the Column Information as a new Data Type by Clicking Create New Complex Type then click OK.
Now the Conectivity Part is Over. EDMX and Custom Data Types were created Successfully. Let us see how to use the Entity in the C# Application. Its very simple, just create a object for the appropriate Entity namely BalaEntities. Using the Object, access all your selected database tables and stored procedures.
C# Source Code:
|
1 2 3 4 5 6 7 8 9 10 11 |
using (var db = new BalaEntities()) { // Access the Person Database Table var person_Collection = db.Person; //Access the Person_Address Database Table var person_address_Collection = db.Person_Address; // Access the GetPersonInfo Database Stored Procedure var gerPersonInfo_Collection = db.GetPersonInfo(1); } |
Conclusion:
This article targets at understanding how efficiently we connect the SQL Server Database into C# .NET Application using Entity Framework. This article is intended for the beginner/intermediate level. I hope, this post will solve all your basic functionalities of Entity Framework. Think Big… Start Small… Do Fast…
