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

Question

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

Answer 1

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

Answer 2

@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"]);
            }
        }
    }
}

Answer 3

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

Answer 4

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

Answer 5

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.SqlClient namespace (SqlConnection, SqlCommand and SqlDataReader) as those classes have been optimized to work with 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.

Answer 6

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());
  }
}