我想要用 MSSQL 查询结果填充 DataTable，但是它完全无法工作，这让我非常生气。
当我使用 SqlDataReader 迭代结果并读取时，我可以得到结果，即使我尝试使用 SqlDataAdapter 填充 DataTable，查询也会在 SQL Profiler 中出现，但是没有返回任何数据。
我不知道我的代码出了什么问题，但也许你可以找出来：
try
{
// Global variables
var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
var textString = "Pasākums {0} sākas pēc {1}!";
var linkString = @"/Event/Index/{0}";
using (SqlConnection conn = new SqlConnection(connectionString))
{
// Set variables
var findIn24HrsEventsCmd = new SqlCommand(@"
SELECT adm.UserID, adm.EventID FROM [dbo].[EventAdmissions] AS adm WHERE EventID IN
(
SELECT EventID FROM [dbo].[Events]
WHERE DATEDIFF(hour, @date, StartTime) BETWEEN 0 AND 24
)
AND
(
SELECT COUNT(URL) FROM [dbo].[Notifications]
WHERE Type = 1 AND UserID = adm.UserID
AND URL LIKE '/Event/Index/'+CAST(adm.EventID AS VARCHAR(36))
) = 0", conn);
findIn24HrsEventsCmd.Parameters.Add(new SqlParameter("date", "2015-05-31 02:17:28.727"));
var test = new SqlCommand(@"SELECT * FROM [dbo].[EventAdmissions]", conn);
var findIn1HrEventsCmd = new SqlCommand(@"
SELECT adm.UserID, adm.EventID FROM [dbo].[EventAdmissions] AS adm WHERE EventID IN
(
SELECT EventID FROM [dbo].[Events]
WHERE DATEDIFF(minute, @date, StartTime) BETWEEN 0 AND 60
)
AND
(
SELECT COUNT(URL) FROM [dbo].[Notifications]
WHERE Type = 1 AND UserID = adm.UserID
AND URL LIKE '/Event/Index/'+CAST(adm.EventID AS VARCHAR(36))
) < 2", conn);
findIn1HrEventsCmd.Parameters.Add(new SqlParameter("date", "2015-05-31 02:17:28.727"));
var t = findIn1HrEventsCmd.CommandTimeout;
// Retrieve data
conn.Open();
log.Debug("Starting with the events that are on in an hour.");
// Do it first for evens within an hour
var oneHrDataAdapter = new SqlDataAdapter(test);
var oneHrDt = new DataTable();
oneHrDataAdapter.Fill(oneHrDt);
findIn1HrEventsCmd.Dispose();
findIn24HrsEventsCmd.Dispose();
oneHrDataAdapter.Dispose();
}
} catch (Exception e)
{
log.Fatal("Fatal error!" + e.Message);
}
请注意，我已经将复杂的查询替换为一个非常简单的测试查询，该查询在Management Studio和DataReader中肯定返回结果，但由于某种原因在DataTable中无法工作。请注意，它没有超时，服务器位于同一台机器上，查询运行时间最多可能只有1-2秒钟。
连接是有效的，因为如我之前所提到的，DataReader方法可以工作，并且也没有抛出任何异常。