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