public static SODatabase ToSODatabase(PDModel model)
{
if (model == null) return null;
SODatabase db = new SODatabase();
db.Name = model.Code;
db.Comment = string.IsNullOrEmpty(model.Comment) ? model.Name : model.Comment;
db.TableList = new List<SOTable>();
foreach (PDTable item in model.AllTableList)
{
db.TableList.Add(ToSOTable(item));
}
db.ViewList = new List<SOView>();
//foreach (PDView item in model.AllViewList)
//{
// db.ViewList.Add(ToSOView(item));
//}
db.CommandList = new List<SOCommand>();
//foreach (PDProcedure item in model.AllProcedureList)
//{
// db.CommandList.Add(ToSOCommand(item));
//}
return db;
}
public static SODatabase ToSODatabase(PDPackage package)
{
if (package == null) return null;
SODatabase db = new SODatabase();
db.Name = package.Code;
db.Comment = string.IsNullOrEmpty(package.Comment) ? package.Name : package.Comment;
db.TableList = new List<SOTable>();
foreach (PDTable item in package.TableList)
{
db.TableList.Add(ToSOTable(item));
}
db.ViewList = new List<SOView>();
//foreach (PDView item in package.ViewList)
//{
// db.ViewList.Add(ToSOView(item));
//}
db.CommandList = new List<SOCommand>();
//foreach (PDProcedure item in package.ProcedureList)
//{
// db.CommandList.Add(ToSOCommand(item));
//}
return db;
}
string GetDBComment(SODatabase db)
{
string cmdText = string.Format(@"use [{0}];
SELECT value as 'comment'
FROM fn_listextendedproperty(default, default, default, default, default, default, default); ",db.Name);
return this.DbProvider.ExecuteScalar<string>(CommandType.Text, cmdText);
}
public override List<SODatabase> GetDatabaseList()
{
string cmdText = "select * from dba_users";
DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
List<SODatabase> list = new List<SODatabase>();
foreach (DataRow row in dt.Rows)
{
SODatabase db = new SODatabase();
db.Name = row["USERNAME"].ToString();
db.Comment = db.Name;
db.Parent = this;
list.Add(db);
}
return list;
}
/// <summary>
/// 获取表列表
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public override List<SOTable> GetTableList(SODatabase db)
{
string cmdText = string.Format(@"select t.OWNER,t.TABLE_NAME,t.NUM_ROWS,c.TABLE_TYPE,c.COMMENTS
from dba_tables t left join dba_tab_comments c on t.TABLE_NAME = c.TABLE_NAME where t.owner='{0}'", db.Name);
SortedDictionary<string, SOTable> dic = new SortedDictionary<string, SOTable>();
DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
foreach (DataRow row in dt.Rows)
{
//if (row["TABLE_TYPE"].ToString() != "TABLE") continue;
if (row["TABLE_NAME"].ToString().StartsWith("BIN$")) continue;
SOTable table = new SOTable { Parent = db, Name = row["TABLE_NAME"].ToString(), Owner = row["OWNER"].ToString(), Comment = row["COMMENTS"].ToString() };
table.SchemaName = table.Owner;
if (!dic.ContainsKey(table.Name)) dic.Add(table.Name, table);
}
return dic.Values.ToList<SOTable>();
}
public override DataSet ExecuteQuery(SODatabase db, string cmdText)
{
DataSet ds = new DataSet();
using (DbConnection cn = base.DbProvider.DbProviderFactory.CreateConnection())
{
cn.ConnectionString = base.DbProvider.ConnectionString;
cn.Open();
DbCommand cmd = base.DbProvider.DbProviderFactory.CreateCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = cmdText;
DbDataAdapter adapter = base.DbProvider.DbProviderFactory.CreateDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(ds, 0, 100000, "temp");
}
return ds;
}
//改变数据库
void ChangeDatabase(SODatabase db)
{
listBox1.Items.Clear();
listBox2.Items.Clear();
CurrentDatabase = db;
List<SOTable> list = currentSchema.GetTableList(db);
foreach (SOTable table in list)
{
listBox1.Items.Add(table);
}
foreach (object item in cbDatabase.Items)
{
if (item.ToString() == CurrentDatabase.Name)
{
cbDatabase.SelectedItem = item;
break;
}
}
}
/// <summary>
/// 获取表列表
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public override List<SOTable> GetTableList(SODatabase db)
{
List<SOTable> list = new List<SOTable>();
string[] restrictions = new string[4];
restrictions[0] = db.Name;
DataTable dt = GetSchema(MetaDataCollectionName_Tables, restrictions);
foreach (DataRow dr in dt.Rows)
{
if (dr["TABLE_TYPE"].ToString() != "TABLE") continue; //排除系统表
SOTable table = new SOTable();
table.Name = dr["table_name"].ToString();
table.Comment = table.Name;
table.Parent = db;
list.Add(table);
}
return list;
}
private void NewQuery(SODatabase db, string sqlText)
{
QueryAnalyzer qa = new QueryAnalyzer();
qa.CurrentDatabase = db;
base.MainForm.SetCurrentDB(db);
int count = 1;
string fileName = string.Format("SqlQuery{0}.sql", count.ToString());
while (base.MainForm.FindDockDocument(fileName) != null)
{
count++;
fileName = string.Format("SqlQuery{0}.sql", count.ToString());
}
qa.FileName = fileName;
qa.SqlText = sqlText;
qa.Show(DockPanel);
}
/// <summary>
/// 在指定数据库上执行一个查询，返回一个结果集
/// </summary>
/// <param name="db"></param>
/// <param name="cmdText"></param>
/// <returns></returns>
public virtual DataSet ExecuteQuery(SODatabase db, string cmdText)
{
this.DbProvider.CurrentDatabaseName = db.Name;
DataSet ds = this.DbProvider.ExecuteDataSet(CommandType.Text, cmdText);
return ds;
}
/// <summary>
/// 获取数据库列表
/// </summary>
/// <returns></returns>
public virtual List<SODatabase> GetDatabaseList()
{
List<SODatabase> list = new List<SODatabase>();
DataTable dt = GetSchema(MetaDataCollectionName_Databases);
foreach (DataRow dr in dt.Rows)
{
SODatabase db = new SODatabase();
db.Name = dr["database_name"].ToString();
db.Comment = db.Name;
db.Parent = this;
list.Add(db);
}
return list;
}
/// <summary>
/// 获取存储过程列表
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public virtual List<SOCommand> GetCommandList(SODatabase db)
{
List<SOCommand> list = new List<SOCommand>();
string[] restrictions = new string[4];
restrictions[0] = db.Name;
DataTable dt = GetSchema(MetaDataCollectionName_Procedures, restrictions);
foreach (DataRow dr in dt.Rows)
{
SOCommand cmd = new SOCommand();
cmd.Name = dr["routine_name"].ToString();
cmd.Comment = cmd.Name;
cmd.Parent = db;
list.Add(cmd);
}
return list;
}
public virtual SOCommand GetCommand(SODatabase db, string spName)
{
var sp = GetCommandList(db).Find(p => p.Name == spName);
return sp;
}
/// <summary>
/// 获取数据库列表
/// </summary>
/// <returns></returns>
public virtual List<SODatabase> GetDatabaseList()
{
List<SODatabase> list = new List<SODatabase>();
DataTable dt = GetSchema(MetaDataCollectionName_Databases);
string str=string.Empty;
foreach (DataRow dr in dt.Rows)
{
SODatabase db = new SODatabase();
str = dr["database_name"].ToString();
if (str.Contains("."))
{
str = string.Format("[{0}]", str);
}
db.Name = str;
db.Comment = db.Name;
db.Parent = this;
list.Add(db);
}
return list;
}
public virtual SOView GetView(SODatabase db, string viewName)
{
var view = GetViewList(db).Find(p => p.Name == viewName);
return view;
}
/// <summary>
/// 获取视图列表
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public override List<SOView> GetViewList(SODatabase db)
{
string cmdText = string.Format("select VIEW_NAME,TEXT from dba_views where owner='{0}'", db.Name);
SortedDictionary<string, SOView> dic = new SortedDictionary<string, SOView>();
DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
foreach (DataRow row in dt.Rows)
{
SOView view = new SOView { Parent = db, Name = row["VIEW_NAME"].ToString(), SqlText=row["TEXT"].ToString(), Owner = db.Name };
view.SchemaName = view.Owner;
dic.Add(view.Name, view);
}
return dic.Values.ToList<SOView>();
}
private List<SOTable> Export(DbSchema schema, SODatabase db, List<SOTable> tableList, ExportTyep exportType)
{
if (schema == null) throw new ArgumentException("参数schema不能为空", "schema");
if (db == null) throw new ArgumentException("参数dbName不能为空", "dbName");
Document doc = new Document(PageSize.A4.Rotate(), 20, 20, 20, 20);
DocWriter w;
switch (exportType)
{
case ExportTyep.PDF:
w = PdfWriter.GetInstance(doc, new FileStream(fileName, FileMode.Create, FileAccess.Write));
break;
case ExportTyep.RTF:
w = RtfWriter2.GetInstance(doc, new FileStream(fileName, FileMode.Create, FileAccess.Write));
break;
//case ExportTyep.HTML:
// w = HtmlWriter.GetInstance(doc, new FileStream(fileName, FileMode.Create, FileAccess.Write));
//break;
default:
break;
}
doc.Open();
doc.NewPage();
if (tableList == null) tableList = schema.GetTableList(db);
Chapter cpt = new Chapter(db.Name, 1);
Section sec;
doc.AddTitle(db.Name);
doc.AddAuthor("Kalman");
doc.AddCreationDate();
doc.AddCreator("Kalman");
doc.AddSubject("数据库文档");
foreach (SOTable table in tableList)
{
sec = cpt.AddSection(new Paragraph(table.Name, font));
if (string.IsNullOrEmpty(table.Comment) == false)
{
Chunk chunk = new Chunk(table.Comment, font);
sec.Add(chunk);
}
List<SOColumn> columnList = schema.GetTableColumnList(table);
t = new Table(7, columnList.Count);
t.AutoFillEmptyCells = true;
t.CellsFitPage = true;
t.TableFitsPage = true;
t.Cellpadding = 3;
//if (exportType == ExportTyep.PDF) t.Cellspacing = 2;
t.DefaultVerticalAlignment = Element.ALIGN_MIDDLE;
t.SetWidths(new int[] { 200, 150, 50, 50, 50, 100, 300 });
t.AddCell(BuildHeaderCell("名称"));
t.AddCell(BuildHeaderCell("数据类型"));
t.AddCell(BuildHeaderCell("主键"));
t.AddCell(BuildHeaderCell("标志"));
t.AddCell(BuildHeaderCell("可空"));
t.AddCell(BuildHeaderCell("默认值"));
t.AddCell(BuildHeaderCell("注释"));
foreach (SOColumn column in columnList)
{
t.AddCell(BuildCell(column.Name));
t.AddCell(BuildCell(GetDbColumnType(column)));
t.AddCell(BuildCell(column.PrimaryKey ? " √" : ""));
t.AddCell(BuildCell(column.Identify ? " √" : ""));
t.AddCell(BuildCell(column.Nullable ? " √" : ""));
t.AddCell(BuildCell(column.DefaultValue == null ? "" : column.DefaultValue.ToString()));
t.AddCell(BuildCell(column.Comment));
}
sec.Add(t);
}
doc.Add(cpt);
doc.Close();
return tableList;
}
/// <summary>
/// 获取表列表
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public override List<SOTable> GetTableList(SODatabase db)
{
string cmdText = string.Format("use [{0}];exec sp_tables;",db.Name);
SortedDictionary<string, SOTable> dic = new SortedDictionary<string,SOTable>();
DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
foreach (DataRow row in dt.Rows)
{
if (row["TABLE_TYPE"].ToString() != "TABLE") continue;
if (row["TABLE_OWNER"].ToString() == "sys") continue;
SOTable table = new SOTable { Parent = db, Name = row["TABLE_NAME"].ToString(), Owner = row["TABLE_OWNER"].ToString() };
table.SchemaName = table.Owner;
table.Comment = GetTableComment(table);
dic.Add(table.Name,table);
}
return dic.Values.ToList<SOTable>();
}
//string templatePath = Path.Combine(Application.StartupPath, "T4Template\\SP");
public BatchBuildCode(SODatabase db)
{
InitializeComponent();
currentDatabase = db;
tableList = db.TableList;
}
public override List<SOCommand> GetCommandList(SODatabase db)
{
return new List<SOCommand>();
}
public BatchBuildCustomCode(SODatabase db)
{
InitializeComponent();
currentDatabase = db;
}
// /// <summary>
// /// 获取视图所拥有的索引列表
// /// </summary>
// /// <param name="view"></param>
// /// <returns></returns>
// public override List<SOIndex> GetViewIndexList(SOView view)
// {
// string cmdText = string.Format(@"SELECT *
// FROM INFORMATION_SCHEMA.`constraints`
// WHERE table_schema='{0}' AND table_name='{1}';", view.Database.Name, view.Name);
// List<SOIndex> indexList = new List<SOIndex>();
// List<SOColumn> columnList = GetViewColumnList(view);
// DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
// foreach (DataRow row in dt.Rows)
// {
// SOIndex index = new SOIndex
// {
// Parent = view,
// Name = row["constraint_name"].ToString(),
// Comment = row["constraint_name"].ToString(),
// IsCluster = false,
// IsFullText = row["constraint_type"].ToString() == "Full Text",
// IsPrimaryKey = row["constraint_type"].ToString() == "PRIMARY KEY",
// IsUnique = row["constraint_type"].ToString() == "UNIQUE"
// };
// indexList.Add(index);
// string cmdText2 = string.Format(@"SELECT column_name
// FROM INFORMATION_SCHEMA.`statistics`
// WHERE table_schema='{0}' AND table_name='{1}';", view.Database.Name, view.Name);
// DataTable dt2 = this.DbProvider.ExecuteDataSet(CommandType.Text, cmdText2).Tables[0];
// index.Columns = new List<SOColumn>();
// foreach (DataRow row2 in dt2.Rows)
// {
// foreach (SOColumn column in columnList)
// {
// if (row2[0].ToString() == column.Name) index.Columns.Add(column);
// }
// }
// }
// return indexList;
// }
/// <summary>
/// 获取存储过程列表
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public override List<SOCommand> GetCommandList(SODatabase db)
{
string cmdText = string.Format(@"use [{0}];SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_catalog='{0}' AND routine_type='PROCEDURE';", db.Name);
List<SOCommand> commandList = new List<SOCommand>();
DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
foreach (DataRow row in dt.Rows)
{
SOCommand command = new SOCommand { Parent = db, Name = row["routine_name"].ToString(), Comment = row["routine_name"].ToString() };
commandList.Add(command);
}
return commandList;
}
public void SetCurrentDB(SODatabase db)
{
foreach (object item in toolItemDbList.Items)
{
if (item.ToString() == db.Name) toolItemDbList.SelectedItem = item;
}
}
// /// <summary>
// /// 获取表所拥有的索引列表
// /// </summary>
// /// <param name="table"></param>
// /// <returns></returns>
// public override List<SOIndex> GetTableIndexList(SOTable table)
// {
// string cmdText = string.Format(@"SELECT *
// FROM INFORMATION_SCHEMA.`constraints`
// WHERE table_schema='{0}' AND table_name='{1}';", table.Database.Name, table.Name);
// List<SOIndex> indexList = new List<SOIndex>();
// List<SOColumn> columnList = GetTableColumnList(table);
// DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
// foreach (DataRow row in dt.Rows)
// {
// SOIndex index = new SOIndex
// {
// Parent = table,
// Name = row["constraint_name"].ToString(),
// Comment = row["constraint_name"].ToString(),
// IsCluster = false,
// IsFullText = row["constraint_type"].ToString() == "Full Text",
// IsPrimaryKey = row["constraint_type"].ToString() == "PRIMARY KEY",
// IsUnique = row["constraint_type"].ToString() == "UNIQUE"
// };
// indexList.Add(index);
// string cmdText2 = string.Format(@"SELECT column_name
// FROM INFORMATION_SCHEMA.`statistics`
// WHERE table_schema='{0}' AND table_name='{1}';", table.Database.Name, table.Name);
// DataTable dt2 = this.DbProvider.ExecuteDataSet(CommandType.Text, cmdText2).Tables[0];
// index.Columns = new List<SOColumn>();
// foreach (DataRow row2 in dt2.Rows)
// {
// foreach (SOColumn column in columnList)
// {
// if (row2[0].ToString() == column.Name) index.Columns.Add(column);
// }
// }
// }
// return indexList;
// }
/// <summary>
/// 获取视图列表
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public override List<SOView> GetViewList(SODatabase db)
{
string cmdText = string.Format("use [{0}];exec sp_tables;", db.Name);
SortedDictionary<string, SOView> dic = new SortedDictionary<string, SOView>();
DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
foreach (DataRow row in dt.Rows)
{
if (row["TABLE_TYPE"].ToString() != "VIEW") continue;
if (row["TABLE_OWNER"].ToString() == "sys" || row["TABLE_OWNER"].ToString() == "INFORMATION_SCHEMA") continue;
SOView view = new SOView { Parent = db, Name = row["TABLE_NAME"].ToString(), Owner = row["TABLE_OWNER"].ToString() };
view.SchemaName = view.Owner;
dic.Add(view.Name, view);
}
return dic.Values.ToList<SOView>();
}
public void AddDbListItem(SODatabase db)
{
try
{
toolItemDbList.Items.Add(db);
}
catch { }
}
/// <summary>
///
/// </summary>
/// <param name="db"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public virtual SOTable GetTable(SODatabase db, string tableName)
{
var table = GetTableList(db).Find(p => p.Name == tableName);
return table;
}
/// <summary>
/// 获取存储过程列表
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public override List<SOCommand> GetCommandList(SODatabase db)
{
string cmdText = string.Format(@"select PROCEDURE_NAME from dba_procedures where owner='{0}'", db.Name);
List<SOCommand> commandList = new List<SOCommand>();
DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
foreach (DataRow row in dt.Rows)
{
SOCommand command = new SOCommand { Parent = db, Name = row["PROCEDURE_NAME"].ToString(), Comment = row["PROCEDURE_NAME"].ToString() };
commandList.Add(command);
}
return commandList;
}
/// <summary>
/// 获取视图列表
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public virtual List<SOView> GetViewList(SODatabase db)
{
List<SOView> list = new List<SOView>();
string[] restrictions = new string[3];
restrictions[0] = db.Name;
DataTable dt = GetSchema(MetaDataCollectionName_Views, restrictions);
foreach (DataRow dr in dt.Rows)
{
SOView view = new SOView();
view.Name = dr["table_name"].ToString();
view.Comment = view.Name;
view.Parent = db;
list.Add(view);
}
return list;
}
/// <summary>
/// 获取表列表
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public virtual List<SOTable> GetTableList(SODatabase db)
{
List<SOTable> list = new List<SOTable>();
string[] restrictions = new string[4];
restrictions[0] = db.Name;
DataTable dt = GetSchema(MetaDataCollectionName_Tables, restrictions);
foreach (DataRow dr in dt.Rows)
{
SOTable table = new SOTable();
table.Name = dr["table_name"].ToString();
table.Comment = table.Name;
table.Parent = db;
list.Add(table);
}
return list;
}
/// <summary>
/// 在指定数据库上执行一个查询，返回一个结果集
/// </summary>
/// <param name="db"></param>
/// <param name="cmdText"></param>
/// <returns></returns>
public virtual DataSet ExecuteQuery(SODatabase db, string cmdText)
{
this.DbProvider.CurrentDatabaseName = db.Name.Contains("[") ? db.Name.Replace("[", "").Replace("]", "") : db.Name;
DataSet ds = this.DbProvider.ExecuteDataSet(CommandType.Text, cmdText);
return ds;
}