c# - SqlException：过程或函数指定的参数过多

Question

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

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

这就是我目前所拥有的，我需要做什么才能不出现异常？

private void SaveGlobalOrderDays(List<SessionInfoList> sessionList, List<Holiday> selectedOrderHolidays, List<Holiday> selectedHolidays, List<string> orderDays, List<string> 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  

标签： c#sqlasp.net-coreado.net

Answer 1

我们有这个循环：

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<SessionInfoList> sessionList, IEnumerable<Holiday> selectedOrderHolidays, IEnumerable<Holiday> selectedHolidays, IEnumerable<string> orderDays, IEnumerable<string> 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);
        }
    }

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