public TestContextFilter()
{
SchemaFilters.AddRange(new List <IFilterType <Schema> >
{
// Only include the schemas 'dbo' and 'events'
new RegexIncludeFilter("^dbo$"),
new RegexIncludeFilter("^events$")
});
TableFilters.AddRange(new List <IFilterType <Table> >
{
// Exclude filters
new RegexExcludeFilter("(.*_FR_.*)|(^data_.*)"), // Exclude all tables that contain '_FR_' or begin with 'data_'
new RegexExcludeFilter(".*[Bb]illing.*"), // This excludes all tables with 'billing' anywhere in the name
// Include filters
new RegexIncludeFilter("^[Cc]ustomer.*"), // This includes any remaining tables with names beginning with 'customer'
new RegexIncludeFilter("^Order.*"), // This includes any remaining tables with names beginning with 'customer'
});
ColumnFilters.AddRange(new List <IFilterType <Column> >
{
// Exclude any columns that begin with 'FK_'
new RegexExcludeFilter("^FK_.*$"),
});
}
public TableFilters GetSearchTableColumns()
{
TableFilters TableFiltersModel = new TableFilters();
TableFiltersModel.Page = ElsevierMaterialsMVC.Models.Shared.PageEnum.SearchResults;
TableFiltersModel.HasColumnsHidePosibility = true;
TableFiltersModel.HasOrderPosibility = true;
TableFiltersModel.HasInputSearch = true;
TableFiltersModel.ContainerId = "resultsResizable";
TableFiltersModel.TableName = "materialList";
ElsevierMaterials.Models.Domain.SearchFilterColumnsAll allFilters = (ElsevierMaterials.Models.Domain.SearchFilterColumnsAll)System.Web.HttpContext.Current.Session["SearchFilterColumnsAll"];
if (allFilters != null)
{
foreach (var colItem in allFilters.AllFilters)
{
TableFiltersModel.Columns.Add(new ElsevierMaterialsMVC.Models.Shared.Column {
Id = colItem.Id, Class = "cbSelectColumn1", IsChecked = colItem.isVisible, IsDisabled = (colItem.Id == 0 ? true : false), Name = colItem.Name
});
}
}
else
{
TableFiltersModel.Columns.Add(new ElsevierMaterialsMVC.Models.Shared.Column {
Id = 0, Class = "cbSelectColumn1", IsChecked = true, IsDisabled = true, Name = "Material Name"
});
TableFiltersModel.Columns.Add(new ElsevierMaterialsMVC.Models.Shared.Column {
Id = 1, Class = "cbSelectColumn1", IsChecked = true, IsDisabled = false, Name = "Type"
});
TableFiltersModel.Columns.Add(new ElsevierMaterialsMVC.Models.Shared.Column {
Id = 2, Class = "cbSelectColumn1", IsChecked = true, IsDisabled = false, Name = "Class"
});
TableFiltersModel.Columns.Add(new ElsevierMaterialsMVC.Models.Shared.Column {
Id = 3, Class = "cbSelectColumn1", IsChecked = true, IsDisabled = false, Name = "Subclass"
});
TableFiltersModel.Columns.Add(new ElsevierMaterialsMVC.Models.Shared.Column {
Id = 4, Class = "cbSelectColumn1", IsChecked = true, IsDisabled = false, Name = "Group"
});
TableFiltersModel.Columns.Add(new ElsevierMaterialsMVC.Models.Shared.Column {
Id = 5, Class = "cbSelectColumn1", IsChecked = false, IsDisabled = false, Name = "UNS No."
});
TableFiltersModel.Columns.Add(new ElsevierMaterialsMVC.Models.Shared.Column {
Id = 6, Class = "cbSelectColumn1", IsChecked = false, IsDisabled = false, Name = "CAS RN"
});
}
return(TableFiltersModel);
}
public void LoadTableColumns()
{
this.TableColumns = GetTableColumns(this.Connectionstring, TableFilters.ToArray <string>());
foreach (TableInfo t in this.Tables)
{
t.Columns = (from c in this.TableColumns
where c.TableName == t.Name
select c).ToList <TableColumn>();
t.PKConstraints = (from cn in this.Constraints
where cn.table_name == t.Name &&
cn.constraint_type == "PRIMARY KEY"
select cn).ToList <ConstraintInfo>();
//load description for columns
string query = @"select [value] as [Description],COL_NAME(major_id,minor_id) as ColumnName
from sys.extended_properties xp
where xp.class = 1 and xp.minor_id > 0 and
xp.major_id = object_id(N'[[schema]].[[table]]')
and xp.name in (N'MS_Description')"
.Replace("[[schema]]", t.SchemaName).Replace("[[table]]", t.Name);
SqlConnection conn;
using (IDataReader reader = SqlCommandX.Instance.GetReader(
_connectionString, query, CommandType.Text, out conn))
{
while (reader.Read())
{
var col = reader["ColumnName"].Text();
var desc = reader["Description"].Text();
if (col.IsNotNullOrEmpty())
{
var column = (from c in t.Columns where c.Name.ToLower() == col.ToLower() select c).One();
if (column.IsNotNull())
{
column.Description = desc;
}
}
}
}
conn.Destroy();
}
}
public void LoadConstraints()
{
this.Constraints = GetConstraints(this.Connectionstring, TableFilters.ToArray <string>());
}
public void LoadTables()
{
this.Tables = GetTables(this.Connectionstring, TableFilters.ToArray <string>()); // load tables
}
