C#如何实现返回SQL结果列表的方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18754688/
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
C# How to implement method that return list of SQL result?
提问by SuicideSheep
using (var connection = new SqlConnection("user id=xxx;" +
"password=xxx;server=xxx;" +
"Trusted_Connection=yes;" +
"database=xxx; " +
"connection timeout=30"))
{
connection.Open();
string sql = "SELECT * FROM testTable";
using (var command = new SqlCommand(sql, connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
.
.
.
.
.
}
}
}
}
The code above can be used to retrive the list of rows of records and display them in my C# console application but it will be very messy to be in Main method. I'm wondering how to handle this chunk of records and store them into a list or something by calling a method. Will it be possible in C# console application?
上面的代码可用于检索记录行列表并将它们显示在我的 C# 控制台应用程序中，但在 Main 方法中会非常混乱。我想知道如何处理这块记录并通过调用方法将它们存储到列表或其他东西中。在 C# 控制台应用程序中可以吗？
采纳答案by Karl Anderson
Not knowing what is in your database table, I will make up an example:
不知道你的数据库表里有什么，我举个例子：
Account class:
账户类：
public class Person
{
public string FirstName { get; set; }
public string LastName { get; set; }
public int Age { get; set; }
}
Now you can have your readerloop populate each Person, like this:
现在您可以让reader循环填充 each Person，如下所示：
while (reader.Read())
{
var person = new Person();
person.FirstName = reader["FirstName"].ToString();
person.LastName = reader["LastName"].ToString();
person.Age = Convert.ToInt32(reader["Age"]);
}
Finally, we want to build a list of Personobjects to return, like this:
最后，我们要构建一个Person要返回的对象列表，如下所示：
var listOfPerson = new List<Person>();
while (reader.Read())
{
var person = new Person();
person.FirstName = reader["FirstName"].ToString();
person.LastName = reader["LastName"].ToString();
person.Age = Convert.ToInt32(reader["Age"]);
listOfPerson.Add(person);
}
return listOfPerson;
Complete code:
完整代码：
public List<Person> LoadPeople()
{
var listOfPerson = new List<Person>();
using (var connection = new SqlConnection("user id=xxx;" +
"password=xxx;server=xxx;" +
"Trusted_Connection=yes;" +
"database=xxx; " +
"connection timeout=30"))
{
connection.Open();
string sql = "SELECT * FROM testTable";
using (var command = new SqlCommand(sql, connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var person = new Person();
person.FirstName = reader["FirstName"].ToString();
person.LastName = reader["LastName"].ToString();
person.Age = Convert.ToInt32(reader["Age"]);
listOfPerson.Add(person);
}
}
}
}
return listOfPerson;
}
回答by Damith
You can create custom class with the properties what you want and then in each record you can create object of it and add it to a list. if you want to print any results given in sql statement then you can do as below.
您可以使用所需的属性创建自定义类，然后在每个记录中创建它的对象并将其添加到列表中。如果要打印 sql 语句中给出的任何结果，则可以执行以下操作。
using (var connection = new SqlConnection(connectionString))
using (var adapter = new SqlDataAdapter(sql, connection))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
foreach (DataRow row in dt.Rows)
{
Console.WriteLine("--- Row ---");
foreach (var item in row.ItemArray)
{
Console.Write("Item: ");
Console.WriteLine(item);
}
}
}
回答by Pyromancer
The Read() Method reads the data (table) you are accessing so, you can save it to a list.
Read() 方法读取您正在访问的数据（表），因此您可以将其保存到列表中。
here I suppose that the data in the rows are strings
这里我假设行中的数据是字符串
list<string> firstRow = new list<string>;
list<string> secondRow = new list<string>;
//in your while(reader.Read()) code . . .
while (reader.Read())
{
firstRow.Add(reader[0].ToString());
secondRow.Add(reader[1].ToString());
}
Hope this helps.
希望这可以帮助。
