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();

Points of Interest

When creating the Command Object, make sure to add all other command types as well if you wish to do more than to add to the datatable.

History

• 07/03/2010 - Initial version