/// <summary>
/// Loads the grid.
/// </summary>
private void LoadGrid()
{
if(String.IsNullOrEmpty(tableName))
throw new ArgumentException("No tableName property set - please be sure to set the name of the table or view you'd like to see", tableName);
DecideSortDirection();
//load the data
DataProvider provider = DataService.GetInstance(ProviderName);
SqlQuery q = new Select(provider).From(tableName);
//set the select list
StringBuilder selectList = new StringBuilder("*");
if(!String.IsNullOrEmpty(columnList))
{
selectList = new StringBuilder();
for(int i = 0; i < colList.Count; i++)
{
selectList.Append(colList[i].ToString().Trim());
if(i + 1 < colList.Count)
selectList.Append(",");
}
}
q.SelectColumnList = selectList.ToString().Split(new[] {','}, StringSplitOptions.RemoveEmptyEntries);
//sorting
if(!String.IsNullOrEmpty(sortBy))
{
TableSchema.TableColumn col = provider.GetTableSchema(tableName, TableType.Table).GetColumn(sortBy);
if(col != null && col.MaxLength < 2048 && col.DataType != DbType.Binary && col.DataType != DbType.Byte)
{
if(String.IsNullOrEmpty(sortDirection) || sortDirection.Trim() == SqlFragment.ASC.Trim())
q.OrderAsc(sortBy);
else
q.OrderDesc(sortBy);
}
}
//paging
if(pageSize > 0)
{
q.Paged(pageIndex, pageSize);
ddlPages.SelectedValue = pageIndex.ToString();
}
//honor logical deletes
q.CheckLogicalDelete();
//where
if(!String.IsNullOrEmpty(whereExpression))
q.WhereExpression(whereExpression);
SqlQueryBridge.AddLegacyWhereCollection(q, whereCollection);
DataSet ds = q.ExecuteDataSet();
if(ds.Tables.Count > 0)
dataSource = ds.Tables[0];
else
throw new Exception("Bad query - no data returned. Did you set the correct provider?");
EnsureTotals(q);
//set the buttons
SetPagingButtonState();
//create a table
BuildRows();
}
/// <summary>
/// Binds the grid.
/// </summary>
/// <param name="orderBy">The order by.</param>
protected void BindGrid(string orderBy)
{
if(TableSchema != null && TableSchema.PrimaryKey != null)
{
SqlQuery query = new Select(TableSchema.Provider).From(TableSchema);
if(!String.IsNullOrEmpty(_whereExpression))
query.WhereExpression(_whereExpression);
if(_whereCollection != null)
SqlQueryBridge.AddLegacyWhereCollection(query, _whereCollection);
string sortColumn = null;
if(!String.IsNullOrEmpty(orderBy))
sortColumn = orderBy;
else if(ViewState[ORDER_BY] != null)
sortColumn = (string)ViewState[ORDER_BY];
int colIndex = -1;
if(!String.IsNullOrEmpty(sortColumn))
{
ViewState.Add(ORDER_BY, sortColumn);
TableSchema.TableColumn col = TableSchema.GetColumn(sortColumn);
if(col == null)
{
for(int i = 0; i < TableSchema.Columns.Count; i++)
{
TableSchema.TableColumn fkCol = TableSchema.Columns[i];
if(fkCol.IsForeignKey && !String.IsNullOrEmpty(fkCol.ForeignKeyTableName))
{
TableSchema.Table fkTbl = DataService.GetSchema(fkCol.ForeignKeyTableName, ProviderName, TableType.Table);
if(fkTbl != null)
{
col = Utility.GetDisplayTableColumn(fkTbl);
colIndex = i;
break;
}
}
}
}
if(col != null && col.MaxLength < 2048)
{
string sortAlias = colIndex > -1 ? SqlFragment.JOIN_PREFIX + colIndex : col.ColumnName;
if(ViewState[SORT_DIRECTION] == null || ((string)ViewState[SORT_DIRECTION]) == SqlFragment.ASC)
{
query.OrderAsc(sortAlias);
//query.OrderBy = colIndex > -1 ? OrderBy.Asc(col, SqlFragment.JOIN_PREFIX + colIndex) : OrderBy.Asc(col);
ViewState[SORT_DIRECTION] = SqlFragment.ASC;
}
else
{
query.OrderDesc(sortAlias);
//query.OrderBy = colIndex > -1 ? OrderBy.Desc(col, SqlFragment.JOIN_PREFIX + colIndex) : OrderBy.Desc(col);
ViewState[SORT_DIRECTION] = SqlFragment.DESC;
}
}
}
DataTable dt = query.ExecuteJoinedDataSet().Tables[0];
grid.DataSource = dt;
grid.AutoGenerateColumns = false;
grid.Columns.Clear();
int columnOffset = 0;
string dataKey = TableSchema.PrimaryKey.ColumnName;
if(Utility.IsMappingTable(TableSchema) && dt.Columns.Count > TableSchema.Columns.Count)
{
columnOffset = 1;
dataKey = dt.Columns[0].ColumnName;
}
grid.DataKeyNames = new[] {dataKey};
CommandField link = new CommandField
{
ShowEditButton = true,
EditText = "Edit"
};
grid.Columns.Insert(0, link);
for(int i = 0; i < TableSchema.Columns.Count; i++)
{
int dtColIndex = i + columnOffset;
BoundField field = new BoundField
{
DataField = dt.Columns[dtColIndex].ColumnName,
SortExpression = dt.Columns[dtColIndex].ColumnName, HtmlEncode = false
};
//field.SortExpression = Utility.QualifyColumnName(schema.Name, dt.Columns[i].ColumnName, schema.Provider);
TableSchema.TableColumn col = TableSchema.Columns[i];
if(col.IsForeignKey)
{
TableSchema.Table fkSchema = col.ForeignKeyTableName == null
? DataService.GetForeignKeyTable(col, TableSchema)
: DataService.GetSchema(col.ForeignKeyTableName, ProviderName, TableType.Table);
if(fkSchema != null)
field.HeaderText = fkSchema.DisplayName;
}
else
field.HeaderText = col.DisplayName;
if(!Utility.IsAuditField(dt.Columns[dtColIndex].ColumnName) && !listHiddenGridColumns.Contains(dt.Columns[dtColIndex].ColumnName.ToLower()))
grid.Columns.Add(field);
}
grid.DataBind();
}
}