C# with Access DAtabases?

[Azel Low]

I am trying to grasp how to use select, update, insert and delete command. I tried reading from some books Programming C# by Oreilly but it just doesnt really explain it in detail. I was hoping if anyone can point me to some article (don't seem to have them at code project) where i can learn how to perform the above operations on MS ACCESS databases. thanks

[Mike Dimmick]

Look at OleDbConnection or OdbcConnection (Framework 1.1, some people have indicated that ODBC seems to perform better than OLE DB when invoked from C#). Simple example:

using ( OleDbConnection conn = new OleDbConnection() )
{
conn.ConnectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"User ID=Admin;" +
@"Data Source=FPNWIND.MDB;" +
@"Mode=Read|Share Deny Read|Share Deny Write";

using ( OleDbCommand cmd = new OleDbCommand() )
{
cmd.CommandText =
"SELECT CategoryName FROM Categories ORDER BY CategoryID";
cmd.Connection = conn;

  conn.Open();

  OleDbDataReader rdr = cmd.ExecuteReader();

  try
  {
     while ( rdr.Read() )
     {
        System.Console.WriteLine( 
           "Category: {0}",
           rdr.GetString( 0 ) );
     }
  }
  finally
  {
     rdr.Close();
  }

}
}

To try this, create a new C# console application, paste into Main and add using System.Data; using System.Data.OleDb; at the top. This code opens the FPNWIND.MDB database (supplied with Office 2000) and outputs a list of all known categories to the console. For simple manipulations of data, use OleDbCommand's Execute_Xxx_ functions. If you don't expect any results (for example, if you're running an INSERT, UPDATE or DELETE statement), use ExecuteNonQuery. If you only want a single data value (the first column of the first row of the results), use ExecuteScalar. If you have an OleDbDataReader open on a connection, you can only scroll forwards through the data, and you cannot modify the data. You cannot do anything else with that connection until you have either read all the data (Read returns false) or have closed the reader. ADO.NET does not have an equivalent of classic ADO's live update of recordsets. The closest is essentially a batch mode involving a DataSet (an offline cache of one or more DataTables) and an OleDbDataAdapter. DataSet and DataTable are general classes; you use a DataAdapter from the appropriate provider to fill the data set or table and reflect any modifications back to the data source.