Thursday, February 7, 2013

Access .mdb database file from C#

My newest project requires me to keep a database with the email addresses of my clients, which my program will read and write to. To avoid the hassle of setting up a SQL server, and also since i'm familiar with using Microsoft Access, i decided on going with a database-in-file solution.


This presented the problem of figuring out how to do all this in C#, and after a good bit of googling i hacked together a class that retrieved the contacts list from the .mdb file as a DataTable, that i could show in my DataGridView by setting it as the DataSource:

private void Form1_Load(object sender, EventArgs e)
        {
            // Populate the dataGridView with the database content
            dataGridView1.DataSource = mdb_reader_class.readContactsTable();
            // Set proper column sizes
            dataGridView1.Columns[0].Width = 30;
            dataGridView1.Columns[1].Width = 200;
            dataGridView1.Columns[2].Width = 230;

            // Dont allow the user to resize the form (it's perfect as is)
            this.FormBorderStyle = FormBorderStyle.FixedSingle;

            // This goes for maximize button as well
            this.MaximizeBox = false;
        }
The mdb_reader_class.readContactsTable method:


public static DataTable readContactsTable()
{
    try
    {
        // Variables that will be used 
        string AccessDB = "folder-path\\Contacts.mdb";
        DataTable MyDataTable = new DataTable();

        // using an ridiculously long connection string, 
        // only reluctant to change it since it works
        OleDbConnection lConn;
        lConn = new OleDbConnection(
        "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" +
        AccessDB + ";Mode=Share Deny None;Extended Properties='';
        Jet OLEDB:System database='';
        Jet OLEDB:Registry Path='';Jet OLEDB:Engine Type=4;
        Jet OLEDB:Database Locking Mode=0;
        Jet OLEDB:Global Partial Bulk Ops=2;
        Jet OLEDB:Global Bulk Transactions=1;
        Jet OLEDB:Create System Database=False;
        Jet OLEDB:Encrypt Database=False;
        Jet OLEDB:Don't Copy Locale on Compact=False;
        Jet OLEDB:Compact Without Replica Repair=False;
        Jet OLEDB:SFP=False"
        );

        // connect to database
        lConn.Open();

        // using SQL 
        // Where 'Contacts' is the name of the table in the MDB file
        string lSQL = "Select ID, Name, Email from Contacts";  
        
        //This assigns the Select statement and connection of the data adapter
        OleDbDataAdapter dadapt = new OleDbDataAdapter(lSQL, lConn);  
        //This builds the update and Delete-
        //queries for the table in the above SQL. 
        //this only works if the select is a single table.
        OleDbCommandBuilder cb = new OleDbCommandBuilder(dadapt);    

        // populate the dataTable
        dadapt.Fill(MyDataTable);

        //Then save to the MDB file 
        dadapt.Update(MyDataTable);
        lConn.Close();

        // then return the datatable
        //dataGridView1.DataSource = MyDataTable;
        return MyDataTable;
    }
    catch (Exception)
    {
        // if an exception is thrown, a empty table is returned,
        // with one column named "Error"
        DataTable errorTable = new DataTable();
        errorTable.Columns.Add("Error!", typeof(string));
        return errorTable;
    }
}