c# - 更新语句通过 C# Winform 应用程序更新 0 行？

Question

首先，请帮帮我!我再也受不了了。我找不到错误所在。这是我的问题:

我正在尝试通过 c# winform 应用程序更新一行。从应用程序生成的更新查询格式正确。我在sql server环境下测试过，效果不错。当我从应用程序运行它时，我更新了 0 行。

这是使用反射生成更新语句的片段 - 不要试图弄明白。代码部分后继续阅读:

        public void Update(int cusID)
        {
            SqlCommand objSqlCommand = new SqlCommand();
            Customer cust = new Customer();

            string SQL = null;

            try
            {
                if ((cusID != 0))
                {
                        foreach (PropertyInfo PropertyItem in this.GetType().GetProperties())
                        {
                            if (!(PropertyItem.Name.ToString() == cust.PKName))
                            {
                                if (PropertyItem.Name.ToString() != "TableName")
                                {
                                    if (SQL == null)
                                    {
                                        SQL = PropertyItem.Name.ToString() + " = @" + PropertyItem.Name.ToString();
                                    }
                                    else
                                    {
                                        SQL = SQL + ", " + PropertyItem.Name.ToString() + " = @" + PropertyItem.Name.ToString();
                                    }
                                 }
                                 else
                                 {
                                    break;
                                 }
                            }
                       }

                        objSqlCommand.CommandText = "UPDATE " + this.TableName + " SET " + SQL + " WHERE " + cust.PKName + " = @cusID AND PhoneNumber = " + "'" + "@phNum" + "'";

                        foreach (PropertyInfo PropertyItem in this.GetType().GetProperties())
                        {
                            if (!(PropertyItem.Name.ToString() == cust.PKName))
                            {
                                if (PropertyItem.Name.ToString() != "TableName")
                                {
                                    objSqlCommand.Parameters.AddWithValue("@" + PropertyItem.Name.ToString(), PropertyItem.GetValue(this, null));
                                }
                                else
                                {
                                    break;
                                }

                            }
                        }

                        objSqlCommand.Parameters.AddWithValue("@cusID", cusID);
                        objSqlCommand.Parameters.AddWithValue("@phNum", this.PhoneNumber);
                        DAL.ExecuteSQL(objSqlCommand);
                }
                else
                {
                    //AppEventLog.AddWarning("Primary Key is not provided for Update.")
                }

            }
            catch (Exception ex)
            {
                //AppEventLog.AddError(ex.Message.ToString)
            }
        }

下面这部分:

objSqlCommand.CommandText = "UPDATE " + this.TableName + " SET " + SQL + " WHERE " + cust.PKName + " = @cusID AND PhoneNumber = " + "'" + "@phNum" + "'";

生成 dml:

UPDATE CustomerPhone SET PhoneTypeID = @PhoneTypeID, PhoneNumber = @PhoneNumber WHERE CustomerID = @cusID AND PhoneNumber = '@phNum'

@PhoneTypeID 和@PhoneNumber 是从两个属性中获取的。我们从用户输入文本框中将值分配给表示层中的这些属性。下面的部分获取值:

objSqlCommand.Parameters.AddWithValue("@" + PropertyItem.Name.ToString(), PropertyItem.GetValue(this, null));

下面的代码填充了 WHERE 的值:

                    objSqlCommand.Parameters.AddWithValue("@cusID", cusID);
                    objSqlCommand.Parameters.AddWithValue("@phNum", this.PhoneNumber);

最终代码应如下所示:

UPDATE CustomerPhone 
SET PhoneTypeID = 7, PhoneNumber = 999444
WHERE CustomerID = 500 AND PhoneNumber = '911';

电话类型 ID 为 7 - 从文本框中获取的用户值电话号码是 999444 - 从文本框中获取的用户值

上面最后的update语句在sql环境下是有效的，但是运行时通过应用程序，执行非查询运行正常并更新了 0 行!我想知道为什么？

Answer 1

问题是:

AND PhoneNumber = '@phNum'

它正在寻找一个电话号码，它恰好是文本 '@phNum' - 它不使用名为 phNum 的参数。你要

AND PhoneNumber = @phNum

您还无缘无故地分解了字符串文字。这个声明:

objSqlCommand.CommandText = "UPDATE " + this.TableName + " SET " + SQL + 
    " WHERE " + cust.PKName + " = @cusID AND PhoneNumber = " + 
    "'" + "@phNum" + "'";

将更易于阅读:

objSqlCommand.CommandText = "UPDATE " + this.TableName + " SET " + SQL +
    " WHERE " cust.PKName + " = @cusID AND PhoneNumber = '@phNum'";

显然你想从中去掉单引号，让它只是:

objSqlCommand.CommandText = "UPDATE " + this.TableName + " SET " + SQL +
    " WHERE " cust.PKName + " = @cusID AND PhoneNumber = @phNum";

一点重构也不会出错。这个循环:

foreach (PropertyInfo PropertyItem in this.GetType().GetProperties())
{
    if (!(PropertyItem.Name.ToString() == cust.PKName))
    {
        if (PropertyItem.Name.ToString() != "TableName")
        {
            if (SQL == null)
            {
                SQL = PropertyItem.Name.ToString() + " = @" + PropertyItem.Name.ToString();
            }
            else
            {
                SQL = SQL + ", " + PropertyItem.Name.ToString() + " = @" + PropertyItem.Name.ToString();
            }
         }
         else
         {
            break;
         }
    }

这样会更简单、更易读:

StringBuilder sqlBuilder = new StringBuilder();
foreach (PropertyInfo property in this.GetType().GetProperties())
{
    string name = property.Name;
    // I believe you had a bug before - the properties being updated
    // would depend on the ordering of the properties - if it
    // ran into "TableName" first, it would exit early!
    // I *suspect* this is what you want
    if (name != cust.PKName && name != "TableName")
    {
        sqlBuilder.AppendFormat("{0} = @{0}, ", name);
    }
}
// Remove the trailing ", "
if (sqlBuilder.Length > 0)
{
    sqlBuilder.Length -= 2;
}

您也可以对最终循环执行类似的操作。