Working with MS-Office Access Database(.mdb) and C#.

This post will have a description of how to work with Office access and C#. This will cover the connection with C# and your office access file .mdb and normal CRUD operations.

Connection With Ms-Access DB Database and C#:

The following code provide a method that return an OleDbConnection object to you.

//Crecte and Return a OleDbConnection obj.
      private static OleDbConnection GetConnection()
      {
          OleDbConnection conn = new OleDbConnection();
          try
          {
              String connectionString = @"Provider=Microsoft.JET.OlEDB.4.0;"
             + @"Data Source=D:\Product1.mdb";
              conn = new OleDbConnection(connectionString);
              conn.Open();
          }
          catch (Exception e)
          {
              Console.WriteLine(e.Message);
          }
          return conn;
      }

Here, you should change the “Data Source” to the mdf file location you intended to work with.

Also there should be way to close the open connection, so, we have a method to close the connection safely.

private static void CloseConnection(OleDbConnection conn)
       {
           try
           {
               conn.Close();
           }
           catch (Exception e)
           {
               Console.WriteLine(e.Message);
           }
       }

So as he have a connection, we can proceed to CRUD operations.

Let We have a product table in product.mdb file:

Fig: ProductDetails Table

Also a DTO created representing a product from the above table:

Retrieve: Select Operation:

Now, we want to select the list of Product ( a DTO in the above fig) .

So using the connection we select an OleDbDataAdapter an eventually fill the DTO or Product class object.

//Return List<Product>
       public static List<Product> GetProducts()
       {
           List<Product> productList = new List<Product>();

           DataSet ds = new DataSet();
           OleDbConnection conn = GetConnection();
           OleDbDataAdapter da = new OleDbDataAdapter("Select * from ProductDetails", conn);
           da.Fill(ds);
           conn.Close();
           DataTable dt = ds.Tables[0];
           foreach (DataRow rows in dt.Rows)
           {
               Product product = new Product();
               product.ProductID = int.Parse(rows["ProductId"].ToString());
               product.ProductName = rows["ProductName"].ToString();
               product.CompanyName = rows["CompanyName"].ToString();
               product.Price = rows["Price"].ToString();
               product.ProductAvailability = rows["ProductAvailability"].ToString();
               product.ProductType = rows["ProductType"].ToString();
               product.ProductDescription = rows["Description"].ToString();
               product.ImageUrl = rows["ImageUrl"].ToString();
               productList.Add(product);
           }
           return productList;
       }

CREATE: Insert Operation:

We can insert easily:

public static void InsertProductToCart(Product product)
       {

           OleDbConnection conn = GetConnection();
           String MyString = @"INSERT INTO ProductCart(ProductId, ProductName, Price, CompanyName, Description, ProductType, ProductAvailability, SessionId)
           VALUES(" + product.ProductID + ",'" + product.ProductName + "',"+ product.Price + ",'" + product.CompanyName + "','"
                     + product.ProductDescription + "','" + product.ProductType + "','"
                     + product.ProductAvailability + "','" + "your sessionId" + "')";
           OleDbCommand command = new OleDbCommand(MyString, conn);            
           command.ExecuteNonQuery();
           conn.Close();
       }

DELETE Operation

public static void DeleteProductFromCartByIndex(Int64 index, string sessionId)
      {

          OleDbConnection conn = GetConnection();
          String MyString = @"DELETE FROM ProductCart WHERE Id = "+ index + " and SessionId = '" + sessionId +"'";
          OleDbCommand command = new OleDbCommand(MyString, conn);
          command.ExecuteNonQuery();
          conn.Close();
      }

I am sure you can help yourself with the UPDATE operation.

Hope this will help.

NB: MS ACCESS 2007 use another file type other than MDB. So, this connection may not work for Access 2007. One option to work with this might be saving the file compatible with Access 2003.