使用带有c#的Entity Framework 6调用现有存储过程。

I need to know how to call the existing stored procedure in the Entity framework 6 Code First using c#.

我需要知道如何首先使用c#调用实体框架6代码中的现有存储过程。

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:

DBContext类如下:

    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:

定义存储过程resultset的类如下:

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?

在调用存储过程之前，请让我知道是否需要执行其他操作。我是EF6新手，遇到了问题。代码中缺少什么?我需要在课堂上做些改变吗?

2 个解决方案

#1

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:

我可以从你的方法中看出一个问题你可以调用这个过程。您正在设置数组的所有元素等于param，但是您正在不断地更改param。所有元素都等于param的最终状态。试试这个:

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:

一个快速的俯冲，我发现了这个。从本质上说，如果在查询中将数字转换为int类型，应该没问题。而不是:

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

#2

Something of an aside, but it is not really necessary for you to use dynamic SQL. Particularly when having search criteria being strings it is safer to stick to a parameterised query. In your case you can do something like:

顺便提一下，但是使用动态SQL并不是必要的。特别是当搜索条件为字符串时，更安全的做法是坚持使用参数化查询。在你的情况下，你可以这样做:

;WITH employeetable as 
(
    select 
    CASE WHEN @sortDir = 'asc' THEN
    ROW_NUMBER() OVER (ORDER BY 
    CASE WHEN @sortCol = 'ID' THEN ID END,
    CASE WHEN @sortCol = 'FirstName' THEN FirstName END,
    CASE WHEN @sortCol = 'LastName' THEN LastName END,
    CASE WHEN @sortCol = 'Designation' THEN Designation END,
    CASE WHEN @sortCol = 'DepartmentName' THEN DepartmentName END,
    CASE WHEN @sortCol = 'Contact' THEN Contact END,
    CASE WHEN @sortCol = 'EmailAddress' THEN EmailAddress END,
    CASE WHEN @sortCol = 'Location' THEN Location END
    ASC) ELSE ROW_NUMBER() OVER (ORDER BY  
    CASE WHEN @sortCol = 'ID' THEN ID END,
    CASE WHEN @sortCol = 'FirstName' THEN FirstName END,
    CASE WHEN @sortCol = 'LastName' THEN LastName END,
    CASE WHEN @sortCol = 'Designation' THEN Designation END,
    CASE WHEN @sortCol = 'DepartmentName' THEN DepartmentName END,
    CASE WHEN @sortCol = 'Contact' THEN Contact END,
    CASE WHEN @sortCol = 'EmailAddress' THEN EmailAddress END,
    CASE WHEN @sortCol = 'Location' THEN Location END
    DESC) END AS RowNumber,
    COUNT(*) over() as TotalRecords, ID,FirstName,LastName,Designation,
    DepartmentName,Contact,EmailAddress,Location from Employees
    where DepartmentName = @departmentname 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 + '%')
)
select * from employeetable