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