Home > Articles > Programming > C#

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

This chapter is from the book

Making the ADO.NET Façade

In the Façade we will create for our grocery database, we start with an abstract DBase class that represents a connection to a database. This encapsulates making the connection and opening a table and an SQL query.

 public abstract class DBase   { 
    protected OleDbConnection conn; 
 private void openConnection() { 
    if (conn.State == ConnectionState.Closed){ 
       conn.Open (); 
    } 
 } 
 //------
 private void closeConnection() { 
    if (conn.State == ConnectionState.Open ){ 
       conn.Close (); 
    } 
 } 
 //------
 public DataTable openTable (string tableName) { 
    OleDbDataAdapter adapter = new OleDbDataAdapter (); 
    DataTable dtable = null; 
    string query = "Select * from " + tableName; 
    adapter.SelectCommand = new OleDbCommand (query, conn); 
    DataSet dset = new DataSet ("mydata"); 
    try { 
       openConnection(); 
       adapter.Fill (dset); 
       dtable = dset.Tables [0]; 
    } 
    catch(Exception e) { 
       Console.WriteLine (e.Message ); 
    } 
    return dtable; 
 } 
 //------
 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); 
       openConnection();  //open the connection 
       //fill the dataset 
       dsCmd.Fill(dset, "mine"); 
       //get the table 
       dtable = dset.Tables[0]; 
       closeConnection();       //always close it 
       return dtable;         //and return it 
    } 
    catch (Exception e) { 
       Console.WriteLine (e.Message); 
       return null; 
    } 
 } 
 //------
 public void openConnection(string connectionString) { 
    conn = new OleDbConnection(connectionString); 
 } 
 //------
 public OleDbConnection getConnection() { 
    return conn; 
 } 
 } 

Note that this class is complete except for constructors. We'll make derived classes that create the connection strings for various databases. Here we'll make a version for Access.

 public class AxsDatabase :Dbase { 
    public AxsDatabase(string dbName)   { 
       string connectionString = 
       "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
          dbName; 
       openConnection(connectionString); 
    } 
 } 

Here is one for SQL Server.

 public class SQLServerDatabase:DBase  { 
    string connectionString; 
    //-----
 public SQLServerDatabase(String dbName)       { 
    connectionString = "Persist Security Info = False;" + 
       "Initial Catalog =" + dbName + ";" + 
       "Data Source = myDataServer;User ID = myName;" + 
       "password="; 
    openConnection(connectionString); 
 } 
 //-----
 public SQLServerDatabase(string dbName, string serverName, 
    string userid, string pwd) { 
    connectionString = "Persist Security Info = False;" + 
       "Initial Catalog =" + dbName + ";" + 
       "Data Source =" + serverName + ";" + 
       "User ID =" + userid + ";" + 
       "password=" + pwd; 
    openConnection(connectionString); 
    } 
 } 

The DBTable Class

The other major class we will need is the DBTable class. It encapsulates opening, loading, and updating a single database table. We will also use this class in this example to add the single values. Then we can derive food and store classes that do this addition for each class.

 public class DBTable   { 
    protected DBase db; 
    protected string tableName; 
    private bool filled, opened; 
    private DataTable dtable; 
    private int rowIndex; 
    private Hashtable names; 
    private string columnName; 
    private DataRow row; 
    private OleDbConnection conn; 
    private int index; 
 //-----
 public DBTable(DBase datab, string tb_Name)    { 
    db = datab; 
    tableName = tb_Name; 
    filled =false; 
    opened = false; 
    names = new Hashtable(); 
 } 
 //-----
 public void createTable() { 
    try { 
       dtable = new DataTable(tableName); 
       dtable.Clear(); 
    } 
    catch (Exception e) { 
       Console.WriteLine (e.Message ); 
    } 
 } 
 //-----
 public bool hasMoreElements() { 
    if(opened) 
       return (rowIndex < dtable.Rows.Count) ; 
    else 
       return false; 
 } 
 //-----
 public int getKey(string nm, string keyname){ 
    DataRow row; 
    int key; 
  if(! filled) 
       return (int)names[ nm]; 
  else { 
    string query = "select * from " + tableName + " where " + 
       columnName + "=\'"+ nm + "\'"; 
    dtable = db.openQuery(query); 
    row = dtable.Rows[0]; 
    key = Convert.ToInt32 (row[keyname].ToString()); 
    return key; 
  } 
 } 
 //-----
 public virtual void makeTable(string cName) { 
    //shown below 
 //-----
 private void closeConn() { 
 if( conn.State == ConnectionState.Open) { 
       conn.Close(); 
 } 
 } 
 //-----
 private void openConn() { 
    if(conn.State == ConnectionState.Closed ) { 
       conn.Open(); 
    } 
 } 
 //-----
 public void openTable() { 
    dtable = db.openTable(tableName); 
    rowIndex = 0; 
    if(dtable != null) 
       opened = true; 
    } 
 //-----
 public void delete() { 
    //shown above 
 } 
 } 
  • + Share This
  • 🔖 Save To Your Account