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