- 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
Creating Classes for Each Table
We can derive the Store, Food, and Prices classes from DBTable and reuse much of the code. When we parse the input file, both the Store and Food classes will require that we create a table of unique names: store names in one class and food names in the other.
C# provides a very convenient way to create these classes using the Hashtable. A Hashtable is an unbounded array where each element is identified with a unique key. One way Hashtables are used is to add objects to the table with a short nickname as the key. Then you can fetch the object from the table by using its nickname. The objects need not be unique, but, of course, the keys must be.
The other place Hashtables are convenient is in making a list of unique names. If the names are the keys and some other numbers are the contents, we can add names to the Hashtable and assure ourselves that each will be unique. For them to be unique, the Hashtable must treat attempts to add a duplicate key in a predictable way. For example, the Java Hashtable simply replaces a previous entry having that key with the new one. The C# implementation of the Hashtable, on the other hand, throws an exception when we try to add a nonunique key value.
Now bearing in mind that we want to accumulate the entire list of names before adding them into the database, we can use the following method to add names to a Hashtable and make sure they are unique.
public void addTableValue(string nm) {
//accumulates names in hash table
try {
names.Add(nm, index++);
}
catch (ArgumentException) {}
//do not allow duplicate names to be added
}
Then once we have added all the names, we can add each of them to the database table. Here we use the Enumerator property of the Hashtable to iterate though all the names we have entered in the list.
public virtual void makeTable(string cName) {
columnName = cName;
//stores current hash table values in data table
DataSet dset = new DataSet(tableName); //create dataset
dtable = new DataTable(tableName); //and a datatable
dset.Tables.Add(dtable); //add to collection
conn = db.getConnection();
openConn(); //open the connection
OleDbDataAdapter adcmd = new OleDbDataAdapter();
//open the table
adcmd.SelectCommand =
new OleDbCommand("Select * from " + tableName, conn);
OleDbCommandBuilder olecb = new OleDbCommandBuilder(adcmd);
adcmd.TableMappings.Add("Table", tableName);
//load current data into the local table copy
adcmd.Fill(dset, tableName);
//get the Enumerator from the Hashtable
IEnumerator ienum = names.Keys.GetEnumerator();
//move through the table, adding the names to new rows
while (ienum.MoveNext()) {
string name = (string)ienum.Current;
row = dtable.NewRow(); //get new rows
row[columnName] = name;
dtable.Rows.Add(row); //add into table
}
//Now update the database with this table
try {
adcmd.Update(dset);
closeConn();
filled = true;
}
catch (Exception e) {
Console.WriteLine (e.Message);
}
}
This simplifies our derived Stores table to just the following.
public class Stores :DBTable {
public Stores(DBase db):base(db, "Stores"){
}
//-----
public void makeTable() {
base.makeTable ("Storename");
}
}
And it simplifies the Foods table to much the same thing.
public class Foods: DBTable {
public Foods(DBase db):base(db, "Foods"){
}
//-----
public void makeTable() {
base.makeTable ("Foodname");
}
//-----
public string getValue() {
return base.getValue ("FoodName");
}
}
The getValue method allows us to enumerate the list of names of Stores or Foods, and we can put it in the base DBTable class.
public virtual string getValue(string cname) {
//returns the next name in the table
//assumes that openTable has already been called
if (opened) {
DataRow row = dtable.Rows[rowIndex++];
return row[cname].ToString().Trim ();
}
else
return "";
}
Note that we make this method virtual so we can override it where needed.