Puneet Verma Puneet Verma - 3 years ago 89
C# Question

Calling existing Stored procedure using Entity Framework 6 with C#

Need to know how to call the Existing stored procedure in the Entity framework 6 Code first using c#.

Below is the procedure that I am using

CREATE PROCEDURE proc_getEmployees
@departmentname varchar(50),
@sortCol varchar(30),
@sortdir varchar(25),
@searchString varchar(50)
AS
BEGIN
SET NOCOUNT ON;
declare @strSQl varchar(1000);
declare @strSQlwhere varchar(500);
declare @strSelectEndPart varchar(100);

set @strSQl = ';WITH employeetable as
(
select ROW_NUMBER() OVER (ORDER BY '+@sortCol+' '+@sortdir+' ) AS RowNumber,COUNT(*) over() as TotalRecords, ID,FirstName,LastName,Designation,DepartmentName,Contact,EmailAddress,Location from Employees ';
set @strSQlwhere = 'where DepartmentName = '''+@departmentname+'''';
set @strSQlwhere = @strSQlwhere+ ' and (Id like ''%' + @searchString + '%'' Or FirstName like ''%' + @searchString + '%'' Or LastName like ''%' + @searchString + '%'' Or Designation like ''%' + @searchString + '%'' Or DepartmentName like ''%' + @searchString + '%'' Or Contact like ''%' + @searchString + '%'' Or EmailAddress like ''%' + @searchString + '%'' Or Location like ''%' + @searchString + '%'')';
set @strSelectEndPart =') select * from employeetable';

set @strSQl = @strSQl +@strSQlwhere+@strSelectEndPart;
execute (@strSQl);
END
GO


Table I am querying is Employees having the structure as

Column Type Length
ID int 4
FirstName varchar 50
LastName varchar 50
Designation varchar 50
DepartmentName varchar 50
Contact varchar 50
EmailAddress varchar 50
Location varchar 50


DBContext Class is as below

public class DevelopmentTestDatabaseContext :DbContext
{
public DevelopmentTestDatabaseContext() : base("name =DevelopmentTestDatabaseContext")
{

}
public virtual DbSet<Employee> EmployeeData { get; set; }

}


Method for calling the stored procedure as below

public void GetEmployeeDataUsingProcedure()
{
object[] parameters = new SqlParameter[4];
List<EmployeeResultSet> lstEmployees = new List<EmployeeResultSet>();
try
{
using (var db = new DevelopmentTestDatabaseContext())
{
SqlParameter param = new SqlParameter("@departmentname", "IT");
parameters[0] = param;
param = new SqlParameter("@sortCol", "ID");
parameters[1] = param;
param = new SqlParameter("@sortdir", "asc");
parameters[2] = param;
param = new SqlParameter("@searchString", "ope");
parameters[3] = param;

var results = db.Database.SqlQuery<EmployeeResultSet>("proc_getEmployees @departmentname, @sortCol, @sortdir, @searchString", parameters);
db.Database.Log = query => System.Diagnostics.Debug.Write(query);
lstEmployees = results.ToList();
}
}
catch (Exception ex)
{

}
}


Defined the class for the stored procedure resultset as below

public class EmployeeResultSet
{
public int rowNumber { get; set; }
public int totalRecords { get; set; }
public int ID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Designation { get; set; }
public string DepartmentName { get; set; }
public string Contact { get; set; }
public string EmailAddress { get; set; }
public string Location { get; set; }
}


Please let me know what else need to be done for calling the stored procedure. I am new to EF6 and running into issues. Please help me what is missing in the code. Do I need to make some changes in any of the class

Please help with this.

Answer Source

One thing I can tell an issue with is in your method where you call the procedure. You are setting all elements of the array equal to param but you are constantly changing param. All of your elements will be equal to the final state of param. Try this instead:

public void GetEmployeeDataUsingProcedure()
{
    object[] parameters = new SqlParameter[4];
    List<EmployeeResultSet> lstEmployees = new List<EmployeeResultSet>();
    try
    {
        using (var db = new DevelopmentTestDatabaseContext())
        {
            parameters[0] = new SqlParameter("@departmentname", "IT");
            parameters[1] = new SqlParameter("@sortCol", "ID");
            parameters[2] = new SqlParameter("@sortdir", "asc");
            parameters[3] = new SqlParameter("@searchString", "ope");

            var results = db.Database.SqlQuery<EmployeeResultSet>("proc_getEmployees @departmentname, @sortCol, @sortdir, @searchString", parameters);
            db.Database.Log = query => System.Diagnostics.Debug.Write(query);
            lstEmployees = results.ToList();
        }
    }
    catch (Exception ex)
    {
        //log it or something
    }
}

There may be other issues but without looking too much into it I'd need more information about specific errors or behavior you are experiencing.

You may also try typing out the full name of your database:

"MyDatabase.MySchema.proc_getEmployees @departmentname, @sortCol, @sortdir, @searchString"

EDIT Per your comments:

A quick ducking and I found this. Essentially it states that if you cast your numbers as int in your query you should be fine. So instead of:

select * from employeetable

Try:

select CAST(RowNumber as int) as RowNumber, 
    CAST(TotalRecords as int) as TotalRecords, 
    CAST(ID as int) as ID, 
    FirstName, 
    LastName, 
    Designation, 
    DepartmentName, 
    Contact, 
    EmailAddress, 
    Location 
from employeetable
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download