MVC4：存储过程带有多个参数

Using Visual Studio Express 2012, MVC4 web application, and SQL 2008 R2...

使用Visual Studio Express 2012，MVC4 Web应用程序和SQL 2008 R2 ...

I used to have a stored procedure that only accepted one parameter, and when my MVC model included that stored procedure, I could successfully invoke it from my Controller and get Json results just fine.

我曾经有一个只接受一个参数的存储过程，当我的MVC模型包含该存储过程时，我可以从我的Controller成功调用它并获得Json结果。

Now I have updated the stored procedure to accept two parameters. I've refreshed my MVC model but am unable to invoke the stored procedure correctly from my controller when passing two parameters.

现在我已更新存储过程以接受两个参数。我刷新了我的MVC模型但是在传递两个参数时无法从我的控制器中正确调用存储过程。

Here is my old stored procedure and controller using one parameter (this works):

这是我的旧存储过程和控制器使用一个参数（这工作）：

PROCEDURE [dbo].[GetDependencyNodes]     
@CISearchString nvarchar(100)

Old Controller:

老控制器：

namespace CMDB.Controllers
{
public class GoJSDiagramNodesAndLinksController : Controller
{
   private CMDBEntities db = new CMDBEntities();
   public ActionResult GetDependencyNodes(string CISearchString)
    {
        return Json(db.GetDependencyNodes(CISearchString).ToList(), JsonRequestBehavior.AllowGet);
    }
}
}

Here is my new stored procedure and controller accepting two parameters:

这是我的新存储过程和控制器接受两个参数：

PROCEDURE [dbo].[GetDependencyNodes]
@CISearchString nvarchar(100),
@ExcludeString nvarchar(100)

New Controller:

新控制器：

namespace CMDB.Controllers
{
public class GoJSDiagramNodesAndLinksController : Controller
{
   private CMDBEntities db = new CMDBEntities();
   public ActionResult GetDependencyNodes(string CISearchString, string ExcludeString)
    {
   return Json(db.GetDependencyNodes(CISearchString, ExcludeString).ToList(), JsonRequestBehavior.AllowGet);
    }
}
}

In Visual Studio Express 2012, I get an error on my "return Json" line stating:

在Visual Studio Express 2012中，我在“返回Json”行中收到错误，指出：

'int' does not contain a definition for 'ToList' and no extension method 'ToList' accepting a first argument of type 'int' could not be found.

'int'不包含'ToList'的定义，并且找不到接受类型'int'的第一个参数的扩展方法'ToList'。

If I change the line from:

如果我更改以下行：

return Json(db.GetDependencyNodes(CISearchString, ExcludeString).ToList(), JsonRequestBehavior.AllowGet);

to:

至：

return Json(db.GetDependencyNodes(CISearchString, ExcludeString).ToString().ToList(), JsonRequestBehavior.AllowGet);

The error goes away but the controller returns a -1 instead of the correct Json results. I've tested the stored procedure in SQL Manager and can see the expected results.

错误消失但控制器返回-1而不是正确的Json结果。我已经在SQL Manager中测试了存储过程，可以看到预期的结果。

Here is partial code from my file CMDBModels.Desginer.cs with GetDependencyNodes:

以下是我的文件CMDBModels.Desginer.cs中包含GetDependencyNodes的部分代码：

    /// <summary>
    /// No Metadata Documentation available.
    /// </summary>
    /// <param name="cISearchString">No Metadata Documentation available.</param>
    /// <param name="excludeString">No Metadata Documentation available.</param>
    public int GetDependencyNodes(global::System.String cISearchString, global::System.String excludeString)
    {
        ObjectParameter cISearchStringParameter;
        if (cISearchString != null)
        {
            cISearchStringParameter = new ObjectParameter("CISearchString", cISearchString);
        }
        else
        {
            cISearchStringParameter = new ObjectParameter("CISearchString", typeof(global::System.String));
        }

        ObjectParameter excludeStringParameter;
        if (excludeString != null)
        {
            excludeStringParameter = new ObjectParameter("ExcludeString", excludeString);
        }
        else
        {
            excludeStringParameter = new ObjectParameter("ExcludeString", typeof(global::System.String));
        }

        return base.ExecuteFunction("GetDependencyNodes", cISearchStringParameter, excludeStringParameter);
    }

Any ideas?

有任何想法吗？

Thanks.

谢谢。

1 个解决方案

#1

Couple of things first move your Data Access to another class, keep your controller as light as possible.

有些事情首先将您的数据访问移动到另一个类，让您的控制器尽可能轻。

Next map your stored procure output to an basic class that has properties that match the returned columns (entity class)

接下来将存储的procure输出映射到具有与返回列匹配的属性的基本类（实体类）

this should let you look at the returned data in debug a lot easier and is just good programming practice

这应该让你更容易看看调试中返回的数据，这只是一个很好的编程实践

#1