C# 如何从 SQL 查询生成 List<String>？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12024226/
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
How to generate List<String> from SQL query?
提问by CJ7
If I have a DbCommanddefined to execute something like:
如果我有一个DbCommand定义来执行类似的东西：
SELECT Column1 FROM Table1
What is the best way to generate a List<String>of the returned records?
生成List<String>返回记录的最佳方法是什么？
No Linq etc. as I am using VS2005.
没有 Linq 等，因为我使用的是 VS2005。
回答by Chuck Callebs
I think this is what you're looking for.
我想这就是你要找的。
List<String> columnData = new List<String>();
using(SqlConnection connection = new SqlConnection("conn_string"))
{
connection.Open();
string query = "SELECT Column1 FROM Table1";
using(SqlCommand command = new SqlCommand(query, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
columnData.Add(reader.GetString(0));
}
}
}
}
Not tested, but this should work fine.
未经测试，但这应该可以正常工作。
回答by Shyju
Loop through the Items and Add to the Collection. You can use the Addmethod
循环遍历项目并添加到集合中。您可以使用该Add方法
List<string>items=new List<string>();
using (var con= new SqlConnection("yourConnectionStringHere")
{
string qry="SELECT Column1 FROM Table1";
var cmd= new SqlCommand(qry, con);
cmd.CommandType = CommandType.Text;
con.Open();
using (SqlDataReader objReader = cmd.ExecuteReader())
{
if (objReader.HasRows)
{
while (objReader.Read())
{
//I would also check for DB.Null here before reading the value.
string item= objReader.GetString(objReader.GetOrdinal("Column1"));
items.Add(item);
}
}
}
}
回答by Valen S
If you would like to query all columns
如果要查询所有列
List<Users> list_users = new List<Users>();
MySqlConnection cn = new MySqlConnection("connection");
MySqlCommand cm = new MySqlCommand("select * from users",cn);
try
{
cn.Open();
MySqlDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
list_users.Add(new Users(dr));
}
}
catch { /* error */ }
finally { cn.Close(); }
The User's constructor would do all the "dr.GetString(i)"
用户的构造函数将执行所有“dr.GetString(i)”
回答by Edward Knueppel Jr.
Where the data returned is a string; you could cast to a different data type:
其中返回的数据是一个字符串；您可以转换为不同的数据类型：
(from DataRow row in dataTable.Rows select row["columnName"].ToString()).ToList();
回答by Dave Martin
Or a nested List (okay, the OP was for a single column and this is for multiple columns..):
或者嵌套列表（好吧，OP 是针对单列的，而这是针对多列的……）：
//Base list is a list of fields, ie a data record
//Enclosing list is then a list of those records, ie the Result set
List<List<String>> ResultSet = new List<List<String>>();
using (SqlConnection connection =
new SqlConnection(connectionString))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(qString, connection);
// Create and execute the DataReader..
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var rec = new List<string>();
for (int i = 0; i <= reader.FieldCount-1; i++) //The mathematical formula for reading the next fields must be <=
{
rec.Add(reader.GetString(i));
}
ResultSet.Add(rec);
}
}
