9 Ağustos 2016 Salı

c# access database

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 clickTest 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 :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
//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.