/// <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> /// <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> 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> /// <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>()); } }