Connection With Ms-Access Database and C#

Introduction:

Here I will explain how to Connection With Ms-Access Database and C#.

Description:


This post will have a description of how to work with Office access and C#. This will cover the connection with C# and your office access file .mdb and normal CRUD operations.

Connection With Ms-Access DB Database and C#:

The following code provide a method that return an OleDbConnection object to you.

private static OleDbConnection GetConnection()

        {

            OleDbConnection oledbconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\manojkumar\Documents\manojkumar\Databases\college.accdb;");
            oledbconn.Open();
            return oledbconn;
        }

Let We have a product table in customer_Table.mdb file

                                             Fig: Customer_Table Table
Retrieve: Select Operation:

Now, we want to select the list of Customer_Table ( in the above fig) .

So using the connection we select an OleDbDataAdapter an eventually fill the customer_table object.

public void GetCustomerList()

        {

            OleDbConnection oledbconn = GetConnection();

            OleDbCommand oledbcmd = new OleDbCommand("select * from customer_table", oledbconn);

            OleDbDataAdapter da = new OleDbDataAdapter(oledbcmd);

            DataSet ds = new DataSet();

            da.Fill(ds);

            oledbconn.Close();

            dgcustomer.DataSource = ds.Tables[0];

        }

CREATE: Insert Operation:

We can insert easily:

private void button1_Click(object sender, EventArgs e)

        {

            try

            {

                OleDbConnection oledbconn = GetConnection();

                OleDbCommand oledbcmd = new OleDbCommand("insert into customer_table(FirstName,LastName,CreditLimit,State) values('" + txtFirstName.Text + "','" + txtLastName.Text + "'," + txtCreditLimit.Text + ",'" + txtState.Text + "')", oledbconn);

                int ResultValue = oledbcmd.ExecuteNonQuery();

                oledbconn.Close();

                if (ResultValue > 0)

                    GetCustomerList();

                Clear();

            }

            catch (OleDbException ox)

            {

                MessageBox.Show(ox.Message, "Sample Application");

            }

            catch (ArgumentException ax)

            {

                MessageBox.Show(ax.Message, "Sample Application");

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message, "Sample Application");

            }

        }

 After Insert Clear the Fields..

        private void Clear()

        {

            txtFirstName.Text = "";

            txtLastName.Text = "";

            txtCreditLimit.Text = "";

            txtState.Text = "";

        }

Finally we got a result form like this

I am sure you can help yourself with the Update and Delete operation.

Hope this will help.