I 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 if anything else needs to be done before calling the stored procedure. I am new to EF6 and running into issues. What is missing in the code? Do I need to make some changes in any of the class?
