Ejemplo n.º 1
0
        /// <summary>
        /// 获取所有的表信息包括表注释信息
        /// </summary>
        /// <returns></returns>
        public static Dictionary <string, Dictionary <string, string> > TableinfoList()
        {
            Dictionary <string, Dictionary <string, string> > result = new Dictionary <string, Dictionary <string, string> >();
            DataTable tables = UtilSqlserver.SqlExecute(Sql_Table_Comment);

            if ((tables == null) || (tables.Rows.Count <= 0))
            {
                tables = UtilSqlserver.SqlExecute(Sql_Tables);
            }
            string tablename, key;

            foreach (DataRow item in tables.Rows)
            {
                tablename = (string)item.ItemArray[0];
                Dictionary <string, string> tableInfo = new Dictionary <string, string>();
                tableInfo["Name"] = tablename;
                if (item.ItemArray.Count() > 1)
                {
                    tableInfo["Comment"] = (string)item.ItemArray[1];
                }
                else
                {
                    tableInfo["Comment"] = (string)item.ItemArray[0];
                }
                key         = tablename;//.ToLower();
                result[key] = tableInfo;
            }
            return(result);
        }
Ejemplo n.º 2
0
        /// <summary>
        /// 获取不同表里列名相同的列名
        /// 不包括列名为ID,CommitTime,UpdateTime
        /// 不包含列名为_ID
        /// </summary>
        /// <returns></returns>
        public static string[] Same_Column_Name_In_TableName(string type = "tinyint")
        {
            string typeSql = "";

            if (!string.IsNullOrEmpty(type))
            {
                typeSql = " DATA_TYPE='" + type + "' AND ";
            }
            string    SQLStr = string.Format(Sql_Same_Column_Name, typeSql);
            DataTable tables = UtilSqlserver.SqlExecute(SQLStr);

            string[] Result = new string[tables.Rows.Count];
            string   Column_Name;
            int      i_index = 0;

            foreach (DataRow item in tables.Rows)
            {
                Column_Name = (string)item.ItemArray[1];
                if (Column_Name.ToUpper().Contains("_ID") || Column_Name.ToUpper().Equals("ID") || Column_Name.ToUpper().Equals("COMMITTIME") || Column_Name.ToUpper().Equals("UPDATETIME"))
                {
                }
                else
                {
                    Result.SetValue(Column_Name, i_index);
                    i_index += 1;
                }
            }
            return(Result);
        }
Ejemplo n.º 3
0
        /// <summary>
        /// 返回所有的数据库列表
        /// </summary>
        /// <returns></returns>
        public static List <string> AllDatabaseNames()
        {
            List <string> result    = new List <string>();
            DataTable     databases = UtilSqlserver.SqlExecute(Sql_Databases);
            string        database_name;

            foreach (DataRow item in databases.Rows)
            {
                database_name = (string)item.ItemArray[0];
                result.Add(database_name);
            }
            return(result);
        }
Ejemplo n.º 4
0
        /// <summary>
        /// 查询所有表名
        /// </summary>
        /// <returns></returns>
        public static Dictionary <string, string> TableList()
        {
            Dictionary <string, string> result = new Dictionary <string, string>();
            DataTable tables = UtilSqlserver.SqlExecute(Sql_Tables);
            string    tablename, key;

            foreach (DataRow item in tables.Rows)
            {
                tablename = (string)item.ItemArray[0];
                key       = tablename;//.ToLower();
                if (!key.ToLower().Equals("sysdiagrams"))
                {
                    result[key] = tablename;
                }
            }
            return(result);
        }
Ejemplo n.º 5
0
        /// <summary>
        /// 获取数据库所有的主键外键默认值
        /// </summary>
        /// <param name="table_name"></param>
        /// <returns></returns>
        public static Dictionary <string, Dictionary <string, string> > Table_FK_PK_DK(string table_name)
        {
            Dictionary <string, Dictionary <string, string> > result = new Dictionary <string, Dictionary <string, string> >();
            string    sql_fkpkdk = string.Format(Sql_PK_DK_FK, table_name);
            DataTable tables     = UtilSqlserver.SqlExecute(sql_fkpkdk);
            Dictionary <string, string> fkpkdkInfo;

            foreach (DataRow item in tables.Rows)
            {
                fkpkdkInfo = new Dictionary <string, string>();
                ///外键约束名,子表名,外键列名,父表名
                fkpkdkInfo["constrant_type"] = (string)item.ItemArray[0];
                fkpkdkInfo["constrant_name"] = (string)item.ItemArray[1];
                fkpkdkInfo["constrant_keys"] = (string)item.ItemArray[6];

                result[table_name] = fkpkdkInfo;
            }
            return(result);
        }
Ejemplo n.º 6
0
        /// <summary>
        /// 查询数据库所有的外键
        /// </summary>
        /// <returns></returns>
        public static Dictionary <string, Dictionary <string, string> > Table_Foreign_Key()
        {
            Dictionary <string, Dictionary <string, string> > result = new Dictionary <string, Dictionary <string, string> >();
            DataTable tables = UtilSqlserver.SqlExecute(Sql_Table_Foreign_Key);
            string    tablename;
            Dictionary <string, string> foreignKeyInfo;

            foreach (DataRow item in tables.Rows)
            {
                tablename      = (string)item.ItemArray[1];
                foreignKeyInfo = new Dictionary <string, string>();
                ///外键约束名,子表名,外键列名,父表名
                foreignKeyInfo["fkname"]     = (string)item.ItemArray[0];
                foreignKeyInfo["ftablename"] = (string)item.ItemArray[1];
                foreignKeyInfo["fkcol"]      = (string)item.ItemArray[2];
                foreignKeyInfo["rtable"]     = (string)item.ItemArray[3];

                result[tablename] = foreignKeyInfo;
            }
            return(result);
        }
Ejemplo n.º 7
0
        /// <summary>
        /// 获取指定表所有的列信息
        /// </summary>
        /// <param name="table_name"></param>
        /// <returns></returns>
        public static Dictionary <string, Dictionary <string, string> > FieldInfoList(string table_name)
        {
            Dictionary <string, Dictionary <string, string> > result = new Dictionary <string, Dictionary <string, string> >();

            string    sql = string.Format(Sql_Table_Columns, table_name);
            DataTable columns = UtilSqlserver.SqlExecute(sql);
            string    column_name, column_type;

            foreach (DataRow item in columns.Rows)
            {
                Dictionary <string, string> columnInfo = new Dictionary <string, string>();
                column_name         = (string)item.ItemArray[3];
                columnInfo["Field"] = column_name;
                column_type         = (string)item.ItemArray[7];
                columnInfo["Type"]  = column_type;
                var nullYN = (string)item.ItemArray[6];
                if (nullYN.Contains("YES"))
                {
                    nullYN = "是";
                }
                else
                {
                    nullYN = "否";
                }

                columnInfo["Null"] = nullYN;
                if (column_type.Contains("char"))
                {
                    var len = item.ItemArray[8];
                    columnInfo["Length"] = len.ToString();
                }
                else if (column_type.Contains("numeric") || (column_type.Contains("int")))
                {
                    var len = item.ItemArray[10];
                    columnInfo["Length"] = len.ToString();
                }
                else
                {
                    columnInfo["Length"] = "";
                }
                var fkPk = "";
                if (IsSqlserverDefault)
                {
                    if (column_name.Equals("ID"))
                    {
                        fkPk = "PK";
                    }
                    if (column_name.Contains("_ID"))
                    {
                        fkPk = "FK";
                    }
                }
                else
                {
                    string[] tbl = table_name.Split('_');
                    if (column_name.ToUpper().Contains("_ID") && (column_name.ToUpper().Contains(tbl[tbl.Length - 1].ToUpper())))
                    {
                        fkPk = "PK";
                    }

                    if (column_name.ToUpper().Contains("_ID") && (!column_name.ToUpper().Contains(tbl[tbl.Length - 1].ToUpper())))
                    {
                        fkPk = "FK";
                    }
                }
                columnInfo["Fkpk"] = fkPk;
                sql = string.Format(Sql_Table_Columns_Comment, table_name, column_name);
                DataTable column_comment = UtilSqlserver.SqlExecute(sql);
                if ((column_comment == null) || (column_comment.Rows.Count == 0))
                {
                    columnInfo["Comment"] = column_name;
                    Console.WriteLine("表:" + table_name + "-列:" + column_name + "【无注释】");
                }
                foreach (DataRow item_c in column_comment.Rows)
                {
                    columnInfo["Comment"] = (string)item_c.ItemArray[1];
                }
                result[column_name] = columnInfo;
            }
            return(result);
        }