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