显示类似字段的SqlDataReader

问题描述：

当我尝试在SqlDataReader的数据库中仅显示不同的值时，它仍显示重复的列。显示类似字段的SqlDataReader

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,

你试图添加你的SQL SELECT语句中有一个GROUP BY子句？ – abhi

[SQL注入警报]（http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx） - 你应该**不**连接你的SQL语句 - 使用**参数化查询**，而不是避免SQL注入 - 检查[小Bobby表]（https://xkcd.com/327/） –

我还没有尝试过GROUP BY子句，我会研究这一点。这是我在工作中使用的一个小型内部工具，所以我现在不担心注射，但谢谢！ – jsnhndrsn1985

答

当使用distinct创建查询时，它将为您提供所有选定列的独特组合。完成你想要的东西，它会更喜欢这个

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(); 
     } 
    }

然后重复别人

我会试一试。谢谢！ – jsnhndrsn1985

看来它不像我打开多个SqlDataReader reader = cmd.ExecuteReader（）;同时运行。我已经分离了SqlCommands并更新了变量，但告诉我原始阅读器需要关闭，不过简单地说明rdr.Close（）;也不起作用。 – jsnhndrsn1985

像这样做 SqlCommand cmd = new SqlCommand ..获取唯一状态的查询 con.Open（）;使用（SqlDataReader rdr = cmd.ExecuteReader（）） { while（rdr.Read（）） {this.STATE.Text = rdr [“State”]。ToString（）; } } 然后重复说明下一个城市，交换类型等 –

答

什么，当你更换+ = =用会怎么样？

它将只显示每个字段的值，并不显示任何其他唯一值， \t BOID：205,\t BEX：9205,\t NPA：205,\t NXX：995, – jsnhndrsn1985

答

假设您出于某种原因无法对数据库进行多重查询，并且结果数据集很小。如果你的服务器往返时间很长，或者你的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;

假定列是整数。你也可以实现文本仅更改一次，而不是突变的每一行与+ =

作为一个方面说明我建议使用子句中包扎连接/读卡器/命令IDisposables

显示类似字段的SqlDataReader

相关推荐