/// <summary> /// Gets the reader. /// </summary> /// <returns></returns> public IDataReader GetReader() { IDataReader result = DataService.GetReader(_cmd); OutputValues = _cmd.OutputValues; return(result); }
/// <summary> /// Gets the SP params. /// </summary> /// <param name="spName">Name of the sp.</param> /// <returns></returns> public override IDataReader GetSPParams(string spName) { QueryCommand cmd = new QueryCommand(SP_PARAM_SQL, Name); cmd.AddParameter(OBJECT_NAME_PARAMETER, spName, DbType.AnsiString); return(DataService.GetReader(cmd)); }
/// <summary> /// Gets the reader. /// </summary> /// <returns></returns> public IDataReader GetReader() { IDataReader result = DataService.GetReader(Command); OutputValues = Command.OutputValues; return(result); }
/// <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> /// Returns all records for the given column, comparison operator, and parameter /// This overload is used for queries that don't use and '=' operator, i.e. IS, IS NOT, etc. /// </summary> /// <param name="columnName">The name of the column, as defined in the database</param> /// <param name="comparison">The comparison operator used for the query</param> /// <param name="oValue">The value to match when fetching</param> /// <returns>IDataReader</returns> public static IDataReader FetchByParameter(string columnName, Comparison comparison, object oValue) { Query q = new Query(BaseSchema); q.AddWhere(columnName, comparison, oValue); CheckLogicalDelete(q); return(DataService.GetReader(q.BuildSelectCommand())); }
/// <summary> /// Returns all records for this table, ordered /// </summary> /// <returns>Generic Typed List</returns> /// <param name="orderBy">OrderBy object used to specify sort behavior</param> public static IDataReader FetchAll(OrderBy orderBy) { Query q = new Query(BaseSchema); q.OrderBy = orderBy; CheckLogicalDelete(q); return(DataService.GetReader(q.BuildSelectCommand())); }
/// <summary> /// Returns all records for this table /// </summary> /// <returns>IDataReader</returns> public static IDataReader FetchAll() { Query q = new Query(BaseSchema); CheckLogicalDelete(q); IDataReader rdr = DataService.GetReader(q.BuildSelectCommand()); return(rdr); }
/// <summary> /// Finds the specified item by building a SELECT query with conditions matching /// the values of the properties on the AbstractRecord object to the columns /// on the corresponding database table /// </summary> /// <param name="item">The AbstractRecord derived type</param> /// <param name="orderBy">The OrberBy object used to return results</param> /// <returns></returns> public static IDataReader Find(T item, OrderBy orderBy) { Query q = new Query(BaseSchema); CheckLogicalDelete(q); // retrieve data from database foreach (TableSchema.TableColumn col in BaseSchema.Columns) { string columnName = col.ColumnName; object columnValue = item.GetColumnValue <object>(columnName); if (!Utility.IsAuditField(columnName)) { object defaultValue = String.Empty; switch (col.DataType) { case DbType.Boolean: defaultValue = false; break; case DbType.Currency: case DbType.Decimal: case DbType.Int16: case DbType.Double: case DbType.Int32: defaultValue = 0; break; case DbType.Date: case DbType.DateTime: defaultValue = new DateTime(1900, 1, 1); break; case DbType.Guid: defaultValue = Guid.Empty; break; } if (columnValue != null) { if (!columnValue.Equals(defaultValue)) { q.AddWhere(columnName, columnValue); } } } } if (orderBy != null) { q.OrderBy = orderBy; } return(DataService.GetReader(q.BuildSelectCommand())); }
/// <summary> /// Gets the reader. /// </summary> /// <param name="spAndParams">The sp and params.</param> /// <param name="providerName">Name of the provider.</param> /// <returns></returns> public static IDataReader GetReader(string spAndParams, string providerName) { QueryCommand cmd = new QueryCommand("EXEC " + spAndParams, providerName); cmd.CommandType = CommandType.Text; cmd.ProviderName = providerName; IDataReader result = DataService.GetReader(cmd); return(result); }
/// <summary> /// Executes the typed list. /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public List <T> ExecuteTypedList <T>() where T : new() { List <T> result; using (IDataReader rdr = DataService.GetReader(Command)) { result = SqlQuery.BuildTypedResult <T>(rdr); rdr.Close(); } return(result); }
/// <summary> /// Gets the reader. /// </summary> /// <param name="spAndParams">The sp and params.</param> /// <param name="providerName">Name of the provider.</param> /// <returns></returns> public static IDataReader GetReader(string spAndParams, string providerName) { QueryCommand cmd = new QueryCommand(String.Concat("EXEC ", spAndParams), providerName) { CommandType = CommandType.Text, ProviderName = providerName }; IDataReader result = DataService.GetReader(cmd); return(result); }
/// <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(); if (Items.Count == 0) { //make sure the default props are set if (String.IsNullOrEmpty(PrimaryKeyValue) || String.IsNullOrEmpty(PrimaryTableName) || String.IsNullOrEmpty(ProviderName) || String.IsNullOrEmpty(MapTableName)) { throw new Exception( "Missing a setting. Please be sure to set the PrimaryKeyValue (e.g. 'ProductID', PrimaryTableName (e.g. 'Products'), ProviderName (e.g. 'Northwind'), and MapTableName (e.g. Product_Category_Map)"); } DataProvider provider = DataService.GetInstance(ProviderName); // load column names LoadColumnNames(); QueryCommand cmd = BuildForeignQueryCommand(); BuildMappedElementCommand(provider, cmd); //load the list items using (IDataReader rdr = DataService.GetReader(cmd)) { while (rdr.Read()) { ListItem item = new ListItem(rdr[ForeignTextColumnName].ToString(), rdr[ForeignValueColumnName].ToString()); Items.Add(item); } rdr.NextResult(); while (rdr.Read()) { string thisVal = rdr[MapTableFkToForeignTable].ToString().ToLower(); foreach (ListItem loopItem in Items) { if (loopItem.Value.ToLower() == thisVal) { loopItem.Selected = true; } } } rdr.Close(); } } }
/// <summary> /// Gets the primary key table names. /// </summary> /// <param name="tableName">Name of the table.</param> /// <returns></returns> public override ArrayList GetPrimaryKeyTableNames(string tableName) { QueryCommand cmd = new QueryCommand(GET_PRIMARY_KEY_SQL, Name); cmd.AddParameter(TABLE_NAME_PARAMETER, tableName, DbType.AnsiString); ArrayList list = new ArrayList(); using (IDataReader rdr = DataService.GetReader(cmd)) { while (rdr.Read()) { list.Add(new string[] { rdr[SqlSchemaVariable.TABLE_NAME].ToString(), rdr[SqlSchemaVariable.COLUMN_NAME].ToString() }); } rdr.Close(); } return(list); }
/// <summary> /// Executes as collection. /// </summary> /// <typeparam name="ListType">The type of the ist type.</typeparam> /// <param name="sql">The SQL.</param> /// <param name="values">The values.</param> /// <returns></returns> public virtual ListType ExecuteAsCollection <ListType>(string sql, params object[] values) where ListType : IAbstractList, new() { QueryCommand cmd = GetCommand(sql, values); ListType list = new ListType(); try { IDataReader rdr = DataService.GetReader(cmd); list.LoadAndCloseReader(rdr); } catch (Exception x) { SqlQueryException ex = new SqlQueryException(x.Message); throw ex; } return(list); }
/// <summary> /// Gets the SP list. /// </summary> /// <returns></returns> public override string[] GetSPList() { QueryCommand cmd = new QueryCommand(SP_SQL, Name); StringBuilder sList = new StringBuilder(); using (IDataReader rdr = DataService.GetReader(cmd)) { bool isFirst = true; while (rdr.Read()) { if (!isFirst) { sList.Append(DELIMITER); } isFirst = false; sList.Append(rdr[0]); } rdr.Close(); return(sList.ToString().Split(DELIMITER.ToCharArray(), StringSplitOptions.RemoveEmptyEntries)); } }
/// <summary> /// Gets the view name list. /// </summary> /// <returns></returns> public override string[] GetViewNameList() { QueryCommand cmd = new QueryCommand(VIEW_SQL, Name); StringBuilder sList = new StringBuilder(); using (IDataReader rdr = DataService.GetReader(cmd)) { while (rdr.Read()) { string viewName = rdr[SqlSchemaVariable.NAME].ToString(); if (String.IsNullOrEmpty(ViewStartsWith) || viewName.StartsWith(ViewStartsWith)) { sList.Append(rdr[SqlSchemaVariable.NAME]); sList.Append("|"); } } rdr.Close(); } string[] strArray = sList.ToString().Split(DELIMITER.ToCharArray(), StringSplitOptions.RemoveEmptyEntries); Array.Sort(strArray); return(strArray); }
/// <summary> /// Gets the table name list. /// </summary> /// <returns></returns> public override string[] GetTableNameList() { QueryCommand cmd = new QueryCommand(TABLE_SQL, Name); StringBuilder sList = new StringBuilder(); using (IDataReader rdr = DataService.GetReader(cmd)) { bool isFirst = true; while (rdr.Read()) { if (!isFirst) { sList.Append(DELIMITER); } isFirst = false; sList.Append(rdr[SqlSchemaVariable.NAME]); } rdr.Close(); } string[] strArray = sList.ToString().Split(DELIMITER.ToCharArray(), StringSplitOptions.RemoveEmptyEntries); Array.Sort(strArray); return(strArray); }
/// <summary> /// Executes the reader. /// </summary> /// <param name="sql">The SQL.</param> /// <param name="values">The values.</param> /// <returns></returns> public IDataReader ExecuteReader(string sql, params object[] values) { IDataReader result = DataService.GetReader(GetCommand(sql, values)); return(result); }
/// <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> /// Executes the typed list. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql">The SQL.</param> /// <param name="values">The values.</param> /// <returns></returns> public List <T> ExecuteTypedList <T>(string sql, params object[] values) where T : new() { QueryCommand cmd = GetCommand(sql, values); List <T> result = new List <T>(); IDataReader rdr; Type iType = typeof(T); try { rdr = DataService.GetReader(cmd); } catch (Exception x) { SqlQueryException ex = new SqlQueryException(x.Message); throw ex; } if (rdr != null) { using (rdr) { if (iType is IActiveRecord) { //load it while (rdr.Read()) { T item = new T(); //set to ActiveRecord IActiveRecord arItem = (IActiveRecord)item; arItem.Load(rdr); result.Add(item); } } else { //coerce the values, using some light reflection iType.GetProperties(); //set the values PropertyInfo prop; while (rdr.Read()) { T item = new T(); for (int i = 0; i < rdr.FieldCount; i++) { prop = iType.GetProperty(rdr.GetName(i)); if (prop != null && !DBNull.Value.Equals(rdr.GetValue(i))) { prop.SetValue(item, rdr.GetValue(i), null); } } result.Add(item); } } rdr.Close(); } } return(result); }
/// <summary> /// Gets the table schema. /// </summary> /// <param name="tableName">Name of the table.</param> /// <param name="tableType">Type of the table.</param> /// <returns></returns> public override TableSchema.Table GetTableSchema(string tableName, TableType tableType) { TableSchema.TableColumnCollection columns = new TableSchema.TableColumnCollection(); TableSchema.Table tbl = new TableSchema.Table(tableName, tableType, this); //tbl.ClassName = Convention.ClassName(tableName); //string sql = TABLE_COLUMN_SQL; QueryCommand cmd = new QueryCommand(TABLE_COLUMN_SQL, Name); cmd.AddParameter(TABLE_NAME_PARAMETER, tableName, DbType.AnsiString); TableSchema.TableColumn column; using (IDataReader rdr = DataService.GetReader(cmd)) { //get information about both the table and it's columns while (rdr.Read()) { tbl.SchemaName = rdr["USER"].ToString(); column = new TableSchema.TableColumn(tbl); column.ColumnName = rdr[OracleSchemaVariable.COLUMN_NAME].ToString(); string scale = rdr[OracleSchemaVariable.NUMBER_SCALE].ToString(); string precision = rdr[OracleSchemaVariable.NUMBER_PRECISION].ToString(); column.NumberScale = 0; column.NumberPrecision = 0; if (!String.IsNullOrEmpty(scale) && scale != "0") { column.NumberScale = int.Parse(scale); } if (!String.IsNullOrEmpty(precision) && precision != "0") { column.NumberPrecision = int.Parse(precision); } column.DataType = GetDbType(rdr[OracleSchemaVariable.DATA_TYPE].ToString().ToLower()); column.AutoIncrement = false; int maxLength; int.TryParse(rdr[OracleSchemaVariable.MAX_LENGTH].ToString(), out maxLength); column.MaxLength = maxLength; column.IsNullable = Utility.IsMatch(rdr[OracleSchemaVariable.IS_NULLABLE].ToString(), "Y"); column.IsReadOnly = false; columns.Add(column); } } cmd.CommandSql = INDEX_SQL; //cmd.AddParameter(TABLE_NAME_PARAMETER, tableName); using (IDataReader rdr = DataService.GetReader(cmd)) { while (rdr.Read()) { string colName = rdr[OracleSchemaVariable.COLUMN_NAME].ToString(); string constraintType = rdr[OracleSchemaVariable.CONSTRAINT_TYPE].ToString(); column = columns.GetColumn(colName); if (constraintType == SqlSchemaVariable.PRIMARY_KEY) { column.IsPrimaryKey = true; } else if (constraintType == SqlSchemaVariable.FOREIGN_KEY) { column.IsForeignKey = true; } //HACK: Allow second pass naming adjust based on whether a column is keyed column.ColumnName = column.ColumnName; } rdr.Close(); } if (columns.Count > 0) { tbl.Columns = columns; return(tbl); } return(null); }
/// <summary> /// Returns all records for the given Query object /// </summary> /// <param name="query">Query for complex records</param> /// <returns>Generic Typed List</returns> public static IDataReader FetchByQuery(Query query) { CheckLogicalDelete(query); return(DataService.GetReader(query.BuildSelectCommand())); }