views:

1429

answers:

6

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

+11  A: 

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);
      }
    }
  }
}
Goyuix
+16  A: 

@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"]);
}
}
}
}
Leon Bambrick
+3  A: 

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());
MojoFilter
+2  A: 

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);
Marek Grzenkowicz
+1  A: 

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.

Christian Hagelid
+2  A: 

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