/// <summary>
/// 表说明
/// </summary>
/// <returns></returns>
public static void InitTable(Data_Source link, bool IsLoad)
{
if (AppCache.ExistsTable(link) && IsLoad)
{
return;
}
var list = new List <Cache_Table>();
var dt = new DataTable();
//oracle 表信息
if (link.Type == DataDbType.Oracle)
{
#region oracle
using (var conn = new OracleConnection(BaseLink.GetConnStr(link)))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "select a.table_name,comments from all_tables a inner join all_tab_comments b on a.TABLE_NAME=b.TABLE_NAME and a.TABLESPACE_NAME!='SYSAUX' and a.TABLESPACE_NAME!='SYSTEM'";
var rd = cmd.ExecuteReader();
dt.Load(rd);
conn.Close();
}
#endregion
}
//sql server 表信息
if (link.Type == DataDbType.SqlServer)
{
#region sqlserver
using (var conn = new SqlConnection(BaseLink.GetConnStr(link)))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "select name,(select top 1 value from sys.extended_properties where major_id=object_id(a.name) and minor_id=0) as value from sys.objects a where type='U'";
var rd = cmd.ExecuteReader();
dt.Load(rd);
conn.Close();
}
#endregion
}
//mysql 表信息
if (link.Type == DataDbType.MySql)
{
#region mysql
using (var conn = new MySqlConnection(BaseLink.GetConnStr(link)))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = string.Format("select table_name, table_comment from information_schema.TABLES where table_schema='{0}' and table_type='BASE TABLE'", link.ServerName);
var rd = cmd.ExecuteReader();
dt.Load(rd);
conn.Close();
}
#endregion
}
foreach (DataRow item in dt.Rows)
{
var table = new Cache_Table();
table.Comments = item.ItemArray[1] == DBNull.Value ? "" : item.ItemArray[1].ToString();
table.Name = item.ItemArray[0] == DBNull.Value ? "" : item.ItemArray[0].ToString();
list.Add(table);
Parallel.Invoke(() => {
InitColumn(link, IsLoad, table.Name);
});
}
AppCache.SetTableList(list, link);
}
/// <summary>
/// 获取列的信息
/// </summary>
/// <returns></returns>
private static void InitColumn(Data_Source link, bool IsLoad, string tableName)
{
if (AppCache.ExistsColumn(link, tableName) && IsLoad)
{
return;
}
var list = new List <Cache_Column>();
var dt = new DataTable();
//oracle 列信息
if (link.Type == DataDbType.Oracle)
{
#region oracle
using (var conn = new OracleConnection(BaseLink.GetConnStr(link)))
{
tableName = tableName.ToUpper();
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = @"select a.column_name,data_type,data_length,b.comments,
(select count(0) from all_cons_columns aa, all_constraints bb
where aa.constraint_name = bb.constraint_name and bb.constraint_type = 'P' and bb.table_name = '"
+ tableName + @"' and aa.column_name=a.column_name),(select count(0) from all_ind_columns t,all_indexes i
where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = '"
+ tableName + @"' and t.column_name=a.column_name),nullable,data_precision,data_scale
from all_tab_columns a inner join all_col_comments b
on a.table_name='" + tableName +
"' and a.table_name=b.table_name and a.column_name=b.column_name order by a.column_id asc";
var rd = cmd.ExecuteReader();
dt.Load(rd);
rd.Close();
conn.Close();
}
#endregion
}
if (link.Type == DataDbType.SqlServer)
{
#region sql server
using (var conn = new SqlConnection(BaseLink.GetConnStr(link)))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = @"select a.name,(select top 1 name from sys.systypes c where a.xtype=c.xtype) as type ,
length,b.value,(select count(0) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME='"
+ tableName + @"' and COLUMN_NAME=a.name),
(SELECT count(0) FROM sysindexes aa JOIN sysindexkeys bb ON aa.id=bb.id AND aa.indid=bb.indid
JOIN sysobjects cc ON bb.id=cc.id JOIN syscolumns dd ON bb.id=dd.id AND bb.colid=dd.colid
WHERE aa.indid NOT IN(0,255) AND cc.name='" + tableName + @"' and dd.name=a.name),isnullable,prec,scale
from syscolumns a left join sys.extended_properties b
on major_id = id and minor_id = colid and b.name ='MS_Description'
where a.id=object_id('" + tableName + "') order by a.colid asc";
var rd = cmd.ExecuteReader();
dt.Load(rd);
rd.Close();
conn.Close();
}
#endregion
}
if (link.Type == DataDbType.MySql)
{
#region mysql
using (var conn = new MySqlConnection(BaseLink.GetConnStr(link)))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = @"select column_name,data_type,character_maximum_length,column_comment,
(select count(0) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE a where TABLE_SCHEMA='" + link.ServerName
+ "' and TABLE_NAME='" + tableName + @"' and constraint_name='PRIMARY' and c.column_name=a.column_name),
(SELECT count(0) from information_schema.statistics a where table_schema = '"
+ link.ServerName + "' and table_name = '" + tableName + @"' and c.column_name=a.column_name),
is_nullable,numeric_precision,numeric_scale,column_type from information_schema.columns c where table_name='"
+ tableName + "' order by ordinal_position asc";
var rd = cmd.ExecuteReader();
dt.Load(rd);
rd.Close();
conn.Close();
}
#endregion
}
foreach (DataRow item in dt.Rows)
{
var column = new Cache_Column();
column.Name = (item.ItemArray[0] == DBNull.Value ? "" : item.ItemArray[0].ToString());
column.Type = item.ItemArray[1] == DBNull.Value ? "" : item.ItemArray[1].ToString();
column.Length = item.ItemArray[2] == DBNull.Value ? 0 : decimal.Parse(item.ItemArray[2].ToString());
column.Comments = item.ItemArray[3] == DBNull.Value ? "" : item.ItemArray[3].ToString();
column.Precision = item.ItemArray[7] == DBNull.Value ? 0 : int.Parse(item.ItemArray[7].ToString());
column.ShowName = string.Format("{0}({1})", column.Name, column.Comments);
list.Add(column);
}
AppCache.SetColumnList(list, link, tableName);
}