c# - 如何将字符串列表传递给要删除的存储过程的sql参数?
问题描述
我有以下方法应该从作为参数传入的列表中给出的数据库中删除假日名称。我遇到的问题是它没有从数据库中删除任何内容。这是我遇到问题的方法的一部分:
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
解决方案
据我所知,您正在传递以逗号分隔的名称列表,并且您想删除所有这些名称。您需要使用 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 中拆分逗号分隔值的更好方法。
推荐阅读
- azure - 在功能 gitflow 分支上使用 VS2017 中的 Gitversiontask,修订不增加
- java - DP-Coin Change 结果为零
- android - Angular 7、Cordova、console.log(cordova.plugins) 在 android 上运行时未显示插件
- java - Maven 中的阴影依赖项的依赖项
- unity3d - 面板/精灵在设置为非活动然后重新激活后不会更新
- python - 当使用多个样本时,如何在 Keras 中使用多元时间序列预测
- oracle - 案例表达式的查询性能
- sql - 通过将总价值除以总价值来计算百分比
- google-chrome - 用于本地 Win10 原生应用和 Web 应用之间通信的自签名证书
- python - sqlalchemy.exc.ProgrammingError:(psycopg2.ProgrammingError)不能适应类型'dict'