/// <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); }