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