将Int参数从C＃发送到存储过程

I have stored procedure which takes ID (INT) when I run query using DbContext.Database.SqlQuery I doesn't work. I have another Store Procedure which been script in very similar pattern except it taking all string parameter, perhaps I doing something wrong on this Int passing parameter!!!!

当我使用DbContext.Database.SqlQuery运行查询时，我有存储过程，该过程采用ID（INT）我不起作用。我有另一个存储过程，它是非常相似模式的脚本，除了它采取所有字符串参数，也许我在这个Int传递参数上做错了！

model class

public class DeleteFunctionNavigation_SP_Map
{  
    public int FunctionID { get; set; }
}

Stored Procedure

ALTER PROCEDURE [dbo].[DeleteFunctionsNavigation]

 @FunctionID INT,
 @Action_identity INT OUTPUT,
 @ActionInFunction_Count INT OUT,
 @Controller_identity INT OUTPUT

 AS
 BEGIN

SET NOCOUNT ON;

SELECT @Action_identity = Navigation_FunctionInAction.ActionID 
FROM Navigation_FunctionInAction
WHERE Navigation_FunctionInAction.Function_ID = @FunctionID
..........
//my other code here!

RETURN
END

C# Class

public void DeleteNavigationFunctionByID(int _FunctionNavigationID)
{
    using (var dbContext = new FunctionContext())
    {

           var Action_identity_out = new SqlParameter("Action_identity", SqlDbType.Int) { Direction = System.Data.ParameterDirection.Output };
           var ActionInFunction_Count_out = new SqlParameter("ActionInFunction_Count", SqlDbType.Int) { Direction = System.Data.ParameterDirection.Output };
           var Controller_identity_out = new SqlParameter("Controller_identity", SqlDbType.Int) { Direction = System.Data.ParameterDirection.Output };

           var _query = dbContext.Database.SqlQuery<DeleteFunctionNavigation_SP_Map>("exec DeleteFunctionsNavigation @FunctionID, @Action_identity out, @ActionInFunction_Count out, Controller_identity out",
           new SqlParameter("@FunctionID", SqlDbType.Int).Value = _FunctionNavigationID,
           Action_identity_out,
           ActionInFunction_Count_out,
           Controller_identity_out
        );           
    }
}

Controller Method

[HttpPost]
public ActionResult DeleteFunctionNavigationByID(int _selectedNavigationFunctionID)
{
    try
    {
        _FN_Services_a2.DeleteFunctionNavigationByID(_selectedNavigationFunctionID);
    }
    catch (Exception ex)
    {
        ModelState.AddModelError("", "Unable To Delete Requested Record!" + ex);
    }
    return RedirectToAction("SystemCoreHome");
}

2 个解决方案

#1

Here's how I did this. I created a function like this:

这就是我这样做的方式。我创建了一个这样的函数：

    public int ExecuteNonQueryInt(string commandString, bool isStoredProc = false, params object[] param)
    {
        int result = 0;

        try
        {
            using (SqlConnection con = new SqlConnection("Your connection string here"))
            {
                con.Open();

                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = commandString;
                    cmd.CommandType = isStoredProc ? CommandType.StoredProcedure : CommandType.Text;

                    foreach (var parm in param)
                    {
                        cmd.Parameters.Add(parm);
                    }

                    result = cmd.ExecuteNonQuery();
                }

                con.Close();

            }
        }
        catch (Exception)
        {
            result = 0;
        }

        return result;
    }

Then I called it like this

然后我这样称呼它

    SqlParameter[] sqlParameters = new SqlParameter[3]
            {
                new SqlParameter() { ParameterName = "keywords" , Value = dataTable, SqlDbType = SqlDbType.Structured },
                new SqlParameter() { ParameterName = "moduleId" , Value = moduleId, SqlDbType = SqlDbType.UniqueIdentifier },
                new SqlParameter() { ParameterName = "createdBy" , Value = createdBy, SqlDbType = SqlDbType.Int }
            };

            return base.ExecuteNonQueryInt("Your stored procedure", true, sqlParameters) > 0;

#2

I have found the reason why it was not working

我找到了它无法正常工作的原因

1) I use list of against business class 2) I was instantiating SQL parameter wrong or well it did not work for me; working model as following;

1）我使用反对业务类的列表2）我实例化SQL参数错误或者它对我不起作用;工作模式如下;

public void DeleteNavigationFunctionByID(int _FunctionNavigationID)
   {
       using (var dbContext = new FunctionContext())
       {
           List<DeleteFunctionNavigation_SP_Map> _query;


           var Action_identity_out = new SqlParameter("Action_identity", SqlDbType.Int) { Direction = System.Data.ParameterDirection.Output };
           var ActionInFunction_Count_out = new SqlParameter("ActionInFunction_Count", SqlDbType.Int) { Direction = System.Data.ParameterDirection.Output };
           var Controller_identity_out = new SqlParameter("Controller_identity", SqlDbType.Int) { Direction = System.Data.ParameterDirection.Output };

           _query = dbContext.Database.SqlQuery<DeleteFunctionNavigation_SP_Map>("exec DeleteFunctionsNavigation @FunctionID, @Action_identity out, @ActionInFunction_Count out, @Controller_identity out",
                     new SqlParameter("@FunctionID", _FunctionNavigationID),
                     Action_identity_out,
                     ActionInFunction_Count_out,
                     Controller_identity_out
           ).ToList();

       }
}

#1