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