存储过程使用 SqlDataReader 在 Sql 中正确返回结果，但在 C# 中返回结果不正确

Question

SQL Server table：

CREATE TABLE [dbo].[MessageSubscribers] 
(
[SubscriberId]     INT IDENTITY (1, 1) NOT NULL,
[SubscriberCode]   NVARCHAR (MAX)  NULL,
[SubscriberName]   NVARCHAR(MAX) NOT NULL,
[SubscriberActiveDate] DATETIME NOT NULL,
[SubscriberExpiryDate] DATETIME NULL,
[SubscriberEndpoint]   NVARCHAR (MAX) NOT NULL,
[TopicId]              INT NOT NULL,
[EventId]              INT NOT NULL,
CONSTRAINT [PK_MessageSubscribers] PRIMARY KEY ([SubscriberId])
);

存储过程：

CREATE PROCEDURE [dbo].[FetchSubscriberDetails_BasedonTopicEvent]
@eventid int,
@topicid int,
@subscribers NVARCHAR(max) = null
AS 
BEGIN
SET NOCOUNT ON
/** Fetching Subscriber Details for all Subscribers based on Topic and Event**/
SELECT DISTINCT  
s.SubscriberCode, s.SubscriberName,
s.SubscriberActiveDate, s.SubscriberExpiryDate,
s.SubscriberEndpoint
FROM
([dbo].[MessageSubscribers] s 
WHERE 
s.TopicId = @topicid 
AND s.EventId = @eventid
AND CURRENT_TIMESTAMP BETWEEN s.SubscriberActiveDate AND ISNULL(s.SubscriberExpiryDate,CURRENT_TIMESTAMP)
AND (s.SubscriberCode IS NULL OR s.SubscriberCode = '' 
OR s.SubscriberCode IN (SELECT [value] FROM STRING_SPLIT(@subscribers, ',')))
END

示例数据：

SubscriberId -- 1
SubscriberCode --   100
Subscriber Name --  Google
ActiveDate --   01-01-2022
ExpiryDate  -- NULL
Endpoint --     https://..1
TopicId --  1
EventId --  1
SubscriberId -- 2
SubscriberCode --   200
Subscriber Name --  Microsoft
ActiveDate --   01-01-2022
ExpiryDate  -- NULL
Endpoint --     https://..2
TopicId --  1
EventId --  1
SubscriberId -- 3
SubscriberCode -- NULL
Subscriber Name --  Wipro
ActiveDate --   01-01-2022
ExpiryDate  -- NULL
Endpoint --     https://..3
TopicId --  1
EventId --  1

C# 代码：

public async Task<List<Subscriber>> Fetch_SubscribersDetails(int topicid, int eventid, string subscribers)
{
log.LogInformation($"Fetch_SubscribersDetails: Started -- Retrieving Subscriber details  from database based on topicid: {topicid}, eventid: {eventid} subscribers: {subscribers}.");
List<Subscriber> subscribersDetails = new List<Subscriber>();
subscribersDetails.Clear();
try
{
string sqlconnectionString = SharedMethods.GetEnvironmentVariable("SqlConnectionString");
SqlConnectionStringBuilder sbstr = new SqlConnectionStringBuilder(sqlconnectionString);
sbstr.Password = SharedMethods.GetEnvironmentVariable("DbSQLPassword");
using (SqlConnection conn = new SqlConnection(sbstr.ConnectionString))
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = Constants.FetchSubscribersbasedonTopicEventStorProcName;
// Setting command timeout to 1 minute
cmd.CommandTimeout = 60;
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.Add($"@topicid", SqlDbType.Int).Value = topicid;
//cmd.Parameters.Add($"@eventid", SqlDbType.Int).Value = eventid;
//cmd.Parameters.Add($"@subscribers", SqlDbType.NVarChar).Value = subscribers.Trim();
cmd.Parameters.AddWithValue($"@topicid", topicid);
cmd.Parameters.AddWithValue($"@eventid", eventid);
cmd.Parameters.AddWithValue($"@subscribers", subscribers.Trim());
cmd.Connection = conn;
await conn.OpenAsync();
var reader = await cmd.ExecuteReaderAsync();
if (await reader.ReadAsync())
{
//subscribersDetails = await GenerateSubscribersDataAsync(reader);
while (await reader.ReadAsync())
{
Subscriber subscriber = new Subscriber();
subscriber.SubscriberCode = Convert.ToString(reader.GetValue(reader.GetOrdinal("SubscriberCode")));
subscriber.SubscriberEndpoint = Convert.ToString(reader.GetValue(reader.GetOrdinal("SubscriberEndpoint")));
subscriber.SubscriberName = Convert.ToString(reader.GetValue(reader.GetOrdinal("SubscriberName")));
subscriber.TopicName = Convert.ToString(reader.GetValue(reader.GetOrdinal("TopicName")));
subscriber.EventName = Convert.ToString(reader.GetValue(reader.GetOrdinal("EventName")));
subscriber.SubscriberActiveDate = Convert.ToDateTime(reader.GetValue(reader.GetOrdinal("SubscriberActiveDate")));
if (!reader.IsDBNull(reader.GetOrdinal("SubscriberExpiryDate")))
{
subscriber.SubscriberExpiryDate = Convert.ToDateTime(reader.GetValue(reader.GetOrdinal("SubscriberExpiryDate")));
}
subscribersDetails.Add(subscriber);
}
}
_logger.LogInformation($"Fetch_SubscribersDetails: Completed -- Retrieving Subscriber details  from database based on topicid: {topicid}, eventid: {eventid} subscribers: {subscribers} , TotalSubscribers: {subscribersDetails.Count}.");
return subscribersDetails;
}
}
catch (SqlException ex)
{
_logger.LogError($"Message: {ex.Message}. nInnerException:{ex.InnerException}. nStackTrace: {ex.StackTrace}. nInnerExceptionMessage:{ex.InnerException?.Message}.");
subscribersDetails = null;
}
catch (Exception ex)
{
subscribersDetails = null;
_logger.LogError($"Message: {ex.Message}. nInnerException:{ex.InnerException}. nStackTrace: {ex.StackTrace}. nInnerExceptionMessage:{ex.InnerException?.Message}.");
}
return subscribersDetails;
}
public class Subscriber
{
public string SubscriberCode { get; set; }
public string SubscriberName { get; set; }
public string SubscriberEndpoint { get; set; }
public string TopicName { get; set; }
public string EventName { get; set; }
public int TopicId { get; set; }
public int EventId { get; set; }
public DateTime SubscriberActiveDate { get; set; }
public DateTime? SubscriberExpiryDate { get; set; }
}

SubscriberCode列可能为 NULL 或包含数据。subscribers作为逗号分隔符值传递的数据。

情况 #1：如果传递了subscribers数据，则存储过程应返回匹配的订阅者详细信息，以及基于主题 ID 和事件 ID 的 subscrbercode 为空的位置

exec FetchSubscriberDetails_BasedonTopicEvent @topicid =1,@eventid = 1, @subscribers = '100,200' 

在上面的示例中，所有 3 条记录都必须返回，但只有 SubscriberId=1 和 SubcriberId=2 记录出现在 c# 中，而不是 SubscriberId =3

情况 #2：如果未传递subscribers数据，则仅应根据主题 ID 和事件 ID 返回 subscrbercode 空记录。

exec FetchSubscriberDetails_BasedonTopicEvent @topicid =1,@eventid = 1, @subscribers = '' 

在上面的示例中，只应返回 1 条订阅者 ID = 3 行数据的记录。

执行时，这在 Azure SQL 中工作正常，但在使用 C# 代码运行时，我没有得到订阅者代码为 NULL 的正确记录。

C# 代码应返回 SubscriberId = 3 记录，但该记录未发生。

案例 #2 的预期输出：

SubscriberId -- 3
SubscriberCode -- NULL
Subscriber Name --  Wipro
ActiveDate --   01-01-2022
ExpiryDate  -- NULL
Endpoint --     https://..3
TopicId --  1
EventId --  1

案例 #2 的实际输出：

No records found

我尝试在行数据中用空白的空文本替换NULL。这也对我没有帮助。我做错了什么？

Answer 1

你的代码中有很多缺陷

• 需要using

SqlCommand cmd = new SqlCommand();

• 还需要using

var reader = await cmd.ExecuteReaderAsync();

• 明确指定参数类型和长度/精度

cmd.Parameters.AddWithValue($"@subscribers", subscribers.Trim());

而是做

cmd.Parameters.Add("@subscribers", SqlDbType.NVarchar, -1).Value = subscribers.Trim();

• 这是您的主要问题。这根本不应该存在，它导致您始终跳过第一行。

if (await reader.ReadAsync())

• 这个非常复杂，完全没有必要

Convert.ToString(reader.GetValue(reader.GetOrdinal("SubscriberCode")))

而是这样做

(string)reader["SubscriberCode"]

或者，如果您可能会得到空值，请执行此操作

reader["SubscriberCode"] as string

---

理想情况下，您根本不会使用逗号分隔的列表。请改用表值参数。

首先定义一个表类型。我保留了一些标准的。

CREATE TYPE dbo.StringList AS TABLE (value nvarchar(100) PRIMARY KEY);

CREATE PROCEDURE [dbo].[FetchSubscriberDetails_BasedonTopicEvent]
@eventid int,
@topicid int,
@subscribers dbo.StringList READONLY,
@noSubscriber bit
AS 
BEGIN
/** Fetching Subscriber Details for all Subscribers based on Topic and Event**/
SELECT DISTINCT  
s.SubscriberCode, s.SubscriberName,
s.SubscriberActiveDate, s.SubscriberExpiryDate,
s.SubscriberEndpoint
FROM
[dbo].[MessageSubscribers] s 
WHERE 
s.TopicId = @topicid 
AND s.EventId = @eventid
AND CURRENT_TIMESTAMP BETWEEN s.SubscriberActiveDate AND ISNULL(s.SubscriberExpiryDate, CURRENT_TIMESTAMP)
AND (@noSubscriber = 1
OR s.SubscriberCode IN (SELECT [value] FROM @subscribers);

在 C# 中使用它，如下所示：

var table = new DataTable { Columns = {
{"value", typeof(string)}
} };
foreach (var subscriber in SomeSubscriberList)
table.Rows.Add(subscriber);
cmd.Parameters.Add(new SqlParameter("@subscribers", SqlDbType.Structured) {
TypeName = "dbo.StringList",
Value = table
});

还有为什么要使用DISTINCT，它能给你带来什么？为什么你首先有重复项。