2009/01/25

C#: How to load Excel (xls) and Access (mdb) files (updated: 2012.07.25)

Here is how I load the excel and access files:
1.Includes the name space
using System.Data.OleDb;
2.Set up the connection string
//for xls
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog1.FileName + @";Persist Security Info=True;Extended Properties=EXCEL 8.0";
//for mdb
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog1.FileName + @";Persist Security Info=True";
3.Get the schema
private void GetSchemaTable()
{
  try
  {
    //mdb and xls needs OleDbConnection class, not SqlConnection.
    using (OleDbConnection conn = new OleDbConnection(ConnectionString))
    {
      conn.Open();
      //Fetch all tables' schema. This line is the key point of this method.
      DataTable schemaTable =
        conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

      //Fetch all tables' name and assign them to comboBox1.
      foreach (DataRow dr in schemaTable.Rows)
      {
        comboBox1.Items.Add(dr[2]);
      }
    }
  }
  catch (Exception ex)
  {
    MessageBox.Show(ex.Message);
  }
}
4.Load table contents
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
  try
  {
    using (OleDbConnection conn = new OleDbConnection(ConnectionString))
    {
      if (String.Equals(FileExtName, "xls"))
        SelectCommand = "SELECT * FROM [" + comboBox1.SelectedItem.ToString() + "]";
      else if (String.Equals(FileExtName, "mdb"))
        SelectCommand = "SELECT * FROM " + comboBox1.SelectedItem.ToString();

      OleDbDataAdapter da = new OleDbDataAdapter(SelectCommand, conn);
      DataTable dt = new DataTable();
      da.Fill(dt);
      dataGridView1.DataSource = dt;
    }
  }
  catch (Exception ex)
  {
    MessageBox.Show(ex.Message);
  }
}

Updated: 2012.07.25
Using parameters:
using (OleDbConnection cnn = new OleDbConnection(
    ConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString))
{
    cnn.Open();
    using (OleDbCommand cmd = new OleDbCommand("Select * From " + comboBox1.SelectedItem + " Where ID = ?", cnn))
    {
        cmd.Parameters.AddWithValue("memberID", mID);
        OleDbDataReader reader;
        reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            ....
        }
    }
}
1. Use the question mark (?) to represent the parameter you want to add.
2. When AddWithValue(), the parameter name doesn't matter. The only thing matter is that if the column type you want to filter is an int (as my example here), the value you gave (mID) has to be able to convert to an int. Otherwise, you will get an error.

4 comments:

  1. I know a lot of other types of files. But one of them,such as access files I din't know what to do next. Yesterday something happened with its. And I used the next software - Recovery Access. The utiltiy solved my issue for seconds and for free as far as I remembered.

    ReplyDelete
  2. Thanks for let me know about this.

    ReplyDelete
  3. Kenny,
    I can not access you proof of concept file... can you please check the link?

    ReplyDelete
  4. Zack,
    I have updated the link.

    ReplyDelete