string GetTableComment(SOTable table) { string key = string.Format("{0}_{1}_{2}", table.Database.Name, table.Owner, table.Name); lock (syncObject) { if (dic.ContainsKey(key)) return dic[key]; else { string cmdText = string.Format(@"use [{0}]; SELECT objname as 'table_name', value as 'comment' FROM fn_listextendedproperty (NULL, 'schema', '{1}', 'table', default, NULL, NULL); ", table.Database.Name, table.Owner); DataTable dt = this.DbProvider.ExecuteDataSet(CommandType.Text, cmdText).Tables[0]; foreach (DataRow row in dt.Rows) { string tempKey = string.Format("{0}_{1}_{2}", table.Database.Name, table.Owner, row["table_name"].ToString()); if (dic.ContainsKey(tempKey)) continue; dic.Add(tempKey, row["comment"].ToString()); } } if (dic.ContainsKey(key)) return dic[key]; else return table.Name; } }
//获取表的主键列 List<string> GetPrimaryKeys(SOTable table) { string cmdText = string.Format(@"select c.owner,c.constraint_name,c.table_name,cc.column_name,cc.position from dba_constraints c inner join dba_cons_columns cc on c.constraint_name = cc.constraint_name where c.owner='{0}' and c.constraint_type='P' and c.table_name='{1}'", table.Database.Name, table.Name); List<string> list = new List<string>(); DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0]; foreach (DataRow row in dt.Rows) { list.Add(row["column_name"].ToString()); } return list; }
public static SOTable ToSOTable(PDTable table) { SOTable t = new SOTable(); t.Name = table.Code; t.Comment = string.IsNullOrEmpty(table.Comment) ? table.Name : table.Comment; t.ColumnList = new List<SOColumn>(); foreach (PDColumn item in table.ColumnList) { t.ColumnList.Add(ToSOColumn(item)); } t.IndexList = new List<SOIndex>(); return t; }
/// <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>(); }
/// <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; }
/// <summary> /// 获取表的Sql脚本 /// </summary> /// <param name="table"></param> /// <returns></returns> public override string GetTableSqlText(SOTable table) { throw new NotImplementedException(); }
/// <summary> /// 获取表的Sql脚本 /// </summary> /// <param name="table"></param> /// <returns></returns> public virtual string GetTableSqlText(SOTable table) { return "该方法目前还没有实现"; }
/// <summary> /// 获取表所拥有的索引列表 /// </summary> /// <param name="table"></param> /// <returns></returns> public virtual List<SOIndex> GetTableIndexList(SOTable table) { List<SOIndex> list = new List<SOIndex>(); string[] restrictions = new string[4]; restrictions[0] = table.Database.Name; //restrictions[4] = table.Name; DataTable dt = GetSchema(MetaDataCollectionName_Indexes, restrictions); foreach (DataRow dr in dt.Rows) { if (dr["TABLE_NAME"].ToString() != table.Name) continue; SOIndex index = new SOIndex(); index.Name = dr["INDEX_NAME"].ToString(); index.IndexColumnName = dr["COLUMN_NAME"].ToString(); index.IsPrimaryKey = dr["PRIMARY_KEY"].ToString().ToLower() == "true" ? true : false; index.IsUnique = dr["UNIQUE"].ToString().ToLower() == "true" ? true : false; index.IsCluster = dr["CLUSTERED"].ToString().ToLower() == "true" ? true : false; index.IsIdentity = dr["NULLS"].ToString() == "1" ? true : false; //这里判断自增列默认情况下是没问题的 index.Comment = index.Name; index.Parent = table; list.Add(index); } return list; }
/// <summary> /// 获取表所拥有的列列表 /// </summary> /// <param name="table"></param> /// <returns></returns> public virtual List<SOColumn> GetTableColumnList(SOTable table) { List<SOColumn> list = new List<SOColumn>(); string[] restrictions = new string[4]; restrictions[0] = table.Database.Name; restrictions[2] = table.Name; DataTable dt = GetSchema(MetaDataCollectionName_Columns, restrictions); foreach (DataRow dr in dt.Rows) { SOColumn c = new SOColumn(); c.Name = dr["column_name"].ToString(); c.Comment = c.Name; c.Parent = table; c.DefaultValue = ConvertUtil.ToString(dr["column_default"]); c.Nullable = dr["is_nullable"].ToString() == "YES" ? true : false; c.NativeType = dr["data_type"].ToString(); c.Length = ConvertUtil.ToInt32(dr["character_maximum_length"], -1); c.Precision = ConvertUtil.ToInt32(dr["numeric_precision"], -1); c.Scale = ConvertUtil.ToInt32(dr["numeric_scale"], -1); c.DataType = this.GetDbType(c.NativeType); list.Add(c); } 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; }
public DbTableViewer(SOTable table) { InitializeComponent(); currentTable = table; }
//获取表的主键列 List<string> GetPrimaryKeys(SOTable table) { string cmdText = string.Format(@"use [{2}];exec sp_pkeys '{0}','{1}','{2}';", table.Name, table.Owner, table.Database.Name); List<string> list = new List<string>(); DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0]; foreach (DataRow row in dt.Rows) { list.Add(row["column_name"].ToString()); } return list; }
/// <summary> /// 获取表所拥有的列列表 /// </summary> /// <param name="table"></param> /// <returns></returns> public override List<SOColumn> GetTableColumnList(SOTable table) { string cmdText = string.Format(@"use [{2}];exec sp_columns '{0}','{1}','{2}';", table.Name, table.Owner, table.Database.Name); List<SOColumn> columnList = new List<SOColumn>(); List<string> pkList = GetPrimaryKeys(table); DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0]; foreach (DataRow row in dt.Rows) { SOColumn column = new SOColumn { Parent = table, Name = row["column_name"].ToString(), DefaultValue = row["column_def"].ToString(), Nullable = row["is_nullable"].ToString().ToUpper() == "YES", NativeType = row["type_name"].ToString().Replace(" identity",""), Identify = row["type_name"].ToString().IndexOf("identity") != -1, //ForeignKey Length = ConvertUtil.ToInt32(row["length"], -1), Precision = ConvertUtil.ToInt32(row["precision"], -1), Scale = ConvertUtil.ToInt32(row["scale"], -1), }; column.PrimaryKey = pkList.Contains(column.Name); column.DataType = this.GetDbType(column.NativeType); column.Comment = GetColumnComment(column); columnList.Add(column); } return columnList; }
/// <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>(); }
/// <summary> /// 获取表所拥有的索引列表 /// </summary> /// <param name="table"></param> /// <returns></returns> public virtual List<SOIndex> GetTableIndexList(SOTable table) { throw new NotImplementedException(); }
/// <summary> /// 获取表的Sql脚本 /// </summary> /// <param name="table"></param> /// <returns></returns> public override string GetTableSqlText(SOTable table) { List<SOColumn> columnnList = table.ColumnList; StringBuilder sb = new StringBuilder(); sb.AppendLine("create table [dbo].[" + table.Name + "] ("); foreach(SOColumn item in columnnList) { sb.AppendFormat("\t[{0}] {1}", item.Name, item.NativeType); if (item.PrimaryKey) { sb.Append(" PRIMARY KEY "); } if (item.Identify) { sb.Append(" identity "); } else if (!item.Identify) { sb.Append(" not null "); if (item.DefaultValue != null && item.DefaultValue != "") { sb.Append(" default " + item.DefaultValue); } } sb.Append(",\r\n"); } sb = sb.TrimEnd(",\r\n"); sb.AppendLine("\r\n)"); return sb.ToString() ; }
/// <summary> /// 获取表所拥有的列列表 /// </summary> /// <param name="table"></param> /// <returns></returns> public override List<SOColumn> GetTableColumnList(SOTable table) { string cmdText = string.Format(@"select c.COLUMN_ID,c.COLUMN_NAME,c.DATA_TYPE,c.DATA_LENGTH,c.DATA_PRECISION,c.DATA_SCALE,c.NULLABLE,c.DATA_DEFAULT,c.CHAR_COL_DECL_LENGTH,c.CHAR_LENGTH,cc.COMMENTS from dba_tab_columns c left join dba_col_comments cc on c.table_name=cc.table_name and c.column_name=cc.column_name where c.owner='{0}' and cc.owner='{0}' and c.table_name='{1}' order by c.column_id", table.Database.Name, table.Name); List<SOColumn> columnList = new List<SOColumn>(); List<string> pkList = GetPrimaryKeys(table); DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0]; StringCollection sc = new StringCollection(); foreach (DataRow row in dt.Rows) { SOColumn column = new SOColumn { Parent = table, Name = row["COLUMN_NAME"].ToString(), DefaultValue = row["DATA_LENGTH"].ToString(), Nullable = row["NULLABLE"].ToString().ToUpper() == "Y", NativeType = row["DATA_TYPE"].ToString().Replace(" identity", ""), //Identify = row["type_name"].ToString().IndexOf("identity") != -1, Identify = false, //ForeignKey Length = ConvertUtil.ToInt32(row["CHAR_LENGTH"], 0), Precision = ConvertUtil.ToInt32(row["DATA_PRECISION"], 0), Scale = ConvertUtil.ToInt32(row["DATA_SCALE"], 0), Comment = row["COMMENTS"].ToString() }; column.PrimaryKey = pkList.Contains(column.Name); column.DataType = this.GetDbType(column.NativeType, column.Precision, column.Scale); if (!sc.Contains(column.Name)) { columnList.Add(column); } sc.Add(column.Name); } return columnList; }
/// <summary> /// 获取表所拥有的列列表 /// </summary> /// <param name="table"></param> /// <returns></returns> public override List<SOColumn> GetTableColumnList(SOTable table) { List<SOColumn> list = new List<SOColumn>(); string[] restrictions = new string[4]; restrictions[0] = table.Database.Name; restrictions[2] = table.Name; DataTable dt = GetSchema(MetaDataCollectionName_Columns, restrictions); List<SOIndex> indexList = this.GetTableIndexList(table); foreach (DataRow dr in dt.Rows) { SOColumn c = new SOColumn(); c.Name = dr["column_name"].ToString(); c.Comment = dr["DESCRIPTION"].ToString(); c.Parent = table; if (dr["COLUMN_HASDEFAULT"].ToString().ToLower() != "false") { c.DefaultValue = ConvertUtil.ToString(dr["COLUMN_DEFAULT"]); } c.Nullable = dr["IS_NULLABLE"].ToString().ToLower() == "true" ? true : false; c.NativeType = dr["DATA_TYPE"].ToString(); c.Length = ConvertUtil.ToInt32(dr["CHARACTER_MAXIMUM_LENGTH"], -1); c.Precision = ConvertUtil.ToInt32(dr["NUMERIC_PRECISION"], -1); c.Scale = ConvertUtil.ToInt32(dr["NUMERIC_SCALE"], -1); c.DataType = this.GetDbType(c.NativeType); c.PrimaryKey = IndexColumnIsPrimary(indexList, c.Name); c.Identify = IndexColumnIsIdentity(indexList, c.Name); list.Add(c); } return list; }