//public override void SetUp()
//{
// try
// {
// base.SetUp();
// // Start a Local DB instance.
// _SqlLocalDbInstance = _SqlLocalDbProvider.GetOrCreateInstance("SchemaTesting");
// _SqlLocalDbInstance.Start();
// }
// catch (Exception e)
// {
// Console.WriteLine(e.ToString());
// throw;
// }
//}
private void PrepareTestData()
{
_SqlLocalDbProvider = new SqlLocalDbProvider();
_SqlLocalDbInstance = _SqlLocalDbProvider.GetOrCreateInstance("SchemaTesting");
_SqlLocalDbInstance.Start();
// Get the SQL to create a couple of differen tables
string tableOneName;
string createTableSql1 = GetCreateTestTableSql(out tableOneName);
TestEntityName = tableOneName;
string table2Name;
string createTableSql2 = GetCreateTestTableSql(out table2Name);
TestEntityName2 = table2Name;
// create a foreign key column between them.
var alterTableAddForeignKey = string.Format("ALTER TABLE {0} ADD {1}Id UNIQUEIDENTIFIER CONSTRAINT {0}_{1} REFERENCES {1}", tableOneName, table2Name);
// CreateTestEntity();
// Create table in LocalDB
using (SqlConnection connection = _SqlLocalDbInstance.CreateConnection())
{
connection.Open();
// create the first table
var command = connection.CreateCommand();
command.CommandType = CommandType.Text;
Console.WriteLine("Executing local db command " + createTableSql1);
command.CommandText = createTableSql1;
var result = command.ExecuteNonQuery();
Assert.AreEqual(result, -1);
// create the second table
command.CommandText = createTableSql2;
result = command.ExecuteNonQuery();
Assert.AreEqual(result, -1);
// create a column in the first table that is a foreign key so it references the second table
command.CommandText = alterTableAddForeignKey;
result = command.ExecuteNonQuery();
Assert.AreEqual(result, -1);
}
// Create first table in Crm
ExecuteCrmNonQuery(createTableSql1, -1);
// Create second table in Crm
ExecuteCrmNonQuery(createTableSql2, -1);
// create a column in the first table that is a foreign key so it references the second table
ExecuteCrmNonQuery(alterTableAddForeignKey, -1);
}
/// <summary> /// Gets the default SQL Local DB instance. /// </summary> /// <param name="value">The <see cref="ISqlLocalDbProvider"/> to use to get the default instance.</param> /// <returns> /// The default SQL Local DB instance. /// </returns> /// <exception cref="ArgumentNullException"> /// <paramref name="value"/> is <see langword="null"/>. /// </exception> public static ISqlLocalDbInstance GetDefaultInstance(this ISqlLocalDbProvider value) => value.GetOrCreateInstance(SqlLocalDbApi.DefaultInstanceName);
//public override void SetUp()
//{
// try
// {
// base.SetUp();
// // Start a Local DB instance.
// _SqlLocalDbInstance = _SqlLocalDbProvider.GetOrCreateInstance("SchemaTesting");
// _SqlLocalDbInstance.Start();
// }
// catch (Exception e)
// {
// Console.WriteLine(e.ToString());
// throw;
// }
//}
private void PrepareTestData()
{
_SqlLocalDbProvider = new SqlLocalDbProvider();
_SqlLocalDbInstance = _SqlLocalDbProvider.GetOrCreateInstance("SchemaTesting");
_SqlLocalDbInstance.Start();
// Get the SQL to create a couple of differen tables
string tableOneName;
string createTableSql1 = GetCreateTestTableSql(out tableOneName);
TestEntityName = tableOneName;
string table2Name;
string createTableSql2 = GetCreateTestTableSql(out table2Name);
TestEntityName2 = table2Name;
// create a foreign key column between them.
var alterTableAddForeignKey = string.Format("ALTER TABLE {0} ADD {1}Id UNIQUEIDENTIFIER CONSTRAINT {0}_{1} REFERENCES {1}", tableOneName, table2Name);
// CreateTestEntity();
// Create table in LocalDB
using (SqlConnection connection = _SqlLocalDbInstance.CreateConnection())
{
connection.Open();
// create the first table
var command = connection.CreateCommand();
command.CommandType = CommandType.Text;
Console.WriteLine("Executing local db command " + createTableSql1);
command.CommandText = createTableSql1;
var result = command.ExecuteNonQuery();
Assert.AreEqual(result, -1);
// create the second table
command.CommandText = createTableSql2;
result = command.ExecuteNonQuery();
Assert.AreEqual(result, -1);
// create a column in the first table that is a foreign key so it references the second table
command.CommandText = alterTableAddForeignKey;
result = command.ExecuteNonQuery();
Assert.AreEqual(result, -1);
}
// Create first table in Crm
ExecuteCrmNonQuery(createTableSql1, -1);
// Create second table in Crm
ExecuteCrmNonQuery(createTableSql2, -1);
// create a column in the first table that is a foreign key so it references the second table
ExecuteCrmNonQuery(alterTableAddForeignKey, -1);
}
public void WriteSchemaFilesForComparison()
{
PrepareTestData();
var sut = new SchemaCollectionsProvider();
_SqlLocalDbInstance = _SqlLocalDbProvider.GetOrCreateInstance("SchemaTesting");
_SqlLocalDbInstance.Start();
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
var builder = GetStringBuilder();
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
// Output common schema collections (https://msdn.microsoft.com/en-us/library/ms254501(v=vs.110).aspx)
// and also sql local db sql server schema collections (https://msdn.microsoft.com/en-us/library/ms254501(v=vs.110).aspx)
using (SqlConnection connection = _SqlLocalDbInstance.CreateConnection())
{
connection.Open();
// for each connection, we are writing its schema collection to CSV format for easy comparison.
// We are writing sql server first, followed by crmado's.
WriteDataTableToCsv("Sql MetaDataCollections", builder, connection.GetSchema("MetaDataCollections"));
WriteDataTableToCsv("CrmAdo MetaDataCollections", builder, sut.GetMetadataCollections());
WriteDataTableToCsv("Sql DataSourceInformation", builder, connection.GetSchema("DataSourceInformation"));
WriteDataTableToCsv("CrmAdo DataSourceInformation", builder, sut.GetDataSourceInfo(conn));
WriteDataTableToCsv("Sql DataTypes", builder, connection.GetSchema("DataTypes"));
WriteDataTableToCsv("CrmAdo DataTypes", builder, sut.GetDataTypes());
WriteDataTableToCsv("Sql Restrictions", builder, connection.GetSchema("Restrictions"));
WriteDataTableToCsv("CrmAdo Restrictions", builder, sut.GetRestrictions());
WriteDataTableToCsv("Sql ReservedWords", builder, connection.GetSchema("ReservedWords"));
WriteDataTableToCsv("CrmAdo ReservedWords", builder, sut.GetReservedWords());
WriteDataTableToCsv("Sql Tables", builder, connection.GetSchema("Tables"));
WriteDataTableToCsv("CrmAdo Tables", builder, sut.GetTables(conn, null));
WriteDataTableToCsv("Sql Columns", builder, connection.GetSchema("Columns"));
WriteDataTableToCsv("CrmAdo Columns", builder, sut.GetColumns(conn, null));
WriteDataTableToCsv("Sql Views", builder, connection.GetSchema("Views"));
WriteDataTableToCsv("CrmAdo Views", builder, sut.GetViews(conn, null));
WriteDataTableToCsv("Sql ViewColumns", builder, connection.GetSchema("ViewColumns"));
WriteDataTableToCsv("CrmAdo View Columns", builder, sut.GetViewColumns(conn, null));
WriteDataTableToCsv("Sql Indexes", builder, connection.GetSchema("Indexes"));
WriteDataTableToCsv("CrmAdo Indexes", builder, sut.GetIndexes(conn, null));
WriteDataTableToCsv("Sql IndexColumns", builder, connection.GetSchema("IndexColumns"));
WriteDataTableToCsv("CrmAdo IndexColumns", builder, sut.GetIndexColumns(conn, null));
WriteDataTableToCsv("Sql ForeignKeys", builder, connection.GetSchema("ForeignKeys"));
WriteDataTableToCsv("CrmAdo ForeignKeys", builder, sut.GetForeignKeys(conn, null));
WriteDataTableToCsv("Sql Users", builder, connection.GetSchema("Users"));
WriteDataTableToCsv("CrmAdo Users", builder, sut.GetUsers(conn, null));
WriteDataTableToCsv("Sql Databases", builder, connection.GetSchema("Databases"));
WriteDataTableToCsv("CrmAdo Databases", builder, sut.GetDatabases(conn, null));
}
_SqlLocalDbInstance.Stop();
}
// save the csv file to disk
var path = System.IO.Path.Combine(Environment.CurrentDirectory, "schema comparison report" + ".csv");
System.IO.File.WriteAllText(path, builder.ToString());
Console.WriteLine("comparison report written to: " + path);
}