Step by Step Connect to Access Database in C# Tutorial

For some reason I must back to code in C# for a while, the problem I just have to solve is how to connect my C# application to my Access Database. I have been search and found many solution out there and today I want to share step by step how easy it to be done. Here we go 😉 . At first simply create Access database (e.g.  book.mdb) like picture below, their column name and data type is ID (Autonumber), bookName (Text), description (Text).

After that save your database and create connection parameter which will be use in the code. Now open notepad.exe , then simply save as it to mdsaputra.udl , don’t forget to change Save as type into All Files (*.*).

Now close your notepad and double click mdsaputra.udl which you just created. You gonna see wizard like picture below :

Click Provider Tab (1), select Microsof Jet 4.0 ILE DB Provider (2) then click Next (3). Now you are in Connection Tab.

Click … /browse button (1) , select book.mdb (2), open (3) and then click Test Connection, if you do everything properly you must see  Test Connection Succeed like picture below :

Click OK, and now reopen mdsaputra.udl with notepad, you gonna see some provider properties like shown below :

Text I highlight above is provider properties that we gonna need in our code, for now just close it. We are done with database and it connection things, now open your Visual Studio and create Visual C#, Windows Forms Application. Fill and arrange your form with Label, TextBox, Button and DataGridView like shown below.

Double click your Insert Button and Show All button to auto create Event Handler method. Now, inside your Form.cs add code that highlighted and shown below :

//author EtaYuy | mdsaputra.wordpress.com | Meihta Dwiguna Saputra

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace TutorialConnectToAccessDB
{
    public partial class FormMain : Form
    {
        private OleDbConnection bookConn;
        private OleDbCommand oleDbCmd = new OleDbCommand();
        //parameter from mdsaputra.udl
        private String connParam = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\EtaYuy\Documents\book.mdb;Persist Security Info=False";

        public FormMain()
        {
            //create connection using parameter from mdsaputra.udl
            bookConn = new OleDbConnection(connParam);
            InitializeComponent();
        }

        private void buttonInsert_Click(object sender, EventArgs e)
        {
            bookConn.Open();
            oleDbCmd.Connection = bookConn;
            oleDbCmd.CommandText = "insert into book (bookName, description) values ('" + this.textBoxBookName.Text + "','" + this.textBoxDescription.Text + "');";
            int temp = oleDbCmd.ExecuteNonQuery();
            if (temp > 0)
            {
                textBoxBookName.Text = null;
                textBoxDescription.Text = null;
                MessageBox.Show("Record Successfuly Added");
            }
            else
            {
                MessageBox.Show("Record Fail to Added");
            }
            bookConn.Close();
        }

        private void buttonShowAll_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = null;
            dataGridView1.Rows.Clear();
            dataGridView1.Refresh();

            OleDbDataAdapter dAdapter = new OleDbDataAdapter("select * from book", connParam);
            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);

            DataTable dataTable = new DataTable();
            DataSet ds = new DataSet();

            dAdapter.Fill(dataTable);

            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                dataGridView1.Rows.Add(dataTable.Rows[i][0], dataTable.Rows[i][1], dataTable.Rows[i][2]);
            }
        }
    }
}

See highlighted code above, that connParameter assigned by provider properties from mdsaputra.udl. Now everything is done, if you following my instruction carefully, the program should be able Insert new Record into book.mdb like shown below,

and also can show all data you have been insert into book.mdb like picture below.

Well, that’s all, hope the tutorial easy to understand, thanks for reading ;). You can download my working copy here.

Subscribe via twitter : Follow @etayuy

Passing Parameters from Main Report to Table’s datasource in iReport

Today, I have to make custom header for my report, this custom parameter is variable that stored in Map (which is named “year”) from my java code. Now what I have to do is passing this (year) Main Report parameter trough the Table’s datasource. By the way, I currently using iReport 3.7.4. Back to the topic, this is how to do it :

1. Expand Table Dataset 1, create parameter in Parameters, in my case I name it currentYear.
2. Right Click on your table, choose Edit table data source then select Parameters tab.
3. Click Add button, it will show you Add/modify parameter dialog.
4. Click on Dataset parameter name combo box and select your parameter (mine is currentYear).
5. Edit it Value expression to $P{year}.
6. Click OK until all dialog closed.

Now you are done. The rest you have to do is passed parameter. Don’t forget to compile 😉 .