将 SQL 结果添加到对象列表时，所有记录都会被最新结果覆盖

Question

我在尝试将SQL查询的结果提取到C#中的对象列表中时遇到了问题。出于某种原因，它用最新的查询结果覆盖了所有行，我看不到我做错了什么。用于测试的 Console.WriteLine 返回正确的数据，以便查询运行正常。

我的代码是：

private void getData(string qry)
  {
      int count = 0;
      string strProject = "ARTUR-PC\SQLEXPRESS"; //Enter your SQL server instance name
      string strDatabase = "Northwind"; //Enter your database name
      string strUserID = "Artlemaks"; // Enter your SQL Server User Name
      string strPassword = "rootUser"; // Enter your SQL Server Password
      string strconn = "data source=" + strProject +
        ";Persist Security Info=false;database=" + strDatabase +
        ";user id=" + strUserID + ";password=" +
        strPassword + ";Connection Timeout = 0";
      //conn = new SqlConnection(strconn);
      using (SqlConnection connection = new SqlConnection(strconn))
      {
          List<CustomerObj> Customers = new List<CustomerObj>();
          connection.Open();
          SqlCommand cmd = connection.CreateCommand();
          cmd.CommandText = qry;
          cmd.ExecuteNonQuery();
          SqlDataReader reader = cmd.ExecuteReader();
          if (reader.HasRows)
          {
              while (reader.Read())
              {
                  row.custID = reader[0] as int?;
                  row.cmpName = reader[1] as string;
                  row.cntName = reader[2] as string;
                  row.cntTitle = reader[4] as string;
                  row.address = reader[5] as string;
                  row.city = reader[3] as string;
                  row.region = reader[6] as string;
                  row.postalCode = reader[7] as string;
                  row.country = reader[8] as string;
                  row.phone = reader[9] as string;
                  row.fax = reader[10] as string;
                  Console.WriteLine("{0}t{1}", reader[0],  reader[1]);
                  Customers.Add(row);
              }
          }
          reader.Close();
          connection.Close();
      }

Answer 1

我猜row是在函数外部定义的全局变量。 如果是这种情况，则始终会一遍又一遍地使用相同的行实例，因为该行是引用类型。

定义 while 循环中的行，因此您的代码应如下所示：

private void getData(string qry)
  {
      int count = 0;
      string strProject = "ARTUR-PC\SQLEXPRESS"; //Enter your SQL server instance name
      string strDatabase = "Northwind"; //Enter your database name
      string strUserID = "Artlemaks"; // Enter your SQL Server User Name
      string strPassword = "rootUser"; // Enter your SQL Server Password
      string strconn = "data source=" + strProject +
        ";Persist Security Info=false;database=" + strDatabase +
        ";user id=" + strUserID + ";password=" +
        strPassword + ";Connection Timeout = 0";
      //conn = new SqlConnection(strconn);
      using (SqlConnection connection = new SqlConnection(strconn))
      {
          List<CustomerObj> Customers = new List<CustomerObj>();
          connection.Open();
          SqlCommand cmd = connection.CreateCommand();
          cmd.CommandText = qry;
          cmd.ExecuteNonQuery();
          SqlDataReader reader = cmd.ExecuteReader();
          if (reader.HasRows)
          {
              while (reader.Read())
              {
                  CustomerObj row = new CustomerObj();
                  row.custID = reader[0] as int?;
                  row.cmpName = reader[1] as string;
                  row.cntName = reader[2] as string;
                  row.cntTitle = reader[4] as string;
                  row.address = reader[5] as string;
                  row.city = reader[3] as string;
                  row.region = reader[6] as string;
                  row.postalCode = reader[7] as string;
                  row.country = reader[8] as string;
                  row.phone = reader[9] as string;
                  row.fax = reader[10] as string;
                  Console.WriteLine("{0}t{1}", reader[0],  reader[1]);
                  Customers.Add(row);
              }
          }
          reader.Close();
          connection.Close();
      }

Answer 2

您在任何地方都使用相同的row对象实例，因此仅在此对象实例上设置值。相反，您需要初始化类的新实例，以便在每次循环迭代中保存新结果。

修复问题更改

while (reader.Read())
{
    row.custID = reader[0] as int?;

自

while (reader.Read())
{
    CustomerObj row = new CustomerObj();
    row.custID = reader[0] as int?;

并从声明的位置删除row声明。