/// <summary>
/// Returns a DELETE QueryCommand object to delete the records with
/// matching passed column/value criteria
/// </summary>
/// <param name="columnName">Name of the column to match</param>
/// <param name="oValue">Value of column to match</param>
/// <returns></returns>
public static QueryCommand GetDeleteCommand(string columnName, object oValue)
{
T item = new T();
QueryCommand cmd;
TableSchema.Table tbl = item.GetSchema();
TableSchema.TableColumn colDeleted = tbl.GetColumn(ReservedColumnName.DELETED);
TableSchema.TableColumn colIsDeleted = tbl.GetColumn(ReservedColumnName.IS_DELETED);
if (colDeleted != null)
{
cmd = new Update(tbl).Set(colDeleted).EqualTo(true).Where(columnName).IsEqualTo(oValue).BuildCommand();
}
else if (colIsDeleted != null)
{
cmd = new Update(tbl).Set(colIsDeleted).EqualTo(true).Where(columnName).IsEqualTo(oValue).BuildCommand();
}
else
{
Query q = new Query(item.GetSchema())
{
QueryType = QueryType.Delete
};
q.AddWhere(columnName, oValue);
cmd = DataService.BuildCommand(q);
}
return(cmd);
}
public void MigrationAddRemoveColumns()
{
Migration m = new MigrationTest003();
m.Migrate("Northwind", Migration.MigrationDirection.Up);
DataService.ClearSchemaCache("Northwind");
TableSchema.Table table = DataService.GetSchema("Products", "Northwind");
Assert.IsNotNull(table.GetColumn("ProductExpiration"));
Assert.IsNull(table.GetColumn("ProductName"));
}
public void QueryExecuteJoinedDataSet_ORDER_BY_DESC_Collection()
{
Query q = new Query("Products");
TableSchema.Table ts = DataService.GetTableSchema("Products", "Northwind");
DataSet ds = q.ORDER_BY(ts.GetColumn("SupplierID"), SqlFragment.DESC).
ORDER_BY(ts.GetColumn("ProductID"), SqlFragment.DESC).
ExecuteJoinedDataSet();
//should bring 48 as first (SupplierID is replaced by CompanyName)
Assert.IsTrue(ds.Tables[0].Rows[0]["ProductID"].Equals(48));
}
public void Query_ORDER_BY_DESC_Collection()
{
Query q = new Query("Products");
TableSchema.Table ts = DataService.GetTableSchema("Products", "Northwind");
DataSet ds = q.ORDER_BY(ts.GetColumn("CategoryID"), SqlFragment.DESC).
ORDER_BY(ts.GetColumn("ProductID"), SqlFragment.DESC).
ExecuteDataSet();
//should bring 73 as first
Assert.IsTrue(ds.Tables[0].Rows[0]["ProductID"].Equals(73));
}
public void Query_ORDER_BY_Collection()
{
Query q = new Query("Products");
TableSchema.Table ts = DataService.GetTableSchema("Products", "Northwind");
DataSet ds = q.ORDER_BY(ts.GetColumn("SupplierID")).
ORDER_BY(ts.GetColumn("ProductID")).
ExecuteDataSet();
//should bring 2 as first
Assert.IsTrue(ds.Tables[0].Rows[0]["ProductID"].Equals(2));
}
public void Query_OrderByCollection()
{
Query q = new Query("Products");
TableSchema.Table ts = DataService.GetTableSchema("Products", "Northwind");
q.OrderByCollection.Add(OrderBy.Desc(ts.GetColumn("CategoryID")));
q.OrderByCollection.Add(OrderBy.Desc(ts.GetColumn("ProductID")));
DataSet ds = q.ExecuteDataSet();
//should bring 73 as first
Assert.IsTrue(ds.Tables[0].Rows[0]["ProductID"].Equals(73));
}
public void Acc_Query_JoinedDataSet_OrderByFK()
{
Query q = new Query("Products", "NorthwindAccess");
TableSchema.Table ts = DataService.GetTableSchema("Products", "NorthwindAccess");
//q.OrderBy = OrderBy.Desc(ts.GetColumn("CategoryID"));
q.ORDER_BY(ts.GetColumn("CategoryID"), "DESC");
q.ORDER_BY(ts.GetColumn("ProductID"), "ASC");
DataSet ds = q.ExecuteJoinedDataSet();
//should bring 10 as first
Assert.IsTrue(ds.Tables[0].Rows[0]["ProductID"].Equals(10));
}
/// <summary>
/// Builds the delete statement.
/// </summary>
/// <returns></returns>
public string BuildDeleteStatement()
{
StringBuilder sb = new StringBuilder();
//see if the from table has a "Deleted"
TableSchema.Table tbl = query.FromTables[0];
if (tbl.Columns.Contains(ReservedColumnName.DELETED) && query.GetType().Name != "Destroy")
{
TableSchema.TableColumn col = tbl.GetColumn("deleted");
sb.Append(SqlFragment.UPDATE);
sb.Append(tbl.QualifiedName);
sb.Append(SqlFragment.SET);
sb.Append(col.QualifiedName);
//by default this is a bit in the DB
//may have to rework this for MySQL
sb.Append(" = 1");
}
else
{
sb.Append(SqlFragment.DELETE_FROM);
sb.Append(query.FromTables[0].QualifiedName);
}
sb.Append(GenerateWhere());
return(sb.ToString());
}
/// <summary>
/// Gets the current schema version for the named provider.
/// </summary>
/// <param name="providerName">Name of the provider.</param>
/// <returns>Current version of the schema as stored in the schema info table.</returns>
public static int GetCurrentVersion(string providerName)
{
int currentVersion = 0;
DataProvider p = DataService.Providers[providerName];
TableSchema.Table schemaTable = p.GetTableSchema(SCHEMA_INFO, TableType.Table);
if (schemaTable != null && schemaTable.GetColumn("version") != null)
{
currentVersion = new Select(p, "version").From(SCHEMA_INFO).ExecuteScalar <int>();
}
else
{
//create schema table if it doesn't exist
if (schemaTable == null)
{
CreateSchemaInfo(providerName);
}
//delete all rows & add the version row
new Delete(providerName).From(SCHEMA_INFO).Execute();
new Insert(SCHEMA_INFO, providerName).Values(0).Execute();
}
return(currentVersion);
}
/// <summary>
/// Sets a value for a particular column in the record
/// </summary>
/// <param name="columnName">Name of the column, as defined in the database</param>
/// <param name="oValue">The value to set the type to</param>
public void SetColumnValue(string columnName, object oValue)
{
columnSettings = columnSettings ?? new TableSchema.TableColumnSettingCollection();
// add the column to the DirtyColumns
// if this instance has already been loaded
// and this is a change to existing values
if (IsLoaded && !IsNew)
{
TableSchema.Table schema = GetSchema();
object oldValue = null;
string oldValueMsg = "NULL";
string newValueMsg = "NULL";
bool areEqualOrBothNull = false;
try
{
oldValue = columnSettings.GetValue(columnName);
}
catch {}
if (oldValue == null && oValue == null)
{
areEqualOrBothNull = true;
}
else
{
if (oldValue != null)
{
oldValueMsg = oldValue.ToString();
areEqualOrBothNull = oldValue.Equals(oValue);
}
if (oValue != null)
{
newValueMsg = oValue.ToString();
}
}
TableSchema.TableColumn dirtyCol = schema.GetColumn(columnName);
if (dirtyCol != null && !areEqualOrBothNull)
{
string auditMessage = String.Format("Value changed from {0} to {1}{2}", oldValueMsg, newValueMsg, Environment.NewLine);
TableSchema.TableColumn dirtyEntry = DirtyColumns.GetColumn(columnName);
if (dirtyEntry != null)
{
DirtyColumns.Remove(dirtyEntry);
auditMessage = String.Concat(dirtyCol.AuditMessage, auditMessage);
}
dirtyCol.AuditMessage = auditMessage;
DirtyColumns.Add(dirtyCol);
}
}
columnSettings.SetValue(columnName, oValue);
}
/// <summary>
/// Removes the column named columnName from the table named tableName.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <param name="columnName">Name of the column to remove.</param>
public void RemoveColumn(string tableName, string columnName)
{
TableSchema.Table table = Provider.GetTableSchema(tableName, TableType.Table);
if (table == null)
{
throw new ArgumentException("Unknown table name " + tableName);
}
TableSchema.TableColumn column = table.GetColumn(columnName);
AddMigrationStep(MigrationStepType.DropColumn, table, column);
}
public void Remove_Column()
{
TableSchema.Table productSchema = Product.Schema;
TableSchema.TableColumn column = productSchema.GetColumn("ProductName");
ISqlGenerator generator = new MySqlGenerator(null);
string sql = generator.BuildDropColumnStatement(productSchema, column);
Assert.AreEqual("ALTER TABLE `products` DROP COLUMN `ProductName`", sql);
}
public void MigrationShouldCreateAndDropTestTable()
{
//up
Migrator.Migrate("Northwind", MigrationDirectory, 1);
DataService.ClearSchemaCache("Northwind");
TableSchema.Table table = DataService.GetSchema("Test1", "Northwind");
Assert.IsNotNull(table.GetColumn("Name"));
Assert.IsNotNull(table.GetColumn("Description"));
Assert.IsNotNull(table.GetColumn("DateEntered"));
int schemaVersion = Migrator.GetCurrentVersion("Northwind");
Assert.AreEqual(1, schemaVersion);
//down
Migrator.Migrate("Northwind", MigrationDirectory, 0);
Assert.IsNull(DataService.GetSchema("Test1", "Northwind"));
schemaVersion = Migrator.GetCurrentVersion("Northwind");
Assert.AreEqual(0, schemaVersion);
}
public void Remove_Column()
{
TableSchema.Table productSchema = Product.Schema;
TableSchema.TableColumn column = productSchema.GetColumn("ProductName");
ANSISqlGenerator gen = new ANSISqlGenerator(null);
string sql = gen.BuildDropColumnStatement(productSchema, column);
Assert.AreEqual("ALTER TABLE [dbo].[Products] DROP COLUMN [ProductName]", sql);
}
public void Alter_Column()
{
TableSchema.Table productSchema = Product.Schema;
TableSchema.TableColumn column = productSchema.GetColumn("ProductName");
column.MaxLength = 150;
ISqlGenerator generator = new MySqlGenerator(null);
string sql = generator.BuildAlterColumnStatement(column);
Assert.AreEqual("ALTER TABLE `products` ALTER COLUMN `ProductName` nvarchar(150) NOT NULL", sql);
}
public void QueryExecuteJoinedDataSet_ORDER_BY()
{
Query q = new Query("Products");
TableSchema.Table ts = DataService.GetTableSchema("Products", "Northwind");
DataSet ds = q.ORDER_BY(ts.GetColumn("ProductID")).ExecuteJoinedDataSet();
//should bring 1 as first
Assert.IsTrue(ds.Tables[0].Rows[0]["ProductID"].Equals(1));
}
public void Acc_QueryExecuteJoinedDataSet_ORDER_BY_DESC()
{
Query q = new Query("Products", "NorthwindAccess");
TableSchema.Table ts = DataService.GetTableSchema("Products", "NorthwindAccess");
DataSet ds = q.ORDER_BY(ts.GetColumn("ProductID"), SqlFragment.DESC).ExecuteJoinedDataSet();
//should bring 77 as first
Assert.IsTrue(ds.Tables[0].Rows[0]["ProductID"].Equals(77));
}
/// <summary>
/// Adds the column.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <param name="columnName">Name of the column to add.</param>
/// <param name="dbType">Type of the db.</param>
/// <param name="length">The length.</param>
/// <param name="nullable">if set to <c>true</c> [nullable].</param>
/// <param name="defaultValue">The default value.</param>
public void AddColumn(string tableName, string columnName, DbType dbType, int length, bool nullable, string defaultValue)
{
TableSchema.Table table = Provider.GetTableSchema(tableName, TableType.Table);
if (table == null)
{
throw new ArgumentException("Unknown table name " + tableName);
}
table.AddColumn(columnName, dbType, length, nullable, defaultValue);
TableSchema.TableColumn column = table.GetColumn(columnName);
AddMigrationStep(MigrationStepType.AddColumn, table, column);
}
public void MigrationAlterColumn()
{
DataService.ClearSchemaCache("Northwind");
Migration m = new AlterProductNameMigration();
//Up
m.Migrate("Northwind", Migration.MigrationDirection.Up);
DataService.ClearSchemaCache("Northwind");
TableSchema.Table table = DataService.GetSchema("Products", "Northwind");
TableSchema.TableColumn column = table.GetColumn("ProductName");
Assert.AreEqual(100, column.MaxLength);
//Down
m.Migrate("Northwind", Migration.MigrationDirection.Down);
DataService.ClearSchemaCache("Northwind");
table = DataService.GetSchema("Products", "Northwind");
column = table.GetColumn("ProductName");
Assert.AreEqual(50, column.MaxLength);
}
public void Acc_Query_OrderBy()
{
Query q = new Query("Products", "NorthwindAccess");
TableSchema.Table ts = DataService.GetTableSchema("Products", "NorthwindAccess");
q.OrderBy = OrderBy.Desc(ts.GetColumn("ProductID"));
DataSet ds = q.ExecuteDataSet();
//should bring 77 as first
Assert.IsTrue(ds.Tables[0].Rows[0]["ProductID"].Equals(77));
}
public void Acc_CorrectSchemaLoad()
{
MySqlDataProvider prov = (MySqlDataProvider)DataService.Providers["Southwind"];
TableSchema.Table products = prov.GetTableSchema("Products", TableType.Table);
//Assert.IsTrue(products.GetColumn("IsDeleted").IsNullable == true); Not in MySql schema
Assert.IsTrue(products.GetColumn("Discontinued").IsNullable == false);
TableSchema.TableColumn col = products.GetColumn("Discontinued");
ICodeLanguage lang = new CSharpCodeLanguage();
string varTypeNonNull = Utility.GetVariableType(col.DataType, col.IsNullable, lang);
Assert.IsTrue(varTypeNonNull == "bool");
//Not in MySql Schema
//col = products.GetColumn("IsDeleted");
//string varTypeNullable = Utilities.Utility.GetVariableType(col.DataType, col.IsNullable, lang);
//Assert.IsTrue(varTypeNullable == "bool?");
}
public void Delete <T>(string columnName, object columnValue) where T : RepositoryRecord <T>, new()
{
T item = new T();
TableSchema.Table tbl = item.GetSchema();
if (tbl.GetColumn(ReservedColumnName.DELETED) != null)
{
//create an update command
new Update(tbl).Set(ReservedColumnName.DELETED).EqualTo(true).Where(columnName).IsEqualTo(columnValue).Execute();
}
else if (tbl.GetColumn(ReservedColumnName.IS_DELETED) != null)
{
new Update(tbl).Set(ReservedColumnName.IS_DELETED).EqualTo(true).Where(columnName).IsEqualTo(columnValue).Execute();
}
else
{
QueryCommand del = ActiveHelper <T> .GetDeleteCommand(columnName, columnValue);
DataService.ExecuteQuery(del);
}
}
public void Dual_ForeignKey_Relationships_ShouldBe_Possible_From_One_To_Many()
{
//load em
using (Migration m = new Migration("Northwind")) {
TableSchema.Table one_table = m.CreateTableWithKey("One");
TableSchema.Table many_table = m.CreateTableWithKey("ManyTable");
many_table.AddColumn("first_reference_to_table_one", System.Data.DbType.Int32);
many_table.AddColumn("second_reference_to_table_one", System.Data.DbType.Int32);
m.CreateForeignKey(one_table.GetColumn("Id"), many_table.GetColumn("first_reference_to_table_one"));
m.CreateForeignKey(one_table.GetColumn("Id"), many_table.GetColumn("second_reference_to_table_one"));
}
DataService.ClearSchemaCache("Northwind");
//drop em
using (Migration m = new Migration("Northwind")) {
m.DropTable("ManyTable");
m.DropTable("One");
}
}
public void Alter_Column()
{
TableSchema.Table productSchema = Product.Schema;
TableSchema.TableColumn column = productSchema.GetColumn("ProductName");
column.MaxLength = 150;
ANSISqlGenerator gen = new ANSISqlGenerator(null);
string sql = gen.BuildAlterColumnStatement(column);
Assert.AreEqual("ALTER TABLE [dbo].[Products] ALTER COLUMN [ProductName] nvarchar(150) NOT NULL", sql);
// Set it back to 40 or Create_Table fails.
column.MaxLength = 40;
}
public void MigrationShouldExecMultipleMigrations()
{
DataService.ClearSchemaCache("Northwind");
Migrator.Migrate("Northwind", MigrationDirectory, null);
DataService.ClearSchemaCache("Northwind");
TableSchema.Table table = DataService.GetSchema("Test1", "Northwind");
Assert.IsNotNull(table.GetColumn("Name"));
Assert.IsNotNull(table.GetColumn("Description"));
Assert.IsNotNull(table.GetColumn("DateEntered"));
Assert.IsNotNull(table.GetColumn("MyNewColumn"));
Assert.IsNotNull(table.GetColumn("MaxInventory"));
// Down()
Migrator.Migrate("Northwind", MigrationDirectory, 1);
DataService.ClearSchemaCache("Northwind");
table = DataService.GetSchema("Test1", "Northwind");
Assert.IsNotNull(table.GetColumn("Name"));
Assert.IsNotNull(table.GetColumn("Description"));
Assert.IsNotNull(table.GetColumn("DateEntered"));
Assert.IsNull(table.GetColumn("MyNewColumn"));
Assert.IsNull(table.GetColumn("MaxInventory"));
}
/// <summary>
/// Alters the column.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <param name="columnName">Name of the column to add.</param>
/// <param name="dbType">Type of the db.</param>
/// <param name="length">The length.</param>
/// <param name="nullable">if set to <c>true</c> [nullable].</param>
/// <param name="defaultValue">The default value.</param>
public void AlterColumn(string tableName, string columnName, DbType dbType, int length, bool nullable, string defaultValue)
{
TableSchema.Table table = Provider.GetTableSchema(tableName, TableType.Table);
if (table == null)
{
throw new ArgumentException("Unknown table " + tableName);
}
TableSchema.TableColumn column = table.GetColumn(columnName);
if (column == null)
{
throw new ArgumentException("Unknown column " + columnName);
}
column.DataType = dbType;
column.MaxLength = length;
column.IsNullable = nullable;
column.DefaultSetting = defaultValue;
AddMigrationStep(MigrationStepType.AlterColumn, table, column);
}
/// <summary>
/// Initializes a new instance of the <see cref="Join"/> class.
/// </summary>
/// <param name="from">From.</param>
/// <param name="to">To.</param>
/// <param name="joinType">Type of the join.</param>
public Join(TableSchema.Table from, TableSchema.Table to, JoinType joinType)
{
TableSchema.TableColumn fromCol = null;
TableSchema.TableColumn toCol = null;
foreach (TableSchema.TableColumn col in from.Columns)
{
if (col.IsForeignKey && !String.IsNullOrEmpty(col.ForeignKeyTableName))
{
TableSchema.Table fkTable = col.Table.Provider.GetTableSchema(col.ForeignKeyTableName, col.Table.TableType);
if (Utility.IsMatch(fkTable.Name, to.Name))
{
fromCol = col;
//found it - use the PK
toCol = fkTable.PrimaryKey;
break;
}
}
}
//reverse it - just in case we can't find a match
if (fromCol == null || toCol == null)
{
foreach (TableSchema.TableColumn col in to.Columns)
{
if (col.IsForeignKey && !String.IsNullOrEmpty(col.ForeignKeyTableName))
{
TableSchema.Table fkTable = col.Table.Provider.GetTableSchema(col.ForeignKeyTableName, col.Table.TableType);
if (Utility.IsMatch(fkTable.Name, from.Name))
{
toCol = col;
//found it - use the PK
fromCol = fkTable.PrimaryKey;
break;
}
}
}
}
//if that fails, see if there is a matching column name
if (fromCol == null || toCol == null)
{
//first, try to match the PK on the from table
//to a column in the "to" table
if (to.Columns.Contains(from.PrimaryKey.ColumnName))
{
FromColumn = from.PrimaryKey;
ToColumn = to.GetColumn(from.PrimaryKey.ColumnName);
//if that doesn't work, see if the PK of the "to" table has a
//matching column in the "from" table
}
else if (from.Columns.Contains(to.PrimaryKey.ColumnName))
{
FromColumn = from.GetColumn(to.PrimaryKey.ColumnName);
ToColumn = to.PrimaryKey;
}
}
//if that fails - run a match on any column that matches in "from" to "to"
if (fromCol == null || toCol == null)
{
foreach (TableSchema.TableColumn col in from.Columns)
{
if (to.Columns.Contains(col.ColumnName))
{
toCol = to.GetColumn(col.ColumnName);
fromCol = col;
break;
}
}
}
//still null? this seems exhausting, but the good thing is that these are indexed loops
//so they execute fast :)
if (fromCol == null || toCol == null)
{
foreach (TableSchema.TableColumn col in to.Columns)
{
if (from.Columns.Contains(col.ColumnName))
{
fromCol = from.GetColumn(col.ColumnName);
toCol = col;
break;
}
}
}
//if it's still null, throw since the join can't be made
//and that's a failure of this method
if (fromCol == null || toCol == null)
{
throw new SqlQueryException("Can't create a join for " + from.TableName + " to " + to.TableName +
" - can't determine the columns to link on. Try specifying the columns (using their schema) or specifying the table/column pair");
}
FromColumn = fromCol;
ToColumn = toCol;
_joinType = joinType;
}
/// <summary>
/// Loops the underlying settings collection to validate type, nullability, and length
/// </summary>
public void ValidateColumnSettings()
{
// loop the current settings and make sure they are valid for their type
foreach (TableSchema.TableColumnSetting setting in GetColumnSettings())
{
Utility.WriteTrace(String.Format("Validating {0}", setting.ColumnName));
object settingValue = setting.CurrentValue;
bool isNullValue = (settingValue == null || settingValue == DBNull.Value);
TableSchema.TableColumn col = table.GetColumn(setting.ColumnName);
if (!col.IsReadOnly)
{
string formattedName = Utility.ParseCamelToProper(col.ColumnName);
Type t = col.GetPropertyType();
//// Convert the existing value to the type for this column
//// if there's an error, report it.
//// OK to bypass if the column is nullable and this setting is null
//// just check for now if the value isn't null - it will be checked
//// later for nullability
if (!col.IsNullable && !isNullValue)
{
try
{
if (col.DataType != DbType.Guid)
{
Convert.ChangeType(settingValue, t);
}
}
catch
{
// there's a conversion problem here
// add it to the Exception List<>
if (col.IsNumeric)
{
errorList.Add(String.Format(InvalidTypeExceptionMessage, formattedName, "number"));
}
else if (col.IsDateTime)
{
errorList.Add(String.Format(InvalidTypeExceptionMessage, formattedName, "date"));
}
else
{
errorList.Add(String.Format(InvalidTypeExceptionMessage, formattedName, "value"));
}
}
}
bool isDbControlledAuditField = (Utility.IsAuditField(col.ColumnName) && !String.IsNullOrEmpty(col.DefaultSetting));
// now make sure that this column's null settings match with what's in the setting
Utility.WriteTrace(String.Format("Testing nullability of {0}", setting.ColumnName));
if (!col.IsNullable && isNullValue && !isDbControlledAuditField)
{
Utility.WriteTrace(String.Format("Null Error Caught {0}", setting.ColumnName));
errorList.Add(String.Format(NullExceptionMessage, formattedName));
}
// finally, check the length
Utility.WriteTrace(String.Format("Testing Max Length of {0}", setting.ColumnName));
if (!isNullValue && col.MaxLength > 0)
{
if (col.DataType != DbType.Boolean && settingValue.ToString().Length > col.MaxLength)
{
Utility.WriteTrace(String.Format("Max Length Exceeded {0} (can't exceed {1}); current value is set to {2}",
col.ColumnName,
col.MaxLength,
settingValue.ToString().Length));
errorList.Add(String.Format(LengthExceptionMessage, formattedName, col.MaxLength));
}
}
}
}
}
/// <summary>
/// Builds the header.
/// </summary>
private void BuildHeader()
{
tr = new HtmlTableRow();
string[] customCols = columnList.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (customCols.Length != 0)
{
foreach (string s in customCols)
{
td = new HtmlTableCell();
if (!String.IsNullOrEmpty(tableHeaderCSSClass))
{
td.Attributes.Add(CLASS, tableHeaderCSSClass);
}
else
{
td.Attributes.Add(STYLE, tableHeaderStyle);
}
if (s.Contains(":"))
{
//it's a cast in the form of "productID:ID"
string[] castedList = s.Split(new[] { ':' }, StringSplitOptions.RemoveEmptyEntries);
try
{
TableSchema.TableColumn col = schema.GetColumn(castedList[0].Trim()) ?? schema.GetColumn(castedList[1].Trim());
if (col == null)
{
throw new Exception("Can't find a column for this table named " + castedList[0] + " or " + castedList[1]);
}
colList.Add(col.ColumnName.ToLower());
AddHeaderText(col, td, castedList[1]);
}
catch
{
throw new Exception("Invalid Custom Columns. If you want to pass in a custom colum, it should be in the form 'columnName:Replacement Name'");
}
}
else
{
TableSchema.TableColumn col = schema.GetColumn(s.Trim());
if (col == null)
{
throw new Exception("Can't find a column for this table named " + s);
}
colList.Add(col.ColumnName.ToLower());
AddHeaderText(col, td, String.Empty);
}
tr.Cells.Add(td);
}
}
else
{
//loop the schema
foreach (TableSchema.TableColumn col in schema.Columns)
{
td = new HtmlTableCell();
td.Attributes.Add(STYLE, tableHeaderStyle);
AddHeaderText(col, td, String.Empty);
tr.Cells.Add(td);
colList.Add(col.ColumnName.ToLower());
}
}
tbl.Rows.Add(tr);
}
/// <summary>
/// Data retrieval
/// </summary>
/// <returns></returns>
private DataSet GenerateReturnSet()
{
DataSet result = null;
if (_url != null)
{
Query q;
if (!String.IsNullOrEmpty(_url.TableName))
{
q = new Query(_url.TableName);
TableSchema.Table schema = q.Schema;
if (_url.PrimaryKey != null)
{
q.WHERE(q.Schema.PrimaryKey.ParameterName, _url.PrimaryKey);
}
if (_url.Parameters != null)
{
IDictionaryEnumerator loopy = _url.Parameters.GetEnumerator();
TableSchema.TableColumn column;
string paramName;
object paramValue;
while (loopy.MoveNext())
{
paramName = loopy.Key.ToString();
paramValue = loopy.Value;
if (paramName.ToLowerInvariant() == "pagesize" || paramName.ToLowerInvariant() == "pageindex")
{
if (paramName.ToLowerInvariant() == "pagesize")
{
q.PageSize = int.Parse(paramValue.ToString());
}
if (paramName.ToLowerInvariant() == "pageindex")
{
q.PageIndex = int.Parse(paramValue.ToString());
}
}
else
{
Comparison comp;
EvalComparison(paramName, out paramName, out comp);
column = schema.GetColumn(paramName);
//if this column is a string, by default do a fuzzy search
if (comp == Comparison.Like || column.IsString)
{
comp = Comparison.Like;
paramValue = String.Concat("%", paramValue, "%");
}
else if (paramValue.ToString().ToLower() == "null")
{
paramValue = DBNull.Value;
}
q.WHERE(column.ColumnName, comp, paramValue);
}
}
}
result = q.ExecuteDataSet();
}
else if (!String.IsNullOrEmpty(_url.SpName))
{
StoredProcedure sp = new StoredProcedure(_url.SpName);
if (_url.Parameters != null)
{
IDictionaryEnumerator loopy = _url.Parameters.GetEnumerator();
while (loopy.MoveNext())
{
sp.Command.AddParameter(loopy.Key.ToString(), loopy.Value, DbType.AnsiString);
}
}
result = sp.GetDataSet();
}
}
return(result);
}
