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#?

score 35 · Accepted Answer · edited Feb 07 '16 at 00:43

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

score 17 · Answer 2 · edited Feb 07 '16 at 00:44

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

score 12 · Answer 3 · answered Aug 04 '08 at 04:45

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

score 7 · Answer 4 · answered Aug 05 '08 at 14:17

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

score 4 · Answer 5 · edited Dec 18 '19 at 12:26

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.

score 4 · Answer 6 · edited Feb 07 '16 at 00:44

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

score 1 · Answer 7 · edited Sep 14 '12 at 06:31

1

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
}

edited Sep 14 '12 at 06:31

Himanshu

31,810
31
111
133

answered Sep 05 '12 at 19:11

kril

233
2
5

score 0 · Answer 8 · edited Dec 18 '19 at 12:28

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

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

8 Answers8

Linked