Home > Articles > Programming > C#

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

This chapter is from the book

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)); 
    } 
 } 
  • + Share This
  • 🔖 Save To Your Account