Home » Questions » Computers [ Ask a new question ]

How do I connect to a database and loop over a recordset in C#?

How do I connect to a database and loop over a recordset in C#?

What's the simplest way to connect and query a database for a set of records in C#?

Asked by: Guest | Views: 375
Total answers/comments: 4
Guest [Entry]

"@Goyuix -- that's excellent for something written from memory.
tested it here -- found the connection wasn't opened. Otherwise very nice.

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""]);
}
}
}
}"
Guest [Entry]

"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);
}
}
}
}"
Guest [Entry]

"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:

MyDB db = new MyDB(""Data Source=..."");
var q = from db.MyTable
select c;
foreach (var c in q)
Console.WriteLine(c.MyField.ToString());"
Guest [Entry]

"This is an alternative way (DataReader is faster than this one):

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);"