C# SqlDataReader 读入 List<string>
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19299935/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SqlDataReader to read into List<string>
提问by reallybadatmath
I am writing a method in C# to query a SQL Server Express database from a WCF service. I have to use ADO.NET to do this (then rewrite it with LINQ later on).
我正在用 C# 编写一个方法来从 WCF 服务查询 SQL Server Express 数据库。我必须使用 ADO.NET 来做到这一点（然后用 LINQ 重写它）。
The method takes two strings (fname, lname) then returns a "Health Insurance NO" attribute from the matching record. I want to read this into a list (there are some other attribs to retrieve as well).
该方法采用两个字符串 ( fname, lname)，然后从匹配的记录中返回“健康保险号”属性。我想将其读入一个列表（还有一些其他属性要检索）。
The current code returns an empty list. Where am I going wrong?
当前代码返回一个空列表。我哪里错了？
public List<string> GetPatientInfo(string fname, string lname)
{
string connString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\xxxx\Documents\Visual Studio 2010\Projects\ADOWebApp\ADOWebApp\App_Data\ADODatabase.mdf;Integrated Security=True;User Instance=True";
SqlConnection conn = new SqlConnection(connString);
string sqlquery = "SELECT Patient.* FROM Patient WHERE ([First Name] = '"+fname+"') AND ([Last Name] = '"+lname+"')";
SqlCommand command = new SqlCommand(sqlquery, conn);
DataTable dt = new DataTable();
List<string> result = new List<string>();
using (conn)
{
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader != null && reader.Read())
{
dt.Load(reader);
result.Add(Convert.ToString(reader["Health Insurance NO"]));
}
}
}
return result;
}
采纳答案by Tim Schmelter
You are trying to load a DataTablevia DataTable.Load>in a loop<. You just need that once. You're also using reader.Read()in the loop. SqlDataReader.Read()advances the reader to the next record without to consume it. If you're going to use DataTable.Loadyou don't need to read the reader first. So you just have to remove the loop completely to load the table.
您正在尝试加载一个DataTablevia DataTable.Load>in a loop<。你只需要一次。您也在reader.Read()循环中使用。SqlDataReader.Read()将读取器推进到下一条记录而不消耗它。如果您要使用DataTable.Load，则无需先阅读阅读器。所以你只需要完全删除循环来加载表。
But since you want to return a list you don't need the DataTableat all, just loop the reader:
但是由于您想返回一个根本不需要的列表DataTable，只需循环阅读器即可：
List<string> result = new List<string>();
using (conn)
{
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while(reader.Read())
{
result.Add(Convert.ToString(reader["Health Insurance NO"]));
}
}
}
Apart from that, you are open for sql-injection without sql-parameters.
除此之外，您可以在没有 sql 参数的情况下进行 sql 注入。
回答by Jamie
I would do it this way:
我会这样做：
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
dt.Load(reader);
}
foreach (var row in dt.AsEnumerable())
{
result.Add(row["Health Insurance NO"].ToString());
}
