public override void TearDown()
{
// Drop test entity.
if (_SqlLocalDbInstance != null)
{
using (SqlConnection connection = _SqlLocalDbInstance.CreateConnection())
{
connection.Open();
var command = connection.CreateCommand();
command.CommandType = CommandType.Text;
var sql = GetDropTestTableSql(TestEntityName2);
Console.WriteLine("Executing local db command " + sql);
command.CommandText = sql;
var result = command.ExecuteNonQuery();
Assert.AreEqual(result, -1);
sql = GetDropTestTableSql(TestEntityName);
Console.WriteLine("Executing local db command " + sql);
command.CommandText = sql;
result = command.ExecuteNonQuery();
Assert.AreEqual(result, -1);
}
// Stop localdb instance.
_SqlLocalDbInstance.Stop();
}
base.TearDown();
}
public override TestCluster CreateTestCluster()
{
var options = new TestClusterOptions();
options.ClientConfiguration.AddSimpleMessageStreamProvider(StreamProviderName, false);
options.ClusterConfiguration.AddSimpleMessageStreamProvider(StreamProviderName, false);
foreach (var item in dbNames)
{
string actualDBName = item.Value;
string dbName = item.Key;
using (SqlConnection connection = instance.CreateConnection())
{
connection.Open();
using (SqlCommand command = new SqlCommand($"create database [{actualDBName}]", connection))
{
command.ExecuteNonQuery();
}
}
var sbuilder = instance.CreateConnectionStringBuilder();
sbuilder.SetInitialCatalogName(actualDBName);
options.ClusterConfiguration.AddSimpleSQLStorageProvider(dbName, sbuilder.ConnectionString, "true");
}
return(new TestCluster(options));
}
public override void Dispose()
{
using (var sqlConnection = new SqlConnection(ConnectionString))
{
// Fixes: "Cannot drop database because it is currently in use"
SqlConnection.ClearPool(sqlConnection);
}
using (var connection = _localDbInstance.CreateConnection())
{
connection.Open();
using (var command = new SqlCommand($"DROP DATABASE [{_databaseName}]", connection))
{
command.ExecuteNonQuery();
}
}
}
private async Task CreateDatabase(string location = null)
{
var commandText = location == null
? $"CREATE DATABASE {_databaseName}"
: $"CREATE DATABASE {_databaseName} ON (name = '{_databaseName}', filename = '{Path.Combine(location, _databaseName)}')";
using (var connection = _localDbInstance.CreateConnection())
{
await connection.OpenAsync();
using (var command = new SqlCommand(commandText, connection))
{
await command.ExecuteNonQueryAsync();
}
}
_databaseCreated = true;
}
//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 SqlServerTests()
{
_localDb = new SqlLocalDbApiWrapper();
ISqlLocalDbProvider provider = new SqlLocalDbProvider();
string instanceName = Guid.NewGuid().ToString();
_instance = provider.CreateInstance(instanceName);
_instance.Start();
_connection = _instance.CreateConnection();
_connection.Open();
_database = new Database(_connection);
_database.Execute("create database [SqlServerTests]");
}
private SqlConnectionStringBuilder CreateADatabase(Random rnd)
{
string actualDBName = rnd.Next().ToString();
using (SqlConnection connection = instance.CreateConnection())
{
connection.Open();
using (SqlCommand command = new SqlCommand($"create database [{actualDBName}]", connection))
{
command.ExecuteNonQuery();
}
}
var sbuilder = instance.CreateConnectionStringBuilder();
sbuilder.SetInitialCatalogName(actualDBName);
return(sbuilder);
}
/// <summary>
/// Teste l'existence d'une base de donnée de KL2 dans l'instance par défault
/// </summary>
/// <returns></returns>
public static async Task <bool> LocalDB_KL2DataBase_Exists()
{
try
{
ISqlLocalDbInstance instance = (new SqlLocalDbProvider()).GetInstance(Const.InstanceName_v3);
using (var conn = instance.CreateConnection())
{
await conn.OpenAsync();
conn.ChangeDatabase(Const.DataBaseName_v3);
conn.Close();
}
return(true);
}
catch
{
return(false);
}
}
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
ISqlLocalDbApi localDB = new SqlLocalDbApiWrapper();
if (!localDB.IsLocalDBInstalled())
{
MessageBox.Show("Máy tính của bạn phải cài đặt SQL Server LocalDB hoặc phiên bản Express để có thể sử dụng phần mềm.");
return;
}
string uuid = "nhom7-928cf731-171f-464f-aa55-24e814eeb224";
ISqlLocalDbProvider provider = new SqlLocalDbProvider();
IList <ISqlLocalDbInstanceInfo> instances = provider.GetInstances();
bool flag = false;
foreach (ISqlLocalDbInstanceInfo instanceInfo in instances)
{
if (instanceInfo.Name == uuid)
{
flag = true;
break;
}
}
if (!flag)
{
WaitForm frm = new WaitForm();
frm.Show();
ISqlLocalDbInstance instance = provider.CreateInstance(uuid);
instance.Start();
try
{
if (IsCurrentUserAdmin())
{
}
try
{
using (SqlConnection connection = instance.CreateConnection())
{
connection.Open();
try
{
string scriptText = Resources.script.ToString();
string[] splitter = new string[] { "\r\nGO\r\n" };
string[] commandTexts = scriptText.Split(splitter, StringSplitOptions.RemoveEmptyEntries);
foreach (string commandText in commandTexts)
{
using (SqlCommand command = new SqlCommand(commandText, connection))
{
command.ExecuteNonQuery();
}
}
}
finally
{
connection.Close();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
if (IsCurrentUserAdmin())
{
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
instance.Stop();
}
frm.Close();
}
else
{
ISqlLocalDbInstance instance = provider.GetInstance(uuid);
}
Global.connstring = @"Data Source=(LocalDB)\nhom7-928cf731-171f-464f-aa55-24e814eeb224;Initial Catalog=QUANLYTHUVIEN_Nhom7_14521100_15520048_15520062_15520115;Integrated Security=True";
Application.Run(new frmDangNhap());
}
//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);
}
internal static void Main(string[] args)
{
PrintBanner();
ISqlLocalDbApi localDB = new SqlLocalDbApiWrapper();
if (!localDB.IsLocalDBInstalled())
{
Console.WriteLine(SR.SqlLocalDbApi_NotInstalledFormat, Environment.MachineName);
return;
}
if (args?.Length == 1 && string.Equals(args[0], "/deleteuserinstances", StringComparison.OrdinalIgnoreCase))
{
SqlLocalDbApi.DeleteUserInstances(deleteFiles: true);
}
ISqlLocalDbProvider provider = new SqlLocalDbProvider();
IList <ISqlLocalDbVersionInfo> versions = provider.GetVersions();
Console.WriteLine(Strings.Program_VersionsListHeader);
Console.WriteLine();
foreach (ISqlLocalDbVersionInfo version in versions)
{
Console.WriteLine(version.Name);
}
Console.WriteLine();
IList <ISqlLocalDbInstanceInfo> instances = provider.GetInstances();
Console.WriteLine(Strings.Program_InstancesListHeader);
Console.WriteLine();
foreach (ISqlLocalDbInstanceInfo instanceInfo in instances)
{
Console.WriteLine(instanceInfo.Name);
}
Console.WriteLine();
string instanceName = Guid.NewGuid().ToString();
ISqlLocalDbInstance instance = provider.CreateInstance(instanceName);
instance.Start();
try
{
if (IsCurrentUserAdmin())
{
instance.Share(Guid.NewGuid().ToString());
}
try
{
using (SqlConnection connection = instance.CreateConnection())
{
connection.Open();
try
{
using (SqlCommand command = new SqlCommand("create database [MyDatabase]", connection))
{
command.ExecuteNonQuery();
}
using (SqlCommand command = new SqlCommand("drop database [MyDatabase]", connection))
{
command.ExecuteNonQuery();
}
}
finally
{
connection.Close();
}
}
}
finally
{
if (IsCurrentUserAdmin())
{
instance.Unshare();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
instance.Stop();
localDB.DeleteInstance(instance.Name);
}
Console.WriteLine();
Console.Write(Strings.Program_ExitPrompt);
Console.ReadKey();
}
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);
}
/// <summary>
/// Tests the lifecycle of SQL LocalDB instances.
/// </summary>
/// <param name="localDB">The <see cref="ISqlLocalDbApi"/> to use.</param>
/// <param name="provider">The <see cref="ISqlLocalDbProvider"/> to use.</param>
private static void TestInstanceLifecycle(ISqlLocalDbApi localDB, ISqlLocalDbProvider provider)
{
string instanceName = Guid.NewGuid().ToString();
string sharedInstanceName = string.Empty;
ISqlLocalDbInstance instance = provider.CreateInstance(instanceName);
instance.Start();
try
{
bool currentUserIsAdmin = Helpers.IsCurrentUserAdmin();
if (currentUserIsAdmin)
{
sharedInstanceName = Guid.NewGuid().ToString();
instance.Share(sharedInstanceName);
// Restart the instance so it listens on the new shared name's pipe
instance.Restart();
}
try
{
ISqlLocalDbInstanceInfo info = provider.GetInstances()
.Where((p) => string.Equals(p.Name, instanceName, StringComparison.Ordinal))
.FirstOrDefault();
Assert.IsNotNull(info, "GetInstances() did not return the created instance.");
Assert.AreEqual(sharedInstanceName, info.SharedName, "ISqlLocalDbInstanceInfo.SharedName is incorrect.");
using (SqlConnection connection = instance.CreateConnection())
{
Assert.IsNotNull(connection, "CreateConnection() returned null.");
TestConnection(connection);
}
if (currentUserIsAdmin)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder()
{
DataSource = $@"(localdb)\.\{sharedInstanceName}",
IntegratedSecurity = true
};
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
TestConnection(connection);
}
}
}
finally
{
if (currentUserIsAdmin)
{
instance.Unshare();
}
}
}
finally
{
instance.Stop();
localDB.DeleteInstance(instance.Name);
}
}
/// <summary>
/// Creates a connection to the LocalDB instance.
/// </summary>
/// <returns>
/// An instance of <see cref="SqlConnection" /> that
/// can be used to connect to the LocalDB instance.
/// </returns>
/// <exception cref="ObjectDisposedException">
/// The instance has been disposed.
/// </exception>
public SqlConnection CreateConnection()
{
EnsureNotDisposed();
return(_instance.CreateConnection());
}