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