public void ExtractSchema()
{
var sourceModel = new ExtractSchema {
ConnectionString = "Data Source=.\\SQL2016;Initial Catalog=Source;Integrated Security=True;Connect Timeout=30",
OutputFile = "Source.xml"
};
using (var service = new ExtractSchemaService(sourceModel))
service.ExtractSchema(Model.CompareType.Schema);
var targetModel = new ExtractSchema {
ConnectionString = "Data Source=.\\SQL2016;Initial Catalog=Target;Integrated Security=True;Connect Timeout=30",
OutputFile = "Target.xml"
};
using (var service = new ExtractSchemaService(targetModel))
service.ExtractSchema(Model.CompareType.Schema);
}
public ExtractSchemaService(ExtractSchema model)
{
Model = model;
CurrentSqlConnection = new System.Data.SqlClient.SqlConnection(model.ConnectionString);
CurrentSqlConnection.Open();
AllTables = CurrentSqlConnection.GetSchema("Tables");
// Fetch All References from SQL
ReferencesMapped = SqlService.LoadData <ForeignKey>(CurrentSqlConnection, "References", Properties.Resources.REFERENTIAL_CONSTRAINTS);
// Fetch All index_columns from SQL
IndexColumnsMapped = SqlService.LoadData <IndexColumns>(CurrentSqlConnection, "index_columns", "SELECT * FROM sys.index_columns");
// fetch COLUMNS schema
ColumnsMapped = SqlService.LoadData <Column>(CurrentSqlConnection, "Columns", "select * from INFORMATION_SCHEMA.COLUMNS");
// Fetch All Index from SQL
IndexMapped = SqlService.LoadData <Index>(CurrentSqlConnection, "index_columns", "SELECT * FROM sys.indexes");
// Fetch All sys.columns from SQL
SystemColumnsMapped = SqlService.LoadData <SystemColumns>(CurrentSqlConnection, "allSysColumns", "SELECT * FROM sys.columns");
// Fetch All Objects from SQL
ObjectMapped = SqlService.LoadData <SqlObject>(CurrentSqlConnection, "sys.object", "SELECT o.*, s.name as schemaName FROM sys.objects o join sys.schemas s on s.schema_id = o.schema_id");
// Get All Key Constraint
KeyConstraints = SqlService.LoadData <KeyConstraint>(CurrentSqlConnection, "keyConstraints", "SELECT * FROM [sys].[key_constraints]");
// Get All Table Extend Property
ExtendProperties = SqlService.LoadData <ExtendedProperty>(CurrentSqlConnection, "extendProperties", "SELECT [major_id] ,[name] ,[value] FROM [sys].[extended_properties] where minor_id = 0 and major_id <> 0");
// Identity Columns
IdentityColumns = SqlService.LoadData <IdentityData>(CurrentSqlConnection, "IdentityData", "select OBJECT_NAME(object_id) as TableName,OBJECT_SCHEMA_NAME(object_id) as SchemaName, * FROM sys.identity_columns");
ConstraintInformation = (from ind in IndexMapped
join ic in IndexColumnsMapped on new { ind.object_id, ind.index_id } equals new { ic.object_id, ic.index_id }
join col in SystemColumnsMapped on new { ic.object_id, ic.column_id } equals new { col.object_id, col.column_id }
select new ConstraintInformationModel {
Index = ind, IndexColumn = ic, SystemColumn = col
}).ToList();
Model = model;
Database = new Database();
Doc = new XDocument {
Declaration = new XDeclaration("1.0", "UTF-8", "true")
};
RootDatabase = new XElement("Database");
}
