コード例 #1
0
ファイル: ActiveHelper.cs プロジェクト: vogong/SubSonic-2.0
        /// <summary>
        /// Returns a DELETE QueryCommand object to delete the records with
        /// matching passed column/value criteria
        /// </summary>
        /// <param name="columnName">Name of the column to match</param>
        /// <param name="oValue">Value of column to match</param>
        /// <returns></returns>
        public static QueryCommand GetDeleteCommand(string columnName, object oValue)
        {
            T            item = new T();
            QueryCommand cmd;

            TableSchema.Table       tbl          = item.GetSchema();
            TableSchema.TableColumn colDeleted   = tbl.GetColumn(ReservedColumnName.DELETED);
            TableSchema.TableColumn colIsDeleted = tbl.GetColumn(ReservedColumnName.IS_DELETED);

            if (colDeleted != null)
            {
                cmd = new Update(tbl).Set(colDeleted).EqualTo(true).Where(columnName).IsEqualTo(oValue).BuildCommand();
            }
            else if (colIsDeleted != null)
            {
                cmd = new Update(tbl).Set(colIsDeleted).EqualTo(true).Where(columnName).IsEqualTo(oValue).BuildCommand();
            }
            else
            {
                Query q = new Query(item.GetSchema())
                {
                    QueryType = QueryType.Delete
                };
                q.AddWhere(columnName, oValue);
                cmd = DataService.BuildCommand(q);
            }

            return(cmd);
        }
コード例 #2
0
ファイル: MigrationTests.cs プロジェクト: vogong/SubSonic-2.0
        public void MigrationAddRemoveColumns()
        {
            Migration m = new MigrationTest003();

            m.Migrate("Northwind", Migration.MigrationDirection.Up);

            DataService.ClearSchemaCache("Northwind");
            TableSchema.Table table = DataService.GetSchema("Products", "Northwind");
            Assert.IsNotNull(table.GetColumn("ProductExpiration"));
            Assert.IsNull(table.GetColumn("ProductName"));
        }
コード例 #3
0
        public void QueryExecuteJoinedDataSet_ORDER_BY_DESC_Collection()
        {
            Query q = new Query("Products");

            TableSchema.Table ts = DataService.GetTableSchema("Products", "Northwind");

            DataSet ds = q.ORDER_BY(ts.GetColumn("SupplierID"), SqlFragment.DESC).
                         ORDER_BY(ts.GetColumn("ProductID"), SqlFragment.DESC).
                         ExecuteJoinedDataSet();

            //should bring 48 as first (SupplierID is replaced by CompanyName)
            Assert.IsTrue(ds.Tables[0].Rows[0]["ProductID"].Equals(48));
        }
コード例 #4
0
        public void Query_ORDER_BY_DESC_Collection()
        {
            Query q = new Query("Products");

            TableSchema.Table ts = DataService.GetTableSchema("Products", "Northwind");

            DataSet ds = q.ORDER_BY(ts.GetColumn("CategoryID"), SqlFragment.DESC).
                         ORDER_BY(ts.GetColumn("ProductID"), SqlFragment.DESC).
                         ExecuteDataSet();

            //should bring 73 as first
            Assert.IsTrue(ds.Tables[0].Rows[0]["ProductID"].Equals(73));
        }
コード例 #5
0
        public void Query_ORDER_BY_Collection()
        {
            Query q = new Query("Products");

            TableSchema.Table ts = DataService.GetTableSchema("Products", "Northwind");

            DataSet ds = q.ORDER_BY(ts.GetColumn("SupplierID")).
                         ORDER_BY(ts.GetColumn("ProductID")).
                         ExecuteDataSet();

            //should bring 2 as first
            Assert.IsTrue(ds.Tables[0].Rows[0]["ProductID"].Equals(2));
        }
コード例 #6
0
        public void Query_OrderByCollection()
        {
            Query q = new Query("Products");

            TableSchema.Table ts = DataService.GetTableSchema("Products", "Northwind");
            q.OrderByCollection.Add(OrderBy.Desc(ts.GetColumn("CategoryID")));
            q.OrderByCollection.Add(OrderBy.Desc(ts.GetColumn("ProductID")));

            DataSet ds = q.ExecuteDataSet();

            //should bring 73 as first
            Assert.IsTrue(ds.Tables[0].Rows[0]["ProductID"].Equals(73));
        }
コード例 #7
0
ファイル: QueryTest.cs プロジェクト: vogong/SubSonic-2.0
        public void Acc_Query_JoinedDataSet_OrderByFK()
        {
            Query q = new Query("Products", "NorthwindAccess");

            TableSchema.Table ts = DataService.GetTableSchema("Products", "NorthwindAccess");
            //q.OrderBy = OrderBy.Desc(ts.GetColumn("CategoryID"));
            q.ORDER_BY(ts.GetColumn("CategoryID"), "DESC");
            q.ORDER_BY(ts.GetColumn("ProductID"), "ASC");
            DataSet ds = q.ExecuteJoinedDataSet();

            //should bring 10 as first
            Assert.IsTrue(ds.Tables[0].Rows[0]["ProductID"].Equals(10));
        }
コード例 #8
0
        /// <summary>
        /// Builds the delete statement.
        /// </summary>
        /// <returns></returns>
        public string BuildDeleteStatement()
        {
            StringBuilder sb = new StringBuilder();

            //see if the from table has a "Deleted"
            TableSchema.Table tbl = query.FromTables[0];

            if (tbl.Columns.Contains(ReservedColumnName.DELETED) && query.GetType().Name != "Destroy")
            {
                TableSchema.TableColumn col = tbl.GetColumn("deleted");
                sb.Append(SqlFragment.UPDATE);
                sb.Append(tbl.QualifiedName);
                sb.Append(SqlFragment.SET);
                sb.Append(col.QualifiedName);

                //by default this is a bit in the DB
                //may have to rework this for MySQL
                sb.Append(" = 1");
            }
            else
            {
                sb.Append(SqlFragment.DELETE_FROM);
                sb.Append(query.FromTables[0].QualifiedName);
            }

            sb.Append(GenerateWhere());

            return(sb.ToString());
        }
コード例 #9
0
        /// <summary>
        /// Gets the current schema version for the named provider.
        /// </summary>
        /// <param name="providerName">Name of the provider.</param>
        /// <returns>Current version of the schema as stored in the schema info table.</returns>
        public static int GetCurrentVersion(string providerName)
        {
            int currentVersion = 0;

            DataProvider p = DataService.Providers[providerName];

            TableSchema.Table schemaTable = p.GetTableSchema(SCHEMA_INFO, TableType.Table);

            if (schemaTable != null && schemaTable.GetColumn("version") != null)
            {
                currentVersion = new Select(p, "version").From(SCHEMA_INFO).ExecuteScalar <int>();
            }
            else
            {
                //create schema table if it doesn't exist
                if (schemaTable == null)
                {
                    CreateSchemaInfo(providerName);
                }

                //delete all rows & add the version row
                new Delete(providerName).From(SCHEMA_INFO).Execute();
                new Insert(SCHEMA_INFO, providerName).Values(0).Execute();
            }

            return(currentVersion);
        }
コード例 #10
0
        /// <summary>
        /// Sets a value for a particular column in the record
        /// </summary>
        /// <param name="columnName">Name of the column, as defined in the database</param>
        /// <param name="oValue">The value to set the type to</param>
        public void SetColumnValue(string columnName, object oValue)
        {
            columnSettings = columnSettings ?? new TableSchema.TableColumnSettingCollection();

            // add the column to the DirtyColumns
            // if this instance has already been loaded
            // and this is a change to existing values
            if (IsLoaded && !IsNew)
            {
                TableSchema.Table schema      = GetSchema();
                object            oldValue    = null;
                string            oldValueMsg = "NULL";
                string            newValueMsg = "NULL";
                bool areEqualOrBothNull       = false;

                try
                {
                    oldValue = columnSettings.GetValue(columnName);
                }
                catch {}

                if (oldValue == null && oValue == null)
                {
                    areEqualOrBothNull = true;
                }
                else
                {
                    if (oldValue != null)
                    {
                        oldValueMsg        = oldValue.ToString();
                        areEqualOrBothNull = oldValue.Equals(oValue);
                    }

                    if (oValue != null)
                    {
                        newValueMsg = oValue.ToString();
                    }
                }

                TableSchema.TableColumn dirtyCol = schema.GetColumn(columnName);

                if (dirtyCol != null && !areEqualOrBothNull)
                {
                    string auditMessage = String.Format("Value changed from {0} to {1}{2}", oldValueMsg, newValueMsg, Environment.NewLine);
                    TableSchema.TableColumn dirtyEntry = DirtyColumns.GetColumn(columnName);
                    if (dirtyEntry != null)
                    {
                        DirtyColumns.Remove(dirtyEntry);
                        auditMessage = String.Concat(dirtyCol.AuditMessage, auditMessage);
                    }

                    dirtyCol.AuditMessage = auditMessage;
                    DirtyColumns.Add(dirtyCol);
                }
            }

            columnSettings.SetValue(columnName, oValue);
        }
コード例 #11
0
ファイル: Migration.cs プロジェクト: livlign/banhbanh
 /// <summary>
 /// Removes the column named columnName from the table named tableName.
 /// </summary>
 /// <param name="tableName">Name of the table.</param>
 /// <param name="columnName">Name of the column to remove.</param>
 public void RemoveColumn(string tableName, string columnName)
 {
     TableSchema.Table table = Provider.GetTableSchema(tableName, TableType.Table);
     if (table == null)
     {
         throw new ArgumentException("Unknown table name " + tableName);
     }
     TableSchema.TableColumn column = table.GetColumn(columnName);
     AddMigrationStep(MigrationStepType.DropColumn, table, column);
 }
コード例 #12
0
        public void Remove_Column()
        {
            TableSchema.Table       productSchema = Product.Schema;
            TableSchema.TableColumn column        = productSchema.GetColumn("ProductName");

            ISqlGenerator generator = new MySqlGenerator(null);
            string        sql       = generator.BuildDropColumnStatement(productSchema, column);

            Assert.AreEqual("ALTER TABLE `products` DROP COLUMN `ProductName`", sql);
        }
コード例 #13
0
ファイル: MigrationTests.cs プロジェクト: vogong/SubSonic-2.0
        public void MigrationShouldCreateAndDropTestTable()
        {
            //up
            Migrator.Migrate("Northwind", MigrationDirectory, 1);
            DataService.ClearSchemaCache("Northwind");
            TableSchema.Table table = DataService.GetSchema("Test1", "Northwind");
            Assert.IsNotNull(table.GetColumn("Name"));
            Assert.IsNotNull(table.GetColumn("Description"));
            Assert.IsNotNull(table.GetColumn("DateEntered"));
            int schemaVersion = Migrator.GetCurrentVersion("Northwind");

            Assert.AreEqual(1, schemaVersion);

            //down
            Migrator.Migrate("Northwind", MigrationDirectory, 0);
            Assert.IsNull(DataService.GetSchema("Test1", "Northwind"));
            schemaVersion = Migrator.GetCurrentVersion("Northwind");
            Assert.AreEqual(0, schemaVersion);
        }
コード例 #14
0
        public void Remove_Column()
        {
            TableSchema.Table       productSchema = Product.Schema;
            TableSchema.TableColumn column        = productSchema.GetColumn("ProductName");

            ANSISqlGenerator gen = new ANSISqlGenerator(null);
            string           sql = gen.BuildDropColumnStatement(productSchema, column);

            Assert.AreEqual("ALTER TABLE [dbo].[Products] DROP COLUMN [ProductName]", sql);
        }
コード例 #15
0
        public void Alter_Column()
        {
            TableSchema.Table       productSchema = Product.Schema;
            TableSchema.TableColumn column        = productSchema.GetColumn("ProductName");
            column.MaxLength = 150;

            ISqlGenerator generator = new MySqlGenerator(null);
            string        sql       = generator.BuildAlterColumnStatement(column);

            Assert.AreEqual("ALTER TABLE `products` ALTER COLUMN `ProductName` nvarchar(150) NOT NULL", sql);
        }
コード例 #16
0
        public void QueryExecuteJoinedDataSet_ORDER_BY()
        {
            Query q = new Query("Products");

            TableSchema.Table ts = DataService.GetTableSchema("Products", "Northwind");

            DataSet ds = q.ORDER_BY(ts.GetColumn("ProductID")).ExecuteJoinedDataSet();

            //should bring 1 as first
            Assert.IsTrue(ds.Tables[0].Rows[0]["ProductID"].Equals(1));
        }
コード例 #17
0
ファイル: QueryTest.cs プロジェクト: vogong/SubSonic-2.0
        public void Acc_QueryExecuteJoinedDataSet_ORDER_BY_DESC()
        {
            Query q = new Query("Products", "NorthwindAccess");

            TableSchema.Table ts = DataService.GetTableSchema("Products", "NorthwindAccess");

            DataSet ds = q.ORDER_BY(ts.GetColumn("ProductID"), SqlFragment.DESC).ExecuteJoinedDataSet();

            //should bring 77 as first
            Assert.IsTrue(ds.Tables[0].Rows[0]["ProductID"].Equals(77));
        }
コード例 #18
0
ファイル: Migration.cs プロジェクト: livlign/banhbanh
 /// <summary>
 /// Adds the column.
 /// </summary>
 /// <param name="tableName">Name of the table.</param>
 /// <param name="columnName">Name of the column to add.</param>
 /// <param name="dbType">Type of the db.</param>
 /// <param name="length">The length.</param>
 /// <param name="nullable">if set to <c>true</c> [nullable].</param>
 /// <param name="defaultValue">The default value.</param>
 public void AddColumn(string tableName, string columnName, DbType dbType, int length, bool nullable, string defaultValue)
 {
     TableSchema.Table table = Provider.GetTableSchema(tableName, TableType.Table);
     if (table == null)
     {
         throw new ArgumentException("Unknown table name " + tableName);
     }
     table.AddColumn(columnName, dbType, length, nullable, defaultValue);
     TableSchema.TableColumn column = table.GetColumn(columnName);
     AddMigrationStep(MigrationStepType.AddColumn, table, column);
 }
コード例 #19
0
ファイル: MigrationTests.cs プロジェクト: vogong/SubSonic-2.0
        public void MigrationAlterColumn()
        {
            DataService.ClearSchemaCache("Northwind");

            Migration m = new AlterProductNameMigration();

            //Up
            m.Migrate("Northwind", Migration.MigrationDirection.Up);
            DataService.ClearSchemaCache("Northwind");
            TableSchema.Table       table  = DataService.GetSchema("Products", "Northwind");
            TableSchema.TableColumn column = table.GetColumn("ProductName");
            Assert.AreEqual(100, column.MaxLength);

            //Down
            m.Migrate("Northwind", Migration.MigrationDirection.Down);
            DataService.ClearSchemaCache("Northwind");
            table  = DataService.GetSchema("Products", "Northwind");
            column = table.GetColumn("ProductName");
            Assert.AreEqual(50, column.MaxLength);
        }
コード例 #20
0
ファイル: QueryTest.cs プロジェクト: vogong/SubSonic-2.0
        public void Acc_Query_OrderBy()
        {
            Query q = new Query("Products", "NorthwindAccess");

            TableSchema.Table ts = DataService.GetTableSchema("Products", "NorthwindAccess");
            q.OrderBy = OrderBy.Desc(ts.GetColumn("ProductID"));

            DataSet ds = q.ExecuteDataSet();

            //should bring 77 as first
            Assert.IsTrue(ds.Tables[0].Rows[0]["ProductID"].Equals(77));
        }
コード例 #21
0
ファイル: MySqlTests.cs プロジェクト: vogong/SubSonic-2.0
        public void Acc_CorrectSchemaLoad()
        {
            MySqlDataProvider prov = (MySqlDataProvider)DataService.Providers["Southwind"];

            TableSchema.Table products = prov.GetTableSchema("Products", TableType.Table);
            //Assert.IsTrue(products.GetColumn("IsDeleted").IsNullable == true); Not in MySql schema
            Assert.IsTrue(products.GetColumn("Discontinued").IsNullable == false);

            TableSchema.TableColumn col = products.GetColumn("Discontinued");

            ICodeLanguage lang           = new CSharpCodeLanguage();
            string        varTypeNonNull = Utility.GetVariableType(col.DataType, col.IsNullable, lang);

            Assert.IsTrue(varTypeNonNull == "bool");

            //Not in MySql Schema
            //col = products.GetColumn("IsDeleted");

            //string varTypeNullable = Utilities.Utility.GetVariableType(col.DataType, col.IsNullable, lang);
            //Assert.IsTrue(varTypeNullable == "bool?");
        }
コード例 #22
0
        public void Delete <T>(string columnName, object columnValue) where T : RepositoryRecord <T>, new()
        {
            T item = new T();

            TableSchema.Table tbl = item.GetSchema();
            if (tbl.GetColumn(ReservedColumnName.DELETED) != null)
            {
                //create an update command
                new Update(tbl).Set(ReservedColumnName.DELETED).EqualTo(true).Where(columnName).IsEqualTo(columnValue).Execute();
            }
            else if (tbl.GetColumn(ReservedColumnName.IS_DELETED) != null)
            {
                new Update(tbl).Set(ReservedColumnName.IS_DELETED).EqualTo(true).Where(columnName).IsEqualTo(columnValue).Execute();
            }
            else
            {
                QueryCommand del = ActiveHelper <T> .GetDeleteCommand(columnName, columnValue);

                DataService.ExecuteQuery(del);
            }
        }
コード例 #23
0
ファイル: MigrationTests.cs プロジェクト: vogong/SubSonic-2.0
        public void Dual_ForeignKey_Relationships_ShouldBe_Possible_From_One_To_Many()
        {
            //load em
            using (Migration m = new Migration("Northwind")) {
                TableSchema.Table one_table = m.CreateTableWithKey("One");

                TableSchema.Table many_table = m.CreateTableWithKey("ManyTable");
                many_table.AddColumn("first_reference_to_table_one", System.Data.DbType.Int32);
                many_table.AddColumn("second_reference_to_table_one", System.Data.DbType.Int32);

                m.CreateForeignKey(one_table.GetColumn("Id"), many_table.GetColumn("first_reference_to_table_one"));
                m.CreateForeignKey(one_table.GetColumn("Id"), many_table.GetColumn("second_reference_to_table_one"));
            }

            DataService.ClearSchemaCache("Northwind");

            //drop em
            using (Migration m = new Migration("Northwind")) {
                m.DropTable("ManyTable");
                m.DropTable("One");
            }
        }
コード例 #24
0
        public void Alter_Column()
        {
            TableSchema.Table       productSchema = Product.Schema;
            TableSchema.TableColumn column        = productSchema.GetColumn("ProductName");
            column.MaxLength = 150;

            ANSISqlGenerator gen = new ANSISqlGenerator(null);
            string           sql = gen.BuildAlterColumnStatement(column);

            Assert.AreEqual("ALTER TABLE [dbo].[Products] ALTER COLUMN [ProductName] nvarchar(150) NOT NULL", sql);

            // Set it back to 40 or Create_Table fails.
            column.MaxLength = 40;
        }
コード例 #25
0
ファイル: MigrationTests.cs プロジェクト: vogong/SubSonic-2.0
        public void MigrationShouldExecMultipleMigrations()
        {
            DataService.ClearSchemaCache("Northwind");

            Migrator.Migrate("Northwind", MigrationDirectory, null);
            DataService.ClearSchemaCache("Northwind");
            TableSchema.Table table = DataService.GetSchema("Test1", "Northwind");
            Assert.IsNotNull(table.GetColumn("Name"));
            Assert.IsNotNull(table.GetColumn("Description"));
            Assert.IsNotNull(table.GetColumn("DateEntered"));
            Assert.IsNotNull(table.GetColumn("MyNewColumn"));
            Assert.IsNotNull(table.GetColumn("MaxInventory"));

            // Down()
            Migrator.Migrate("Northwind", MigrationDirectory, 1);

            DataService.ClearSchemaCache("Northwind");
            table = DataService.GetSchema("Test1", "Northwind");
            Assert.IsNotNull(table.GetColumn("Name"));
            Assert.IsNotNull(table.GetColumn("Description"));
            Assert.IsNotNull(table.GetColumn("DateEntered"));
            Assert.IsNull(table.GetColumn("MyNewColumn"));
            Assert.IsNull(table.GetColumn("MaxInventory"));
        }
コード例 #26
0
ファイル: Migration.cs プロジェクト: livlign/banhbanh
        /// <summary>
        /// Alters the column.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="columnName">Name of the column to add.</param>
        /// <param name="dbType">Type of the db.</param>
        /// <param name="length">The length.</param>
        /// <param name="nullable">if set to <c>true</c> [nullable].</param>
        /// <param name="defaultValue">The default value.</param>
        public void AlterColumn(string tableName, string columnName, DbType dbType, int length, bool nullable, string defaultValue)
        {
            TableSchema.Table table = Provider.GetTableSchema(tableName, TableType.Table);
            if (table == null)
            {
                throw new ArgumentException("Unknown table " + tableName);
            }

            TableSchema.TableColumn column = table.GetColumn(columnName);
            if (column == null)
            {
                throw new ArgumentException("Unknown column " + columnName);
            }

            column.DataType       = dbType;
            column.MaxLength      = length;
            column.IsNullable     = nullable;
            column.DefaultSetting = defaultValue;

            AddMigrationStep(MigrationStepType.AlterColumn, table, column);
        }
コード例 #27
0
ファイル: Join.cs プロジェクト: vogong/SubSonic-2.0
        /// <summary>
        /// Initializes a new instance of the <see cref="Join"/> class.
        /// </summary>
        /// <param name="from">From.</param>
        /// <param name="to">To.</param>
        /// <param name="joinType">Type of the join.</param>
        public Join(TableSchema.Table from, TableSchema.Table to, JoinType joinType)
        {
            TableSchema.TableColumn fromCol = null;
            TableSchema.TableColumn toCol   = null;

            foreach (TableSchema.TableColumn col in from.Columns)
            {
                if (col.IsForeignKey && !String.IsNullOrEmpty(col.ForeignKeyTableName))
                {
                    TableSchema.Table fkTable = col.Table.Provider.GetTableSchema(col.ForeignKeyTableName, col.Table.TableType);
                    if (Utility.IsMatch(fkTable.Name, to.Name))
                    {
                        fromCol = col;
                        //found it - use the PK
                        toCol = fkTable.PrimaryKey;
                        break;
                    }
                }
            }

            //reverse it - just in case we can't find a match
            if (fromCol == null || toCol == null)
            {
                foreach (TableSchema.TableColumn col in to.Columns)
                {
                    if (col.IsForeignKey && !String.IsNullOrEmpty(col.ForeignKeyTableName))
                    {
                        TableSchema.Table fkTable = col.Table.Provider.GetTableSchema(col.ForeignKeyTableName, col.Table.TableType);
                        if (Utility.IsMatch(fkTable.Name, from.Name))
                        {
                            toCol = col;
                            //found it - use the PK
                            fromCol = fkTable.PrimaryKey;
                            break;
                        }
                    }
                }
            }

            //if that fails, see if there is a matching column name
            if (fromCol == null || toCol == null)
            {
                //first, try to match the PK on the from table
                //to a column in the "to" table
                if (to.Columns.Contains(from.PrimaryKey.ColumnName))
                {
                    FromColumn = from.PrimaryKey;
                    ToColumn   = to.GetColumn(from.PrimaryKey.ColumnName);
                    //if that doesn't work, see if the PK of the "to" table has a
                    //matching column in the "from" table
                }
                else if (from.Columns.Contains(to.PrimaryKey.ColumnName))
                {
                    FromColumn = from.GetColumn(to.PrimaryKey.ColumnName);
                    ToColumn   = to.PrimaryKey;
                }
            }

            //if that fails - run a match on any column that matches in "from" to "to"
            if (fromCol == null || toCol == null)
            {
                foreach (TableSchema.TableColumn col in from.Columns)
                {
                    if (to.Columns.Contains(col.ColumnName))
                    {
                        toCol   = to.GetColumn(col.ColumnName);
                        fromCol = col;
                        break;
                    }
                }
            }

            //still null? this seems exhausting, but the good thing is that these are indexed loops
            //so they execute fast :)
            if (fromCol == null || toCol == null)
            {
                foreach (TableSchema.TableColumn col in to.Columns)
                {
                    if (from.Columns.Contains(col.ColumnName))
                    {
                        fromCol = from.GetColumn(col.ColumnName);
                        toCol   = col;
                        break;
                    }
                }
            }

            //if it's still null, throw since the join can't be made
            //and that's a failure of this method
            if (fromCol == null || toCol == null)
            {
                throw new SqlQueryException("Can't create a join for " + from.TableName + " to " + to.TableName +
                                            " - can't determine the columns to link on. Try specifying the columns (using their schema) or specifying the table/column pair");
            }
            FromColumn = fromCol;
            ToColumn   = toCol;
            _joinType  = joinType;
        }
コード例 #28
0
ファイル: RecordBase.cs プロジェクト: vogong/SubSonic-2.0
        /// <summary>
        /// Loops the underlying settings collection to validate type, nullability, and length
        /// </summary>
        public void ValidateColumnSettings()
        {
            // loop the current settings and make sure they are valid for their type
            foreach (TableSchema.TableColumnSetting setting in GetColumnSettings())
            {
                Utility.WriteTrace(String.Format("Validating {0}", setting.ColumnName));
                object settingValue         = setting.CurrentValue;
                bool   isNullValue          = (settingValue == null || settingValue == DBNull.Value);
                TableSchema.TableColumn col = table.GetColumn(setting.ColumnName);

                if (!col.IsReadOnly)
                {
                    string formattedName = Utility.ParseCamelToProper(col.ColumnName);
                    Type   t             = col.GetPropertyType();

                    //// Convert the existing value to the type for this column
                    //// if there's an error, report it.
                    //// OK to bypass if the column is nullable and this setting is null
                    //// just check for now if the value isn't null - it will be checked
                    //// later for nullability

                    if (!col.IsNullable && !isNullValue)
                    {
                        try
                        {
                            if (col.DataType != DbType.Guid)
                            {
                                Convert.ChangeType(settingValue, t);
                            }
                        }
                        catch
                        {
                            // there's a conversion problem here
                            // add it to the Exception List<>
                            if (col.IsNumeric)
                            {
                                errorList.Add(String.Format(InvalidTypeExceptionMessage, formattedName, "number"));
                            }
                            else if (col.IsDateTime)
                            {
                                errorList.Add(String.Format(InvalidTypeExceptionMessage, formattedName, "date"));
                            }
                            else
                            {
                                errorList.Add(String.Format(InvalidTypeExceptionMessage, formattedName, "value"));
                            }
                        }
                    }

                    bool isDbControlledAuditField = (Utility.IsAuditField(col.ColumnName) && !String.IsNullOrEmpty(col.DefaultSetting));

                    // now make sure that this column's null settings match with what's in the setting
                    Utility.WriteTrace(String.Format("Testing nullability of {0}", setting.ColumnName));
                    if (!col.IsNullable && isNullValue && !isDbControlledAuditField)
                    {
                        Utility.WriteTrace(String.Format("Null Error Caught {0}", setting.ColumnName));
                        errorList.Add(String.Format(NullExceptionMessage, formattedName));
                    }

                    // finally, check the length
                    Utility.WriteTrace(String.Format("Testing Max Length of {0}", setting.ColumnName));
                    if (!isNullValue && col.MaxLength > 0)
                    {
                        if (col.DataType != DbType.Boolean && settingValue.ToString().Length > col.MaxLength)
                        {
                            Utility.WriteTrace(String.Format("Max Length Exceeded {0} (can't exceed {1}); current value is set to {2}",
                                                             col.ColumnName,
                                                             col.MaxLength,
                                                             settingValue.ToString().Length));
                            errorList.Add(String.Format(LengthExceptionMessage, formattedName, col.MaxLength));
                        }
                    }
                }
            }
        }
コード例 #29
0
ファイル: QuickTable.cs プロジェクト: vogong/SubSonic-2.0
        /// <summary>
        /// Builds the header.
        /// </summary>
        private void BuildHeader()
        {
            tr = new HtmlTableRow();

            string[] customCols = columnList.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            if (customCols.Length != 0)
            {
                foreach (string s in customCols)
                {
                    td = new HtmlTableCell();
                    if (!String.IsNullOrEmpty(tableHeaderCSSClass))
                    {
                        td.Attributes.Add(CLASS, tableHeaderCSSClass);
                    }
                    else
                    {
                        td.Attributes.Add(STYLE, tableHeaderStyle);
                    }

                    if (s.Contains(":"))
                    {
                        //it's a cast in the form of "productID:ID"
                        string[] castedList = s.Split(new[] { ':' }, StringSplitOptions.RemoveEmptyEntries);

                        try
                        {
                            TableSchema.TableColumn col = schema.GetColumn(castedList[0].Trim()) ?? schema.GetColumn(castedList[1].Trim());

                            if (col == null)
                            {
                                throw new Exception("Can't find a column for this table named " + castedList[0] + " or " + castedList[1]);
                            }

                            colList.Add(col.ColumnName.ToLower());
                            AddHeaderText(col, td, castedList[1]);
                        }
                        catch
                        {
                            throw new Exception("Invalid Custom Columns. If you want to pass in a custom colum, it should be in the form 'columnName:Replacement Name'");
                        }
                    }
                    else
                    {
                        TableSchema.TableColumn col = schema.GetColumn(s.Trim());
                        if (col == null)
                        {
                            throw new Exception("Can't find a column for this table named " + s);
                        }

                        colList.Add(col.ColumnName.ToLower());
                        AddHeaderText(col, td, String.Empty);
                    }
                    tr.Cells.Add(td);
                }
            }
            else
            {
                //loop the schema
                foreach (TableSchema.TableColumn col in schema.Columns)
                {
                    td = new HtmlTableCell();
                    td.Attributes.Add(STYLE, tableHeaderStyle);

                    AddHeaderText(col, td, String.Empty);

                    tr.Cells.Add(td);
                    colList.Add(col.ColumnName.ToLower());
                }
            }
            tbl.Rows.Add(tr);
        }
コード例 #30
0
ファイル: RESTHandler.cs プロジェクト: vogong/SubSonic-2.0
        /// <summary>
        /// Data retrieval
        /// </summary>
        /// <returns></returns>
        private DataSet GenerateReturnSet()
        {
            DataSet result = null;

            if (_url != null)
            {
                Query q;

                if (!String.IsNullOrEmpty(_url.TableName))
                {
                    q = new Query(_url.TableName);
                    TableSchema.Table schema = q.Schema;

                    if (_url.PrimaryKey != null)
                    {
                        q.WHERE(q.Schema.PrimaryKey.ParameterName, _url.PrimaryKey);
                    }

                    if (_url.Parameters != null)
                    {
                        IDictionaryEnumerator   loopy = _url.Parameters.GetEnumerator();
                        TableSchema.TableColumn column;

                        string paramName;
                        object paramValue;

                        while (loopy.MoveNext())
                        {
                            paramName  = loopy.Key.ToString();
                            paramValue = loopy.Value;

                            if (paramName.ToLowerInvariant() == "pagesize" || paramName.ToLowerInvariant() == "pageindex")
                            {
                                if (paramName.ToLowerInvariant() == "pagesize")
                                {
                                    q.PageSize = int.Parse(paramValue.ToString());
                                }

                                if (paramName.ToLowerInvariant() == "pageindex")
                                {
                                    q.PageIndex = int.Parse(paramValue.ToString());
                                }
                            }
                            else
                            {
                                Comparison comp;
                                EvalComparison(paramName, out paramName, out comp);
                                column = schema.GetColumn(paramName);

                                //if this column is a string, by default do a fuzzy search
                                if (comp == Comparison.Like || column.IsString)
                                {
                                    comp       = Comparison.Like;
                                    paramValue = String.Concat("%", paramValue, "%");
                                }
                                else if (paramValue.ToString().ToLower() == "null")
                                {
                                    paramValue = DBNull.Value;
                                }

                                q.WHERE(column.ColumnName, comp, paramValue);
                            }
                        }
                    }
                    result = q.ExecuteDataSet();
                }
                else if (!String.IsNullOrEmpty(_url.SpName))
                {
                    StoredProcedure sp = new StoredProcedure(_url.SpName);

                    if (_url.Parameters != null)
                    {
                        IDictionaryEnumerator loopy = _url.Parameters.GetEnumerator();
                        while (loopy.MoveNext())
                        {
                            sp.Command.AddParameter(loopy.Key.ToString(), loopy.Value, DbType.AnsiString);
                        }
                    }
                    result = sp.GetDataSet();
                }
            }
            return(result);
        }