将参数问题从实体框架传递到存储过程 [英] Passing parameter issue from Entity Framework to store procedure

问题描述

我有应用程序在MVC中运行，我打算将数据从MVC应用程序C＃发送到存储过程。我已经测试了我的存储过程，它的工作原理，如果我从SQL Server Management Studio执行，并且在C＃中，我从网页（视图）获取所有的值，以运行它期望调用存储过程，所以我猜我是在参数传递中发生错误!!!

它不存储数据库中的数据

非常感谢提前

商店流程

  USE [MySolution01_DB] 
 GO 
 
 SET ANSI_NULLS ON 
 GO 
 SET QUOTED_IDENTIFIER ON 
 GO 
 
 ALTER PROCEDURE [dbo]。[CreateNewFunctionsNavigation] 
 @FunctionName nvarchar（250），
 @Hierarchy_Level INT，
 @Function_identity INT OUTPUT，
 @ControllerName nvarchar（250），
 @Controller_identity INT OUTPUT，
 @ControllerInFunction_identity INT OUTPUT ，
 @ActionName nvarchar（250），
 @Action_identity INT OUTPUT，
 @ActionInFunction_identity INT OUTPUT，
 @Function_ParentsFunctionID INT，
 @Function_P arentsFunction_identity INT OUTPUT 
 AS 
 BEGIN 
 SET NOCOUNT ON; 
 
 INSERT INTO [dbo]。[Navigation_Functions]（[FunctionName]，[Hierarchy_Level]）
 VALUES（@FunctionName，@Hierarchy_Level）
 SET @ Function_identity = SCOPE_IDENTITY（）
 
 
 INSERT INTO [dbo]。[Navigation_FunctionController]（[ControllerName]）
 VALUES（@ControllerName）
 SET @Controller_identity = SCOPE_IDENTITY（）
 
 
 INSERT INTO [dbo]。[Navigation_FunctionInController]（[Function_ID]，[ControllerID]）
 VALUES（@Function_identity，@Controller_identity）
 SET @ControllerInFunction_identity = SCOPE_IDENTITY（）
 
 INSERT INTO [dbo]。[Navigation_FunctionAction]（[ActionName]，[ControllerID]）
 VALUES（@ActionName，@Controller_identity）
 SET @Action_identity = SCOPE_IDENTITY（）
 
 INSERT INTO [dbo]。[Navigation_FunctionInAction]（[ActionID]，[Function_ID]）
 VALUES（@Action_identity，@Function_identity）
 SET @ActionInFunction_identity = SCOPE_IDENTITY（）
 
 INSERT INTO [dbo]。[Navigation_FunctionHierarchy]（[Functi on_IDs]，[Parent_Function_ID]）
 VALUES（@Function_identity，@Function_ParentsFunctionID）
 SET @Function_ParentsFunction_identity = SCOPE_IDENTITY（）
 
返回
 END 
  





模型类

  public class CreateFunctionNavigation_SP_Map 
 {
 public CreateFunctionNavigation_SP_Map（）
 {
 
} 
 
 [StringLength（250）] 
 [必需（ ErrorMessage =Required Function Title）] 
 [Display（Name =Function Title）] 
 public string FunctionName {get;组; } 
 
 [必需（ErrorMessage =必需的函数层次结构;即层次结构树中存在的函数\\顶层从1开始）] 
 [Display（Name =Function Hierarchy级别）] 
 public int FunctionHierarchy_Level {get;组; 
 
 [StringLength（250）] 
 [必需（ErrorMessage =必需控制器标题）] 
 [显示（名称=控制器标题）] 
 public string ControllerName {get;组; 
 
 [StringLength（250）] 
 [必需（ErrorMessage =需要的操作标题）] 
 [显示（名称=动作标题）] 
 public string ActionName {get;组; } 
 
 [必需（ErrorMessage =必需函数父 - 子关系ID \\\
将0赋给函数没有父函数）] 
 [Display（Name = Function Parent's FunctionID）] 
 public int Function_ParentsFunctionID {get;组; 
 
} 
  

调用存储过程的类

  public void CreateFunctionNavigation（CreateFunctionNavigation_SP_Map _entity）
 {
 
 using（var dbContext = new FunctionContext（））
 {
 CreateFunctionNavigation_SP_Map modelObj = new CreateFunctionNavigation_SP_Map（）; 
 
 var parameters = new [] {
 new SqlParameter（@ FunctionName，_entity.FunctionName），
 new SqlParameter（@ FunctionHierarchy_Level，_entity.FunctionHierarchy_Level），
 new SqlParameter（@ ControllerName，_entity.ControllerName），
 new SqlParameter（@ ActionName，_entity.ActionName），
 new SqlParameter（@ Function_ParentsFunctionID，_entity.Function_ParentsFunctionID）
}; 
 
 
 dbContext.Database.SqlQuery< CreateFunctionNavigation_SP_Map>（exec CreateNewFunctionsNavigation，参数）; 
} 
 
 
} 
  

新更新

我已经更改了C＃代码，但仍然无法存储数据

  dbContext.Database.SqlQuery< CreateFunctionNavigation_SP_Map>（EXEC CreateNewFunctionsNavigation @FunctionName，@FunctionHierarchy_Level，@ControllerName，@ActionName，@Function_ParentsFunctionID，
 new SqlParameter（FunctionName，_entity.FunctionName），
 new SqlParameter （FunctionHierarchy_Level，_entity.FunctionHierarchy_Level），
 new SqlParameter（ControllerName，_entity.ControllerName），
 new SqlParameter（ActionName，_entity.ActionName），
 new SqlParameter Function_ParentsFunctionID，_entity.Function_ParentsFunctionID）
）; 
  

解决方案

您可以使用SQL Profiler对其进行调试对服务器执行的命令。无论如何，尝试以下

dbContext.Database.SqlQuery（CreateNewFunctionsNavigation
@FunctionName，@FunctionHierarchy_Level，@ControllerName，@ActionName ，
@Function_ParentsFunctionID，参数）;

更新

由于上面的答案没有为您工作，您可以尝试手动创建查询字符串。

  var query = string.Format（EXEC CreateNewFunctionsNavigation+ 
@FunctionName = {0}，+ 
@FunctionHierarchy_Level = {1}，+ 
@ ControllerName = {2}，+ 
@ActionName = {3}，+ 
@Function_ParentsFunctionID = {4}，
 _entity.FunctionName，
 _entity.FunctionH ierarchy_Level，
 entity.ControllerName，
 _entity.ActionName，
 _entity.Function_ParentsFunctionID）; 
 
 
 dbContext.Database.SqlQuery< CreateFunctionNavigation_SP_Map>（query）; 
  

I have app running in MVC and I am intended to send data from MVC app C# to Store Procedure. I have tested my stored procedure, it works on if I execute from SQL Server Management Studio, and in C# I am getting all the values from web page (view) to function where it is expecting to call store procedure so I am guessing I am doing something wrong in parameter passing!!!

Its not storing data in database

Many thanks in advance

Store Procedure

USE [MySolution01_DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[CreateNewFunctionsNavigation]
 @FunctionName nvarchar(250),
 @Hierarchy_Level INT,
 @Function_identity INT OUTPUT, 
 @ControllerName nvarchar(250), 
 @Controller_identity INT OUTPUT,
 @ControllerInFunction_identity INT OUTPUT,
 @ActionName nvarchar(250),
 @Action_identity INT OUTPUT,
 @ActionInFunction_identity INT OUTPUT,
 @Function_ParentsFunctionID INT,
 @Function_ParentsFunction_identity INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO [dbo].[Navigation_Functions] ([FunctionName],[Hierarchy_Level] ) 
VALUES(@FunctionName, @Hierarchy_Level)
SET @Function_identity=SCOPE_IDENTITY()


INSERT INTO [dbo].[Navigation_FunctionController] ([ControllerName])
VALUES(@ControllerName)
SET @Controller_identity = SCOPE_IDENTITY()


INSERT INTO [dbo].[Navigation_FunctionInController] ([Function_ID], [ControllerID])
VALUES (@Function_identity, @Controller_identity)
SET @ControllerInFunction_identity = SCOPE_IDENTITY()

INSERT INTO [dbo].[Navigation_FunctionAction] ([ActionName], [ControllerID])
VALUES (@ActionName, @Controller_identity)
SET @Action_identity = SCOPE_IDENTITY()

INSERT INTO [dbo].[Navigation_FunctionInAction] ([ActionID], [Function_ID])
VALUES (@Action_identity, @Function_identity)
SET @ActionInFunction_identity = SCOPE_IDENTITY()

INSERT INTO [dbo].[Navigation_FunctionHierarchy] ([Function_IDs], [Parent_Function_ID])
VALUES (@Function_identity, @Function_ParentsFunctionID)
SET @Function_ParentsFunction_identity = SCOPE_IDENTITY()

RETURN
END

Model Class

  public class CreateFunctionNavigation_SP_Map
{
    public CreateFunctionNavigation_SP_Map()
    {

    }

    [StringLength(250)]
    [Required(ErrorMessage = "Required Function Title")]
    [Display(Name = "Function Title")]
    public string FunctionName { get; set; }

    [Required(ErrorMessage = "Required Function Hierarchy; i.e Where Function Exists In Hierarchy Tree \n Top-Level Start From 1 ")]
    [Display(Name = "Function Hierarchy Level")]
    public int FunctionHierarchy_Level { get; set; }

    [StringLength(250)]
    [Required(ErrorMessage = "Required Controller Title")]
    [Display(Name = "Controller Title")]
    public string ControllerName { get; set; }

    [StringLength(250)]
    [Required(ErrorMessage = "Required Action Title")]
    [Display(Name = "Action Title")]
    public string ActionName { get; set; }

    [Required(ErrorMessage = "Required Function Parent - Child Relation ID \n Put 0 In Case Given Function doesn't Have Any Parent Function ")]
    [Display(Name = "Function Parent's FunctionID")]
    public int Function_ParentsFunctionID { get; set; }    

}

Class to call Stored Procedure

public void CreateFunctionNavigation(CreateFunctionNavigation_SP_Map _entity)
    {

        using(var dbContext = new FunctionContext())
        {
            CreateFunctionNavigation_SP_Map modelObj = new CreateFunctionNavigation_SP_Map();

            var parameters = new[] { 
                new SqlParameter("@FunctionName" , _entity.FunctionName ),
                new SqlParameter("@FunctionHierarchy_Level" , _entity.FunctionHierarchy_Level ),
                new SqlParameter("@ControllerName" , _entity.ControllerName ),
                new SqlParameter("@ActionName" , _entity.ActionName ),
                new SqlParameter("@Function_ParentsFunctionID" , _entity.Function_ParentsFunctionID )
            };


             dbContext.Database.SqlQuery<CreateFunctionNavigation_SP_Map>("exec CreateNewFunctionsNavigation", parameters);
        }


    }

new update

I have change C# code but still not able to store data

dbContext.Database.SqlQuery<CreateFunctionNavigation_SP_Map>("EXEC CreateNewFunctionsNavigation @FunctionName, @FunctionHierarchy_Level, @ControllerName, @ActionName, @Function_ParentsFunctionID",
       new SqlParameter("FunctionName", _entity.FunctionName),
       new SqlParameter("FunctionHierarchy_Level", _entity.FunctionHierarchy_Level),
       new SqlParameter("ControllerName", _entity.ControllerName),
       new SqlParameter("ActionName", _entity.ActionName),
       new SqlParameter("Function_ParentsFunctionID", _entity.Function_ParentsFunctionID)
   );

解决方案

You could have debugged this by using SQL Profiler which shows commands executed against the server. Anyways, try the following

dbContext.Database.SqlQuery("CreateNewFunctionsNavigation @FunctionName, @FunctionHierarchy_Level, @ControllerName, @ActionName, @Function_ParentsFunctionID", parameters);

Update

Since the answer above didnt work for you, you could try and create the query string manually like this..

        var query = string.Format("EXEC CreateNewFunctionsNavigation " +
                                  "@FunctionName = {0}, " +
                                  "@FunctionHierarchy_Level = {1}, " +
                                  "@ControllerName = {2}, " +
                                  "@ActionName = {3}, " +
                                  "@Function_ParentsFunctionID = {4}",
                                  _entity.FunctionName,
                                  _entity.FunctionHierarchy_Level, 
                                  entity.ControllerName,
                                  _entity.ActionName,
                                  _entity.Function_ParentsFunctionID);


        dbContext.Database.SqlQuery<CreateFunctionNavigation_SP_Map>(query);

这篇关于将参数问题从实体框架传递到存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！