public static void CloneDatabase(string connectionStringSource, string connectionStringDestination)
{
var backupFile = Path.GetTempFileName();
var csb = new MySqlConnectionStringBuilder(connectionStringDestination);
var databaseName = csb.Database;
var charset = csb.CharacterSet;
if (string.IsNullOrWhiteSpace(databaseName))
{
return;
}
csb.Remove("Database");
csb.Remove("CharacterSet");
try
{
using (var conn = new MySqlConnection(connectionStringSource))
{
using (var cmd = new MySqlCommand())
{
using (var mb = new MySqlBackup(cmd))
{
mb.ExportInfo.MaxSqlLength = 1024 * 1024; // 1M
cmd.Connection = conn;
conn.Open();
mb.ExportToFile(backupFile);
conn.Close();
}
}
}
//file ready
using (var conn = new MySqlConnection(csb.ConnectionString))
{
conn.Open();
using (var createCommand = new MySqlCommand($"CREATE DATABASE `{databaseName}` CHARACTER SET = {charset};", conn))
{
if (createCommand.ExecuteNonQuery() != 1)
{
return;
}
using (var useCommand = new MySqlCommand($"use {databaseName};", conn))
{
useCommand.ExecuteNonQuery();
using (var cmd = new MySqlCommand())
{
using (var mb = new MySqlBackup(cmd))
{
cmd.Connection = conn;
mb.ImportFromFile(backupFile);
}
}
}
}
}
}
finally
{
File.Delete(backupFile);
}
}
public static void DoWithDBContext <TDBContext>(string shard,
Action <TDBContext> businessWorker,
Action <EntityStatus> errorEvent = null) where TDBContext : DbContext, new()
{
var connectionString = shard;
if (string.IsNullOrEmpty(shard))
{
var connectionName = typeof(TDBContext).GetMethod("GetConnectionName").Invoke(null, null) as string;
connectionString = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
}
MySqlConnectionStringBuilder connectionStringBuilder = new MySqlConnectionStringBuilder(connectionString);
var oldDatabase = connectionStringBuilder.Database;
connectionStringBuilder.Remove("database");
using (MySqlConnection dbConnection = new MySqlConnection(connectionStringBuilder.ToString()))
{
try
{
dbConnection.Open();
dbConnection.ChangeDatabase(oldDatabase);
if (false == _contextConstructors.TryGetValue(typeof(TDBContext), out var contextConstructor))
{
contextConstructor = CreateDBContextConstructor(typeof(TDBContext), new Type[] { typeof(DbConnection), typeof(bool) });
_contextConstructors.TryAdd(typeof(TDBContext), contextConstructor);
}
var dbContext = contextConstructor(dbConnection, false) as TDBContext;
businessWorker(dbContext);
}
catch (DbEntityValidationException ex)
{
errorEvent?.Invoke(new EntityStatus(ex));
}
catch (DbUpdateException ex)
{
errorEvent?.Invoke(new EntityStatus(ex));
}
catch (MySqlException ex)
{
errorEvent?.Invoke(new EntityStatus(ex));
}
catch (Exception ex)
{
errorEvent?.Invoke(new EntityStatus(ex));
Dev.Assert(false, ex.StackTrace);
}
finally
{
dbConnection.Close();
}
}
}
/// <summary>
/// 初始化
/// </summary>
/// <param name="connectionString">数据库链接字符串</param>
public MySqlDatabaseSchema(string connectionString)
{
if (string.IsNullOrEmpty(connectionString))
{
throw new ArgumentNullException("connectionString");
}
//Server=127.0.0.1;Port=3306;Database=FileSystem;User Id=root;Password=mycsv.cn;CharacterSet=UTF8;Pooling=True;MinPoolSize=1;MaxPoolSize=100;ConnectionLifeTime=30;Keepalive=30
var connectionStringBuilder = new MySqlConnectionStringBuilder(connectionString);
this.database = connectionStringBuilder.Database;
connectionStringBuilder.Database = "mysql";
connectionStringBuilder.Remove("MinPoolSize");
connectionStringBuilder.Remove("MaxPoolSize");
connectionStringBuilder.Remove("ConnectionLifeTime");
connectionStringBuilder.Remove("Keepalive");
connectionStringBuilder.Pooling = false;
this.db = new MySqlDatabase(connectionStringBuilder.ConnectionString);
}
public static void DropDatabase(string connectionString)
{
var csb = new MySqlConnectionStringBuilder(connectionString);
var databaseName = csb.Database;
csb.Remove("Database");
using (var connection = new MySqlConnection(csb.ConnectionString))
{
connection.Open();
using (var dropCommand = new MySqlCommand($"DROP DATABASE `{databaseName}`;", connection))
dropCommand.ExecuteNonQuery();
}
}
public static bool CreateEmptyDatabase(string connectionString, string collate)
{
MySqlConnectionStringBuilder csb = new MySqlConnectionStringBuilder(connectionString);
string databaseName = csb.Database;
string charset = csb.CharacterSet;
if (string.IsNullOrWhiteSpace(databaseName))
{
return(false);
}
csb.Remove("Database");
csb.Remove("CharacterSet");
using (MySqlConnection connection = new MySqlConnection(csb.ConnectionString))
{
connection.Open();
using (var createCommand = new MySqlCommand(string.Format("CREATE DATABASE `{0}` CHARACTER SET = {1} COLLATE = {2};", databaseName, charset, collate), connection))
{
if (createCommand.ExecuteNonQuery() == 1)
{
using (var useCommand = new MySqlCommand(string.Format("use {0};", databaseName), connection))
{
useCommand.ExecuteNonQuery();
using (StreamReader scriptReader = new StreamReader(DbSchema.GetSchemaDefinitionStream()))
{
string createStatements = scriptReader.ReadToEnd();
MySqlScript createScript = new MySqlScript(connection, createStatements);
if (createScript.Execute() > 0)
{
return(true);
}
}
}
}
}
return(false);
}
}
public static bool CreateEmptyDatabase(string connectionString, string collate)
{
var csb = new MySqlConnectionStringBuilder(connectionString);
var databaseName = csb.Database;
var charset = csb.CharacterSet;
if (string.IsNullOrWhiteSpace(databaseName))
{
return(false);
}
csb.Remove("Database");
csb.Remove("CharacterSet");
using (var connection = new MySqlConnection(csb.ConnectionString))
{
connection.Open();
using (var createCommand = new MySqlCommand($"CREATE DATABASE `{databaseName}` CHARACTER SET = {charset} COLLATE = {collate};", connection))
{
if (createCommand.ExecuteNonQuery() == 1)
{
using (var useCommand = new MySqlCommand($"use {databaseName};", connection))
{
useCommand.ExecuteNonQuery();
using (var scriptReader = new StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("TAS.Database.MySqlRedundant.database.sql")))
{
var createStatements = scriptReader.ReadToEnd();
var createScript = new MySqlScript(connection, createStatements);
if (createScript.Execute() > 0)
{
return(true);
}
}
}
}
}
return(false);
}
}
private static bool TryCreateDatabase(string connectionString, ThrowawayDatabaseOptions options, out string databaseConnectionString, out string databaseName)
{
var prefix = string.IsNullOrEmpty(options.DatabaseNamePrefix) ? DefaultDatabaseNamePrefix : options.DatabaseNamePrefix;
databaseName = $"{prefix}{Guid.NewGuid().ToString("n").Substring(0, 10)}";
var builder = new MySqlConnectionStringBuilder(connectionString)
{
Database = databaseName
};
databaseConnectionString = builder.ConnectionString;
builder.Remove("Database");
try
{
using var connection = new MySqlConnection(builder.ConnectionString);
connection.Open();
using (var reader = connection.ExecuteReader("SHOW DATABASES;"))
{
while (reader.Read())
{
if (reader.GetString(0) == databaseName)
{
return(true);
}
}
}
var cmdTextBuilder = new StringBuilder()
.AppendFormat("CREATE DATABASE {0}", databaseName);
if (!string.IsNullOrWhiteSpace(options.Collation))
{
cmdTextBuilder.AppendFormat(" COLLATE {0}", options.Collation);
}
connection.ExecuteNonQuery(cmdTextBuilder.Append(';').ToString());
Console.WriteLine("Database created successfully");
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
return(false);
}
}
public static bool DropDatabase(string connectionString)
{
MySqlConnectionStringBuilder csb = new MySqlConnectionStringBuilder(connectionString);
string databaseName = csb.Database;
if (string.IsNullOrWhiteSpace(databaseName))
{
return(false);
}
csb.Remove("Database");
using (MySqlConnection connection = new MySqlConnection(csb.ConnectionString))
{
connection.Open();
using (var dropCommand = new MySqlCommand(string.Format("DROP DATABASE `{0}`;", databaseName), connection))
{
if (dropCommand.ExecuteNonQuery() > 0)
{
return(true);
}
}
return(false);
}
}
public static void DoWithDBContext <TDBContext1, TDBContext2>(
string shard1,
string shard2,
Action <TDBContext1, TDBContext2> businessWorker,
Action <EntityStatus> errorEvent = null) where TDBContext1 : DbContext where TDBContext2 : DbContext
{
var connectionString1 = shard1;
if (string.IsNullOrEmpty(shard1))
{
var connectionName = typeof(TDBContext1).GetMethod("GetConnectionName").Invoke(null, null) as string;
connectionString1 = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
}
var connectionString2 = shard2;
if (string.IsNullOrEmpty(shard2))
{
var connectionName = typeof(TDBContext2).GetMethod("GetConnectionName").Invoke(null, null) as string;
connectionString2 = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
}
MySqlConnectionStringBuilder connectionStringBuilder1 = new MySqlConnectionStringBuilder(connectionString1);
var oldDatabase1 = connectionStringBuilder1.Database;
connectionStringBuilder1.Remove("database");
MySqlConnectionStringBuilder connectionStringBuilder2 = new MySqlConnectionStringBuilder(connectionString2);
var oldDatabase2 = connectionStringBuilder2.Database;
connectionStringBuilder2.Remove("database");
using (MySqlConnection dbConnection1 = new MySqlConnection(connectionStringBuilder1.ToString()))
using (MySqlConnection dbConnection2 = new MySqlConnection(connectionStringBuilder2.ToString()))
{
try
{
dbConnection1.Open();
dbConnection2.Open();
dbConnection1.ChangeDatabase(oldDatabase1);
dbConnection2.ChangeDatabase(oldDatabase2);
if (false == _contextConstructors.TryGetValue(typeof(TDBContext1), out var contextConstructor1))
{
contextConstructor1 = CreateDBContextConstructor(typeof(TDBContext1), new Type[] { typeof(DbConnection), typeof(bool) });
_contextConstructors.TryAdd(typeof(TDBContext1), contextConstructor1);
}
var dbContext1 = contextConstructor1(dbConnection1, false) as TDBContext1;
if (false == _contextConstructors.TryGetValue(typeof(TDBContext2), out var contextConstructor2))
{
contextConstructor2 = CreateDBContextConstructor(typeof(TDBContext2), new Type[] { typeof(DbConnection), typeof(bool) });
_contextConstructors.TryAdd(typeof(TDBContext2), contextConstructor2);
}
var dbContext2 = contextConstructor2(dbConnection2, false) as TDBContext2;
businessWorker(dbContext1, dbContext2);
}
catch (DbEntityValidationException ex)
{
errorEvent?.Invoke(new EntityStatus(ex));
}
catch (DbUpdateException ex)
{
errorEvent?.Invoke(new EntityStatus(ex));
}
catch (MySqlException ex)
{
errorEvent?.Invoke(new EntityStatus(ex));
}
catch (Exception ex)
{
errorEvent?.Invoke(new EntityStatus(ex));
Dev.Assert(false, ex.StackTrace);
}
finally
{
dbConnection2.Close();
dbConnection1.Close();
}
}
}
void Kapcsolatot_Nyit()
{
try
{
conn.Open();
}
catch (MySqlException ex)
{
switch (ex.Number)
{
case 1042:
MessageBox.Show("Az adatbázis szerver nem elérhető!");
Environment.Exit(0);
break;
case 0:
if (ex.InnerException != null)
{
MySqlException kezelheto = (MySqlException)ex.InnerException;
switch (kezelheto.Number)
{
case 1049:
//-- Nincs ilyen adatbázis! ----------------------------
sb.Remove("Database");
conn = new MySqlConnection(sb.ToString());
conn.Open();
sql = conn.CreateCommand();
sql.CommandText = $"CREATE DATABASE IF NOT EXISTS `{database}` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
sql.ExecuteNonQuery();
sql.CommandText = $"USE `{database}`;";
sql.ExecuteNonQuery();
sql.CommandText = CreateVarosok;
sql.ExecuteNonQuery();
sql.CommandText = CreateLatvanyossagok;
sql.ExecuteNonQuery();
sb.Database = database;
conn = new MySqlConnection(sb.ToString());
conn.Open();
break;
case 1044:
//--- Access denied -------------------------------------
MessageBox.Show("Nincs jogosultsága az adatbázis használatára!");
Environment.Exit(0);
break;
case 1045:
//--- Access denied -------------------------------------
MessageBox.Show("Érvénytelen jelszó!");
Environment.Exit(0);
break;
default:
MessageBox.Show(ex.Message);
break;
}
}
break;
default:
MessageBox.Show(ex.Message);
break;
}
}
}
