public static bool RequiresTextImageFileGroup(this SqlServerTable table) { var requiresTextImageFileGroup = false; foreach (var col in table.Columns) { if (col.DataType.DataType == DataType.Text || col.DataType.DataType == DataType.NText || col.DataType.DataType == DataType.Image || col.DataType.DataType == DataType.Xml) { requiresTextImageFileGroup = true; } if (col.DataType.DataType == DataType.VarChar || col.DataType.DataType == DataType.NVarChar || col.DataType.DataType == DataType.VarBinary) { if (col.DataType.Length == SqlServerConstants.MaxLength) { requiresTextImageFileGroup = true; } } } return(requiresTextImageFileGroup); }
public void ShouldContainTableName() { var table = new SqlServerTable { Name = "test1", Schema = "dbo2", Columns = new[] { new Column { DataType = new DataTypeCls { DataType = DataType.Int }, Name = "NotNullable", Nullable = false }, new Column { DataType = new DataTypeCls { DataType = DataType.Int }, Name = "Nullable", Nullable = true } } }; var converter = new TableTypeConverter(); var tab = converter.Convert(table, null, null); tab.Text.ShouldContain("CREATE TABLE [dbo2].[test1]"); }
public void ShouldHaveTextImageIfBigTextColumn() { var table = new SqlServerTable { Name = "test1", Schema = "dbo2", Columns = new[] { new Column { DataType = new DataTypeCls { DataType = DataType.NVarChar, Length = SqlServerConstants.MaxLength }, Name = "NotNullable", Nullable = false }, new Column { DataType = new DataTypeCls { DataType = DataType.Int }, Name = "Nullable", Nullable = true } } }; var converter = new TableTypeConverter(); var tab = converter.Convert(table, null, null); tab.Text.ShouldContain("TEXTIMAGE_ON [PRIMARY]"); }
public void ShouldSetNotNullableOnColumn() { var table = new SqlServerTable { Name = "test1", Schema = "dbo", Columns = new [] { new Column { DataType = new DataTypeCls { DataType = DataType.Int }, Name = "NotNullable", Nullable = false }, new Column { DataType = new DataTypeCls { DataType = DataType.Int }, Name = "Nullable", Nullable = true } } }; var converter = new TableTypeConverter(); var tab = converter.Convert(table, null, null); tab.Text.ShouldContain("[NotNullable] [int] NOT NULL"); tab.Text.ShouldContain("[Nullable] [int] NULL"); }
/// <summary> /// 为某个指定的仓库对象构造一个 DbTable /// </summary> /// <param name="repo"></param> /// <returns></returns> internal static RdbTable CreateORMTable(IRepositoryInternal repo) { RdbTable table = null; var provider = RdbDataProvider.Get(repo).DbSetting.ProviderName; switch (provider) { case DbSetting.Provider_SqlClient: table = new SqlServerTable(repo); break; case DbSetting.Provider_SqlCe: table = new SqlCeTable(repo); break; case DbSetting.Provider_MySql: table = new MySqlTable(repo); break; default: if (DbConnectionSchema.IsOracleProvider(provider)) { table = new OracleTable(repo); break; } throw new NotSupportedException(); } table.IdentifierProvider = DbMigrationProviderFactory.GetIdentifierProvider(provider); table.DbTypeConverter = DbMigrationProviderFactory.GetDbTypeConverter(provider); var em = repo.EntityMeta; foreach (var columnInfo in table.Info.Columns) { var epm = em.Property(columnInfo.Property); if (epm == null) { throw new ArgumentNullException(string.Format("{0}.{1} 属性需要使用托管属性进行编写。", table.Info.Class.FullName, columnInfo.Property.Name)); } var column = table.CreateColumn(columnInfo); table.Add(column); } return(table); }
public void ShouldHaveTriggers() { var table = new SqlServerTable { Name = "test1", Schema = "dbo2", Columns = new[] { new Column { DataType = new DataTypeCls { DataType = DataType.NVarChar, Length = SqlServerConstants.MaxLength }, Name = "NotNullable", Nullable = false }, new Column { DataType = new DataTypeCls { DataType = DataType.Int }, Name = "Nullable", Nullable = true } }, Triggers = new [] { new TableTrigger { Schema = "dbo", Name = "trig1", Action = TriggerAction.Insert, TableName = "test1", TableSchema = "dbo2", Text = "SELECT 1233" } } }; var converter = new TableTypeConverter(); var tab = converter.Convert(table, null, null); tab.Text.ShouldContain("SELECT 1233"); }
public void ShouldHaveForeignKey() { var table = new SqlServerTable { Name = "test1", Schema = "dbo2", Columns = new[] { new Column { DataType = new DataTypeCls { DataType = DataType.NVarChar, Length = SqlServerConstants.MaxLength }, Name = "NotNullable", Nullable = false }, new Column { DataType = new DataTypeCls { DataType = DataType.Int }, Name = "Nullable", Nullable = true } } }; table.ForeignKeyConstraints = new[] { new ForeignKeyConstraint { Name = "fk", DestinationTableColumnNames = new[] { "abc", "cbed" }, DestinationTableSchema = "dbo", DestinationTableName = "awe", SourceTableColumns = table.Columns } }; var converter = new TableTypeConverter(); var tab = converter.Convert(table, null, null); tab.Text.ShouldContain("ALTER TABLE [dbo2].[test1] ADD CONSTRAINT [fk] FOREIGN KEY ([NotNullable], [Nullable]) REFERENCES [dbo].[awe] ([abc], [cbed])"); }
public void ShouldHaveUniqueConstraint() { var table = new SqlServerTable { Name = "test1", Schema = "dbo2", Columns = new[] { new Column { DataType = new DataTypeCls { DataType = DataType.NVarChar, Length = SqlServerConstants.MaxLength }, Name = "NotNullable", Nullable = false }, new Column { DataType = new DataTypeCls { DataType = DataType.Int }, Name = "Nullable", Nullable = true } }, }; table.UniqueConstraints = new[] { new UniqueConstraint { Name = "uq", Clustered = false, Columns = table.Columns, FileGroup = "else" } }; var converter = new TableTypeConverter(); var tab = converter.Convert(table, null, null); tab.Text.ShouldContain("ALTER TABLE [dbo2].[test1] ADD CONSTRAINT [uq] UNIQUE NONCLUSTERED ([NotNullable], [Nullable]) ON [else]"); }
public void ShouldHavePermission() { var table = new SqlServerTable { Name = "test1", Schema = "dbo2", Columns = new[] { new Column { DataType = new DataTypeCls { DataType = DataType.NVarChar, Length = SqlServerConstants.MaxLength }, Name = "NotNullable", Nullable = false }, new Column { DataType = new DataTypeCls { DataType = DataType.Int }, Name = "Nullable", Nullable = true } }, PermissionDeclarations = new [] { new ObjectPermission { PermissionName = "SELECT", StateDescription = "GRANT", User = "******" } } }; var converter = new TableTypeConverter(); var tab = converter.Convert(table, null, null); tab.Text.ShouldContain("GRANT SELECT ON [dbo2].[test1] TO [deffff]"); }
public static SqlServerTable SqlServerTableCollectionToTable(DataRow sqlServerTable, DataTable sqlServerColumnsColletions, DataTable sqlServerForeignKeysCollection) { SqlServerTable table = new SqlServerTable(); table.Name = sqlServerTable["table_name"].ToString(); table.Schema = sqlServerTable["table_schema"].ToString(); DataRow[] rows = sqlServerColumnsColletions.Select("", "ColumnName ASC"); foreach (DataRow row in rows) { SqlServerColumn column = new SqlServerColumn(); SqlServerDbDataType dbDataType = new SqlServerDbDataType(); column.Name = row["ColumnName"].ToString(); column.IsPrimaryKey = (bool)row["IsKey"]; column.IsNullable = (bool)row["AllowDBNull"]; column.IsUnique = (bool)row["IsUnique"]; column.IsIdentity = (bool)row["IsIdentity"]; column.IsReadOnly = (bool)row["IsReadOnly"]; dbDataType.ProviderType = SqlServerDataTypeConverter.SqlDbType2DatabaseType(row["ProviderType"]); dbDataType.Type = (Type)row["DataType"]; dbDataType.Size = (int)row["ColumnSize"]; dbDataType.Precision = (Int16)row["NumericPrecision"]; dbDataType.Scale = (Int16)row["NumericScale"]; column.DbDataType = dbDataType; table.Columns.Add(column); } foreach (DataRow row in sqlServerForeignKeysCollection.Rows) { string columnName = row["ColumnName"].ToString(); table.Columns.Find( delegate(Column column) { return(column.Name.Equals(columnName)); }).IsForeignKey = true; } return(table); }
public void ShouldHavePrimaryKeyConstraint() { var table = new SqlServerTable { Name = "test1", Schema = "dbo2", Columns = new[] { new Column { DataType = new DataTypeCls { DataType = DataType.NVarChar, Length = SqlServerConstants.MaxLength }, Name = "NotNullable", Nullable = false }, new Column { DataType = new DataTypeCls { DataType = DataType.Int }, Name = "Nullable", Nullable = true } } }; table.PrimaryKeyConstraint = new PrimaryKeyConstraint { Clustered = true, Name = "pk", Columns = table.Columns }; var converter = new TableTypeConverter(); var tab = converter.Convert(table, null, null); tab.Text.ShouldContain("ALTER TABLE [dbo2].[test1] ADD CONSTRAINT [pk] PRIMARY KEY CLUSTERED ([NotNullable], [Nullable]) ON [PRIMARY]"); }
public void ShouldSetDefaultConstraintOnColumn() { var table = new SqlServerTable { Name = "test1", Schema = "dbo", Columns = new[] { new Column { DataType = new DataTypeCls { DataType = DataType.Bit }, Name = "NotNullable", Nullable = false, DefaultConstraint = new DefaultConstraint { Name = "df_default", DefaultVlaue = "(0)" } }, new Column { DataType = new DataTypeCls { DataType = DataType.Int }, Name = "Nullable", Nullable = true } } }; var converter = new TableTypeConverter(); var tab = converter.Convert(table, null, null); tab.Text.ShouldContain("[NotNullable] [bit] NOT NULL CONSTRAINT [df_default] DEFAULT ((0))"); tab.Text.ShouldContain("[Nullable] [int] NULL"); }
public SqlServerColumnAdditionDifferentiationStrategy(SqlServerTable sourceControl, SqlServerTable live) { _SourceControl = sourceControl; _Live = live; }
/// <summary> /// 生成读取对象范围 /// </summary> /// <param name="table">作用的表对象</param> /// <param name="name">表名</param> /// <returns>读取对象范围</returns> public static SqlServerReadTableScope <TEntity> CreateScope(SqlServerTable <TEntity> table, string name) { return(new SqlServerReadTableScope <TEntity>(table, name)); }
private void LoadSchema() { SqlServerService sqlServerService = new SqlServerService(ConnectionString, null); string primaryKeyQuery; SqlBuilder sqlBuilder = new SqlBuilder(); sqlBuilder.SELECT("c.TABLE_CATALOG,c.TABLE_SCHEMA,c.TABLE_NAME,c.CONSTRAINT_NAME,c.CONSTRAINT_TYPE,u.COLUMN_NAME"); sqlBuilder.FROM("INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c"); sqlBuilder.INNER_JOIN("INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS u ON c.CONSTRAINT_NAME = u.CONSTRAINT_NAME"); sqlBuilder.WHERE("c.CONSTRAINT_TYPE = 'PRIMARY KEY'"); primaryKeyQuery = sqlBuilder.ToString(); DataTable primaryKeyColumnSchema = sqlServerService.GenericExecuteResultQuery(primaryKeyQuery); foreach (DataRow primaryKeyColumnSchemaRow in primaryKeyColumnSchema.Rows) { SqlServerPrimaryKeyColumn primaryKeyColumn = new SqlServerPrimaryKeyColumn(primaryKeyColumnSchemaRow); PrimaryKeyColumns.Add(primaryKeyColumn); } using (SqlConnection sqlConnection = new SqlConnection(ConnectionString)) { sqlConnection.Open(); DataTable columnSchema = sqlConnection.GetSchema("Columns"); foreach (DataRow columnSchemaRow in columnSchema.Rows) { SqlServerColumn column = new SqlServerColumn(columnSchemaRow); SqlServerPrimaryKeyColumn primaryKeyColumn = PrimaryKeyColumns.Find(pkc => pkc.Name == column.Name && pkc.TableName == column.TableName && pkc.Schema == column.Schema && pkc.Catalog == column.Catalog); if (primaryKeyColumn != null) { column.ConstraintName = primaryKeyColumn.ConstraintName; column.IsPrimaryKey = true; } Columns.Add(column); } DataTable tableSchema = sqlConnection.GetSchema("Tables"); foreach (DataRow tableSchemaRow in tableSchema.Rows) { SqlServerTable table = new SqlServerTable(tableSchemaRow); Tables.Add(table); List <SqlServerColumn> columns = Columns.FindAll(c => c.Schema == table.Schema && c.TableName == table.Name); foreach (SqlServerColumn column in columns) { column.Table = table; table.Columns.Add(column); } } sqlConnection.Close(); } }
private SqlServerDataTableScope(SqlServerDataBase dataBase, SqlServerTable <TEntity> table) : base(dataBase) { Table = table; }
public static SqlServerDataTableScope <TEntity> CreateScope(SqlServerDataBase dataBase, SqlServerTable <TEntity> table) { return(new SqlServerDataTableScope <TEntity>(dataBase, table)); }
public void ColumnAdditionStrategyOutputsStringsForMissingColumns() { var liveTable = new SqlServerTable { Name = "test1", Schema = "dbo2", Columns = new[] { new Column { DataType = new DataTypeCls { DataType = DataType.NVarChar, Length = SqlServerConstants.MaxLength }, Name = "NotNullable", Nullable = false } }, Triggers = new [] { new TableTrigger { Schema = "dbo", Name = "trig1", Action = TriggerAction.Insert, TableName = "test1", TableSchema = "dbo2", Text = "SELECT 1233" } } }; var scTable = new SqlServerTable { Name = "test1", Schema = "dbo2", Columns = new[] { new Column { DataType = new DataTypeCls { DataType = DataType.NVarChar, Length = SqlServerConstants.MaxLength }, Name = "NotNullable", Nullable = false }, new Column { DataType = new DataTypeCls { DataType = DataType.Int }, Name = "Nullable", Nullable = true } }, Triggers = new [] { new TableTrigger { Schema = "dbo", Name = "trig1", Action = TriggerAction.Insert, TableName = "test1", TableSchema = "dbo2", Text = "SELECT 1233" } } }; var strategy = new SqlServerColumnAdditionDifferentiationStrategy(scTable, liveTable); strategy.GetDifferenceAlterString().ShouldContain("ALTER TABLE [dbo2].[test1] ADD"); strategy.GetDifferenceAlterString().ShouldContain("[Nullable] [int] NULL;"); }
string GetClrType(SqlServerTable table, SqlServerColumn column) { string sqlDataType = column.SqlDataType; switch (sqlDataType) { case "bigint": return(typeof(long).FullName); case "smallint": return(typeof(short).FullName); case "int": return(typeof(int).FullName); case "uniqueidentifier": return(typeof(Guid).FullName); case "smalldatetime": case "datetime": case "datetime2": case "date": case "time": return(typeof(DateTime).FullName); case "datetimeoffset": return(typeof(DateTimeOffset).FullName); case "float": return(typeof(double).FullName); case "real": return(typeof(float).FullName); case "numeric": case "smallmoney": case "decimal": case "money": return(typeof(decimal).FullName); case "tinyint": return(typeof(byte).FullName); case "bit": return(typeof(bool).FullName); case "image": case "binary": case "varbinary": case "timestamp": return(typeof(byte[]).FullName); case "nvarchar": case "varchar": case "nchar": case "char": case "text": case "ntext": case "xml": return(typeof(string).FullName); default: Console.WriteLine($"Unknown sqlDataType for {table.TableName}.{column.ColumnName}: {sqlDataType}"); return(null); // Vendor-specific types case "hierarchyid": return("Microsoft.SqlServer.Types.SqlHierarchyId"); // requires Microsoft.SqlServer.Types.dll (EF or Dapper 1.34+) case "geography": return("Microsoft.SqlServer.Types.SqlGeography"); // requires Microsoft.SqlServer.Types.dll (EF or Dapper 1.32+) case "geometry": return("Microsoft.SqlServer.Types.SqlGeometry"); // requires Microsoft.SqlServer.Types.dll (EF or Dapper 1.33)+ } }
private SqlServerReadTableScope(SqlServerTable <TEntity> table, string name) { _table = table; _oldName = table.SetDynamicReadTable(name); }