数据阅读器跳过第一个结果 [英] Datareader skips first result
问题描述
我有一个相当复杂的 SQL 查询，它根据客户 ID 从数据库中提取不同类型的产品.它提取了三种不同类型的产品，由其唯一标识符编号范围标识(即 ID 1000-1999 是一种产品，2000-2999 是另一种，3000-3999 是另一种).
I have a fairly complex SQL query that pulls different types of products from a database based on a customer ID. It pulls three different types of products, identified by their unique identifier number ranges (i.e., IDs 1000-1999 are one type of product, 2000-2999 are another, and 3000-3999 are yet another).
SELECT b.fldMachineName, m2.fldRotaryPressName, mids.fldMachine_ID
FROM dbo.tblCustomerGeneralInfo c
LEFT JOIN dbo.tblMachine_IDs mids ON c.fldCustomer_ID = mids.fldCustomer_ID
LEFT JOIN dbo.tblBobstFlatDieSpecs b ON mids.fldMachine_ID = b.fldMachine_ID
LEFT JOIN dbo.tblDieInfo m1 ON mids.fldMachine_ID = m1.fldMachine_ID
LEFT JOIN dbo.tblRotaryDieSpecs sm ON m1.fldMachine_ID = sm.fldMachine_ID
LEFT JOIN dbo.tblRotaryPresses m2 ON sm.fldRotaryPress_ID = m2.fldRotaryPress_ID
WHERE c.fldCustomer_ID = '20'
ORDER BY fldRotaryPressName
c.fldCustomer_ID 是我唯一需要的用户输入，当我在 SQL Server Management Studio Express 中针对数据库运行此查询时，它运行良好.但是，当我在使用 C# for SharePoint 编写的 Web 部件中将其包装在 using 语句中时，它不会返回它应该返回的第一行，而是返回一个空值.例如，如果在 SSMS 中我得到三个结果(例如，1001"、2008"和3045")，那么我的数据读取器将只返回两个结果，第一个结果为空值(即空"、2008"),' 和 '3045').这是我在 C# 中的代码:
c.fldCustomer_ID is the only piece of user input that I need, and when I run this query against the database in SQL Server Management Studio Express, it runs fine. However, when I wrap this in a using statement in the web part I am writing in C# for SharePoint, it does not return the first row that it should, instead returning a null value. For instance, if in SSMS I get three results (say, '1001,' '2008,' and 3045') then my datareader will return only two results, with a null value for the first (i.e., 'null,' '2008,' and '3045'). Here is my code in C#:
con.Open();
using (SqlCommand cmd = new SqlCommand("SELECT b.fldMachineName, m2.fldRotaryPressName, mids.fldMachine_ID " +
"FROM dbo.tblCustomerGeneralInfo c " +
"LEFT JOIN dbo.tblMachine_IDs mids ON c.fldCustomer_ID = mids.fldCustomer_ID " +
"LEFT JOIN dbo.tblBobstFlatDieSpecs b ON mids.fldMachine_ID = b.fldMachine_ID " +
"LEFT JOIN dbo.tblDieInfo m1 ON mids.fldMachine_ID = m1.fldMachine_ID " +
"LEFT JOIN dbo.tblRotaryDieSpecs sm ON m1.fldMachine_ID = sm.fldMachine_ID " +
"LEFT JOIN dbo.tblRotaryPresses m2 ON sm.fldRotaryPress_ID = m2.fldRotaryPress_ID " +
"WHERE c.fldCustomer_ID = @CustomerID " +
"ORDER BY fldRotaryPressName", con))
{
cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar, 4).Value = customers.SelectedValue.ToString();
我们的 SQL 探查器显示传递的相同查询，无论它是通过 SSMS 还是从 Web 部件完成，除了使用的文字与参数.此外，如果我将参数更改为文字，则会得到相同的结果.与 SSMS 相比，C# 处理 SQL 查询的方式是否存在差异，或者我是否以某种方式错误地实现了它?
Our SQL profiler shows the same query being passed regardless of if it's done through SSMS or from the web part, except for the literal being used versus the parameter. Also, if I change the parameter to a literal, I have the same result. Is there a discrepancy in the way that C# handles SQL queries as opposed to SSMS, or have I somehow implemented it incorrectly?
为了完整起见，这里是将数据从阅读器拉入下拉列表的代码:
Here is the code for pulling the data from the reader into the dropdownlist, for the sake of completeness:
dr.read();
while (dr.Read())
{
try
{
string itemValue = Convert.ToString(dr["fldMachine_ID"]);
string flatName = Convert.ToString(dr["fldMachineName"]);
if (!string.IsNullOrEmpty(flatName))
{
items.Add(flatName, itemValue);
}
string rotaryName = Convert.ToString(dr["fldRotaryPressName"]);
if (!string.IsNullOrEmpty(rotaryName))
{
items.Add(rotaryName, itemValue);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
// Bind list to ddl.
machines.DataSource = items;
machines.DataValueField = "Value";
machines.DataTextField = "Key";
machines.DataBind();
machines.Enabled = true;
}
我完全被难住了，我非常感谢我能得到的任何帮助.
I am completely stumped, and I really appreciate any help I can get.
推荐答案
更新
原来问题是在循环之前额外的 dr.Read() 调用.查看评论.
Turned out the problem was a extra dr.Read() call before the loop. See comments.
更新
看代码，似乎数据绑定在错误的地方——也许是这样的?另外，我将其更改为显示空项目...也许这会暴露逻辑问题.
Looking at the code, it seems the databind is in the wrong place -- maybe something like this? Also, I changed it to show the null items... maybe this will expose a logic problem.
while (dr.Read())
{
try
{
string itemValue = dr["fldMachine_ID"].ToString();
string flatName = dr["fldMachineName"].ToString();
if (string.IsNullOrEmpty(flatName)) flatName = "!NULL!";
if (string.IsNullOrEmpty(itemValue)) itemValue = "!NULL!";
items.Add(flatName, itemValue);
string rotaryName = dr["fldRotaryPressName"].ToString();
if (string.IsNullOrEmpty(rotaryName)) rotaryName= "!NULL!";
items.Add(rotaryName, itemValue);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
// Bind list to ddl.
machines.DataSource = items;
machines.DataValueField = "Value";
machines.DataTextField = "Key";
machines.DataBind();
machines.Enabled = true;
旧
会不会像 customers.SelectedValue.ToString().Trim() 那样愚蠢?
Could it be something silly like customers.SelectedValue.ToString().Trim()?
您可以运行分析器并准确查看服务器正在执行的 SQL...然后在 SSMS 中运行它以查看是否仍然得到不同的结果.
You can run the profiler and see EXACTLY the SQL that the server is executing... then run that in SSMS to see if you still get different results.
这篇关于数据阅读器跳过第一个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
