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