Csharp/C Sharp/Database ADO.net/Access

Connects to a Microsoft Access database

using System;
using System.Data;
using System.Data.OleDb;
public class Connect {    
 public static void Main () { 
   String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Employee.mdb";
   OleDbConnection con = new OleDbConnection(connect);
   con.Open();
   Console.WriteLine("Made the connection to the Sales database");
   con.Close();
 }
}

Connect to an Access database file

using System;
using System.Data;
using System.Data.OleDb;
class AccessConnect {
   static void Main() 
   {
      string connString = @"provider = microsoft.jet.oledb.4.0;data source = Employee.mdb;";
      OleDbConnection conn = new OleDbConnection(connString);
      try 
      {
         conn.Open();
         Console.WriteLine("Connection opened.");
         Console.WriteLine("Connection Properties:");   
         Console.WriteLine("\tConnection String: {0}", conn.ConnectionString);    
         Console.WriteLine("\tDatabase: {0}",conn.Database);   
         Console.WriteLine("\tDataSource: {0}",conn.DataSource);
         Console.WriteLine("\tServerVersion: {0}", conn.ServerVersion);
         Console.WriteLine("\tState: {0}", conn.State);
      } catch (OleDbException e) {
         Console.WriteLine("Error: " + e);  
      } finally  {
         conn.Close();
         Console.WriteLine("Connection closed.");
      }
   }
}

How to use an OleDbConnection object to connect to an Access database

using System;
using System.Data;
using System.Data.OleDb;
class OleDbConnectionAccess {
  public static void Main() {
    string connectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source=.\\Northwind.mdb";
    OleDbConnection myOleDbConnection = new OleDbConnection(connectionString);
    OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand();
    myOleDbCommand.rumandText =
      "SELECT CustomerID, CompanyName, ContactName, Address "+
      "FROM Customers "+
      "WHERE CustomerID = "ALFKI"";
    myOleDbConnection.Open();
    OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader();
    myOleDbDataReader.Read();
    Console.WriteLine("myOleDbDataReader[\" CustomerID\"] = "+ myOleDbDataReader["CustomerID"]);
    Console.WriteLine("myOleDbDataReader[\" CompanyName\"] = "+ myOleDbDataReader["CompanyName"]);
    Console.WriteLine("myOleDbDataReader[\" ContactName\"] = "+ myOleDbDataReader["ContactName"]);
    Console.WriteLine("myOleDbDataReader[\" Address\"] = "+ myOleDbDataReader["Address"]);
    myOleDbDataReader.Close();
    myOleDbConnection.Close();
  }
}

Reading Access Data

 
using System;
using System.Data;           
using System.Data.OleDb;     
using System.Collections.Generic;
using System.Text;
class Program {
    static void Main(string[] args) {
        OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\nwind.mdb");
        thisConnection.Open();
        OleDbCommand thisCommand = thisConnection.CreateCommand();
        thisCommand.rumandText = "SELECT CustomerID, CompanyName FROM Customers";
        OleDbDataReader thisReader = thisCommand.ExecuteReader();
        while (thisReader.Read()) {
            Console.WriteLine("\t{0}\t{1}",thisReader["CustomerID"], thisReader["CompanyName"]);
        }
        thisReader.Close();
        thisConnection.Close();
    }
}

Read query result data from Access database

using System;
using System.Data;
using System.Data.OleDb;
   class CommandOleDbQuery
   {
      static void Main() 
      {
         OleDbConnection thisConnection = new OleDbConnection("provider = microsoft.jet.oledb.4.0;data source = Employee.mdb;");
      
         OleDbCommand thisCommand = new OleDbCommand("SELECT ID, FirstName FROM Employee",thisConnection);
         try 
         {
            thisConnection.Open();
            OleDbDataReader thisReader = thisCommand.ExecuteReader();
            while (thisReader.Read()) {
               Console.WriteLine("Product ID and Name: {0} {1}",
                  thisReader.GetValue(0),
                  thisReader.GetValue(1));
            }
         } 
         catch (OleDbException ex) 
         {
            Console.WriteLine(ex.ToString());
         }
         finally 
         {  
            thisConnection.Close();
            Console.WriteLine("Connection Closed.");
         }
      }
   }

Use OdbcCommand to read data in Access database and fill the DataGrid

 
using System;
using System.Diagnostics;
using System.Drawing;
using System.Collections;
using System.ruponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.Odbc;

public class Form1 : System.Windows.Forms.Form {
    private System.Windows.Forms.Button button1;
    private System.Windows.Forms.DataGrid dataGrid1;
    private System.Windows.Forms.Button button2;
    public Form1() {
        this.button1 = new System.Windows.Forms.Button();
        this.dataGrid1 = new System.Windows.Forms.DataGrid();
        this.button2 = new System.Windows.Forms.Button();
        ((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
        this.SuspendLayout();
        this.button1.Location = new System.Drawing.Point(8, 240);
        this.button1.Size = new System.Drawing.Size(104, 32);
        this.button1.Text = "Get Data From Biblio";
        this.button1.Click += new System.EventHandler(this.button1_Click);
        this.dataGrid1.DataMember = "";
        this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
        this.dataGrid1.Location = new System.Drawing.Point(8, 8);
        this.dataGrid1.Size = new System.Drawing.Size(280, 224);
        this.button2.Location = new System.Drawing.Point(120, 240);
        this.button2.Size = new System.Drawing.Size(152, 32);
        this.button2.Text = "Get Data From Access Northwind";
        this.button2.Click += new System.EventHandler(this.button2_Click);
        this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
        this.ClientSize = new System.Drawing.Size(292, 273);
        this.Controls.AddRange(new System.Windows.Forms.Control[] {
                                                                          this.button2,
                                                                          this.dataGrid1,
                                                                          this.button1});
        ((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
        this.ResumeLayout(false);
    }
    [STAThread]
    static void Main() {
        Application.Run(new Form1());
    }
    private OdbcConnection cn;
    private void button1_Click(object sender, System.EventArgs e) {
        try {
            cn = new OdbcConnection("DRIVER={SQL Server};SERVER=.;uid=admin;pwd=pw;database=biblio");
            cn.Open();
            OdbcCommand sc = new OdbcCommand("SELECT title, price FROM titles WHERE title LIKE "Hi%"", cn);
            OdbcDataAdapter da = new OdbcDataAdapter(sc);
            DataSet ds = new DataSet("TitlesDS");
            da.Fill(ds);
            dataGrid1.DataSource = ds.Tables[0];
        } catch (Exception ex) {
            Debug.WriteLine(ex.ToString());
        } finally {
            cn.Close();
        }
    }
    private void button2_Click(object sender, System.EventArgs e) {
        try {
            cn = new OdbcConnection(@"DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\NorthWind.mdb");
            cn.Open();
            OdbcCommand sc = new OdbcCommand("SELECT * from Employees", cn);
            OdbcDataAdapter da = new OdbcDataAdapter(sc);
            DataSet ds = new DataSet("TitlesDS");
            da.Fill(ds);
            dataGrid1.DataSource = ds.Tables[0];
        } catch (Exception ex) {
            MessageBox.Show("You probably need to change the file path to NorthWind.MDB used in the OdbcConnection Constructor\n or move a copy of the NorthWind.mdb to c:\\");
            Debug.WriteLine(ex.ToString());
        } finally {
            cn.Close();
        }
    }
}