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