dataprovider.cs

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

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

        #region HProperty_Columns 表中列的扩展属性
        /// <summary>
        /// 表中列的扩展属性
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="dbname"></param>
        /// <returns></returns>
        public static DataTable HProperty_Columns(SqlConnection conn, string dbname, string table)
        {
            string sql_hp_column = "select * from sysproperties";
            string sql = "select a.name,b.value from ({0}) a left join ({1}) b on a.tablename=object_name(b.[id]) and a.colid=b.smallid";
            sql = string.Format(sql, string.Format(sql__select_all_column, table)
                , GetHProperty_DatabaseHelper_MS_Description_SQL(sql_hp_column));

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

        #endregion

        #region sys.extended_properties 方案 (仅适用于sql2005)

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

        #region XProperty_Database 数据库的扩展属性
        /// <summary>
        /// 数据库的扩展属性
        /// </summary>
        /// <returns></returns>
        public static string XProperty_Database(SqlConnection conn,string dbname)
        {
            string sql = "select * from sys.extended_properties where class=0";
            sql = GetXProperty_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 XProperty_Tables 表的扩展属性
        /// <summary>
        /// 表的扩展属性
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="dbname"></param>
        /// <returns></returns>
        public static DataTable XProperty_Tables(SqlConnection conn, string dbname)
        {
            //string sql = "select a.name 'tablename',b.* from ({0}) a left join sys.extended_properties b on a.name=object_name(major_id) and minor_id=0";
            //sql = GetXProperty_DatabaseHelper_MS_Description_SQL(string.Format(sql, sql__select_all_table));

            string sql_xp_table = "select * from sys.extended_properties where minor_id=0";
            string sql = "select a.name,b.value from ({0}) a left join ({1}) b on a.name=object_name(b.major_id)";
            sql = string.Format(sql, sql__select_all_table
                , GetXProperty_DatabaseHelper_MS_Description_SQL(sql_xp_table));

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

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

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

        #endregion

        #region 不分服务器版本的获取扩展属性的方法

        #region GetDatabaseProperty
        public static string GetDatabaseProperty(SqlConnection conn, string dbname)
        {
            return GetDatabaseProperty(GetServerVersion(conn), conn, dbname);
        }
        public static string GetDatabaseProperty(ServerVersion ver, SqlConnection conn, string dbname)
        {
            if (ver == ServerVersion.SqlServer2000)
            {
                return HProperty_Database(conn, dbname);
            }
            else if (ver == ServerVersion.SqlServer2005)
            {
                return XProperty_Database(conn, dbname);
            }
            return string.Empty;
        }
        #endregion

        #region GetTableProperty
        //偷懒做法，以后再优化
        public static string GetTableProperty(SqlConnection conn, string dbname,string tablename)
        {
            return GetTableProperty(GetServerVersion(conn), conn, dbname,tablename);
        }
        public static string GetTableProperty(ServerVersion ver, SqlConnection conn, string dbname,string tablename)
        {
            DataTable dt = null;
            if (ver == ServerVersion.SqlServer2000)
            {
                dt = HProperty_Tables(conn, dbname);
            }
            else if (ver == ServerVersion.SqlServer2005)
            {
                dt = XProperty_Tables(conn, dbname);
            }
            if (dt == null || dt.Rows.Count == 0) return string.Empty;

            dt.DefaultView.RowFilter = "name = '" + tablename + "'";
            if (dt.DefaultView.Count == 0) return string.Empty;
            return dt.DefaultView[0]["value"].ToString();
        }
        #endregion

        #region GetTablePropertys
        public static DataTable GetTablePropertys(SqlConnection conn, string dbname)
        {
            return GetTablePropertys(GetServerVersion(conn), conn, dbname);
        }
        public static DataTable GetTablePropertys(ServerVersion ver, SqlConnection conn, string dbname)
        {
            if (ver == ServerVersion.SqlServer2000)
            {
                return HProperty_Tables(conn, dbname);
            }
            else if (ver == ServerVersion.SqlServer2005)
            {
                return XProperty_Tables(conn, dbname);
            }
            return null;
        }
        #endregion

        #region GetColumnProperty
        //偷懒做法，以后再优化
        public static string GetColumnProperty(SqlConnection conn, string dbname, string table, string column)
        {
            return GetColumnProperty(GetServerVersion(conn), conn, dbname, table, column);
        }
        public static string GetColumnProperty(ServerVersion ver, SqlConnection conn, string dbname, string table, string column)
        {
            DataTable dt = null;
            if (ver == ServerVersion.SqlServer2000)
            {
                dt = HProperty_Columns(conn, dbname, table);
            }
            else if (ver == ServerVersion.SqlServer2005)
            {
                dt = XProperty_Columns(conn, dbname, table);
            }
            if (dt == null || dt.Rows.Count == 0) return string.Empty;

            dt.DefaultView.RowFilter = "name = '" + column + "'";
            if (dt.DefaultView.Count == 0) return string.Empty;
            return dt.DefaultView[0]["value"].ToString();
        }
        #endregion

        #region GetColumnPropertys
        public static DataTable GetColumnPropertys(SqlConnection conn, string dbname, string table)
        {
            return GetColumnPropertys(GetServerVersion(conn), conn, dbname, table);
        }
        public static DataTable GetColumnPropertys(ServerVersion ver, SqlConnection conn, string dbname, string table)
        {
            if (ver == ServerVersion.SqlServer2000)
            {
                return HProperty_Columns(conn, dbname, table);
            }
            else if (ver == ServerVersion.SqlServer2005)
            {
                return XProperty_Columns(conn, dbname, table);
            }
            return null;
        }
        #endregion

        #endregion

        #region 增删改 扩展属性

        //要用到的系统存储过程、系统函数和系统表
        //sp_addextendedproperty
        //sp_updateextendedproperty
        //sp_dropextendedproperty 
        //::fn_listextendedproperty
        // sysproperties (sql2000)
        // sys.extended_properties (sql2005)

        #region IsExistExtendedProperty 判断扩展属性是否存在
        /// <summary>
        /// 判断扩展属性是否存在
        /// </summary>
        /// <returns></returns>
        private static bool IsExistExtendedProperty(SqlConnection conn, string dbname, string name, string level0type, string level0name, string level1type, string level1name, string level2type, string level2name, ServerVersion ver)
        {
            #region ::fn_listextendedproperty 函数解决办法
            // ::fn_listextendedproperty 函数解决办法
            string sql = "select * from ::fn_listextendedproperty('" + name + "'{0})";
            string sql_par = string.Empty;

            if (!string.IsNullOrEmpty(level0type))
                sql_par += ",'" + level0type + "','" + level0name + "'";
            else
                sql_par += ",default,default";

            if (!string.IsNullOrEmpty(level1type))
                sql_par += ",'" + level1type + "','" + level1name + "'";
            else
                sql_par += ",default,default";

            if (!string.IsNullOrEmpty(level2type))
                sql_par += ",'" + level2type + "','" + level2name + "'";
            else
                sql_par += ",default,default";

            sql = string.Format(sql, sql_par);

            DataTable dt = ExecuteDataTable(conn, sql, dbname);
            if (dt == null || dt.Rows.Count == 0)
                return false;
            return true; 
            #endregion

            #region sysproperties (sql2000) 和 sys.extended_properties (sql2005) 解决办法
            // sysproperties (sql2000) 和 sys.extended_properties (sql2005) 解决办法

            //if (ver == ServerVersion.SqlServer2000)
            //{

            //    sql = "select * from sysproperties where [name]='" + name + "' ";

            //    if (!string.IsNullOrEmpty(level1name)) sql += " and id=object_id()";


            //    return false;
            //}
            //else if (ver == ServerVersion.SqlServer2005)
            //{
            //    return false;
            //}
            //else
            //{
            //    return false;
            //} 
            #endregion
        }
        #endregion

        #region AddExtendedProperty 添加扩展属性
        /// <summary>
        /// 添加扩展属性
        /// </summary>
        private static void AddExtendedProperty(SqlConnection conn, string dbname, string name, string value, string level0type, string level0name, string level1type, string level1name, string level2type, string level2name)
        {
            string sql = "exec sp_addextendedproperty '" + name + "','" + value + "'";

            if (!string.IsNullOrEmpty(level0type)) sql += ",'" + level0type + "','" + level0name + "'";
            if (!string.IsNullOrEmpty(level1type)) sql += ",'" + level1type + "','" + level1name + "'";
            if (!string.IsNullOrEmpty(level2type)) sql += ",'" + level2type + "','" + level2name + "'";

            Execute(conn, sql, dbname);
        } 
        #endregion

        #region UpdateExtendedProperty 更新扩展属性
        /// <summary>
        /// 更新扩展属性
        /// </summary>
        private static void UpdateExtendedProperty(SqlConnection conn, string dbname, string name, string value, string level0type, string level0name, string level1type, string level1name, string level2type, string level2name)
        {
            string sql = "exec sp_updateextendedproperty '" + name + "','" + value + "'";

            if (!string.IsNullOrEmpty(level0type)) sql += ",'" + level0type + "','" + level0name + "'";
            if (!string.IsNullOrEmpty(level1type)) sql += ",'" + level1type + "','" + level1name + "'";
            if (!string.IsNullOrEmpty(level2type)) sql += ",'" + level2type + "','" + level2name + "'";

            Execute(conn, sql, dbname);
        } 
        #endregion

        #region DropExtendedProperty 删除扩展属性
        /// <summary>
        /// 删除扩展属性
        /// </summary>
        private static void DropExtendedProperty(SqlConnection conn, string dbname, string name, string level0type, string level0name, string level1type