public override void CreateDatabase(DbConfiguration dbConfiguration, DatabaseScriptEntry script) { var connectionStringBuilder = dbConfiguration.DbProviderFactory.CreateConnectionStringBuilder(); connectionStringBuilder.ConnectionString = dbConfiguration.ConnectionString; var dbName = GetDatabaseName(connectionStringBuilder); var dbPassword = GetPWD(connectionStringBuilder); var createDatabase = "CREATE USER " + dbName + " IDENTIFIED BY " + dbPassword; var systemUser = "******"; dbConfiguration.sqlLogger().LogMessage(createDatabase); connectionStringBuilder["USER ID"] = systemUser; connectionStringBuilder["Password"] = dbPassword; var log = dbConfiguration.sqlLogger(); using (var ctx = dbConfiguration.CreateDbContext()) { var conn = ctx.Connection; conn.ConnectionString = connectionStringBuilder.ConnectionString; var cmd = conn.CreateCommand(); cmd.CommandText = createDatabase; conn.Open(); cmd.ExecuteNonQuery(); cmd.CommandText = "GRANT CONNECT,RESOURCE TO " + dbName; cmd.ExecuteNonQuery(); //conn.ChangeDatabase(dbName); conn.Close(); } connectionStringBuilder["USER ID"] = dbName; connectionStringBuilder["Password"] = dbPassword; using (var ctx = dbConfiguration.CreateDbContext()) { var conn = ctx.Connection; conn.ConnectionString = connectionStringBuilder.ConnectionString; conn.Open(); try { ctx.UsingTransaction(() => CreateTables(log, script, ctx)); } catch { conn.Close(); connectionStringBuilder["USER ID"] = systemUser; conn.ConnectionString = connectionStringBuilder.ConnectionString; conn.Open(); var cmd = conn.CreateCommand(); cmd.CommandText = "Drop USER " + dbName + " CASCADE"; cmd.ExecuteNonQuery(); throw; } } }
public void DeleteByPO() { //1. 创建PO对象 Customer c = new Customer { CustomerID = "XX1", CompanyName = "Company1", ContactName = "Contact1", City = "Seattle", Country = "USA" }; //2. 创建DbContext 对象 using (var db = cfg.CreateDbContext()) { //3. 创建DbSet对象 var cusotmers = db.Set <Customer>(); //4. 删除所有以“XX”开头的CustomerID记录 cusotmers.Delete(p => p.CustomerID.StartsWith("XX")); //5. 执行PO插入 cusotmers.Insert(c); //6. 删除PO对象 cusotmers.Delete(c); } }
public void AutoCreateDatabase() { using (var db = dbConfiguration.CreateDbContext()) { var items = db.Set <Customers>().ToArray(); } using (var db = dbConfiguration.CreateDbContext()) { var items = db.Set <Customers>().ToArray(); } }
public override void DeleteDatabase(DbConfiguration dbConfiguration) { var connectionStringBuilder = dbConfiguration.DbProviderFactory.CreateConnectionStringBuilder(); connectionStringBuilder.ConnectionString = dbConfiguration.ConnectionString; var dbName = GetDatabaseName(connectionStringBuilder); var dbPassword = GetPWD(connectionStringBuilder); var deleteDatabase = "Drop USER " + dbName + " CASCADE"; dbConfiguration.sqlLogger().LogMessage(deleteDatabase); connectionStringBuilder["USER ID"] = "SYSTEM"; connectionStringBuilder["Password"] = dbPassword; var log = dbConfiguration.sqlLogger(); using (var ctx = dbConfiguration.CreateDbContext()) { var conn = ctx.Connection; conn.ConnectionString = connectionStringBuilder.ConnectionString; var cmd = conn.CreateCommand(); cmd.CommandText = deleteDatabase; conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); } }
public override IDatabaseSchema Load(DbConfiguration cfg) { var databaseSchema = new DatabaseSchema(); ColumnInfo[] allColumns = null; ForeignKeyInfo[] allFks = null; using (var ctx = cfg.CreateDbContext()) { InitConnection(ctx.Connection); using (var reader = ctx.DbHelper.ExecuteReader(AllColumnsSql)) allColumns = reader.ToList<ColumnInfo>().ToArray(); using (var reader = ctx.DbHelper.ExecuteReader(AllFKsSql)) allFks = reader.ToList<ForeignKeyInfo>().ToArray(); } Dictionary<string, TableSchema> tables = new Dictionary<string,TableSchema>(); foreach (var c in allColumns) { TableSchema table = null; if (!tables.TryGetValue(c.TableName, out table)) { table = new TableSchema { TableName = c.TableName, IsView = c.IsView }; tables[c.TableName] = table; } var key = allFks.FirstOrDefault(p => p.Type == "P" && p.ThisTableName == c.TableName && p.ThisKey == c.ColumnName); c.IsPrimaryKey = key != null; var column = ToColumn(c); table.AddColumn(column); } foreach(var item in allFks.Where(p=>p.OtherTableName.HasValue())) { TableSchema thisTable = tables[item.OtherTableName]; TableSchema otherTable = tables[item.ThisTableName]; IColumnSchema thisKey = thisTable.AllColumns.FirstOrDefault(p=>p.ColumnName == item.OtherKey); IColumnSchema otherKey = otherTable.AllColumns.FirstOrDefault(p => p.ColumnName == item.ThisKey); thisTable.AddFK(new ForeignKeySchema { ThisTable = thisTable, Name = item.Name , ThisKey = thisKey, OtherTable = otherTable, OtherKey = otherKey }); } databaseSchema.Tables = tables.Values.Where(p => !p.IsView).ToArray(); databaseSchema.Views = tables.Values.Where(p => p.IsView).ToArray(); return databaseSchema; }
public virtual void CreateTables(DbConfiguration dbConfiguration, DatabaseScriptEntry script) { var log = dbConfiguration.sqlLogger(); using (var ctx = dbConfiguration.CreateDbContext()) { ctx.UsingTransaction(() => { CreateTables(log, script, ctx); }); } }
public override void CreateDatabase(DbConfiguration dbConfiguration, DatabaseScriptEntry script) { var connectionStringBuilder = dbConfiguration.DbProviderFactory.CreateConnectionStringBuilder(); connectionStringBuilder.ConnectionString = dbConfiguration.ConnectionString; var dbName = dbConfiguration.DatabaseName; var createDatabase = "CREATE DATABASE " + dbName; dbConfiguration.sqlLogger().LogMessage(createDatabase); connectionStringBuilder["Database"] = "master"; var log = dbConfiguration.sqlLogger(); using (var ctx = dbConfiguration.CreateDbContext()) { var conn = ctx.Connection; conn.ConnectionString = connectionStringBuilder.ConnectionString; var cmd = conn.CreateCommand(); cmd.CommandText = createDatabase; conn.Open(); cmd.ExecuteNonQuery(); conn.ChangeDatabase(dbName); try { ctx.UsingTransaction(() => CreateTables(log, script, ctx)); } catch { conn.ChangeDatabase("master"); cmd.CommandText = string.Format(DropDatabaseScriptTemplate, dbName); cmd.ExecuteNonQuery(); throw; } finally { conn.Close(); } } }
public override void DeleteDatabase(DbConfiguration dbConfiguration) { var connectionStringBuilder = dbConfiguration.DbProviderFactory.CreateConnectionStringBuilder(); connectionStringBuilder.ConnectionString = dbConfiguration.ConnectionString; var dbName = dbConfiguration.DatabaseName; var dropDatabase = "DROP DATABASE " + dbConfiguration.Dialect.Quote(dbName); dropDatabase = string.Format(DropDatabaseScriptTemplate, dbName); dbConfiguration.sqlLogger().LogMessage(dropDatabase); using (var ctx = dbConfiguration.CreateDbContext()) using (var cmd = ctx.Connection.CreateCommand()) { connectionStringBuilder["Database"] = "master"; ctx.Connection.ConnectionString = connectionStringBuilder.ConnectionString; cmd.CommandText = dropDatabase; ctx.Connection.Open(); try { cmd.ExecuteNonQuery(); } catch { } try { ctx.Connection.ChangeDatabase(dbName); throw new ApplicationException("drop database failed."); } catch { } } }
public override void CreateDatabase(DbConfiguration dbConfiguration, DatabaseScriptEntry script) { var connectionStringBuilder = dbConfiguration.DbProviderFactory.CreateConnectionStringBuilder(); connectionStringBuilder.ConnectionString = dbConfiguration.ConnectionString; var dbName = dbConfiguration.DatabaseName; var createDatabase = "CREATE DATABASE " + dbName; dbConfiguration.sqlLogger().LogMessage(createDatabase); connectionStringBuilder["Database"] = "mysql"; var log = dbConfiguration.sqlLogger(); using (var ctx = dbConfiguration.CreateDbContext()) { var conn = ctx.Connection; conn.ConnectionString = connectionStringBuilder.ConnectionString; var cmd = conn.CreateCommand(); cmd.CommandText = createDatabase; conn.Open(); cmd.ExecuteNonQuery(); conn.ChangeDatabase(dbName); try { ctx.UsingTransaction(() => { CreateTables(log, script, ctx); }); } catch { conn.ChangeDatabase("mysql"); cmd.CommandText = string.Format("Drop DataBase {0}", dbName); cmd.ExecuteNonQuery(); throw; } } }
public override void DeleteDatabase(DbConfiguration dbConfiguration) { var connectionStringBuilder = dbConfiguration.DbProviderFactory.CreateConnectionStringBuilder(); connectionStringBuilder.ConnectionString = dbConfiguration.ConnectionString; var dbName = dbConfiguration.DatabaseName; var dropDatabase = "DROP DATABASE " + dbName; dbConfiguration.sqlLogger().LogMessage(dropDatabase); using (var ctx = dbConfiguration.CreateDbContext()) using (var cmd = ctx.Connection.CreateCommand()) { connectionStringBuilder["Database"] = "mysql"; ctx.Connection.ConnectionString = connectionStringBuilder.ConnectionString; cmd.CommandText = dropDatabase; ctx.Connection.Open(); try { cmd.ExecuteNonQuery(); } catch { } try { ctx.Connection.ChangeDatabase(dbName); throw new ApplicationException("drop database failed."); } catch { } } }
public void PO() { //1. 创建PO对象 Customer c = new Customer { CustomerID = "XX1", CompanyName = "Company1", ContactName = "Contact1", City = "Seattle", Country = "USA" }; //2. 创建DbContext 对象 using (var db = cfg.CreateDbContext()) { //3. 创建DbSet对象 var q = db.Set <Customer>(); //4. 删除所有以“XX”开头的CustomerID记录 q.Delete(p => p.CustomerID.StartsWith("XX")); //5. 执行PO插入 q.Insert(c); //6. 从数据库中查找指定CustomerID 的PO对象 var c2 = q.Get(c.CustomerID); Assert.IsNotNull(c2); Assert.AreNotSame(c, c2); Assert.AreEqual(c.CustomerID, c2.CustomerID); Assert.AreEqual(c.CompanyName, c2.CompanyName); Assert.AreEqual(c.ContactName, c2.ContactName); Assert.AreEqual(c.City, c2.City); Assert.AreEqual(c.Country, c2.Country); q.Delete(c); } }
public override IDatabaseSchema Load(DbConfiguration cfg) { var databaseSchema = new DatabaseSchema(); ColumnInfo[] allColumns = null; ForeignKeyInfo[] allFks = null; using (var ctx = cfg.CreateDbContext()) { InitConnection(ctx.Connection); using (var reader = ctx.DbHelper.ExecuteReader(AllColumnsSql)) allColumns = reader.ToList <ColumnInfo>().ToArray(); using (var reader = ctx.DbHelper.ExecuteReader(AllFKsSql)) allFks = reader.ToList <ForeignKeyInfo>().ToArray(); } Dictionary <string, TableSchema> tables = new Dictionary <string, TableSchema>(); foreach (var c in allColumns) { TableSchema table = null; if (!tables.TryGetValue(c.TableName, out table)) { table = new TableSchema { TableName = c.TableName, IsView = c.IsView }; tables[c.TableName] = table; } var key = allFks.FirstOrDefault(p => p.Type == "P" && p.ThisTableName == c.TableName && p.ThisKey == c.ColumnName); c.IsPrimaryKey = key != null; var column = ToColumn(c); table.AddColumn(column); } foreach (var item in allFks.Where(p => p.OtherTableName.HasValue())) { TableSchema thisTable = tables[item.OtherTableName]; TableSchema otherTable = tables[item.ThisTableName]; IColumnSchema thisKey = thisTable.AllColumns.FirstOrDefault(p => p.ColumnName == item.OtherKey); IColumnSchema otherKey = otherTable.AllColumns.FirstOrDefault(p => p.ColumnName == item.ThisKey); thisTable.AddFK(new ForeignKeySchema { ThisTable = thisTable, Name = item.Name, ThisKey = thisKey, OtherTable = otherTable, OtherKey = otherKey }); } databaseSchema.Tables = tables.Values.Where(p => !p.IsView).ToArray(); databaseSchema.Views = tables.Values.Where(p => p.IsView).ToArray(); return(databaseSchema); }
public virtual IDatabaseSchema Load(DbConfiguration cfg) { var databaseSchema = new DatabaseSchema(); ColumnInfo[] allColumns = null; ConstraintInfo[] allConstraints = null; ForeignKeyInfo[] allFks = null; using (var ctx = cfg.CreateDbContext()) { InitConnection(ctx.Connection); using (var reader = ctx.DbHelper.ExecuteReader(AllColumnsSql)) allColumns = reader.ToList <ColumnInfo>().ToArray(); using (var reader = ctx.DbHelper.ExecuteReader(AllConstraintsSql)) allConstraints = reader.ToList <ConstraintInfo>().ToArray(); using (var reader = ctx.DbHelper.ExecuteReader(AllFKsSql)) allFks = reader.ToList <ForeignKeyInfo>().ToArray(); } PopulateConstraints(allColumns, allConstraints); Dictionary <string, TableSchema> tables = new Dictionary <string, TableSchema>(); PopulateTables(allColumns, tables); foreach (var item in allFks) { TableSchema thisTable = null; TableSchema otherTable = null; IColumnSchema thisKey = null; IColumnSchema otherKey = null; var key = string.Format("{0}.{1}", item.ThisSchema, item.ThisTableName); if (tables.TryGetValue(key, out thisTable)) { thisKey = thisTable.AllColumns.FirstOrDefault(p => p.ColumnName == item.ThisKey); } key = string.Format("{0}.{1}", item.OtherSchema, item.OtherTableName); if (tables.TryGetValue(key, out otherTable)) { otherKey = otherTable.AllColumns.FirstOrDefault(p => p.ColumnName == item.OtherKey); } thisTable.AddFK(new ForeignKeySchema { ThisTable = thisTable , Name = item.Name , ThisKey = thisKey , OtherTable = otherTable , OtherKey = otherKey }); } databaseSchema.Tables = tables.Values.Where(p => !p.IsView).ToArray(); databaseSchema.Views = tables.Values.Where(p => p.IsView).ToArray(); return(databaseSchema); }