/// <summary>
/// Creates and performs a delete query
/// </summary>
/// <param name="conn">Connection to use for the delete</param>
/// <param name="trans">Transaction to use for the delete</param>
/// <param name="table">Name of the table to delete</param>
/// <param name="whereQuery">Columns to base the delete query on</param>
public static void Delete(
OleDbConnection conn,
OleDbTransaction trans,
string table,
params WhereCol[] whereQuery)
{
DSShared.DB.DBTableType thisTable = DSShared.DB.DBTableTypeCache.Instance.CacheTable(table);
OleDbCommand comm = new OleDbCommand("DELETE FROM " + thisTable.TableName + " WHERE ", conn, trans);
if (whereQuery != null && whereQuery.Length > 0)
{
bool flag = false;
int i = 0;
foreach (WhereCol dbc in whereQuery)
{
if (flag)
{
comm.CommandText += " AND ";
}
else
{
flag = true;
}
string paramID = paramPrefix + (i++);
comm.CommandText += dbc.Column + "=" + paramID;
comm.Parameters.AddWithValue(paramID, dbc.Data);
// comm.Parameters.Add(paramID,dbc.Data);
}
}
comm.ExecuteNonQuery();
}
/// <summary>
/// Caches a table given its table name. The table must have been registered before calling this or an exception will be raised
/// If the object associated with the table name has not been created yet, it will be created and stored for future use
/// </summary>
/// <param name="table">Name of the table you wish to access</param>
/// <returns>The object representing the table in the database</returns>
public DBTableType CacheTable(string table)
{
if (nameType[table] == null)
{
throw new Exception("Type for table: " + table + " has not been registered yet");
}
if (typeHash[table] == null)
{
typeHash[table] = new DBTableType(table, nameType[table]);
}
return(typeHash[table]);
}
/// <summary>
/// Retrieves all rows from a single table according to the columns specified
/// The keys of the hashtable are autonumber values which are retrieved from properties
/// tagged with a DBColumn value of true for autoNumber
/// </summary>
/// <param name="conn">Connection to the database</param>
/// <param name="trans">Transaction object that is open and paired with the connection</param>
/// <param name="table">Name of the table to get all the rows from</param>
/// <param name="cache">If true, values will be retrieved from the cache if available</param>
/// <param name="whereQuery">List of columns to limit the search with. If you cache an incomplete row list, that list will be returned in future cached retrievals</param>
/// <returns>A table of [key:int] [value:Access_DBTable] objects. The key is based on autonumber columns</returns>
public static Hashtable GetAllHash(
OleDbConnection conn,
OleDbTransaction trans,
string table,
bool cache,
params WhereCol[] whereQuery)
{
if (cache && DSShared.DB.DBTableTypeCache.Instance.GetHash(table) != null)
{
return(DSShared.DB.DBTableTypeCache.Instance.GetHash(table));
}
ArrayList list = GetAll(
conn,
trans,
table,
cache,
whereQuery);
DSShared.DB.DBTableType thisType = DSShared.DB.DBTableTypeCache.Instance.GetType(table);
if (thisType.AutoNumber != null)
{
PropertyInfo pi = thisType.AutoNumber;
Hashtable hash = new Hashtable();
foreach (Access_DBTable dt in list)
{
hash[pi.GetGetMethod().Invoke(dt, null)] = dt;
}
if (cache)
{
DSShared.DB.DBTableTypeCache.Instance.CacheHash(table, hash);
}
return(hash);
}
throw new Exception("Call GetAllHash on table: " + table + " invalid since it does not have an autonumber defined");
}
/// <summary>
/// Constructor for an access table
/// </summary>
/// <param name="tableName">Name of the table in the database this object represents</param>
public Access_DBTable(string tableName)
{
myType = DSShared.DB.DBTableTypeCache.Instance.CacheTable(tableName);
}
/// <summary>
/// Retrieves all rows from a single table according to the columns specified
/// The order of items is based on the order retrieved from the query
/// Generally, this method does a SELECT * FROM table
/// </summary>
/// <param name="conn">Connection to use</param>
/// <param name="trans">Transaction to use</param>
/// <param name="table">Table name</param>
/// <param name="cache">If true, cache'ing will be used</param>
/// <param name="whereQuery">List of columns to limit the search with. If you cache an incomplete row list, that list will be returned in future cached retrievals</param>
/// <returns>A list of Access_DBTable in the order returned by the query</returns>
public static ArrayList GetAll(
OleDbConnection conn,
OleDbTransaction trans,
string table,
bool cache,
params WhereCol[] whereQuery)
{
DSShared.DB.DBTableType thisTable = DSShared.DB.DBTableTypeCache.Instance.CacheTable(table);
OleDbCommand comm = new OleDbCommand("SELECT * FROM " + thisTable.TableName, conn, trans);
if (whereQuery != null && whereQuery.Length > 0)
{
bool flag = false;
comm.CommandText += " WHERE ";
int i = 0;
foreach (WhereCol dbc in whereQuery)
{
if (flag)
{
comm.CommandText += " AND ";
}
else
{
flag = true;
}
string paramID = paramPrefix + (i++);
comm.CommandText += dbc.Column + "=" + paramID;
comm.Parameters.AddWithValue(paramID, dbc.Data);
// comm.Parameters.Add(paramID, dbc.Data);
}
}
OleDbDataReader res = comm.ExecuteReader();
ArrayList list = new ArrayList();
Type myType = DSShared.DB.DBTableTypeCache.Instance.GetTableType(thisTable.TableName);
while (res.Read())
{
// if null object here, then the type does not have a default constructor defined
object newObject = myType.GetConstructor(new Type[] {}).Invoke(null);
foreach (PropertyInfo pi in thisTable.Columns)
{
if (thisTable[pi] != null)
{
pi.SetValue(
newObject,
res[thisTable[pi].ColumnName],
null);
}
}
//Console.WriteLine("Type: "+objType);
list.Add(newObject);
// ((DBTable)newObject).FinishGet(conn2);
}
res.Close();
foreach (Access_DBTable dbt in list)
{
dbt.FinishGet(conn, trans);
}
if (cache)
{
DSShared.DB.DBTableTypeCache.Instance.CacheList(table, list);
}
if (FinishGetAll != null)
{
FinishGetAll(table, list);
}
return(list);
}
/// <summary>
/// Caches a table given its object
/// </summary>
/// <param name="dbType">DBTableType to cache</param>
public void CacheTable(DBTableType dbType)
{
typeHash[dbType.TableName] = dbType;
}
/// <summary>
/// Caches a table given its table name. The table must have been registered before calling this or an exception will be raised
/// If the object associated with the table name has not been created yet, it will be created and stored for future use
/// </summary>
/// <param name="table">Name of the table you wish to access</param>
/// <returns>The object representing the table in the database</returns>
public DBTableType CacheTable(string table)
{
if(nameType[table]==null)
throw new Exception("Type for table: "+table+" has not been registered yet");
if(typeHash[table]==null)
typeHash[table] = new DBTableType(table,nameType[table]);
return typeHash[table];
}
/// <summary>
/// Caches a table given its object
/// </summary>
/// <param name="dbType">DBTableType to cache</param>
public void CacheTable(DBTableType dbType)
{
typeHash[dbType.TableName]=dbType;
}
/// <summary>
/// Constructor for an access table
/// </summary>
/// <param name="tableName">Name of the table in the database this object represents</param>
public Access_DBTable(string tableName)
{
myType = DSShared.DB.DBTableTypeCache.Instance.CacheTable(tableName);
}