MS Access (*.mdb) + C#: SELECT, INSERT, DELETE and UPDATE Queries

• Download demo VC# 2010 project - 61.4 KB

See Also

Same tip for SQL Server

Prepare to Run

1. Create a C# project, turn it platform in x86 from Any CPU and save it. Any CPU unsupported because Microsoft Jet Ole DB 4.0 (library that allows you integrate MDB files) has only x86 version. If you'll run you Any CPU application on x64 OS, your application will run in x64 mode, i.e. will can't use x86 libs.
2. Create database in MS Access, save it as Test.mdb name in path with YourApplication.vshost.exe file (probably it's bin\x86\Debug\ folder in your project directory).
3. Create Table_1 table in this database with 3 columns:
   • id (counter, key field)
   • int_col (int)
   • text_col (text)

Tip: it isn't recommended to use spaces in table or column names because it complicates the process of writing queries (you must use [ ] for names with spaces), and may cause troubles in OleDbCommandBuilder work if you use it to automatically generate INSERT, UPDATE, DELETE queries for DB changes saving.

Use "_" characters intead.

Connect Database

// it's your DB file path:
// ApplicationEXEPath\Test.mdb
var DBPath = Application.StartupPath + "\\Test.mdb";

conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;"
    + "Data Source=" + DBPath);
conn.Open();

Run INSERT Query

// txtInsert.Text:
// INSERT INTO Table_1 (text_col, int_col) VALUES ('Text', 9);
//
// inserts 1 row into Table_1 table
using (OleDbCommand cmd = new OleDbCommand(txtInsert.Text, conn))
{
    cmd.ExecuteNonQuery();
}

Run SELECT Query to Get Table Content into Grid

using (DataTable dt = new DataTable())
{
    // txtSelect.Text:
    // SELECT id, text_col, int_col FROM Table_1
    // or
    // SELECT * FROM Table_1
    //
    // selects all content from table and adds it to datatable binded to datagridview
    using (OleDbDataAdapter adapter = new OleDbDataAdapter(txtSelect.Text, conn))
    {
        adapter.Fill(dt);
    }
    dgvSelect.DataSource = dt;
}

Run UPDATE Query to Modify Row

// txtUpdate.Text:
// UPDATE Table_1 SET [text_col]='Updated text', [int_col]=2014 WHERE id=2;
//
// changes 2nd row in Table_1
using (OleDbCommand cmd = new OleDbCommand(txtUpdate.Text, conn))
{
    cmd.ExecuteNonQuery();
}

Run DELETE Query to Delete Row

// txtDelete.Text:
// DELETE FROM Table_1 WHERE id=2;
//
// removes 2nd row in Table_1
using (OleDbCommand cmd = new OleDbCommand(txtDelete.Text, conn))
{
    cmd.ExecuteNonQuery();
}

Security Diclaimer

...Yep, it is easest (for developer) and quickest way to develop DB client applications - to get SQL queries from TextBoxes.

But, not for user. Because it quite unconvenient, and very unsafe, because allows users use SQL injections to modify or remove DB content! 

Last - normal and even useful, if the database is used only a few people, and any "villain" does not have access to them. But in other cases, you should not do it!

To be continued...