Step 1:- Create Table
CREATE TABLE [dbo].[Department]
(
[Id] [int] IDENTITY(1, 1) NOT NULL,
[Name] [nvarchar](50) NULL
)
Step 2:-
Create Store Procedure
CREATE PROCEDURE spGetDepartment
AS
BEGIN
SELECT * FROM Department d
END
CREATE PROCEDURE spAddDepartment
@Name VARCHAR(50)
AS
BEGIN
INSERT INTO Department
(
[Name]
)
VALUES
(
@Name
)
END
CREATE PROCEDURE spUpdateDepartment
@Name VARCHAR(50)
AS
BEGIN
UPDATE Department
SET [Name] = @Name
WHERE [Name] = 'Finance'
END
CREATE PROCEDURE spDeleteDepartment
@Name VARCHAR(50)
AS
BEGIN
DELETE
FROM Department
WHERE [Name] = @Name
END
Step 3:-
Open Visual Studio 2010 => File => New => Project => Click on OK
Step 4:- Open Solution
Explorer from Right pane => Right Click on Project => Add => New Item...
Step 5:- Select Data tab from
Left pane => Select ADO.NET Entity Data Model from Middle Page => Give
Name EntityDataModel.edmx => Click on Add
Step 6:- Select Generate from
database under Entity Data Model Wizard => Click on Next
Step 7:- Click on New
Connection... => Fill database credential => Click on Test Connection
=> Click on OK => Click on OK
Step 8:- Select Respective Radio Button and change TestingEntities to DepartmentEntities => Click on Next
Step 9:- Expand Table node and select Department Table
Step 10:-
Expand Store Procedure node and select spAddDepartment, spUpdateDepartment,
spDeleteDepartment
Step 11:-
Change TestingModel to DepartmentModel => Click on Finish
Step 12:- Expand Table/View and Store Procedure
Step 13:- Right click on
Department entity and select Stored Procedure Mapping
Step 14:- Under Mapping
Details, you will see <Select Insert
Function>, <Select Update Function>, and <Select Delete
Function>. Select the respective stored procedure for each one, e.g.
Select spAddDepartment store procedure for Insert function, Select
spUpdateDepartment store procedure for Update function and Select
spDeleteDepartment store procedure for Delete function
Step 15:- Right click on
Department entity and Click Validate and make sure that there are no warnings
or errors
Step 16:- Go to Solution Explorer => Click on Program.cs file
Step 17:- Copy past follwing code under Main function
using (var context = new DepartmentEntities())
{
Department department = new Department() { Name = "Finance" };
// Insert Department
// On SaveChanges()
spAddDepartment Store Procedure call to add new record
context.Departments.AddObject(department);
context.SaveChanges();
// Update Department
Name
// On SaveChanges()
spUpdateDepartment Store Procedure call to update record
department.Name
= "Finance
Dept";
context.SaveChanges();
// Delete Department
// On SaveChanges()
spDeleteDepartment Store Procedure call delete record
context.Departments.DeleteObject(department);
context.SaveChanges();
// Get Department
context.Departments.ToList().ForEach(a
=> Console.WriteLine("Id : {0}, Name
: {1}",
a.Id, a.Name));
Console.ReadKey();
}
Note:-
If Department entity not mapped with Stored Procedure then default
operation perform on Department entity.
using (var context = new ConnectionEntities())
{
#region Select
Console.WriteLine("********************
FETCH *************************");
var stdQuery = from d in context.Departments
select d;
foreach (var q in stdQuery)
{
Console.WriteLine("Id : " + q.Id + ", Name : " + q.Name);
}
#endregion
#region Add
Console.WriteLine("********************
ADD ***************************");
context.Departments.AddObject(new Department { Name = "Finance" });
context.SaveChanges();
var stdQuery1 = from d in context.Departments
select d;
foreach (var q in stdQuery1)
{
Console.WriteLine("Id : " + q.Id + ", Name : " + q.Name);
}
#endregion
#region Update
Console.WriteLine("********************
UPDATE ************************");
Department updDepartment = (from d in context.Departments.Where(x
=> x.Name == "Finance")
select d).FirstOrDefault();
if (updDepartment != null)
{
updDepartment.Name
= "Finance
Dept";
context.SaveChanges();
}
var stdQuery2 = from d in context.Departments
select d;
foreach (var q in stdQuery2)
{
Console.WriteLine("Id : " + q.Id + ", Name : " + q.Name);
}
#endregion
#region Delete
Console.WriteLine("********************
DELETE ************************");
Department DelDepartment = (from d in context.Departments.Where(x
=> x.Name == "Finance Dept")
select d).FirstOrDefault();
if (DelDepartment != null)
{
context.Departments.DeleteObject(DelDepartment);
context.SaveChanges();
}
var stdQuery3 = from d in context.Departments
select d;
foreach (var q in stdQuery3)
{
Console.WriteLine("Id : " + q.Id + ", Name : " + q.Name);
}
#endregion
//******************************
OR ***********************************
#region Select
Console.WriteLine("********************
FETCH *************************");
context.Departments.ToList().ForEach(a
=> Console.WriteLine("Id : {0} Name :
{1}",
a.Id, a.Name));
#endregion
#region Add
Console.WriteLine("********************
ADD ***************************");
Department department = new Department() { Name = "Finance" };
context.Departments.AddObject(department);
context.SaveChanges();
context.Departments.ToList().ForEach(a
=> Console.WriteLine("Id : {0} Name :
{1}",
a.Id, a.Name));
#endregion
#region Update
Console.WriteLine("********************
UPDATE ************************");
department.Name
= "Finance
Dept";
context.SaveChanges();
context.Departments.ToList().ForEach(a
=> Console.WriteLine("Id : {0} Name :
{1}",
a.Id, a.Name));
#endregion
#region Delete
Console.WriteLine("********************
DELETE ************************");
context.Departments.DeleteObject(department);
context.SaveChanges();
context.Departments.ToList().ForEach(a
=> Console.WriteLine("Id : {0} Name :
{1}",
a.Id, a.Name));
#endregion
Console.ReadKey();
}
