hit tracker

.Net

Home » .Net

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

in C-Sharp, Database by Balamanigandan B Comments are off

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:

Insert Data:

Insert the data into the Person table.

Select Data:

Select the data from the Person table.

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:

Insert Data:

Insert the data into the Person_Address table.

Select Data:

Select the data from the Person_Address table.

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

Execute Stored Procedure:

Execute the Stored Procedure GetPersonInfo.

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:

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…