/// <summary>
/// Gets the primary key tables.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <returns></returns>
public override TableSchema.Table[] GetPrimaryKeyTables(string tableName)
{
QueryCommand cmd = new QueryCommand(GET_PRIMARY_KEY_SQL, Name);
cmd.AddParameter(TABLE_NAME_PARAMETER, tableName, DbType.AnsiString);
ArrayList names = new ArrayList();
using (IDataReader rdr = GetReader(cmd))
{
while (rdr.Read())
{
names.Add(rdr[SqlSchemaVariable.TABLE_NAME].ToString());
}
rdr.Close();
}
if (names.Count > 0)
{
TableSchema.Table[] tables = new TableSchema.Table[names.Count];
for (int i = 0; i < names.Count; i++)
{
tables[i] = DataService.GetSchema((string)names[i], Name, TableType.Table);
}
return(tables);
}
return(null);
}
/// <summary>
/// Called by the ASP.NET page framework to notify server controls that use composition-based implementation to create any child controls they contain in preparation for posting back or rendering.
/// </summary>
protected override void CreateChildControls()
{
base.CreateChildControls();
Items.Clear();
//make sure the default props are set
if (String.IsNullOrEmpty(primaryKeyValue) || String.IsNullOrEmpty(primaryTableName) || String.IsNullOrEmpty(mapTableName))
{
throw new Exception(
"Missing a setting. Please be sure to set the PrimaryKeyValue (e.g. 'ProductID', PrimaryTableName (e.g. 'Products'), and MapTableName (e.g. Product_Category_Map)");
}
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 foreignTextColumn = fkTable.Columns[1].ColumnName;
if (!string.IsNullOrEmpty(ForeignTextField))
{
if (!fkTable.Columns.Contains(ForeignTextField))
{
throw new Exception("Invalid ForeignTextField. Please be sure to set the value to a field name from " + foreignTableName);
}
foreignTextColumn = ForeignTextField;
}
//batch this into one call
string idParam = provider.FormatParameterNameForSQL("id");
QueryCommand cmd = new QueryCommand("SELECT " + fkPK + "," + foreignTextColumn + " FROM " + foreignTableName + ";", providerName);
cmd.CommandSql += "SELECT " + fkPK + " FROM " + mapTableName + " WHERE " + pkTable.PrimaryKey.ColumnName + " = " + idParam;
cmd.Parameters.Add(idParam, primaryKeyValue, pkTable.PrimaryKey.DataType);
//load the list items
using (IDataReader rdr = DataService.GetReader(cmd))
{
while (rdr.Read())
{
ListItem item = new ListItem(rdr[1].ToString(), rdr[0].ToString());
Items.Add(item);
}
rdr.NextResult();
while (rdr.Read())
{
string thisVal = rdr[fkPK].ToString().ToLower();
foreach (ListItem loopItem in Items)
{
if (Utility.IsMatch(loopItem.Value, thisVal))
{
loopItem.Selected = true;
}
}
}
rdr.Close();
}
}
/// <summary>
/// If the user does not specify any column names, they are inferred from the schema.
///
/// </summary>
protected virtual void LoadColumnNames()
{
// we don't have a user-defined key
if (String.IsNullOrEmpty(PrimaryKeyName))
{
// load primary table
TableSchema.Table pkTable = DataService.GetSchema(PrimaryTableName, ProviderName);
PrimaryKeyName = pkTable.PrimaryKey == null ? pkTable.Columns[0].ColumnName : pkTable.PrimaryKey.ColumnName;
}
// we don't have a user-defined key
if (String.IsNullOrEmpty(ForeignValueColumnName) || String.IsNullOrEmpty(ForeignTextColumnName))
{
TableSchema.Table fkTable = DataService.GetSchema(ForeignTableName, ProviderName);
// we don't have a user-defined key
if (String.IsNullOrEmpty(ForeignValueColumnName))
{
ForeignValueColumnName = fkTable.PrimaryKey == null ? fkTable.Columns[0].ColumnName : fkTable.PrimaryKey.ColumnName;
}
// use another column for the name if it is available
if (String.IsNullOrEmpty(ForeignTextColumnName))
{
ForeignTextColumnName = fkTable.Columns[fkTable.Columns.Count >= 1 ? 1 : 0].ColumnName;
}
}
/* detect the mapping table column names */
if (String.IsNullOrEmpty(_mapTableFkToPrimaryTable) || String.IsNullOrEmpty(_mapTableFkToForeignTable))
{
// load mapping table
TableSchema.Table mapTable = DataService.GetSchema(MapTableName, ProviderName);
foreach (TableSchema.ForeignKeyTable fkt in mapTable.ForeignKeys)
{
if (String.IsNullOrEmpty(_mapTableFkToPrimaryTable) && fkt.TableName.ToLower() == PrimaryTableName.ToLower())
{
_mapTableFkToPrimaryTable = fkt.ColumnName;
}
else if (String.IsNullOrEmpty(_mapTableFkToForeignTable) && fkt.TableName.ToLower() == ForeignTableName.ToLower())
{
_mapTableFkToForeignTable = fkt.ColumnName;
}
}
}
}
/// <summary>
/// Selects the elements from the mapped table which are also in the filtered foreign query
///
/// </summary>
/// <param name="provider">the provider</param>
/// <param name="cmd">The command to which the select from the mapped table will be appended</param>
protected virtual void BuildMappedElementCommand(DataProvider provider, QueryCommand cmd)
{
string userFilter = String.Empty;
string idParam = Utility.PrefixParameter("id", provider);
if (!String.IsNullOrEmpty(ForeignWhere))
{
userFilter +=
String.Format("INNER JOIN {0} ON {0}.{1}={2}.{1} WHERE {3}={4} AND {5}", ForeignTableName, ForeignValueColumnName, MapTableName, MapTableFkToPrimaryTable,
idParam, ForeignWhere);
}
else
{
userFilter += String.Format("WHERE {0}={1}", MapTableFkToPrimaryTable, idParam);
}
cmd.CommandSql += String.Format("SELECT {1}.{0} FROM {1} {2}", MapTableFkToForeignTable, MapTableName, userFilter);
cmd.Parameters.Add(idParam, PrimaryKeyValue, DataService.GetSchema(PrimaryTableName, ProviderName).PrimaryKey.DataType);
}
/// <summary>
/// Return a list with all the primary keys of the table and the tables that reference it.
/// See the other overload for a detailed explanation.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <returns></returns>
public override TableSchema.Table[] GetPrimaryKeyTables(string tableName)
{
ArrayList pks = GetPrimaryKeyTableNames(tableName);
if (pks.Count > 0)
{
const int REF_TABLENAME_INDEX = 0;
TableSchema.Table[] tables = new TableSchema.Table[pks.Count];
for (int i = 0; i < pks.Count; i++)
{
string[] refTable = (string[])pks[i];
tables[i] = DataService.GetSchema(refTable[REF_TABLENAME_INDEX], Name, TableType.Table);
}
return(tables);
}
return(null);
}
/// <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>
/// Handles the <see cref="E:System.Web.UI.Control.Init"/> event.
/// </summary>
/// <param name="e">An <see cref="T:System.EventArgs"/> object that contains the event data.</param>
protected override void OnInit(EventArgs e)
{
base.OnInit(e);
if (!DesignMode)
{
// load em up
// cheap way to check for load state
if (Items.Count == 0)
{
if (!String.IsNullOrEmpty(tableName))
{
DataProvider provider = DataService.GetInstance(ProviderName);
SqlQuery q = new Select(provider).From(tableName);
q.CheckLogicalDelete();
if (String.IsNullOrEmpty(valueField) || String.IsNullOrEmpty(textField))
{
// look it up using the table schema
TableSchema.Table tbl = DataService.GetSchema(tableName, providerName, TableType.Table);
if (tbl != null)
{
if (String.IsNullOrEmpty(valueField))
{
valueField = tbl.PrimaryKey.ColumnName;
}
if (String.IsNullOrEmpty(textField))
{
textField = tbl.Columns.Count > 1 ? tbl.Columns[1].ColumnName : tbl.Columns[0].ColumnName;
}
}
else
{
throw new Exception("Table name '" + tableName + "' using Provider '" + providerName + "' doesn't work");
}
}
q.SelectColumnList = new[] { valueField, textField };
if (!String.IsNullOrEmpty(OrderField))
{
q.OrderAsc(OrderField);
}
else
{
q.OrderAsc(textField);
}
if (!String.IsNullOrEmpty(WhereField))
{
q.Where(WhereField).IsEqualTo(WhereValue);
}
IDataReader rdr = null;
try
{
rdr = q.ExecuteReader();
while (rdr.Read())
{
ListItem item = new ListItem(rdr[1].ToString(), rdr[0].ToString());
Items.Add(item);
}
}
catch (DataException x)
{
throw new Exception("Error loading up ListItems for " + ClientID + ": " + x.Message);
}
finally
{
if (rdr != null)
{
rdr.Close();
}
}
ListItem prompt = new ListItem(promptText, PromptValue);
if (showPrompt)
{
Items.Insert(0, prompt);
}
if (!String.IsNullOrEmpty(SelectedValue))
{
foreach (ListItem item in Items)
{
if (Utility.IsMatch(item.Value, SelectedValue))
{
item.Selected = true;
break;
}
}
}
}
}
}
}
/// <summary>
/// Initializes a new instance of the <see cref="Insert"/> class.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <param name="providerName">Name of the provider.</param>
public Insert(string tableName, string providerName)
{
TableSchema.Table tbl = DataService.GetSchema(tableName, providerName);
Init(tbl);
}
/// <summary>
/// Initializes a new instance of the <see cref="Insert"/> class.
/// WARNING: This overload should only be used with applications that use a single provider!
/// </summary>
/// <param name="tableName">Name of the table.</param>
public Insert(string tableName)
{
TableSchema.Table tbl = DataService.GetSchema(tableName, String.Empty);
Init(tbl);
}
/// <summary>
/// Called by the ASP.NET page framework to notify server controls that use composition-based implementation to create any child controls they contain in preparation for posting back or rendering.
/// </summary>
protected override void CreateChildControls()
{
base.CreateChildControls();
tbl.CellPadding = 3;
tbl.CellSpacing = 0;
tbl.Width = "100%";
tblPage.Width = "100%";
//add three rows to this table
//i know this might seem sort of strange
//but given that we're dealing with eventing of buttons
//.NET is a bit clumsy about it - these controls
//need to exist in the control bag in order for the
//event to be recognize. So we add them up front
trTop.Cells.Add(tdTop);
tblWrap.Rows.Add(trTop);
trBottom.Cells.Add(tdBottom);
tblWrap.Rows.Add(trBottom);
tdTop.Controls.Add(tbl);
tdBottom.Controls.Add(tblPage);
Controls.Add(tblWrap);
//set CSS
if (!String.IsNullOrEmpty(tableCSSClass))
{
tbl.Attributes.Add(CLASS, tableCSSClass);
}
else
{
tbl.Attributes.Add(STYLE, tableStyle);
}
if (!String.IsNullOrEmpty(pagerButtonCSS))
{
btnFirst.Attributes.Add(CLASS, pagerButtonCSS);
btnPrev.Attributes.Add(CLASS, pagerButtonCSS);
btnNext.Attributes.Add(CLASS, pagerButtonCSS);
btnLast.Attributes.Add(CLASS, pagerButtonCSS);
ddlPages.Attributes.Add(CLASS, pagerButtonCSS);
}
else
{
btnFirst.Attributes.Add(STYLE, pagerButtonStyle);
btnPrev.Attributes.Add(STYLE, pagerButtonStyle);
btnNext.Attributes.Add(STYLE, pagerButtonStyle);
btnLast.Attributes.Add(STYLE, pagerButtonStyle);
ddlPages.Attributes.Add(STYLE, pagerButtonStyle);
}
//have to load up the pager buttons to the control set so we recognize them on
//postback
//load the schema
schema = DataService.GetSchema(tableName, ProviderName, TableType.Table) ?? DataService.GetSchema(tableName, ProviderName, TableType.View);
if (schema == null)
{
throw new Exception("Can't find a table names " + tableName + ". Did you set the correct providerName?");
}
//load the headers
BuildHeader();
BuildPager();
//if(!Page.IsPostBack)
//{
LoadGrid();
trBottom.Visible = !(pageSize >= totalRecords);
//}
}
/// <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);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="Update"/> class.
/// </summary>
/// <param name="tableName">Name of the table.</param>
public Update(string tableName)
{
TableSchema.Table tbl = DataService.GetSchema(tableName, "");
Init(tbl);
}
