我有一个相当复杂的 SQL 查询,它根据客户 ID 从数据库中提取不同类型的产品。它提取三种不同类型的产品,通过其唯一标识符编号范围进行标识(即 ID 1000-1999 是一种产品类型,2000-2999 是另一种产品类型,3000-3999 是另一种产品类型)。
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),那么我的数据读取器将仅返回两个结果,第一个结果为空值(即“null”、“2008”) 、”和“3045”)。这是我的 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 部件完成,除了所使用的文字与参数之外。另外,如果我将参数更改为文字,则会得到相同的结果。 C# 与 SSMS 处理 SQL 查询的方式是否存在差异,或者我是否以某种方式错误地实现了它?
为了完整起见,以下是将数据从阅读器拉入下拉列表的代码:
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;
}
我完全被难住了,我真的很感激我能得到的任何帮助。