如何传递字符串列表的sql参数以删除存储过程?

贾登·亚当斯(Jaden Adams)

我有以下方法,应该从作为参数传入的列表的给定数据库中删除假日名称。我遇到的问题是它没有从数据库中删除任何内容。这是我遇到问题的部分方法:

private void RemoveGloOrderDays(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].[RemoveGlobalOrderDays]";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = connection;
                    cmd.Parameters.Add("@SessionId", SqlDbType.Int);
                    cmd.Parameters.Add("@SelectedOrderHolidays", SqlDbType.NVarChar);
                    cmd.Parameters.Add("@SelectedHolidays", SqlDbType.NVarChar);
                    cmd.Parameters.Add("@OrderDays", SqlDbType.NVarChar);
                    cmd.Parameters.Add("@NoOrderDays", SqlDbType.NVarChar);

                    connection.Open();

                    foreach (SessionInfoList session in sessionList)
                    {
                        cmd.Parameters["@SessionId"].Value = session.SessionID;

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

                        cmd.Parameters["@SelectedHolidays"].Value = DBNull.Value;
                        joinedNames = string.Join(",", selectedHolidays.Select(h => h.Name.Trim()));
                        if (!string.IsNullOrEmpty(joinedNames))
                        {
                            cmd.Parameters["@SelectedHolidays"].Value = joinedNames;
                        }

这是我的存储过程:

IF OBJECT_ID('[dbo].[RemoveGlobalOrderDays]') IS NOT NULL
DROP PROCEDURE [dbo].[RemoveGlobalOrderDays]
GO
CREATE PROCEDURE [dbo].[RemoveGlobalOrderDays]
@SessionId int,
@SelectedHolidays nvarchar(500),
@SelectedOrderHolidays nvarchar(500),
@OrderDays nvarchar(500),
@NoOrderDays nvarchar(500)


WITH ENCRYPTION
AS
    BEGIN
    SET NOCOUNT ON;

    UPDATE [cfgSchedule]
    SET
        [OrderDays] = @OrderDays,
        [NoOrderDays] = @NoOrderDays
    WHERE [cfgSchedule].[SessionId] = @SessionID

    DELETE FROM [SessionHolidayMapping]
    WHERE [HolidayName] = @SelectedHolidays
    AND
    [SessionId] = @SessionId

    DELETE FROM [SessionOrderHolidayMapping]
    WHERE [SessionId] = @SessionId
    AND
    [HolidayName] = @SelectedOrderHolidays

END
GO
内曼娅·托多罗维奇(Nemanja Todorovic)

据我所知,您正在传递以逗号分隔的名称列表,并且您想删除所有这些名称。您需要使用IN运算符来查找所有应删除的假日名称。

这是一个针对@SelectedHolidays的示例:

declare @SelectedHolidays nvarchar(500) = 'H1,H2,H3'
declare @SelectedHolidaysXml xml = cast(replace(N'<R><I>' + @SelectedHolidays + N'</I></R>', ',', '</I><I>') as xml)


DELETE FROM [SessionHolidayMapping]
WHERE [HolidayName] in (select x.items.value('(.)[1]', 'NVARCHAR(500)') from @SelectedHolidaysXml.nodes('/R/I') as x(items))
AND [SessionId] = @SessionId

这很丑陋,但是我不知道在sql server中分割逗号分隔值的更好方法。

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章