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