/// <summary> /// Delete's a pending order from the system. This will not work /// for orders that have already been paid/cancelled/refunded /// </summary> public void DeletePermanent() { QueryCommandCollection coll = new QueryCommandCollection(); if (this.OrderStatus == OrderStatus.NotProcessed) { //in order - notes, transactions, items, order //notes Query q = new Query(OrderNote.GetTableSchema()); q.AddWhere("orderID", OrderID); coll.Add(q.BuildDeleteCommand()); //transactions q = new Query(Transaction.GetTableSchema()); q.AddWhere("orderID", OrderID); coll.Add(q.BuildDeleteCommand()); //items q = new Query(OrderItem.GetTableSchema()); q.AddWhere("orderID", OrderID); coll.Add(q.BuildDeleteCommand()); q = new Query(Schema); q.AddWhere("orderID", OrderID); coll.Add(q.BuildDeleteCommand()); DataService.ExecuteTransaction(coll); } }
/// <summary> /// Saves records to a mapping table. Deletes all associative records first /// </summary> /// <param name="mapTableName">The Mapping Table</param> /// <param name="mapTableForiegnField">The associated foreign key</param> /// <param name="items">ListItemCollection, such as a CheckList.Items</param> public static void SaveManyToMany(string pkColumnName, object pkValue, string mapTableName, string mapTableForiegnKey, ListItemCollection items) { //remove all existing DeleteAllManyToMany(pkColumnName, pkValue, mapTableName); QueryCommand cmd = null; QueryCommandCollection coll = new QueryCommandCollection(); //loop the items and insert string iSql = ""; foreach (ListItem l in items) { if (l.Selected) { iSql = "INSERT INTO " + mapTableName + " (" + pkColumnName + ", " + mapTableForiegnKey + ")" + " VALUES (@" + pkColumnName + ",@" + mapTableForiegnKey + ")"; cmd = new QueryCommand(iSql); cmd.Parameters.Add("@" + pkColumnName, pkValue); cmd.Parameters.Add("@" + mapTableForiegnKey, l.Value); coll.Add(cmd); } } //execute DataService.ExecuteTransaction(coll); }
/// <summary> /// Executes a transaction using the passed-commands /// </summary> /// <param name="commands"></param> public override void ExecuteTransaction(QueryCommandCollection commands) { Database db = DatabaseFactory.CreateDatabase(); //make sure we have at least one if (commands.Count > 0) { DbCommand cmd = null; //a using statement will make sure we close off the connection using (DbConnection conn = db.CreateConnection()) { //open up the connection and start the transaction conn.Open(); DbTransaction trans = conn.BeginTransaction(); foreach (QueryCommand qry in commands) { cmd = qry.CommandType == CommandType.Text ? db.GetSqlStringCommand(qry.CommandSql) : db.GetStoredProcCommand(qry.CommandSql); try { foreach (QueryParameter param in qry.Parameters) { db.AddInParameter(cmd, param.ParameterName, param.DataType, param.ParameterValue); } db.ExecuteNonQuery(cmd, trans); } catch (DbException x) { //if there's an error, roll everything back trans.Rollback(); //clean up conn.Close(); cmd.Dispose(); //throw the error retaining the stack. throw new Exception(x.Message); } } //if we get to this point, we're good to go trans.Commit(); //close off the connection conn.Close(); if (cmd != null) { cmd.Dispose(); } } } else { throw new Exception("No commands present"); } }
/// <summary> /// Uses a transaction to quickly delete all the records /// marked for deletion /// </summary> public void BatchDelete() { QueryCommandCollection commands = GetDeleteCommands(); DataService.ExecuteTransaction(commands); _deleteList.Clear(); }
/// <summary> /// Executes a transaction of the passed-in commands. /// </summary> /// <param name="commands">The commands.</param> public static void ExecuteTransaction(QueryCommandCollection commands) { if (commands == null || commands.Count == 0) { return; } commands[0].Provider.ExecuteTransaction(commands); //GetInstance().ExecuteTransaction(commands); }
/// <summary> /// Uses a transaction to quickly persists all the records in a collection to the DB /// The primary keys of any new records are not updated in the collection /// You must reload the collection to get the latest data /// </summary> /// <param name="userName">Name of the user.</param> public void BatchSave(string userName) { QueryCommandCollection commands = new QueryCommandCollection(); commands.AddRange(GetSaveCommands(userName)); commands.AddRange(GetDeleteCommands()); DataService.ExecuteTransaction(commands); }
/// <summary> /// /// </summary> /// <param name="mapTableName"></param> private void SaveManyToMany(string mapTableName) { //first, need to get the id of the other field TableSchema.Table fkTable = Query.BuildTableSchema(mapTableName); string fkField = String.Empty; foreach (TableSchema.TableColumn col in fkTable.Columns) { if (col.IsPrimaryKey && col.ColumnName.ToLower() != Schema.PrimaryKey.ColumnName.ToLower()) { fkField = col.ColumnName; break; } } if (fkField != string.Empty) { int pk = Convert.ToInt32(Context.Request.QueryString["id"]); //first, delete out all references in there //this MUST be done in a transaction! QueryCommandCollection transCollection = new QueryCommandCollection(); Query qry = new Query(DataService.GetTableSchema(mapTableName, ProviderName, TableType.Table)); qry.QueryType = QueryType.Delete; qry.AddWhere(Schema.PrimaryKey.ColumnName, pk); transCollection.Add(qry.BuildDeleteCommand()); //now, loop the check list, adding items in for each checked bit string sql = "INSERT INTO " + mapTableName + "(" + fkField + "," + Schema.PrimaryKey.ColumnName + ") VALUES (" + Utility.PrefixParameter("fk", Schema.Provider) + "," + Utility.PrefixParameter("pk)", Schema.Provider); CheckBoxList chk = (CheckBoxList)tblEditor.FindControl(fkTable.Name); if (chk != null) { foreach (ListItem item in chk.Items) { if (item.Selected) { QueryCommand cmd = new QueryCommand(sql, ProviderName); cmd.Parameters.Add(Utility.PrefixParameter("fk", Schema.Provider), item.Value, DbType.Int32); cmd.Parameters.Add(Utility.PrefixParameter("pk", Schema.Provider), pk); transCollection.Add(cmd); } } } //execute DataService.ExecuteTransaction(transCollection); } }
/// <summary> /// Migrates the specified provider name. /// </summary> /// <param name="thisProviderName">Name of the provider.</param> /// <param name="direction">The direction.</param> public void Migrate(string thisProviderName, MigrationDirection direction) { Provider = DataService.GetInstance(thisProviderName); // actually do migration, this is a hack since there isn't // an ExecuteTransaction that takes a single cmd. Will // probably be refactored to generated a single QueryCommand // per MigrationStep but for now this works. QueryCommand cmd = BuildCommands(direction); QueryCommandCollection cmds = new QueryCommandCollection(); cmds.Add(cmd); DataService.ExecuteTransaction(cmds); }
/// <summary> /// Executes the transaction. /// </summary> /// <param name="commands">The commands.</param> public override void ExecuteTransaction(QueryCommandCollection commands) { //make sure we have at least one if (commands.Count > 0) { OracleCommand cmd; //a using statement will make sure we close off the connection using (AutomaticConnectionScope conn = new AutomaticConnectionScope(this)) { //open up the connection and start the transaction if (conn.Connection.State == ConnectionState.Closed) { conn.Connection.Open(); } OracleTransaction trans = (OracleTransaction)conn.Connection.BeginTransaction(); foreach (QueryCommand qry in commands) { cmd = new OracleCommand(qry.CommandSql, (OracleConnection)conn.Connection); cmd.CommandType = qry.CommandType; cmd.Transaction = trans; AddParams(cmd, qry); try { cmd.ExecuteNonQuery(); } catch (OracleException x) { //if there's an error, roll everything back trans.Rollback(); //throw the error retaining the stack. throw new Exception(x.Message); } } //if we get to this point, we're good to go trans.Commit(); } } else { throw new Exception("No commands present"); } }
/// <summary> /// Gets the save commands. /// </summary> /// <param name="userName">Name of the user.</param> /// <returns></returns> public QueryCommandCollection GetSaveCommands(string userName) { QueryCommandCollection commands = new QueryCommandCollection(); foreach (ItemType item in this) { QueryCommand cmd = item.GetSaveCommand(userName); if (cmd != null) { commands.Add(cmd); } } return(commands); }
/// <summary> /// Gets the delete commands. /// </summary> /// <returns></returns> public QueryCommandCollection GetDeleteCommands() { QueryCommandCollection commands = new QueryCommandCollection(); foreach (ItemType item in _deleteList) { QueryCommand dcmd = ActiveRecord <ItemType> .GetDeleteCommand(item.GetPrimaryKeyValue()); if (dcmd != null) { commands.Add(dcmd); } } return(commands); }
/// <summary> /// Execute all commands within a single transaction. /// </summary> /// <param name="commands">The commands.</param> public override void ExecuteTransaction(QueryCommandCollection commands) { //make sure we have at least one if (commands.Count > 0) { SQLiteCommand cmd = null; using (SQLiteConnection conn = (SQLiteConnection)CreateConnection()) { SQLiteTransaction trans = conn.BeginTransaction(); foreach (QueryCommand qry in commands) { cmd = new SQLiteCommand(qry.CommandSql, conn); cmd.CommandType = qry.CommandType; try { AddParams(qry, cmd); cmd.ExecuteNonQuery(); } catch (SQLiteException) { //if there's an error, roll everything back trans.Rollback(); //clean up cmd.Dispose(); throw; } } //if we get to this point, we're good to go trans.Commit(); if (cmd != null) { cmd.Dispose(); } } } else { throw new Exception("No commands present"); } }
public static void SaveTblRoleMap(string varPkSuid, long[] itemList) { var coll = new QueryCommandCollection(); //delete out the existing var cmdDel = new QueryCommand( "DELETE FROM [tbl_RolesForUsers] WHERE [tbl_RolesForUsers].[FP_sBranchID] = @FP_sBranchID", Schema.Provider.Name); cmdDel.AddParameter("@FP_sBranchID", varPkSuid, DbType.String); coll.Add(cmdDel); DataService.ExecuteTransaction(coll); foreach (long item in itemList) { var varTblRolesForUser = new TblRolesForUser(); varTblRolesForUser.SetColumnValue("FP_sBranchID", varPkSuid); varTblRolesForUser.SetColumnValue("FP_sBranchID", item); varTblRolesForUser.Save(); } }
public static void SaveTblManagementUnitMap(long varIRole, TblManagementUnitCollection items) { var coll = new QueryCommandCollection(); //delete out the existing var cmdDel = new QueryCommand( "DELETE FROM [tbl_RolesForUsers] WHERE [tbl_RolesForUsers].[FP_sBranchID] = @FP_sBranchID", Schema.Provider.Name); cmdDel.AddParameter("@FP_sBranchID", varIRole, DbType.String); coll.Add(cmdDel); DataService.ExecuteTransaction(coll); foreach (TblManagementUnit item in items) { var varTblRolesForUser = new TblRolesForUser(); varTblRolesForUser.SetColumnValue("FP_sBranchID", varIRole); varTblRolesForUser.SetColumnValue("FP_sBranchID", item.GetPrimaryKeyValue()); varTblRolesForUser.Save(); } }
/// <summary> /// Saves this instance. /// </summary> public void Save() { QueryCommandCollection coll = new QueryCommandCollection(); DataProvider provider = DataService.GetInstance(providerName); TableSchema.Table fkTable = DataService.GetSchema(foreignTableName, providerName, TableType.Table); TableSchema.Table pkTable = DataService.GetSchema(primaryTableName, providerName, TableType.Table); string fkPK = fkTable.PrimaryKey.ColumnName; string pk = pkTable.PrimaryKey.ColumnName; //delete out the existing string idParam = provider.FormatParameterNameForSQL("id"); QueryCommand cmdDel = new QueryCommand("DELETE FROM " + mapTableName + " WHERE " + pk + " = " + idParam, providerName); cmdDel.AddParameter(idParam, primaryKeyValue, DbType.AnsiString); //cmdDel.ProviderName = Product.Schema.ProviderName; //add this in coll.Add(cmdDel); //loop the items and insert string fkParam = provider.FormatParameterNameForSQL("fkID"); string pkParam = provider.FormatParameterNameForSQL("pkID"); foreach (ListItem l in Items) { if (l.Selected) { string iSql = "INSERT INTO " + mapTableName + " (" + fkPK + ", " + pk + ")" + " VALUES (" + fkParam + "," + pkParam + ")"; QueryCommand cmd = new QueryCommand(iSql, providerName); cmd.Parameters.Add(fkParam, l.Value, fkTable.PrimaryKey.DataType); cmd.Parameters.Add(pkParam, primaryKeyValue, pkTable.PrimaryKey.DataType); coll.Add(cmd); } } //execute DataService.ExecuteTransaction(coll); }
public void SaveItems() { //queue up a SQL batch of the existing items //and save them all at once QueryCommandCollection coll = new QueryCommandCollection(); //first, delete out all the existing items Query qry = new Query(OrderItem.GetTableSchema()); qry.AddWhere("orderID", this.OrderID); qry.QueryType = QueryType.Delete; coll.Add(qry.BuildCommand()); QueryCommand insertItemCommand = null; foreach (OrderItem item in this.Items) { insertItemCommand = item.GetInsertCommand(Utility.GetUserName()); coll.Add(insertItemCommand); } DataService.ExecuteTransaction(coll); }
public void MultiThreadRepeat() { lock(thisLock) { var qcc = new QueryCommandCollection(); int threadId = Thread.CurrentThread.ManagedThreadId; Debug.WriteLine("MultiThreadRepeat: thread id = " + threadId); int count = 0; for(int n = 0; n < 10; n++) { Query qry1 = new Query(Product.Schema); qry1.QueryType = QueryType.Update; qry1.AddWhere(Product.Columns.ProductID, n); qry1.AddUpdateSetting("ProductName", threadId + ": unit test "); QueryCommand cmd = qry1.BuildUpdateCommand(); qcc.Add(cmd); count++; } DataService.ExecuteTransaction(qcc); var p1 = new Product(1); Assert.AreEqual(p1.ProductName, threadId + ": unit test ", StringComparison.InvariantCultureIgnoreCase); } }
/// <summary> /// Executes a transaction of the passed-in commands. /// </summary> /// <param name="commands">The commands.</param> /// <param name="providerName">Name of the provider.</param> public static void ExecuteTransaction(QueryCommandCollection commands, string providerName) { GetInstance(providerName).ExecuteTransaction(commands); }
/// <summary> /// Saves this instance. /// </summary> public new virtual void Save() { DataProvider provider = DataService.GetInstance(ProviderName); LoadColumnNames(); // read the current state of the checkboxes Dictionary<string, bool> newState = new Dictionary<string, bool>(); foreach(ListItem l in Items) newState.Add(l.Value, l.Selected); // read what is in the database List<string> pastState = new List<string>(); QueryCommand lookupCmd = new QueryCommand(String.Empty, ProviderName); // quick hack to re-use BuildMappedElementCommand BuildMappedElementCommand(provider, lookupCmd); using(IDataReader rdr = DataService.GetReader(lookupCmd)) { while(rdr.Read()) pastState.Add(rdr[MapTableFkToForeignTable].ToString()); rdr.Close(); } // build the commands to be executed. QueryCommandCollection coll = new QueryCommandCollection(); string fkParam = provider.FormatParameterNameForSQL("fkID"); string pkParam = provider.FormatParameterNameForSQL("pkID"); foreach(KeyValuePair<string, bool> kvp in newState) { string sql; // if we have it now but did not before if(kvp.Value && !pastState.Contains(kvp.Key)) sql = String.Format("INSERT INTO {0} ({1},{2}) VALUES ({3},{4})", MapTableName, MapTableFkToForeignTable, MapTableFkToPrimaryTable, fkParam, pkParam); else if(!kvp.Value && pastState.Contains(kvp.Key)) // we don't have it now but had it before sql = String.Format("DELETE FROM {0} WHERE {1} = {2} AND {3} = {4}", MapTableName, MapTableFkToPrimaryTable, pkParam, MapTableFkToForeignTable, fkParam); else continue; // nothing changed. QueryCommand cmd = new QueryCommand(sql, ProviderName); cmd.Parameters.Add(fkParam, kvp.Key, DataService.GetSchema(ForeignTableName, ProviderName).PrimaryKey.DataType); cmd.Parameters.Add(pkParam, PrimaryKeyValue, DataService.GetSchema(PrimaryTableName, ProviderName).PrimaryKey.DataType); coll.Add(cmd); } //execute if(coll.Count > 0) DataService.ExecuteTransaction(coll); }
/// <summary> /// Saves this instance. /// </summary> public void Save() { QueryCommandCollection coll = new QueryCommandCollection(); DataProvider provider = DataService.GetInstance(providerName); TableSchema.Table fkTable = DataService.GetSchema(foreignTableName, providerName, TableType.Table); TableSchema.Table pkTable = DataService.GetSchema(primaryTableName, providerName, TableType.Table); string fkPK = fkTable.PrimaryKey.ColumnName; string pk = pkTable.PrimaryKey.ColumnName; //delete out the existing string idParam = Utility.PrefixParameter("id", provider); QueryCommand cmdDel = new QueryCommand("DELETE FROM " + mapTableName + " WHERE " + pk + " = " + idParam, providerName); cmdDel.AddParameter(idParam, primaryKeyValue, DbType.AnsiString); //cmdDel.ProviderName = Product.Schema.ProviderName; //add this in coll.Add(cmdDel); //loop the items and insert string fkParam = Utility.PrefixParameter("fkID", provider); string pkParam = Utility.PrefixParameter("pkID", provider); foreach(ListItem l in Items) { if(l.Selected) { string iSql = "INSERT INTO " + mapTableName + " (" + fkPK + ", " + pk + ")" + " VALUES (" + fkParam + "," + pkParam + ")"; QueryCommand cmd = new QueryCommand(iSql, providerName); cmd.Parameters.Add(fkParam, l.Value, fkTable.PrimaryKey.DataType); cmd.Parameters.Add(pkParam, primaryKeyValue, pkTable.PrimaryKey.DataType); coll.Add(cmd); } } //execute DataService.ExecuteTransaction(coll); }
/// <summary> /// Saves this instance. /// </summary> public new virtual void Save() { DataProvider provider = DataService.GetInstance(ProviderName); LoadColumnNames(); // read the current state of the checkboxes Dictionary <string, bool> newState = new Dictionary <string, bool>(); foreach (ListItem l in Items) { newState.Add(l.Value, l.Selected); } // read what is in the database List <string> pastState = new List <string>(); QueryCommand lookupCmd = new QueryCommand(String.Empty, ProviderName); // quick hack to re-use BuildMappedElementCommand BuildMappedElementCommand(provider, lookupCmd); using (IDataReader rdr = DataService.GetReader(lookupCmd)) { while (rdr.Read()) { pastState.Add(rdr[MapTableFkToForeignTable].ToString()); } rdr.Close(); } // build the commands to be executed. QueryCommandCollection coll = new QueryCommandCollection(); string fkParam = Utility.PrefixParameter("fkID", provider); string pkParam = Utility.PrefixParameter("pkID", provider); foreach (KeyValuePair <string, bool> kvp in newState) { string sql; // if we have it now but did not before if (kvp.Value && !pastState.Contains(kvp.Key)) { sql = String.Format("INSERT INTO {0} ({1},{2}) VALUES ({3},{4})", MapTableName, MapTableFkToForeignTable, MapTableFkToPrimaryTable, fkParam, pkParam); } else if (!kvp.Value && pastState.Contains(kvp.Key)) // we don't have it now but had it before { sql = String.Format("DELETE FROM {0} WHERE {1} = {2} AND {3} = {4}", MapTableName, MapTableFkToPrimaryTable, pkParam, MapTableFkToForeignTable, fkParam); } else { continue; // nothing changed. } QueryCommand cmd = new QueryCommand(sql, ProviderName); cmd.Parameters.Add(fkParam, kvp.Key, DataService.GetSchema(ForeignTableName, ProviderName).PrimaryKey.DataType); cmd.Parameters.Add(pkParam, PrimaryKeyValue, DataService.GetSchema(PrimaryTableName, ProviderName).PrimaryKey.DataType); coll.Add(cmd); } //execute if (coll.Count > 0) { DataService.ExecuteTransaction(coll); } }