/// <summary>
/// 修改表说明
/// </summary>
public static void UpdateTabComments(BaseTable item, DataLink link)
{
try
{
var dt = new DataTable();
var sql = new StringBuilder();
if (link.dbType == DataDbType.SqlServer)
{
#region sqlserver
sql.AppendFormat(@"exec sys.sp_updateextendedproperty N'MS_Description',N'{0}',N'SCHEMA', N'dbo', N'TABLE',N'{1}'", item.tabComments, item.tabName);
using (var conn = new SqlConnection(link.connStr))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = string.Format("select count(0) from sys.extended_properties where object_id('{0}')=major_id and minor_id=0", item.tabName);
var rd = cmd.ExecuteReader();
dt.Load(rd);
if (Int32.Parse(dt.Rows[0][0].ToString()) >= 1)
{
cmd.CommandText = sql.ToString();
cmd.ExecuteNonQuery();
}
else
{
cmd.CommandText = sql.ToString().Replace("sp_updateextendedproperty", "sp_addextendedproperty");
cmd.ExecuteNonQuery();
}
conn.Close();
}
#endregion
}
else if (link.dbType == DataDbType.Oracle)
{
#region oracle
using (var conn = new OracleConnection(link.connStr))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = string.Format("Comment on table {0} is '{1}'", item.tabName, item.tabComments);
cmd.ExecuteNonQuery();
conn.Close();
}
#endregion
}
else if (link.dbType == DataDbType.MySql)
{
#region mysql
using (var conn = new MySqlConnection(link.connStr))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = string.Format("alter table {0} comment '{1}'", item.tabName, item.tabComments);
cmd.ExecuteNonQuery();
conn.Close();
}
#endregion
}
}
catch (Exception ex)
{
log.SaveLog(ex.ToString(), "UpdateTabComments");
}
}
/// <summary>
/// 修改列说明
/// </summary>
public static void UpdateColComments(BaseColumn colItem, BaseTable tabItem, DataLink link)
{
try
{
var dt = new DataTable();
var sql = new StringBuilder();
if (link.dbType == DataDbType.SqlServer)
{
#region sqlserver
sql.AppendFormat(@"exec sys.sp_updateextendedproperty N'MS_Description',N'{0}',N'SCHEMA', N'dbo', N'TABLE',N'{1}',N'column',N'{2}'"
, colItem.colComments, tabItem.tabName, colItem.colName);
using (var conn = new SqlConnection(link.connStr))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = string.Format(@"select count(0) from syscolumns where id = object_id('{0}') and name='{1}'
and exists(select 1 from sys.extended_properties where object_id('{0}')=major_id and colid=minor_id)"
, tabItem.tabName, colItem.colName);
var rd = cmd.ExecuteReader();
dt.Load(rd);
if (Int32.Parse(dt.Rows[0][0].ToString()) >= 1)
{
cmd.CommandText = sql.ToString();
cmd.ExecuteNonQuery();
}
else
{
cmd.CommandText = sql.ToString().Replace("sp_updateextendedproperty", "sp_addextendedproperty");
cmd.ExecuteNonQuery();
}
conn.Close();
}
#endregion
}
else if (link.dbType == DataDbType.Oracle)
{
#region oracle
using (var conn = new OracleConnection(link.connStr))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = string.Format("Comment on column {0}.{1} is '{2}'", tabItem.tabName, colItem.colName, colItem.colComments);
cmd.ExecuteNonQuery();
conn.Close();
}
#endregion
}
else if (link.dbType == DataDbType.MySql)
{
#region mysql
using (var conn = new MySqlConnection(link.connStr))
{
conn.Open();
var cmd = conn.CreateCommand();
if (colItem.isKey)
{
cmd.CommandText = string.Format("update columns set column_comment ='{0}' where table_schema='{1}' and table_name='{2}' and clumn_name='{3}'"
, colItem.colComments, link.serverValue, tabItem.tabName, colItem.colComments);
}
else
{
cmd.CommandText = string.Format("alter table {0} modify {1} {2} comment '{3}'"
, tabItem.tabName, colItem.colName, colItem.showType, colItem.colComments);
}
cmd.ExecuteNonQuery();
conn.Close();
}
#endregion
}
}
catch (Exception ex) { log.SaveLog(ex.ToString(), "UpdateColComments"); }
}
/// <summary>
/// 列缓存键
/// </summary>
/// <returns></returns>
public static string GetColumnKey(DataLink link, string tableName)
{
return(string.Format("{0}_{1}_{2}", link.dbType, tableName, link.hostName));
}
/// <summary>
/// 获取表说明
/// </summary>
/// <param name="dbType">数据库类型</param>
/// <param name="strConn">连接串</param>
/// <returns></returns>
public static List <BaseTable> TableList(DataLink link, bool isUpdate = false)
{
try
{
DbConnection conn = null;
var list = new List <BaseTable>();
var dt = new DataTable();
//oracle 表信息
if (link.dbType == DataDbType.Oracle)
{
#region oracle
conn = new OracleConnection(link.connStr);
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "select distinct 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);
#endregion
}
//sql server 表信息
if (link.dbType == DataDbType.SqlServer)
{
#region sqlserver
conn = new SqlConnection(link.connStr);
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);
#endregion
}
//mysql 表信息
if (link.dbType == DataDbType.MySql)
{
#region mysql
conn = new MySqlConnection(link.connStr);
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.serverValue);
var rd = cmd.ExecuteReader();
dt.Load(rd);
#endregion
}
foreach (DataRow item in dt.Rows)
{
var table = new BaseTable();
table.tabComments = item.ItemArray[1] == DBNull.Value ? "" : item.ItemArray[1].ToString();
table.tabName = item.ItemArray[0] == DBNull.Value ? "" : item.ItemArray[0].ToString();
list.Add(table);
Parallel.Invoke(() => { ColumnList(link, table.tabName, conn, isUpdate); });
}
conn.Close();
return(list);
}
catch
{
return(new List <BaseTable>());
}
}
/// <summary>
/// 获取视图说明
/// </summary>
/// <param name="dbType">数据库类型</param>
/// <param name="strConn">连接串</param>
/// <returns></returns>
public static List <BaseTable> ViewList(DataLink link, bool isUpdate = false)
{
try
{
DbConnection conn = null;
var list = new List <BaseTable>();
var dt = new DataTable();
//oracle 表信息
if (link.dbType == DataDbType.Oracle)
{
#region oracle
conn = new OracleConnection(link.connStr);
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "select a.VIEW_NAME from all_views a where a.owner!='SYSTEM' and a.owner!='EXFSYS' and a.owner!='SYSMAN' and a.owner!='SYS' and a.owner!='WMSYS'";
var rd = cmd.ExecuteReader();
dt.Load(rd);
#endregion
}
//sql server 表信息
if (link.dbType == DataDbType.SqlServer)
{
#region sqlserver
conn = new SqlConnection(link.connStr);
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "select name from sys.views";
var rd = cmd.ExecuteReader();
dt.Load(rd);
#endregion
}
//mysql 表信息
if (link.dbType == DataDbType.MySql)
{
#region mysql
conn = new MySqlConnection(link.connStr);
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = string.Format("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.views WHERE TABLE_SCHEMA = '{0}'", link.serverValue);
var rd = cmd.ExecuteReader();
dt.Load(rd);
#endregion
}
foreach (DataRow item in dt.Rows)
{
var table = new BaseTable();
table.tabComments = "";
table.tabName = item.ItemArray[0] == DBNull.Value ? "" : item.ItemArray[0].ToString();
list.Add(table);
Parallel.Invoke(() => { ColumnList(link, table.tabName, conn, isUpdate); });
}
conn.Close();
return(list);
}
catch
{
return(new List <BaseTable>());
}
}
/// <summary>
/// 获取列的信息
/// </summary>
/// <param name="dbType">数据库类型</param>
/// <param name="strConn">连接串</param>
/// <param name="tableName">表名</param>
/// <returns></returns>
public static List <BaseColumn> ColumnList(DataLink link, string tableName, DbConnection conn = null, bool isUpdate = false)
{
try
{
var key = GetColumnKey(link, tableName);
if (conn == null || isUpdate == false)
{
return(AppCache.GetTableColumn(key));
}
//脱机
if (!AppCache.GetLineState(link))
{
var templist = new List <BaseColumn>();
templist.Add(new BaseColumn {
colName = "数据库脱机"
});
return(templist);
}
var list = new List <BaseColumn>();
var dt = new DataTable();
//oracle 列信息
if (link.dbType == DataDbType.Oracle)
{
#region oracle
tableName = tableName.ToUpper();
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,user_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,data_default
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();
#endregion
}
if (link.dbType == DataDbType.SqlServer)
{
#region sql server
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,REPLACE(REPLACE(REPLACE(REPLACE( t2.text,'((',''),'))',''),'(N''',''),''')','')
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();
#endregion
}
if (link.dbType == DataDbType.MySql)
{
#region mysql
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.serverValue
+ "' 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.serverValue + "' and table_name = '" + tableName + @"' and c.column_name=a.column_name),
is_nullable,numeric_precision,numeric_scale,column_type,default(column_name) from information_schema.columns c where table_name='"
+ tableName + "' order by ordinal_position asc";
var rd = cmd.ExecuteReader();
dt.Load(rd);
rd.Close();
#endregion
}
foreach (DataRow item in dt.Rows)
{
var column = new BaseColumn();
column.colName = (item.ItemArray[0] == DBNull.Value ? "" : item.ItemArray[0].ToString());
column.colType = item.ItemArray[1] == DBNull.Value ? "" : item.ItemArray[1].ToString();
column.colLength = item.ItemArray[2] == DBNull.Value ? 0 : decimal.Parse(item.ItemArray[2].ToString());
column.colComments = item.ItemArray[3] == DBNull.Value ? "" : item.ItemArray[3].ToString();
column.isKey = item.ItemArray[4].ToString() != "0" ? true : false;
column.isNull = (item.ItemArray[6].ToString().ToUpper().Trim() == "Y" || item.ItemArray[6].ToString().ToUpper().Trim() == "YES" || item.ItemArray[6].ToString().ToUpper().Trim() == "1") ? "是" : "否";
column.precision = item.ItemArray[7] == DBNull.Value ? 0 : int.Parse(item.ItemArray[7].ToString());
column.scale = item.ItemArray[8] == DBNull.Value ? 0 : int.Parse(item.ItemArray[8].ToString());
column.isIndex = item.ItemArray[5].ToString() != "0" ? true : false;
if (link.dbType == DataDbType.MySql)
{
column.showType = item.ItemArray[9] == DBNull.Value ? GetShowColType(column) : item.ItemArray[9].ToString();
column.defaultData = item.ItemArray[10].ToString();
}
else
{
column.showType = GetShowColType(column);
}
column.defaultData = item.ItemArray[9].ToString();
column.showColName = string.Format("{0}{1}{2}", column.isKey ? "(主键) " : "", column.isIndex ? "(索引) " : "", column.colName);
column.showTypeColName = string.Format("{0} [{1}]", column.colName, column.showType);
list.Add(column);
}
AppCache.SetOnLine(link);
AppCache.SetTableColumn(list, key);
return(list);
}
catch
{
AppCache.SetOffLine(link);
return(new List <BaseColumn>());
}
}
public static bool ExistsView(DataLink link)
{
return(DataCache.Exists(GetViewKey(link)));
}
private static string GetTableKey(DataLink link)
{
return(string.Format("tableList_{0}_{1}_{2}", link.hostName, link.serverName, link.serverValue));
}
//视图list
public static List <BaseTable> GetViewList(DataLink link)
{
return(DataCache.Get <List <BaseTable> >(GetViewKey(link)) ?? new List <BaseTable>());
}
public static void SetViewList(List <BaseTable> item, DataLink link)
{
DataCache.Set <List <BaseTable> >(GetViewKey(link), item);
}
public static bool ExistsTable(DataLink link)
{
return(DataCache.Exists(GetTableKey(link)));
}
public static void SetBuildLink(DataLink item)
{
DataCache.Set <DataLink>("buildLink", item);
}
/// <summary>
/// 脱机
/// </summary>
/// <param name="link"></param>
public static void SetOffLine(DataLink link)
{
DataCache.Remove(GetLinkKey(link));
}
/// <summary>
/// 联机
/// </summary>
/// <param name="link"></param>
public static void SetOnLine(DataLink link)
{
DataCache.Set(GetLinkKey(link), "");
}
/// <summary>
/// 是否联机
/// </summary>
/// <param name="link"></param>
/// <returns></returns>
public static bool GetLineState(DataLink link)
{
return(DataCache.Exists(GetLinkKey(link)));
}
