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