Simply Connect to and Modify Database

Download OLE DB example - 16.11 KB

Introduction

I've searched all over the internet for a simple tutorial on how to connect to a database, and also modify it. All I found were tutorials that were far too complex, and just had way to much code. Here I offer a tutorial with only the code needed to connect and manipulate a database.

Using the Code

There is no exception handling within the code. That part will be left up to the ones using the tutorial for their own benefit. I just offer descriptions on how to complete certain processes.

// LOCATION OF THE DATABASE

private static string accessConn = 
  @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\HPC\Documents\MyDatabase.mdb";

// DEPENDING ON WHAT YOU WOULD LIKE TO DO WITH THE TABLE.
private static string accessSelect = "SELECT * FROM Table1";

// DATABASE OBJECTS
private static OleDbConnection conn;
private static OleDbCommand comm;
private static OleDbDataAdapter adapter;
private static DataSet ds;
private static DataTable dt;

// CONNECT TO THE DATABASE
conn = new OleDbConnection(accessConn);
conn.Open();

// CREATE THE COMMAND OBJECT
comm = new OleDbCommand(accessSelect, conn);

// CREATE THE DATABASE ADAPTER
adapter = new OleDbDataAdapter(accessSelect, conn);

// MAKE SURE THE ADAPTER CAN HANDLE ALL QUERY COMMANDS (SELECT, DELETE, ECT.)
new OleDbCommandBuilder(adapter);

// CREATE DATASET OBJECT
ds = new DataSet();

// FILL THE DATASET
adapter.Fill(ds, "Table1");

// CREATE A DATATABLE
DataTable dt = ds.Tables["Table1"];

// CREATE A NEW DATAROW AND ASSIGN VALUES TO THE DESIRED COLUMNS
DataRow dr = dt.NewRow();
dr["Column1"] = "TestInsertMethod";

// ADD THE NEW ROW TO THE DATATABLE
dt.Rows.Add(dr);

// UPDATE THE ADAPTER AND/OR UPDATE THE DATABASE
adapter.Update(dt);

// CLOSE THE CONNECTION
conn.Close();