Beispiel #1
0
        /// <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);
        }
Beispiel #2
0
        /// <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);
        }