dataprovider.cs

基于vs2.0开发的WEB管理SQL功能该软件是一个用于管理SQL Server的工具

                string row = dt.Rows[i]["text"].ToString();
                result += row;
            }
            return result;
        } 
        #endregion

        #endregion

        #region 扩展属性相关

        #region fn_listextendedproperty 方案 [否决的]
        /*
         * 查询扩展属性有两种方法，fn_listextendedproperty 与 sys.extended_properties
         * 因为考虑到sys.extended_properties不兼容sql2000，所以我决定用fn_listextendedproperty
         * （其实我也不知道fn_listextendedproperty是否兼容sql200，但至少比sys.extended_properties更有可能的兼容sql2000）
         * 
         * 但，后来发现有个这样的问题，直接想晕倒，解决不了。。。 fn开头的存储过程挺怪的。。。
         * 消息 468，级别 16，状态 9，第 1 行
         * 无法解决 equal to 操作中 "Latin1_General_CI_AI" 和 "Chinese_PRC_CI_AS" 之间的排序规则冲突。
         * 
         * 现在只好改用sys.extended_properties目录视图了，不能兼容sql2000了，想晕。。
        */

        #region fn_listextendedproperty 相关

        #region <fn_listextendedproperty> 查询扩展属性
        /// <summary>
        /// 查询扩展属性 
        /// 请参考
        /// fn_listextendedproperty (Transact-SQL)
        /// sp_addextendedproperty (Transact-SQL)
        /// sp_dropextendedproperty (Transact-SQL)
        /// sp_updateextendedproperty (Transact-SQL)
        /// sys.extended_properties (Transact-SQL)
        /// </summary>
        private static string fn_listextendedproperty(string name, string type0, string name0, string type1, string name1, string type2, string name2)
        {
            string _name = string.IsNullOrEmpty(name) ? "default" : "'" + name + "'";
            string _type0 = string.IsNullOrEmpty(type0) ? "default" : "'" + type0 + "'";
            string _name0 = string.IsNullOrEmpty(name0) ? "default" : "'" + name0 + "'";
            string _type1 = string.IsNullOrEmpty(type1) ? "default" : "'" + type1 + "'";
            string _name1 = string.IsNullOrEmpty(name1) ? "default" : "'" + name1 + "'";
            string _type2 = string.IsNullOrEmpty(type2) ? "default" : "'" + type2 + "'";
            string _name2 = string.IsNullOrEmpty(name2) ? "default" : "'" + name2 + "'";

            string sql = string.Format("fn_listextendedproperty({0},{1},{2},{3},{4},{5},{6})"
                , _name, _type0, _name0, _type1, _name1, _type2, _name2);

            return sql;
        }
        #endregion

        #region <fn_listextendedproperty_database> 查询数据库的扩展属性
        /// <summary>
        /// 查询数据库的扩展属性
        /// </summary>
        private static string fn_listextendedproperty_database()
        {
            return fn_listextendedproperty(null, null, null, null, null, null, null);
        }
        #endregion

        #region <fn_listextendedproperty_table> 查询架构中的所有表的扩展属性
        /// <summary>
        /// 查询架构中的所有表的扩展属性
        /// </summary>
        /// <param name="schema">架构名，默认为dbo</param>
        /// <returns></returns>
        private static string fn_listextendedproperty_table(string schema)
        {
            return fn_listextendedproperty(null, "schema", schema, "table", null, null, null);
        }
        /// <summary>
        /// 查询架构中的所有表的扩展属性
        /// </summary>
        private static string fn_listextendedproperty_table()
        {
            return fn_listextendedproperty_table("dbo");
        }
        #endregion

        #region <fn_listextendedproperty_column> 查询表中的所有列的扩展属性
        /// <summary>
        /// 查询表中的所有列的扩展属性
        /// </summary>
        private static string fn_listextendedproperty_column(string schema, string table)
        {
            return fn_listextendedproperty(null, "schema", schema, "table", table, "column", null);
        }
        /// <summary>
        /// 查询表中的所有列的扩展属性
        /// </summary>
        private static string fn_listextendedproperty_column(string table)
        {
            return fn_listextendedproperty_column("dbo", table);
        }
        #endregion

        #region <fn_listextendedproperty_view> 查询架构中的所有视图的扩展属性
        /// <summary>
        /// 查询架构中的所有视图的扩展属性
        /// </summary>
        private static string fn_listextendedproperty_view(string schema)
        {
            return fn_listextendedproperty(null, "schema", schema, "view", null, null, null);
        }
        /// <summary>
        /// 查询架构中的所有视图的扩展属性
        /// </summary>
        private static string fn_listextendedproperty_view()
        {
            return fn_listextendedproperty_view("dbo");
        }
        #endregion

        #region <fn_listextendedproperty_procedure> 查询架构中的所有存储过程的扩展属性
        /// <summary>
        /// 查询架构中的所有存储过程的扩展属性
        /// </summary>
        private static string fn_listextendedproperty_procedure(string schema)
        {
            return fn_listextendedproperty(null, "schema", schema, "procedure", null, null, null);
        }
        /// <summary>
        /// 查询架构中的所有存储过程的扩展属性
        /// </summary>
        private static string fn_listextendedproperty_procedure()
        {
            return fn_listextendedproperty_procedure("dbo");
        }
        #endregion

        #region <fn_listextendedproperty_proc_param> 查询存储过程中所有参数的扩展属性
        /// <summary>
        /// 查询存储过程中所有参数的扩展属性
        /// </summary>
        private static string fn_listextendedproperty_proc_param(string schema, string proc)
        {
            return fn_listextendedproperty(null, "schema", schema, "procedure", proc, "parameter", null);
        }
        /// <summary>
        /// 查询存储过程中所有参数的扩展属性
        /// </summary>
        private static string fn_listextendedproperty_proc_param(string proc)
        {
            return fn_listextendedproperty_proc_param("dbo", proc);
        }
        #endregion

        #region <fn_listextendedproperty_function> 查询架构中的所有函数的扩展属性
        /// <summary>
        /// 查询架构中的所有函数的扩展属性
        /// </summary>
        private static string fn_listextendedproperty_function(string schema)
        {
            return fn_listextendedproperty(null, "schema", schema, "function", null, null, null);
        }
        /// <summary>
        /// 查询架构中的所有函数的扩展属性
        /// </summary>
        private static string fn_listextendedproperty_function()
        {
            return fn_listextendedproperty_function("dbo");
        }
        #endregion

        #region <fn_listextendedproperty_fun_param> 查询函数中所有参数的扩展属性
        /// <summary>
        /// 查询函数中所有参数的扩展属性
        /// </summary>
        private static string fn_listextendedproperty_fun_param(string schema, string fun)
        {
            return fn_listextendedproperty(null, "schema", schema, "function", fun, "parameter", null);
        }
        /// <summary>
        /// 查询函数中所有参数的扩展属性
        /// </summary>
        private static string fn_listextendedproperty_fun_param(string fun)
        {
            return fn_listextendedproperty_fun_param("dbo", fun);
        }
        #endregion

        #region <fn_listextendedproperty_tigger> 查询表中所有触发器的扩展属性
        /// <summary>
        /// 查询表中所有触发器的扩展属性
        /// </summary>
        private static string fn_listextendedproperty_tigger(string schema, string table)
        {
            return fn_listextendedproperty(null, "schema", schema, "table", table, "tigger", null);
        }
        /// <summary>
        /// 查询表中所有触发器的扩展属性
        /// </summary>
        private static string fn_listextendedproperty_tigger(string table)
        {
            return fn_listextendedproperty_tigger("dbo", table);
        }
        #endregion

        #endregion

        #region GetExtendProperty_DatabaseHelper_MS_Description_SQL 得到扩展属性DatabaseHelper或MS_Description。
        /// <summary>
        /// 得到扩展属性DatabaseHelper或MS_Description。
        /// </summary>
        /// <param name="fn_sql">调用fn_listextendedproperty的sql</param>
        /// <returns></returns>
        private static string GetExtendProperty_DatabaseHelper_MS_Description_SQL(string fn_sql)
        {
            string sql = "select * from " + fn_sql + " where [name]='DatabaseHelper'";
            sql += " union ";
            sql += "select * from " + fn_sql + " where [name]='MS_Description' and objname not in ";
            sql += "(select objname from " + fn_sql + " where [name]='DatabaseHelper')";
            return sql;
        }
        #endregion

        #region ExtendedProperty_Database 数据库的扩展属性
        /// <summary>
        /// 数据库的扩展属性
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="dbname"></param>
        /// <returns></returns>
        public static string ExtendedProperty_Database(SqlConnection conn, string dbname)
        {
            string sql = GetExtendProperty_DatabaseHelper_MS_Description_SQL(fn_listextendedproperty_database());
            DataTable dt = ExecuteDataTable(conn, sql, dbname);
            if (dt == null || dt.Rows.Count == 0) return string.Empty;
            return dt.Rows[0]["value"].ToString();
        }
        #endregion

        #region ExtendedProperty_Tables 表的扩展属性
        /// <summary>
        /// 表的扩展属性
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="dbname"></param>
        /// <returns></returns>
        public static DataTable ExtendedProperty_Tables(SqlConnection conn, string dbname)
        {
            string sql = "select a.name,b.value from ({0}) a left join ({1}) b on a.name=b.name";
            sql = string.Format(sql, sql__select_all_table
                , GetExtendProperty_DatabaseHelper_MS_Description_SQL(fn_listextendedproperty_table()));

            return ExecuteDataTable(conn, sql, dbname);
        }
        #endregion

        #region ExtendedProperty_Columns 表中列的扩展属性
        private static string sql__select_all_column = string.Format("select b.name 'tablename',a.name,a.colid from syscolumns a inner join ({0}) b on a.id=object_id(b.name) and b.name='{1}'", sql__select_all_table, "{0}");
        /// <summary>
        /// 表中列的扩展属性
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="dbname"></param>
        /// <returns></returns>
        public static DataTable ExtendedProperty_Columns(SqlConnection conn, string dbname, string table)
        {
            //string sql = "select a.name,b.value from ({0}) a left join ({1}) b on a.name=b.name";
            //sql = string.Format(sql, sql__select_all_column
            //    , GetExtendProperty_DatabaseHelper_MS_Description_SQL(fn_listextendedproperty_column()));

            string sql = "select a.name,b.value from ({0}) a left join ({1}) b on a.name=b.name";
            sql = string.Format(sql, string.Format(sql__select_all_column, table)
                , GetExtendProperty_DatabaseHelper_MS_Description_SQL(fn_listextendedproperty_column(table)));

            return ExecuteDataTable(conn, sql, dbname);
        }
        #endregion

        #endregion

        #region sysproperties 方案 (仅适用于sql2000)

        #region GetHProperty_DatabaseHelper_MS_Description_SQL 得到扩展属性DatabaseHelper或MS_Description。
        /// <summary>
        /// 得到扩展属性DatabaseHelper或MS_Description。
        /// </summary>
        /// <param name="hp_sql">调用的sql</param>
        /// <returns></returns>
        private static string GetHProperty_DatabaseHelper_MS_Description_SQL(string hp_sql)
        {
            string sql_key = "convert(varchar(50),[id])+'|'+convert(varchar(50),smallid)+'|'+convert(varchar(50),type)";
            string sql = "select * from (" + hp_sql + ") a where name='DatabaseHelper'";
            sql += " union ";
            sql += "select * from (" + hp_sql + ") b where name='MS_Description' and " + sql_key + " not in ";
            sql += "(select " + sql_key + " from (" + hp_sql + ") a where name='DatabaseHelper')";
            return sql;
        }
        #endregion

        #region HProperty_Database 数据库的扩展属性
        /// <summary>
        /// 数据库的扩展属性
        /// </summary>
        /// <returns></returns>
        public static string HProperty_Database(SqlConnection conn, string dbname)
        {
            string sql = "select * from sysproperties where smallid=0 and [id]=0 and [type]=0";
            sql = GetHProperty_DatabaseHelper_MS_Description_SQL(sql);
            DataTable dt = ExecuteDataTable(conn, sql, dbname);
            if (dt == null || dt.Rows.Count == 0) return string.Empty;
            return dt.Rows[0]["value"].ToString();
        }
        #endregion

        #region HProperty_Tables 表的扩展属性
        /// <summary>
        /// 表的扩展属性
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="dbname"></param>
        /// <returns></returns>
        public static DataTable HProperty_Tables(SqlConnection conn, string dbname)
        {
            string sql_xp_table = "select * from sysproperties where smallid=0";
            string sql = "select a.name,b.value from ({0}) a left join ({1}) b on a.name=object_name(b.[id])";
            sql = string.Format(sql, sql__select_all_table
                , GetHProperty_DatabaseHelper_MS_Description_SQL(sql_xp_table));