␡
- What Is a Database?
- Getting Data Out of Databases
- Kinds of Databases
- ODBC
- Database Structure
- Using ADO.NET
- Adding Rows to Database Tables Using ADO.NET
- Building the Façade Classes
- Making the ADO.NET Façade
- Creating Classes for Each Table
- Building the Price Table
- Loading the Database Tables
- The Final Application
This chapter is from the book
This chapter is from the book
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
}
}