4赞

SqlException：过程或函数指定了太多参数

作者：拾味湖 | 2023-06-09 11:22

如何解决《SqlException：过程或函数指定了太多参数》经验，为你挑选了1个好方法。

我对SQL的经验很少，我有一种方法可以访问数据库并将数据添加到几个不同的表中。我认为我的存储过程是正确的。这是我遇到问题的方法。

我正在创建一个Web API端点，该端点是PUT，它应该能够向多个会话添加多个假期和日期并更新数据库。

这是我目前所拥有的，我需要做些什么，以免出现异常？

private void SaveGlobalOrderDays(List sessionList, List selectedOrderHolidays, List selectedHolidays, List orderDays, List noOrderDays)
{
    try
    {
        using (SqlCommand cmd = new SqlCommand())
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            cmd.CommandTimeout = 600;
            cmd.CommandText = "[dbo].[SaveGlobalOrderDays]"; 
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = connection;

            foreach (SessionInfoList session in sessionList)
            {
                cmd.Parameters.Add("@SessionId", SqlDbType.Int).Value = session.SessionID;

                if (selectedOrderHolidays.Count > 0)
                {
                    foreach (Holiday holiday in selectedOrderHolidays)
                    {
                        string orderHolidays = string.Join(",", holiday.Name).Trim();
                        cmd.Parameters.Add("@HolidayName", SqlDbType.NVarChar).Value = orderHolidays; 
                    }                                
                }

                if (selectedHolidays.Count > 0)
                {
                    foreach (Holiday holiday in selectedHolidays)
                    {
                        string noOrderHolidays = string.Join(",", holiday.Name).Trim();
                        cmd.Parameters.Add("@SelectedHolidayName", SqlDbType.NVarChar).Value = noOrderHolidays; 
                    }                                
                }

                if (orderDays.Count > 0)
                {
                    foreach (string order in orderDays)
                    {
                        string od = string.Join(",",order).Trim();
                        cmd.Parameters.Add("@OrderDays", SqlDbType.NVarChar).Value = od; 
                    }                                
                }

                if (noOrderDays.Count > 0)
                {
                    foreach (string noOrder in noOrderDays)
                    {
                        string nod = string.Join(",",noOrder).Trim();
                        cmd.Parameters.Add("@NoOrderDays", SqlDbType.NVarChar).Value = nod; 
                    }                                
                }                                             
            }

            foreach (SqlParameter parameter in cmd.Parameters)
            {
                if (parameter.Value == null)
                    parameter.Value = DBNull.Value;
            }

            connection.Open();

            cmd.ExecuteNonQuery();

            cmd.Dispose();
        }
    }
    catch (Exception ex)
    {
        string message = "Failed to save global order info";
        // Logger.Error(LogSource, "SaveGlobalOrderDays", string.Empty, message, string.Empty, ex);
        throw new Exception(message, ex);
    }
}

存储过程：

IF OBJECT_ID('[dbo].[SaveGlobalOrderDays]') IS NOT NULL
     DROP PROCEDURE [dbo].[SaveGlobalOrderDays]
GO

CREATE PROCEDURE [dbo].[SaveGlobalOrderDays]
    @SessionId INT,
    @HolidayName NVARCHAR(MAX),
    @SelectedHolidayName NVARCHAR(MAX),
    @OrderDays NVARCHAR(MAX),
    @NoOrderDays NVARCHAR(MAX)
WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE [cfgSchedule]
    SET [cfgSchedule].[SessionId] = @SessionId,
        [OrderDays] = @OrderDays,
        [NoOrderDays] = @NoOrderDays
    FROM [cfgSchedule],[SessionHolidayMapping],[SessionOrderHolidayMapping]
    WHERE [cfgSchedule].[SessionId] = [SessionHolidayMapping].[SessionId]
      AND [cfgSchedule].[SessionId] = [SessionOrderHolidayMapping].[SessionId]
      AND [cfgSchedule].[SessionId] = @SessionId

    UPDATE [SessionHolidayMapping]
    SET [SessionHolidayMapping].[HolidayName] = @SelectedHolidayName
    FROM [cfgSchedule],[SessionHolidayMapping],[SessionOrderHolidayMapping]
    WHERE [cfgSchedule].[SessionId] = [SessionHolidayMapping].[SessionId]
      AND [cfgSchedule].[SessionId] = [SessionOrderHolidayMapping].[SessionId]
      AND [cfgSchedule].[SessionId] = @SessionId

    UPDATE [SessionOrderHolidayMapping]
    SET [SessionOrderHolidayMapping].[HolidayName] = @HolidayName
    FROM [cfgSchedule],[SessionHolidayMapping],[SessionOrderHolidayMapping]
    WHERE [cfgSchedule].[SessionId] = [SessionHolidayMapping].[SessionId]
      AND [cfgSchedule].[SessionId] = [SessionOrderHolidayMapping].[SessionId]
      AND [cfgSchedule].[SessionId] = @SessionId
END
GO

Joel Coehoor.. 6

我们有这个循环：

foreach (SessionInfoList session in sessionList)
{

在循环中，我们有以下参数：

cmd.Parameters.Add("@SessionId", SqlDbType.Int).Value = session.SessionID;

并有条件地将其他条件包含在循环中：

cmd.Parameters.Add("@NoOrderDays", SqlDbType.NVarChar).Value = nod; 
cmd.Parameters.Add("@HolidayName", SqlDbType.NVarChar).Value = orderHolidays; 
cmd.Parameters.Add("@SelectedHolidayName", SqlDbType.NVarChar).Value = noOrderHolidays; 
cmd.Parameters.Add("@OrderDays", SqlDbType.NVarChar).Value = od;

这些循环尝试在每次迭代时将新项目重新添加到Parameters集合，这意味着从任何一个参数第一次到达它的第二次迭代开始，参数数量就会错误。

此外，我们在循环的底部执行此操作：

connection.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();

这样的紧密循环是我们不必每次都重新创建并重新打开连接的情况。我们开工吧：

private void SaveGlobalOrderDays(IEnumerable sessionList, IEnumerable selectedOrderHolidays, IEnumerable selectedHolidays, IEnumerable orderDays, IEnumerable noOrderDays)
{
    try
    {
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        using (SqlCommand cmd = new SqlCommand("[dbo].[SaveGlobalOrderDays]", connection))
        {
            cmd.CommandTimeout = 600;
            cmd.CommandType = CommandType.StoredProcedure;

            //Set up parameter placeholders once, before any looping starts.
            // From here on out we'll only ever set their .Value properties.
            cmd.Parameters.Add("@SessionId", SqlDbType.Int);
            //Add a Length to these!
            // If the target columns really are nvarchar(max), use -1
            cmd.Parameters.Add("@HolidayName", SqlDbType.NVarChar, -1); 
            cmd.Parameters.Add("@SelectedHolidayName", SqlDbType.NVarChar, -1);
            cmd.Parameters.Add("@OrderDays", SqlDbType.NVarChar, -1);
            cmd.Parameters.Add("@NoOrderDays", SqlDbType.NVarChar, -1);


            //Open the connection just once, immediately before beginning of the loop.
            // The using block will ensure it is closed later.
            connection.Open(); 
            foreach (SessionInfoList session in sessionList)
            {
                cmd.Parameters["@SessionId"].Value = session.SessionID;

                cmd.Paramters["@HolidayName"].Value = DBNull.Value;
                string joinedNames = string.Join(",", selectedOrderHolidays.Select(h => h.Name.Trim()));
                if (!string.IsNullOrEmpty(joinedNames))
                    cmd.Paramters["@HolidayName"].Value = joinedNames;

                cmd.Paramters["@SelectedHolidayName"].Value = DBNull.Value;
                joinedNames = string.Join(",", selectedHolidays.Select(h => h.Name.Trim()));
                if (!string.IsNullOrEmpty(orderHolidays))
                    cmd.Paramters["@SelectedHolidayName"].Value = joinedNames;

                cmd.Paramters["@OrderDays"].Value = DBNull.Value;
                joinedNames = string.Join(",", orderDays);
                if (!string.IsNullOrEmpty(joinedNames))
                    cmd.Paramters["@OrderDays"].Value = joinedNames;

                cmd.Paramters["@NoOrderDays"].Value = DBNull.Value;
                joinedNames = string.Join(",", noOrderDays);
                if (!string.IsNullOrEmpty(joinedNames))
                    cmd.Paramters["@NoOrderDays"].Value = joinedNames;

                //Now there's no need to Open() or Dispose() anything at this point.
                cmd.ExecuteNonQuery();
            }
        }
        // Consider using a separate Try/Catch inside the loop.
        // ... unless you want one failure to abort everything part way through, with some updates completed and some not.
        catch (Exception ex)
        {
            string message = "Failed to save global order info";
            // Logger.Error(LogSource, "SaveGlobalOrderDays", string.Empty, message, string.Empty, ex);
            throw new Exception(message, ex);
        }
    }

最后，这看起来像是在各个列中设置逗号分隔的值。这真的是糟糕的架构设计！切勿将逗号分隔的数据放在单个列中。将这些列放入其他表中可能会做得更好。

1> Joel Coehoor..：

我们有这个循环：

foreach (SessionInfoList session in sessionList)
{

在循环中，我们有以下参数：

cmd.Parameters.Add("@SessionId", SqlDbType.Int).Value = session.SessionID;

并有条件地将其他条件包含在循环中：

cmd.Parameters.Add("@NoOrderDays", SqlDbType.NVarChar).Value = nod; 
cmd.Parameters.Add("@HolidayName", SqlDbType.NVarChar).Value = orderHolidays; 
cmd.Parameters.Add("@SelectedHolidayName", SqlDbType.NVarChar).Value = noOrderHolidays; 
cmd.Parameters.Add("@OrderDays", SqlDbType.NVarChar).Value = od;

这些循环尝试在每次迭代时将新项目重新添加到Parameters集合，这意味着从任何一个参数第一次到达它的第二次迭代开始，参数数量就会错误。

此外，我们在循环的底部执行此操作：

connection.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();

这样的紧密循环是我们不必每次都重新创建并重新打开连接的情况。我们开工吧：

private void SaveGlobalOrderDays(IEnumerable sessionList, IEnumerable selectedOrderHolidays, IEnumerable selectedHolidays, IEnumerable orderDays, IEnumerable noOrderDays)
{
    try
    {
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        using (SqlCommand cmd = new SqlCommand("[dbo].[SaveGlobalOrderDays]", connection))
        {
            cmd.CommandTimeout = 600;
            cmd.CommandType = CommandType.StoredProcedure;

            //Set up parameter placeholders once, before any looping starts.
            // From here on out we'll only ever set their .Value properties.
            cmd.Parameters.Add("@SessionId", SqlDbType.Int);
            //Add a Length to these!
            // If the target columns really are nvarchar(max), use -1
            cmd.Parameters.Add("@HolidayName", SqlDbType.NVarChar, -1); 
            cmd.Parameters.Add("@SelectedHolidayName", SqlDbType.NVarChar, -1);
            cmd.Parameters.Add("@OrderDays", SqlDbType.NVarChar, -1);
            cmd.Parameters.Add("@NoOrderDays", SqlDbType.NVarChar, -1);


            //Open the connection just once, immediately before beginning of the loop.
            // The using block will ensure it is closed later.
            connection.Open(); 
            foreach (SessionInfoList session in sessionList)
            {
                cmd.Parameters["@SessionId"].Value = session.SessionID;

                cmd.Paramters["@HolidayName"].Value = DBNull.Value;
                string joinedNames = string.Join(",", selectedOrderHolidays.Select(h => h.Name.Trim()));
                if (!string.IsNullOrEmpty(joinedNames))
                    cmd.Paramters["@HolidayName"].Value = joinedNames;

                cmd.Paramters["@SelectedHolidayName"].Value = DBNull.Value;
                joinedNames = string.Join(",", selectedHolidays.Select(h => h.Name.Trim()));
                if (!string.IsNullOrEmpty(orderHolidays))
                    cmd.Paramters["@SelectedHolidayName"].Value = joinedNames;

                cmd.Paramters["@OrderDays"].Value = DBNull.Value;
                joinedNames = string.Join(",", orderDays);
                if (!string.IsNullOrEmpty(joinedNames))
                    cmd.Paramters["@OrderDays"].Value = joinedNames;

                cmd.Paramters["@NoOrderDays"].Value = DBNull.Value;
                joinedNames = string.Join(",", noOrderDays);
                if (!string.IsNullOrEmpty(joinedNames))
                    cmd.Paramters["@NoOrderDays"].Value = joinedNames;

                //Now there's no need to Open() or Dispose() anything at this point.
                cmd.ExecuteNonQuery();
            }
        }
        // Consider using a separate Try/Catch inside the loop.
        // ... unless you want one failure to abort everything part way through, with some updates completed and some not.
        catch (Exception ex)
        {
            string message = "Failed to save global order info";
            // Logger.Error(LogSource, "SaveGlobalOrderDays", string.Empty, message, string.Empty, ex);
            throw new Exception(message, ex);
        }
    }

推荐阅读

程序员
反应器代码中的Facebook Developer API

如何解决《反应器代码中的FacebookDeveloperAPI》经验，为你挑选了1个好方法。 ... [详细]
程序员
在Visual Studio 2015社区中找不到Web应用程序模板

如何解决《在VisualStudio2015社区中找不到Web应用程序模板》经验，为你挑选了1个好方法。 ... [详细]
程序员
如何从Amazon S3下载文件？

如何解决《如何从AmazonS3下载文件？》经验，为你挑选了1个好方法。 ... [详细]
程序员
自Symfony 2.8生成捆绑包时,新选项询问了多个应用程序

如何解决《自Symfony2.8生成捆绑包时,新选项询问了多个应用程序》经验，为你挑选了1个好方法。 ... [详细]
程序员
如何仅为特定URL添加Spring Security验证码过滤器

如何解决《如何仅为特定URL添加SpringSecurity验证码过滤器》经验，为你挑选了1个好方法。 ... [详细]
程序员
哪种类型的修复级别是"DBCC CHECKDB(databasename,repair)"？

如何解决《哪种类型的修复级别是"DBCCCHECKDB(databasename,repair)"？》经验，为你挑选了0个好方法。 ... [详细]
程序员
扩展单应矩阵以防止失真

如何解决《扩展单应矩阵以防止失真》经验，为你挑选了0个好方法。 ... [详细]
程序员
如果int不是类,为什么int x = int(5)合法？

如何解决《如果int不是类,为什么intx=int(5)合法？》经验，为你挑选了1个好方法。 ... [详细]
程序员
Visual Studio 2015,无法更改新资源文件的访问修饰符

如何解决《VisualStudio2015,无法更改新资源文件的访问修饰符》经验，为你挑选了2个好方法。 ... [详细]
程序员
迭代不同的类型

如何解决《迭代不同的类型》经验，为你挑选了6个好方法。 ... [详细]
程序员
使用UIO驱动程序映射一对中断或较小的地址空间

如何解决《使用UIO驱动程序映射一对中断或较小的地址空间》经验，为你挑选了0个好方法。 ... [详细]
程序员
库存大楼

如何解决《库存大楼》经验，为你挑选了1个好方法。 ... [详细]
程序员
我应该有多少个分店？我怎么知道的？

如何解决《我应该有多少个分店？我怎么知道的？》经验，为你挑选了1个好方法。 ... [详细]
程序员
Angular和Internet Explorer 11 - 输入无法正常工作

如何解决《Angular和InternetExplorer11-输入无法正常工作》经验，为你挑选了0个好方法。 ... [详细]
程序员
WebApi - 传递值数组

如何解决《WebApi-传递值数组》经验，为你挑选了1个好方法。 ... [详细]
程序员
Haystack说"无法找到SearchResult的模型"

如何解决《Haystack说"无法找到SearchResult的模型"》经验，为你挑选了1个好方法。 ... [详细]
程序员
Ansible变量覆盖默认为另一个角色

如何解决《Ansible变量覆盖默认为另一个角色》经验，为你挑选了0个好方法。 ... [详细]
程序员
可以在目标c中使用swift中的数组简化概念吗？

如何解决《可以在目标c中使用swift中的数组简化概念吗？》经验，为你挑选了1个好方法。 ... [详细]
程序员
在wpf中显示所选列表框项的数据

如何解决《在wpf中显示所选列表框项的数据》经验，为你挑选了1个好方法。 ... [详细]
程序员
vb.net"if statement"包含加号

如何解决《vb.net"ifstatement"包含加号》经验，为你挑选了1个好方法。 ... [详细]

拾味湖

这个屌丝很懒，什么也没留下！

关注作者

Tags | 热门标签

RankList | 热门文章