SQLDATAREADER显示像字段一样

Question

当我尝试使用SqlDataReader中仅显示DB的不同值时，它仍在显示重复列。

string State = ddlState.SelectedItem.Value;
string City = ddlCities.SelectedItem.Text;
SqlConnection con = new SqlConnection("Data Source=--myDB--;Database=--MyconnectionString--");
SqlCommand cmd = new SqlCommand("Select distinct State, City, ExchangeType, Boid, Bex, Npa, Nxx from TAS where StateId=" + "'" + State + "'" + " AND City=" + "'" + City + "'", con);
if (!string.IsNullOrEmpty(ddlCities.SelectedItem.Text))
{
    exchangesheet.Visible = true;
    con.Open();
    SqlDataReader rdr = cmd.ExecuteReader();
    while (rdr.Read())
    {
        this.STATE.Text = rdr["State"].ToString();
        this.CITY.Text = rdr["City"].ToString();
        this.ExchangeType.Text = rdr["ExchangeType"].ToString();
        this.BOID.Text += rdr["Boid"].ToString() + ", ";
        this.BEX.Text += rdr["Bex"].ToString() + ", ";
        this.NPA.Text += rdr["Npa"].ToString() + ", ";
        this.NXX.Text += rdr["Nxx"].ToString() + ", ";
    }
    con.Close();
}
else
{
    exchangesheet.Visible = false;
}

所有带有+=的查询都会在标签中产生重复的数据，即使我仅指定了在SqlCommand查询中显示的不同数据。如果我没有正确解释，我深表歉意，但是任何帮助或指导都将不胜感激。

搜索特定状态时结果：

BOID: 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205,   
BEX: 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205,    
NPA: 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205,    
NXX: 212, 214, 226, 238, 244, 250, 251, 252, 254, 262, 263, 278, 279, 290, 298, 307, 313, 314, 320, 321, 322, 323, 324, 326, 328, 380, 397, 402, 403, 408, 414, 421, 423, 437, 444, 445, 453, 458, 502, 520, 533, 536, 545, 558, 583, 591, 592, 595, 599, 682, 714, 731, 733, 776, 780, 781, 783, 786, 787, 788, 791, 798, 801, 802, 803, 808, 822, 823, 824, 833, 836, 841, 854, 856, 868, 870, 871, 873, 876, 879, 913, 916, 917, 923, 925, 929, 930, 933, 939, 940, 941, 942, 943, 945, 949, 951, 956, 957, 967, 968, 969, 970, 972, 977, 978, 979, 980, 981, 982, 983, 985, 987, 988, 989, 991, 995,

Answer 1

进行不同的查询时，它将为您提供所有选定列的独特组合。要完成您想要的事情，它必须更像是这样

SqlCommand cmd = new SqlCommand("Select distinct State from TAS where StateId=" + "'" + State + "'" + " AND City=" + "'" + City + "'", con);
...code to collate all the unique values and add to appropriate label...
SqlCommand cmd = new SqlCommand("Select distinct City from TAS where StateId=" + "'" + State + "'" + " AND City=" + "'" + City + "'", con);
...code to collate all the unique values and add to appropriate label...
SqlCommand cmd = new SqlCommand("Select distinct Exchange from TAS where StateId=" + "'" + State + "'" + " AND City=" + "'" + City + "'", con);
...code to collate all the unique values and add to appropriate label...

等。

还有许多其他方法可以完成同一件事

对于重复多个查询，您可以使用大致类似此类查询

SqlCommand cmd = new SqlCommand..the query for getting unique state
con.Open();
using ( SqlDataReader rdr = cmd.ExecuteReader())
  {
    while (rdr.Read())
      {
          this.STATE.Text = rdr["State"].ToString();
       }
    }

然后重复其他

Answer 2

当您替换 = =？

Answer 3

假设由于某种原因，您无法对DB进行多个查询，并且结果数据集很小。如果您的服务器往返很长，或者您的DBA会讨厌您使用6张桌子扫描等，这可能会更快。您可以执行代码中的实际不同。

        con.Open();
        var hsNxx = new HashSet<int>();
        var hsBex = new HashSet<int>();
        using (SqlDataReader rdr = cmd.ExecuteReader())
        {

            while (rdr.Read())
            {
                hsBex.Add((int)rdr["Bex"]);
                hsNxx.Add((int)rdr["Nxx"]);
            }
        }
        con.Close();
        string distinctBex =hsBex.Count == 1 ? hsBex.First().ToString() : hsBex.Select(f => f.ToString()).Aggregate((x, y) => x + "," + y);
        this.BEX.Text = distinctBex;

假定列是ints。您还可以实现仅更改一次的文本，而不会使用 =

突变每行

作为旁注，我建议在使用条款