- 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
Building the Price Table
The Price table is a little more complicated because it contains keys from the other two tables. When it is completed, it will look like Table 18-5.
To create it, we have to reread the file, finding the store and food names, looking up their keys, and adding them to the Price table. The DBTable interface doesn't include this final method, but we can add additional specific methods to the Price class that are not part of that interface.
The Prices class stores a series of StoreFoodPrice objects in an ArrayList and then loads them all into the database at once. Note that we have overloaded the classes of DBTable to take arguments for the store and food key values as well as the price.
Each time we add a storekey, foodkey, and price to the internal ArrayList table, we create an instance of the StoreFoodPrice object and store it.
public class StoreFoodPrice { private int storeKey, foodKey; private float foodPrice; //----- public StoreFoodPrice(int sKey, int fKey, float fPrice) { storeKey = sKey; foodKey = fKey; foodPrice = fPrice; } //----- public int getStore() { return storeKey; } //----- public int getFood() { return foodKey; } //----- public float getPrice() { return foodPrice; } }
Table 18-5. The Price Table in the Grocery Database
Pricekey |
Foodkey |
StoreKey |
Price |
1 |
1 |
1 |
0.27 |
2 |
2 |
1 |
0.36 |
3 |
3 |
1 |
1.98 |
4 |
4 |
1 |
2.39 |
5 |
5 |
1 |
1.98 |
6 |
6 |
1 |
2.65 |
7 |
7 |
1 |
2.29 |
8 |
1 |
2 |
0.29 |
9 |
2 |
2 |
0.29 |
10 |
3 |
2 |
2.45 |
11 |
4 |
2 |
2.99 |
12 |
5 |
2 |
1.79 |
13 |
6 |
2 |
3.79 |
14 |
7 |
2 |
2.19 |
15 |
1 |
3 |
0.33 |
16 |
2 |
3 |
0.47 |
17 |
3 |
3 |
2.29 |
18 |
4 |
3 |
3.29 |
19 |
5 |
3 |
1.89 |
20 |
6 |
3 |
2.99 |
21 |
7 |
3 |
1.99 |
Then when we have them all, we create the actual database table.
public class Prices : DBTable { private ArrayList priceList; public Prices(DBase db) : base(db, "Prices") { priceList = new ArrayList (); } //----- public void makeTable() { //stores current array list values in data table OleDbConnection adc = new OleDbConnection(); DataSet dset = new DataSet(tableName); DataTable dtable = new DataTable(tableName); dset.Tables.Add(dtable); adc = db.getConnection(); if (adc.State == ConnectionState.Closed) adc.Open(); OleDbDataAdapter adcmd = new OleDbDataAdapter(); //fill in price table adcmd.SelectCommand = new OleDbCommand("Select * from " + tableName, adc); OleDbCommandBuilder custCB = new OleDbCommandBuilder(adcmd); adcmd.TableMappings.Add("Table", tableName); adcmd.Fill(dset, tableName); IEnumerator ienum = priceList.GetEnumerator(); //add new price entries while (ienum.MoveNext() ) { StoreFoodPrice fprice = (StoreFoodPrice)ienum.Current; DataRow row = dtable.NewRow(); row["foodkey"] = fprice.getFood(); row["storekey"] = fprice.getStore(); row["price"] = fprice.getPrice(); dtable.Rows.Add(row); //add to table } adcmd.Update(dset); //send back to database adc.Close(); } //----- public DataTable getPrices(string food) { string query= "SELECT Stores.StoreName, " + "Foods.Foodname, Prices.Price " + "FROM (Prices INNER JOIN Foods ON " + "Prices.Foodkey = Foods.Foodkey) " + "INNER JOIN Stores ON " + "Prices.StoreKey = Stores.StoreKey " + "WHERE(((Foods.Foodname) =\'" + food + "\'))" + "ORDER BY Prices.Price"; return db.openQuery(query); } //----- public void addRow(int storeKey, int foodKey, float price) priceList.Add ( new StoreFoodPrice (storeKey, foodKey, price)); } }