Here in this article, I will show you how to connect a C# Windows Form Application with MS Access 2016 database.
Step 1:
Open MS Access, click on a Blank Desktop Database. Give the database name “dbSchool.accdb“. and then click Create.
Step 2: Now create a Table in database, You can name a table anything you want, here I named it “Student” . There are three columns in the table ID, FirstName and LastName like the following,
Step 3: Now open Visual Studio, start a new Windows Form Application and give any name you want.
Step 4: Now Drag and Drop database file from the Documents to the Project Directory folder.
Step 5:
Form Design
Step 6:
Set SelectionMode to FullRowSelect
and ReadOnly to True
Step 7: Write a Namespace for connectivity as in the following code snippet.
1 2 3 | using System.Data.OleDb; |
Step 8: Define global variables.
1 2 3 4 5 6 | OleDbConnection con; OleDbDataAdapter da; OleDbCommand cmd; DataSet ds; |
Step 9: Create a method for get the list of students.
1 2 3 4 5 6 7 8 9 10 11 12 | void GetStudent() { con = new OleDbConnection("Provider=Microsoft.ACE.Oledb.12.0;Data Source=dbSchool.accdb"); da = new OleDbDataAdapter("SELECT *FROM Student", con); ds = new DataSet(); con.Open(); da.Fill(ds, "Student"); dataGridView1.DataSource = ds.Tables["Student"]; con.Close(); } |
Step 10: Create source code for Insert Button
1 2 3 4 5 6 7 8 9 10 11 12 13 | private void btnInsert_Click(object sender, EventArgs e)//INSERT BUTTON { string query = "Insert into Student (FirstName,LastName) values (@fName,@lName)"; cmd = new OleDbCommand(query, con); cmd.Parameters.AddWithValue("@fName", txtFirstName.Text); cmd.Parameters.AddWithValue("@lName", txtLastName.Text); con.Open(); cmd.ExecuteNonQuery(); con.Close(); GetStudent(); } |
Step 11: Create source code for Delete Button
1 2 3 4 5 6 7 8 9 10 11 12 | private void btnDelete_Click(object sender, EventArgs e)//DELETE BUTTON { string query = "Delete From Student Where Id=@id"; cmd = new OleDbCommand(query, con); cmd.Parameters.AddWithValue("@id", dataGridView1.CurrentRow.Cells[0].Value); con.Open(); cmd.ExecuteNonQuery(); con.Close(); GetStudent(); } |
Step 12: Create source code for Update Button
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | private void btnUpdate_Click(object sender, EventArgs e)//UPDATE BUTTON { string query = "Update Student Set FirstName=@fName,LastName=@lName Where Id=@id"; cmd = new OleDbCommand(query, con); cmd.Parameters.AddWithValue("@ad", txtFirstName.Text); cmd.Parameters.AddWithValue("@soyad", txtLastName.Text); cmd.Parameters.AddWithValue("@id", Convert.ToInt32(txtId.Text)); con.Open(); cmd.ExecuteNonQuery(); con.Close(); GetStudent(); } |
Step 13: Create source code for datagridview_cellEnter event
1 2 3 4 5 6 7 8 | private void dataGridView1_CellEnter(object sender, DataGridViewCellEventArgs e) { txtId.Text = dataGridView1.CurrentRow.Cells[0].Value.ToString(); txtFirstName.Text = dataGridView1.CurrentRow.Cells[1].Value.ToString(); txtLastName.Text = dataGridView1.CurrentRow.Cells[2].Value.ToString(); } |
Step 14: Create source code for Form_Load event
1 2 3 4 5 6 | private void Form1_Load(object sender, EventArgs e) { GetStudent(); } |
Finally, finished the project.
Here are the all codes:
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 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 | using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using System.Data.OleDb; namespace WindowsFormsApplication3 { public partial class Form1 : Form { OleDbConnection con; OleDbDataAdapter da; OleDbCommand cmd; DataSet ds; public Form1() { InitializeComponent(); } void GetStudent() { con = new OleDbConnection("Provider=Microsoft.ACE.Oledb.12.0;Data Source=dbSchool.accdb"); da = new OleDbDataAdapter("SELECT *FROM Student", con); ds = new DataSet(); con.Open(); da.Fill(ds, "Student"); dataGridView1.DataSource = ds.Tables["Student"]; con.Close(); } private void Form1_Load(object sender, EventArgs e) { GetStudent(); } private void btnInsert_Click(object sender, EventArgs e)//INSERT BUTTON { string query = "Insert into Student (FirstName,LastName) values (@fName,@lName)"; cmd = new OleDbCommand(query, con); cmd.Parameters.AddWithValue("@fName", txtFirstName.Text); cmd.Parameters.AddWithValue("@lName", txtLastName.Text); con.Open(); cmd.ExecuteNonQuery(); con.Close(); GetStudent(); } private void btnDelete_Click(object sender, EventArgs e)//DELETE BUTTON { string query = "Delete From Student Where Id=@id"; cmd = new OleDbCommand(query, con); cmd.Parameters.AddWithValue("@id", dataGridView1.CurrentRow.Cells[0].Value); con.Open(); cmd.ExecuteNonQuery(); con.Close(); GetStudent(); } private void btnUpdate_Click(object sender, EventArgs e)//UPDATE BUTTON { string query = "Update Student Set FirstName=@fName,LastName=@lName Where Id=@id"; cmd = new OleDbCommand(query, con); cmd.Parameters.AddWithValue("@ad", txtFirstName.Text); cmd.Parameters.AddWithValue("@soyad", txtLastName.Text); cmd.Parameters.AddWithValue("@id", Convert.ToInt32(txtId.Text)); con.Open(); cmd.ExecuteNonQuery(); con.Close(); GetStudent(); } private void dataGridView1_CellEnter(object sender, DataGridViewCellEventArgs e) { txtId.Text = dataGridView1.CurrentRow.Cells[0].Value.ToString(); txtFirstName.Text = dataGridView1.CurrentRow.Cells[1].Value.ToString(); txtLastName.Text = dataGridView1.CurrentRow.Cells[2].Value.ToString(); } } } |
Output:
Hi!
The inserting works only with the insert button? If i write somebody in the grid, he is not int the access file open with Access.
If i insert sombody with insert button, he is in the table in the Access too. Why?