Home > Articles > Programming > C#

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

Using ADO.NET

ADO.NET as implemented in C# consists of a fairly large variety of interrelated objects. Since the operations we want to perform are still the same relatively simple ones, the Façade pattern will be an ideal way to manage them.

  • OleDbConnection This object represents the actual connection to the database. You can keep an instance of this class available but open and close the connection as needed. You must specifically close it when you are done, before it is garbage collected.

  • OleDbCommand This class represents an SQL command you send to the database, which may or may not return results.

  • OleDbDataAdapter Provides a bridge for moving data between a database and a local DataSet. You can specify an OleDbCommand, a Dataset, and a connection.

  • DataSet A representation of one or more database tables or results from a query on your local machine.

  • DataTable A single data table from a database or query.

  • DataRow A single row in a DataTable.

Connecting to a Database

To connect to a database, you specify a connection string in the constructor for the database you want to use. For example, for an Access database, your connection string would be the following.

 string connectionString = 
    "Provider=Microsoft.Jet.OLEDB.4.0;" + 
    "Data Source=" + dbName; 

The following makes the actual connection.

 OleDbConnection conn = 
    new OleDbConnection(connectionString); 

You actually open that connection by calling the open method. To make sure that you don't reopen an already open connection, you can check its state first.

 private void openConnection() { 
    if (conn.State == ConnectionState.Closed){ 
       conn.Open (); 
    } 
 } 

Reading Data from a Database Table

To read data in from a database table, you create an ADOCommand with the appropriate Select statement and connection.

 public DataTable openTable (string tableName) { 
    OleDbDataAdapter adapter = new OleDbDataAdapter (); 
    DataTable dtable = null; 
    string query = "Select * from " + tableName; 
    adapter.SelectCommand = new OleDbCommand (query, conn); 

Then you create a dataset object into which to put the results.

 DataSet dset = new DataSet ("mydata"); 

Then you simply tell the command object to use the connection to fill the dataset. You must specify the name of the table to fill in the FillDataSet method, as is shown here.

 try { 
    openConnection(); 
    adapter.Fill (dset); 
 } 
 catch(Exception e) { 
    Console.WriteLine (e.Message ); 
 } 

The dataset then contains at least one table, and you can obtain it by index or by name and examine its contents.

 //get the table from the dataset 
 dtable = dset.Tables [0]; 

Executing a Query

Executing a Select query is exactly identical to the preceding code, except the query can be an SQL Select statement of any complexity. Here we show the steps wrapped in a try block in case there are SQL or other database errors.

 public DataTable openQuery(string query) { 
    OleDbDataAdapter dsCmd = new OleDbDataAdapter (); 
    DataSet dset = new DataSet (); 
 //create a dataset 
    DataTable dtable = null;   //declare a data table 
    try { 
       //create the command 
       dsCmd.SelectCommand = 
          new OleDbCommand(query, conn); 
    //open the connection 
       openConnection(); 
       //fill the dataset 
       dsCmd.Fill(dset, "mine"); 
       //get the table 
       dtable = dset.Tables[0]; 
       //always close it 
       closeConnection(); 
       //and return it 
       return dtable; 
       catch (Exception e) { 
           Console.WriteLine (e.Message); 
           return null; 
       } 
 } 

Deleting the Contents of a Table

You can delete the contents of a table using the Delete * from Table SQL statement. However, since this is not a Select command, and there is no local table to bridge to, you can simply use the ExecuteNonQuery method of the OleDb Command object.

 public void delete() { 
    //deletes entire table 
    conn = db.getConnection(); 
    openConn(); 
    if (conn.State == ConnectionState.Open ) { 
       OleDbCommand adcmd = 
       new OleDbCommand("Delete * from " + tableName, conn); 
       try{ 
           adcmd.ExecuteNonQuery(); 
           closeConn(); 
       } 
       catch (Exception e) { 
           Console.WriteLine (e.Message); 
       } 
    } 
  • + Share This
  • 🔖 Save To Your Account