private static Model.Table GetTable(DBUtility.DBHelper dbHelper, string dbName, DataRow rTable)
{
string tableName = rTable[0].ToString();
Model.Table table = new Model.Table();
table.Name = tableName;
// 对每个表取字段属性
DataSet dsColumns = dbHelper.ExecuteQuery(CommandType.Text,
string.Format("select * from COLUMNS where TABLE_SCHEMA='{0}' and TABLE_NAME='{1}'", dbName, tableName),
null);
foreach (DataRow rField in dsColumns.Tables[0].Rows)
{
Model.Field field = new Model.Field();
field.IsIdentifier = rField["EXTRA"].ToString().ToLower() == "auto_increment";
field.IsKeyField = rField["COLUMN_KEY"].ToString().ToLower() == "pri";
field.AllowNull = rField["IS_NULLABLE"].ToString().ToLower() == "yes";
field.MySqlTypeString = SchemeHelper.GetString(rField["DATA_TYPE"]);
field.DefaultValue = SchemeHelper.GetString(rField["COLUMN_DEFAULT"]);
field.FieldDescn = SchemeHelper.GetString(rField["COLUMN_COMMENT"]);
field.FieldLength = SchemeHelper.GetLong(rField["CHARACTER_MAXIMUM_LENGTH"]);
field.FieldName = SchemeHelper.GetString(rField["COLUMN_NAME"]);
field.FieldNumber = SchemeHelper.GetInt(rField["ORDINAL_POSITION"]);
table.Fields.Add(field);
}
return(table);
}
public Model.Database GetSchema(string connectionString, Model.DatabaseTypes type)
{
Model.Database database = new Model.Database();
database.ConnectionString = connectionString;
database.Type = type;
//得到获取MySql结构的语句
string connStr = database.ConnectionString;
Match mDatabase = Regex.Match(connStr, @"Database=(?<Database>[^\;]*);");
if (mDatabase.Success)
{
database.Name = mDatabase.Groups["Database"].Value; //已赋数据库名
connStr = connStr.Replace(string.Format("Database={0};", database.Name), "Database=information_schema;");
}
else
{
return(null);
}
dbHelper = new DBUtility.DBHelper(DBUtility.DBHelper.DatabaseTypes.MySql, connStr);
GetTables(database);
GetViews(database);
GetStoreProcedures(database);
return(database);
}
public Model.Database GetSchema(string connectionString, Model.DatabaseTypes type)
{
Model.Database database = new Model.Database();
database.ConnectionString = connectionString;
database.Type = type;
//获取数据库结构
DBUtility.DBHelper dbHelper = new DBUtility.DBHelper(DBUtility.DBHelper.DatabaseTypes.Sql, database.ConnectionString);
DataSet ds = dbHelper.ExecuteQuery(CommandType.Text,
GetSql(database.Type, SchemaHelper.SchemaType.Table), null);
List <Model.Table> tables = GetSQLTableList(ds);
foreach (Model.Table table in tables)
{
database.AddTable(table);
}
ds = dbHelper.ExecuteQuery(CommandType.Text,
GetSql(database.Type, SchemaHelper.SchemaType.View), null);
List <Model.Table> views = GetSQLTableList(ds);
foreach (Model.Table view in views)
{
database.AddView(view);
}
ds = dbHelper.ExecuteQuery(CommandType.Text, SqlForGetStoreProcedures, null);
database.StoreProcedures = GetSQLStoreProcedureList(ds);
return(database);
}
public Model.Database GetSchema(string connectionString, Model.Database.DatabaseType type)
{
Model.Database database = new Model.Database();
database.ConnectionString = connectionString;
database.Type = type;
using (SqlConnection connection = new SqlConnection())
{
try
{
DBUtility.DBHelper dbHelper = new DBUtility.DBHelper(DBUtility.DBHelper.DatabaseTypes.Sql, database.ConnectionString);
DataSet ds = dbHelper.ExecuteQuery(CommandType.Text,
GetSql(database.Type, SchemeHelper.SchemaType.Table), null);
database.Tables = GetSQLTableList(ds);
ds = dbHelper.ExecuteQuery(CommandType.Text,
GetSql(database.Type, SchemeHelper.SchemaType.View), null);
database.Views = GetSQLTableList(ds);
ds = dbHelper.ExecuteQuery(CommandType.Text, SqlForGetStoreProcedures, null);
database.StoreProcedures = GetSQLStoreProcedureList(ds);
return(database);
}
catch
{
return(null);
}
}
}
public Model.Database GetSchema(string connectionString, Model.Database.DatabaseType type)
{
Model.Database database = new Model.Database();
database.ConnectionString = connectionString;
database.Type = type;
//得到获取MySql结构的语句
string dbName;
string connStr = database.ConnectionString;
Match mDatabase = Regex.Match(connStr, @"Database=(?<Database>[^\;]*);");
if (mDatabase.Success)
{
dbName = mDatabase.Groups["Database"].Value;
connStr = connStr.Replace(string.Format("Database={0};", dbName), "Database=information_schema;");
}
else
{
return(null);
}
DBUtility.DBHelper dbHelper = new DBUtility.DBHelper(DBUtility.DBHelper.DatabaseTypes.MySql, connStr);
// 获取表
DataSet dsTables = dbHelper.ExecuteQuery(CommandType.Text,
string.Format("select distinct TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='{0}' and TABLE_TYPE='BASE TABLE'", dbName),
null);
foreach (DataRow rTable in dsTables.Tables[0].Rows)
{
Model.Table table = GetTable(dbHelper, dbName, rTable);
table.Fields.Sort();
database.Tables.Add(table);
}
// 获取视图
DataSet dsViews = dbHelper.ExecuteQuery(CommandType.Text,
string.Format("select distinct TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='{0}' and TABLE_TYPE='VIEW'", dbName),
null);
foreach (DataRow rView in dsViews.Tables[0].Rows)
{
Model.Table view = GetTable(dbHelper, dbName, rView);
view.Fields.Sort();
database.Views.Add(view);
}
// 获取存储过程
DataSet dsStoreProcedure = dbHelper.ExecuteQuery(CommandType.Text,
string.Format("select distinct SPECIFIC_NAME from ROUTINES where ROUTINE_SCHEMA='{0}'", dbName),
null);
foreach (DataRow r in dsStoreProcedure.Tables[0].Rows)
{
database.StoreProcedures.Add(r[0].ToString());
}
return(database);
}
public Model.Database GetSchema(string connectionString, Model.DatabaseTypes type)
{
Model.Database db = new Model.Database();
db.ConnectionString = connectionString;
db.Type = type;
helper = new DBUtility.DBHelper(DBUtility.DBHelper.DatabaseTypes.Oracle, connectionString);
GetTables(db);
GetViews(db);
GetProcedures(db);
return(db);
}
private void cobDatabase_DropDown(object sender, EventArgs e)
{
cobDatabase.Items.Clear();
StringBuilder connStr = GetServer();
connStr.Append("Initial Catalog=master;");
DBUtility.DBHelper dbHelper = new DBUtility.DBHelper(DBUtility.DBHelper.DatabaseTypes.Sql, connStr.ToString());
try
{
DataSet ds = dbHelper.ExecuteQuery(CommandType.StoredProcedure, "sp_databases", null);
foreach (DataRow r in ds.Tables[0].Rows)
{
cobDatabase.Items.Add(r["DATABASE_NAME"].ToString());
}
}
catch
{
}
}
private void cobDatabase_DropDown(object sender, EventArgs e)
{
cobDatabase.Items.Clear();
DBUtility.DBHelper dbHelper = new DBUtility.DBHelper();
dbHelper.DatabaseType = DBUtility.DBHelper.DatabaseTypes.MySql;
dbHelper.ConnectionString = string.Format("Data Source={0};Port={1};User Id={2};Password={3};Database=information_schema;",
txtServer.Text.Trim(), txtPort.Text.Trim(), txtUserName.Text.Trim(), txtPassword.Text.Trim());
try
{
DataSet ds = dbHelper.ExecuteQuery(CommandType.Text, "select DISTINCT SCHEMA_NAME FROM SCHEMATA", null);
foreach (DataRow r in ds.Tables[0].Rows)
{
cobDatabase.Items.Add(r[0].ToString());
}
}
catch
{
}
}