Insert,Update,Delete,Select using StoredProcedure Without EDMX in EntityFramework

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

1

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..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s