.Net

Home » .Net

SQL Server Connectivity using Entity Framework in C# .NET Application

Posted on August 14, 2016 in C-Sharp, Database by Balamanigandan B

Comments are off

Tags:.Net,C#,edmx,Entity Framework,Sql Server

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:

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,
)

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.

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

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.

SELECT * FROM dbo.Person

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:

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
)

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.

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

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.

SELECT * FROM dbo.Person_Address

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

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

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.

EXEC [dbo].[GetPersonInfo] @PersonID = 1

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:

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);
}

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…

Recent Posts
Categories
- C-Sharp (5)
- Database (2)
- Dev Tools (1)
- Programming (7)
- UI (5)
  - AngularJS (3)
- Uncategorized (2)
- WPF (2)
Calendar
February 2019

M T W T F S S

« Aug

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
Popular tags
.Net Advanced Rest Client angularjs Angular material ApexSQL C# debugging Duplicate Records ECMAScript edmx Entity Framework ES6 excel filesystem FileSystemWatcher Floating label html5 HTML Blog IEnumerable Interactive Blog IQueryable Linq LinqPad md-chips MultiSelect Combobox Snoop Sql Server testing Transcompilers Transfer a Large Data from Client to WCF Transpilers WCF WordPress WordPress Blog XAML

Copyright © 2016 IncRev Corporation. All rights reserved.