public void Acc_ExecuteJoinedDataSet_Should_Accept_Parameters()
{
int someProduct = 1;
SubSonic.SqlQuery sq = new Select()
.From(Product.Schema)
.Where(Product.ProductIDColumn).IsEqualTo(someProduct);
DataSet ds = sq.ExecuteJoinedDataSet();
Assert.AreEqual(1, ds.Tables[0].Rows.Count);
}
/// <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();
}
}