Example #1
0
        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);
        }
Example #2
0
        /// <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();
            }
        }