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