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


Translate

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


Toutes les réponses
  • Translate

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

  • Translate

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

  • Translate

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

  • Translate

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

  • Translate

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

  • Translate

    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.


  • Translate

    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
    }
    

  • Translate

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