如何连接到数据库并在 C# 中循环记录集？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/930/
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 do I connect to a database and loop over a recordset in C#?
提问by Michael Pryor
What's the simplest way to connect and query a database for a set of records in C#?
在 C# 中连接和查询数据库以获取一组记录的最简单方法是什么？
采纳答案by Leon Bambrick
@Goyuix -- that's excellent for something written from memory. tested it here -- found the connection wasn't opened. Otherwise very nice.
@Goyuix——这对于从记忆中写出的东西来说非常棒。在这里测试 - 发现连接没有打开。否则非常好。
using System.Data.OleDb;
...
using (OleDbConnection conn = new OleDbConnection())
{
conn.ConnectionString = "Provider=sqloledb;Data Source=yourServername\yourInstance;Initial Catalog=databaseName;Integrated Security=SSPI;";
using (OleDbCommand cmd = new OleDbCommand())
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "Select * from yourTable";
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Console.WriteLine(dr["columnName"]);
}
}
}
}
回答by Goyuix
Very roughly and from memory since I don't have code on this laptop:
由于我在这台笔记本电脑上没有代码，所以非常粗略地根据记忆：
using (OleDBConnection conn = new OleDbConnection())
{
conn.ConnectionString = "Whatever connection string";
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.Connection = conn;
cmd.CommandText = "Select * from CoolTable";
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
// do something like Console.WriteLine(dr["column name"] as String);
}
}
}
}
回答by MojoFilter
That's definitely a good way to do it. But you if you happen to be using a database that supports LINQ to SQL, it can be a lot more fun. It can look something like this:
这绝对是一个好方法。但是，如果您碰巧使用支持 LINQ to SQL 的数据库，那会更有趣。它看起来像这样：
MyDB db = new MyDB("Data Source=...");
var q = from db.MyTable
select c;
foreach (var c in q)
Console.WriteLine(c.MyField.ToString());
回答by Marek Grzenkowicz
This is an alternative way (DataReader is faster than this one):
这是另一种方式（DataReader 比这更快）：
string s = "";
SqlConnection conn = new SqlConnection("Server=192.168.1.1;Database=master;Connect Timeout=30;User ID=foobar;Password=raboof;");
SqlDataAdapter da = new SqlDataAdapter("SELECT TOP 5 name, dbid FROM sysdatabases", conn);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
s += dt.Rows[i]["name"].ToString() + " -- " + dt.Rows[i]["dbid"].ToString() + "\n";
}
MessageBox.Show(s);
回答by Christian Hagelid
If you are querying a SQL Server database (Version 7 and up) you should replace the OleDb classes with corresponding classes in the System.Data.SqlClientnamespace (SqlConnection, SqlCommandand SqlDataReader) as those classes have been optimized to work with SQL Server.
如果您要查询 SQL Server 数据库（版本 7 及更高版本），则应将 OleDb 类替换为System.Data.SqlClient命名空间（SqlConnection、SqlCommand和SqlDataReader）中的相应类，因为这些类已针对 SQL Server 进行了优化。
Another thing to note is that you should 'never' select all as this might lead to unexpected results later on if you add or remove columns to this table.
另一件要注意的事情是，您不应该“永远”全选，因为如果您在此表中添加或删除列，这可能会导致以后出现意外结果。
回答by DamienG
If you are intending on reading a large number of columns or records it's also worth caching the ordinals and accessing the strongly-typed methods, e.g.
如果您打算读取大量列或记录，则还值得缓存序数并访问强类型方法，例如
using (DbDataReader dr = cmd.ExecuteReader()) {
if (dr.Read()) {
int idxColumnName = dr.GetOrdinal("columnName");
int idxSomethingElse = dr.GetOrdinal("somethingElse");
do {
Console.WriteLine(dr.GetString(idxColumnName));
Console.WriteLine(dr.GetInt32(idxSomethingElse));
} while (dr.Read());
}
}
回答by kril
I guess, you can try entity framework.
我想，您可以尝试实体框架。
using (SchoolDBEntities ctx = new SchoolDBEntities())
{
IList<Course> courseList = ctx.GetCoursesByStudentId(1).ToList<Course>();
//do something with courselist here
}
回答by Josue Martinez
Charge the libraries
为图书馆充电
using MySql.Data.MySqlClient;
This is the connection:
这是连接：
public static MySqlConnection obtenerconexion()
{
string server = "Server";
string database = "Name_Database";
string Uid = "User";
string pwd = "Password";
MySqlConnection conect = new MySqlConnection("server = " + server + ";" + "database =" + database + ";" + "Uid =" + Uid + ";" + "pwd=" + pwd + ";");
try
{
conect.Open();
return conect;
}
catch (Exception)
{
MessageBox.Show("Error. Ask the administrator", "An error has occurred while trying to connect to the system", MessageBoxButtons.OK, MessageBoxIcon.Error);
return conect;
}
}
