/// <summary>
/// Summary description for DBTable.
/// 外观模式 Facade Patterns
/// 20220918
/// geovindu,Geovin Du,涂聚文
/// </summary>
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;
/// <summary>
///
/// </summary>
/// <param name="datab"></param>
/// <param name="tb_Name"></param>
public DBTable(DBase datab, string tb_Name)
{
db = datab;
tableName = tb_Name;
filled = false;
opened = false;
names = new Hashtable();
}
/// <summary>
///
/// </summary>
public void createTable()
{
try
{
dtable = new DataTable(tableName);
dtable.Clear();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public bool hasMoreElements()
{
if (opened)
return (rowIndex < dtable.Rows.Count);
else
return false;
}
/// <summary>
///
/// </summary>
/// <param name="cname"></param>
/// <returns></returns>
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 "";
}
/// <summary>
///
/// </summary>
/// <param name="nm"></param>
/// <param name="keyname"></param>
/// <returns></returns>
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;
}
}
/// <summary>
///
/// </summary>
/// <param name="cName"></param>
public virtual void makeTable(string cName)
{
columnName = cName;
//stores current hash table values in data table
DataSet dset = new DataSet(tableName); //create the data set
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);
}
}
/// <summary>
///
/// </summary>
private void closeConn()
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
/// <summary>
///
/// </summary>
private void openConn()
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
}
/// <summary>
/// /
/// </summary>
/// <param name="nm"></param>
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
}
/// <summary>
///
/// </summary>
public void openTable()
{
dtable = db.openTable(tableName);
rowIndex = 0;
if (dtable != null)
opened = true;
}
/// <summary>
///
/// </summary>
public void delete()
{
//deletes entire table
conn = db.getConnection();
openConn();
if (conn.State == ConnectionState.Open)
{
OleDbCommand adcmd =
new OleDbCommand("Delete * from " + tableName, conn);
try
{
adcmd.ExecuteNonQuery();
closeConn();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
}
}
/// <summary>
/// Summary description for DBase.
/// 外观模式 Facade Patterns
/// 20220918
/// geovindu,Geovin Du,涂聚文
/// </summary>
public abstract class DBase
{
/// <summary>
///
/// </summary>
protected OleDbConnection conn;
/// <summary>
///
/// </summary>
private void openConnection()
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
}
/// <summary>
///
/// </summary>
private void closeConnection()
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
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;
}
/// <summary>
///
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
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;
}
}
/// <summary>
///
/// </summary>
/// <param name="connectionString"></param>
public void openConnection(string connectionString)
{
conn = new OleDbConnection(connectionString);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public OleDbConnection getConnection()
{
return conn;
}
}
/// <summary>
/// Summary description for DataLoader.
/// 外观模式 Facade Patterns
/// 20220918
/// geovindu,Geovin Du,涂聚文
/// </summary>
public class DataLoader
{
private csFile vfile;
private Stores store;
private Foods fods;
private Prices price;
private DBase db;
/// <summary>
///
/// </summary>
/// <param name="datab"></param>
public DataLoader(DBase datab)
{
db = datab;
store = new Stores(db);
fods = new Foods(db);
price = new Prices(db);
}
/// <summary>
///
/// </summary>
/// <param name="dataFile"></param>
public void load(string dataFile)
{
string sline;
int storekey, foodkey;
StringTokenizer tok;
//delete current table contents
store.delete();
fods.delete();
price.delete();
//now read in new ones
vfile = new csFile(dataFile);
vfile.OpenForRead();
sline = vfile.readLine();
while (sline != null)
{
tok = new StringTokenizer(sline, ",");
store.addTableValue(tok.nextToken()); //store name
fods.addTableValue(tok.nextToken()); //food name
sline = vfile.readLine();
}
vfile.close();
//construct store and food tables
store.makeTable();
fods.makeTable();
vfile.OpenForRead();
sline = vfile.readLine();
while (sline != null)
{
//get the gets and add to storefoodprice objects
tok = new StringTokenizer(sline, ",");
storekey = store.getKey(tok.nextToken(), "Storekey");
foodkey = fods.getKey(tok.nextToken(), "Foodkey");
price.addRow(storekey, foodkey, Convert.ToSingle(tok.nextToken()));
sline = vfile.readLine();
}
//add all to price table
price.makeTable();
vfile.close();
}
}
/// <summary>
/// Summary description for Stores.
/// 外观模式 Facade Patterns
/// 20220918
/// geovindu,Geovin Du,涂聚文
/// </summary>
public class Stores : DBTable
{
/// <summary>
///
/// </summary>
/// <param name="db"></param>
public Stores(DBase db)
: base(db, "Stores")
{
}
/// <summary>
///
/// </summary>
public void makeTable()
{
base.makeTable("Storename");
}
}
/// <summary>
/// Summary description for Foods.
/// 外观模式 Facade Patterns
/// 20220918
/// geovindu,Geovin Du,涂聚文
/// </summary>
public class Foods : DBTable
{
/// <summary>
///
/// </summary>
/// <param name="db"></param>
public Foods(DBase db)
: base(db, "Foods")
{
}
/// <summary>
///
/// </summary>
public void makeTable()
{
base.makeTable("Foodname");
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public string getValue()
{
return base.getValue("FoodName");
}
}
/// <summary>
/// Summary description for Prices.
/// 外观模式 Facade Patterns
/// 20220918
/// geovindu,Geovin Du,涂聚文
/// </summary>
public class Prices : DBTable
{
/// <summary>
///
/// </summary>
private ArrayList priceList;
/// <summary>
///
/// </summary>
/// <param name="db"></param>
public Prices(DBase db)
: base(db, "Prices")
{
priceList = new ArrayList();
}
/// <summary>
///
/// </summary>
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();
}
/// <summary>
///
/// </summary>
/// <param name="food"></param>
/// <returns></returns>
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));
}
}
/// <summary>
/// Summary description for StoreFoodPrice.
/// 外观模式 Facade Patterns
/// 20220918
/// geovindu,Geovin Du,涂聚文
/// </summary>
public class StoreFoodPrice
{
/// <summary>
///
/// </summary>
private int storeKey, foodKey;
/// <summary>
///
/// </summary>
private float foodPrice;
/// <summary>
///
/// </summary>
/// <param name="sKey"></param>
/// <param name="fKey"></param>
/// <param name="fPrice"></param>
public StoreFoodPrice(int sKey, int fKey, float fPrice)
{
storeKey = sKey;
foodKey = fKey;
foodPrice = fPrice;
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public int getStore()
{
return storeKey;
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public int getFood()
{
return foodKey;
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public float getPrice()
{
return foodPrice;
}
}
/// <summary>
/// Summary description for AxsDatabase.
/// 外观模式 Facade Patterns
/// 20220918
/// geovindu,Geovin Du,涂聚文
/// </summary>
public class AxsDatabase : DBase
{
public AxsDatabase(string dbName)
{
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName;
openConnection(connectionString);
}
}
/// <summary>
/// Summary description for SQLServerDatabase.
/// 外观模式 Facade Patterns
/// 20220918
/// geovindu,Geovin Du,涂聚文
/// </summary>
public class SQLServerDatabase : DBase
{
string connectionString;
/// <summary>
///
/// </summary>
/// <param name="dbName"></param>
public SQLServerDatabase(String dbName)
{
connectionString = "Persist Security Info = False;" +
"Initial Catalog =" + dbName + ";" +
"Data Source = geovindu;User ID = duData;" +
"password=";
openConnection(connectionString);
}
/// <summary>
///
/// </summary>
/// <param name="dbName"></param>
/// <param name="serverName"></param>
/// <param name="userid"></param>
/// <param name="pwd"></param>
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);
}
}
/// <summary>
/// String Tokenizer class
/// 外观模式 Facade Patterns
/// 20220918
/// geovindu,Geovin Du,涂聚文
/// </summary>
public class StringTokenizer
{
private string data, delimiter;
private string[] tokens;
private int index;
/// <summary>
///
/// </summary>
/// <param name="dataLine"></param>
public StringTokenizer(string dataLine)
{
init(dataLine, " ");
}
/// <summary>
///
/// </summary>
/// <param name="dataLine"></param>
/// <param name="delim"></param>
private void init(String dataLine, string delim)
{
delimiter = delim;
data = dataLine;
tokens = data.Split(delimiter.ToCharArray());
index = 0;
}
/// <summary>
///
/// </summary>
/// <param name="dataLine"></param>
/// <param name="delim"></param>
public StringTokenizer(string dataLine, string delim)
{
init(dataLine, delim);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public bool hasMoreElements()
{
return (index < (tokens.Length));
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public string nextToken()
{
return nextElement();
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public string nextElement()
{
string s = tokens[index++].Trim();
while ((s.Length <= 0) && (index < tokens.Length))
s = tokens[index++].Trim();
return s;
}
}
/// <summary>
/// A simple file handlng class
/// 外观模式 Facade Patterns
/// 20220918
/// geovindu,Geovin Du,涂聚文
/// </summary>
public class csFile
{
private string fileName;
StreamReader ts;
StreamWriter ws;
private bool opened, writeOpened;
/// <summary>
///
/// </summary>
public csFile()
{
init();
}
/// <summary>
///
/// </summary>
private void init()
{
opened = false;
writeOpened = false;
}
/// <summary>
///
/// </summary>
/// <param name="file_name"></param>
public csFile(string file_name)
{
fileName = file_name;
init();
}
/// <summary>
///
/// </summary>
/// <param name="file_name"></param>
/// <returns></returns>
public bool OpenForRead(string file_name)
{
fileName = file_name;
try
{
ts = new StreamReader(fileName);
opened = true;
}
catch (FileNotFoundException)
{
return false;
}
return true;
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public bool OpenForRead()
{
return OpenForRead(fileName);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public string readLine()
{
return ts.ReadLine();
}
/// <summary>
///
/// </summary>
/// <param name="s"></param>
public void writeLine(string s)
{
ws.WriteLine(s);
}
/// <summary>
///
/// </summary>
public void close()
{
if (opened)
ts.Close();
if (writeOpened)
ws.Close();
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public bool OpenForWrite()
{
return OpenForWrite(fileName);
}
/// <summary>
///
/// </summary>
/// <param name="file_name"></param>
/// <returns></returns>
public bool OpenForWrite(string file_name)
{
try
{
ws = new StreamWriter(file_name);
fileName = file_name;
writeOpened = true;
return true;
}
catch (FileNotFoundException)
{
return false;
}
}
}
窗体调用试试：
/// <summary>
/// Facade Patterns
/// .net patterns-- architecture， design, and process by Christian Thilmany
/// 外观模式 Facade Patterns
/// 20220918
/// geovindu,Geovin Du,涂聚文
/// </summary>
public partial class FacadePatternsForm : Form
{
private DBase db;
private Stores shops;
private Prices prc;
/// <summary>
///
/// </summary>
private void init()
{
db = new AxsDatabase("Groceries.mdb");
shops = new Stores(db);
prc = new Prices(db);
loadFoodTable();
ToolTip tips = new ToolTip();
tips.SetToolTip(btLoad, "Reload data from groceries.txt file");
}
/// <summary>
///
/// </summary>
private void loadFoodTable()
{
Foods fods = new Foods(db);
fods.openTable();
while (fods.hasMoreElements())
{
lsFoods.Items.Add(fods.getValue());
}
}
/// <summary>
///
/// </summary>
public FacadePatternsForm()
{
InitializeComponent();
init();
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void FacadePatternsForm_Load(object sender, EventArgs e)
{
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void lsFoods_SelectedIndexChanged(object sender, EventArgs e)
{
string food = lsFoods.Text;
DataTable dtable = prc.getPrices(food);
lsPrices.Items.Clear();
foreach (DataRow rw in dtable.Rows)
{
lsPrices.Items.Add(rw["StoreName"].ToString().Trim() +
"\t" + rw["Price"].ToString());
}
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btLoad_Click(object sender, EventArgs e)
{
lsFoods.Items.Clear();
Cursor.Current = Cursors.WaitCursor;
DataLoader dload = new DataLoader(db);
dload.load("groceries.txt");
loadFoodTable();
Cursor.Current = Cursors.Default;
}
}
输出：
