In this article I am going to explain How to execute stored Procedure without EntityFramework EDMX.
Here I am using EntityFramework 5.0.0.0 (Install from here) and for my sample project I am using a simple Department table like below
Put your connection string in config file like below
<connectionStrings>
<add name="DbConnectionContext" connectionString="Data Source=JAMEELM\SQL2012DEV; Initial Catalog=jSchool; Integrated Security=True; MultipleActiveResultSets=True;Pooling=False;" providerName="System.Data.SqlClient" />
</connectionStrings>
You no need to specify metadata in connection string like EDMX.
Create a new class named InitializeDbContext by inherting DbContext class. In this class I have written five method for Insert,Update,Delete,Select and select By id operations.For every operation we can use same ExecuteStoreQuery method.This method will handle everything related to CRUD operations
Source Code : InitializeDbContext class
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
namespace EFSP.Manual.Demo
{
public class InitializeDbContext : DbContext where T : class
{
public InitializeDbContext()
: base("name=DbConnectionContext")
{
Database.SetInitializer<InitializeDbContext>(null);
}
public DbSet CurrentDbset { get; set; }
public T AddorUpdate(string procedureName, params object[] parameters)
{
T returnId =
((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery(procedureName, parameters).
SingleOrDefault();
return returnId;
}
public List Get(string procedureName, params object[] parameters)
{
var returnId =
((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery(procedureName, parameters).ToList();
return returnId;
}
public T GetById(string procedureName, params object[] parameters)
{
var returnId =
((IObjectContextAdapter) this).ObjectContext.ExecuteStoreQuery(procedureName, parameters).FirstOrDefault();
return returnId;
}
public T Delete(string procedureName, params object[] parameters)
{
var returnId =
((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery(procedureName, parameters).SingleOrDefault();
return returnId;
}
}
}
Add a new class named DepartmentModel for mapping table to complex type
Source Code : DepartmentModel class
using System.ComponentModel.DataAnnotations.Schema;
namespace EFSP.Manual.Demo
{
[Table("Department")]
public class DepartmentModel
{
public long Id { get; set; }
public string Department { get; set; }
}
}
Add a new class named ScopeIdentity for mapping Insert,Update,Delete identity column
Source Code : ScopeIdentity
namespace EFSP.Manual.Demo
{
public class ScopeIdentity
{
public decimal Id { get; set; }
}
}
Add one more class DepartmentRepository for our Business functionalities
Source Code : DepartmentRepository
using System.Collections.Generic;
using System.Data.SqlClient;
namespace EFSP.Manual.Demo
{
public class DepartmentRepository
{
public List GetDepartments()
{
using (var dbContext = new InitializeDbContext())
{
List deparment = dbContext.Get(StoredProcedure.GetDepartment);
return deparment;
}
}
public DepartmentModel GetDepartmentById(long id)
{
using (var dbContext = new InitializeDbContext())
{
object[] parameters =
{
new SqlParameter("@Id", id),
};
DepartmentModel deparment = dbContext.GetById(StoredProcedure.GetDepartmentById,parameters);
return deparment;
}
}
public ScopeIdentity InsertDepartment(DepartmentModel departmentModel)
{
using (var dbContext = new InitializeDbContext())
{
object[] parameters =
{
new SqlParameter("@Department", departmentModel.Department)
};
ScopeIdentity scopeIdentity = dbContext.AddorUpdate(StoredProcedure.InsertDepartment, parameters);
return scopeIdentity;
}
}
public ScopeIdentity UpdateDepartment(DepartmentModel departmentModel)
{
using (var dbContext = new InitializeDbContext())
{
object[] parameters =
{
new SqlParameter("@Id", departmentModel.Id),
new SqlParameter("@Department", departmentModel.Department)
};
ScopeIdentity scopeIdentity = dbContext.AddorUpdate(StoredProcedure.UpdateDepartment, parameters);
return scopeIdentity;
}
}
public ScopeIdentity DeleteDepartment(int id)
{
using (var dbContext = new InitializeDbContext())
{
object[] parameters =
{
new SqlParameter("@Id", id),
};
ScopeIdentity scopeIdentity = dbContext.Delete(StoredProcedure.DeleteDepartment, parameters);
return scopeIdentity;
}
}
}
}
Stored Procedures
namespace EFSP.Manual.Demo
{
public class StoredProcedure
{
public const string GetDepartment = "Get_Department";
//if any parameters please specify it with procedure name like below.You can pass multiple parameters by comma
public const string GetDepartmentById = "Get_Department_Id @Id";
public const string InsertDepartment = "Insert_Department @Department";
public const string UpdateDepartment = "Update_Department @Id,@Department";
public const string DeleteDepartment = "Delete_Department @Id";
}
}
Calling methods
var departmentRepository = new DepartmentRepository();
//Get all Department
List departments = departmentRepository.GetDepartments();
//Get Deparment By Id
var departmentById = departmentRepository.GetDepartmentById(1);
//Insert new Department
var departmentModel = new DepartmentModel {Department = "Humanities"};
ScopeIdentity scopeIdentity = departmentRepository.InsertDepartment(departmentModel);
//Update Department
var updatedModel = new DepartmentModel { Id = 1,Department = "Zoology" };
ScopeIdentity updatedscopeIdentity= departmentRepository.UpdateDepartment(updatedModel);
//Delete Department
ScopeIdentity deletedIdentity = departmentRepository.DeleteDepartment(1);
Stored Procedures i have used
USE [jSchool]
GO
CREATE PROCEDURE [dbo].[Get_Department]
AS
BEGIN
SET NOCOUNT ON
SELECT *
FROM [Department]
END
GO
CREATE PROCEDURE [dbo].[Get_Department_Id]
@Id int
AS
BEGIN
SET NOCOUNT ON
SELECT *
FROM [Department]
WHERE Id = @Id
END
GO
CREATE PROCEDURE [dbo].[Insert_Department]
@Department NVARCHAR(100)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO [Department](Department)
VALUES (@Department)
SELECT SCOPE_IDENTITY() AS Id
END
GO
CREATE PROCEDURE [dbo].[Update_Department]
@Id int,
@Department NVarchar(100)
AS
BEGIN
SET NOCOUNT ON
UPDATE [Department]
SET Department = @Department
WHERE Id = @Id
SELECT CONVERT(DECIMAL(6,2),@Id) AS Id
END
GO
CREATE PROCEDURE [dbo].[Delete_Department]
@Id int
AS
BEGIN
SET NOCOUNT ON
DELETE FROM [Department]
WHERE Id = @Id
SELECT CONVERT(DECIMAL(6,2),@Id) AS Id
END
Enjoy coding..
