原创:SQL Server 数据库附加分离类

最近太忙.忙着项目收关,忙着新项目的标书,今天下班前刚好有些时间

写一个了关SQL Server 数据库附加分离的类.经过测式可以用,希望大家还用得上

 

有空又加了一个添加用户的功能

修改为类库如下

 

 

   /**//// <summary>
    /// SQL Server 数据库附加分离
    /// 作者：天纯蓝
    /// </summary>
    public class SQL_DBCommon
    {
        Members#region Members

        SQL 语句#region SQL 语句

        附加分离 SQL#region 附加分离 SQL
        /**//// <summary>
        /// 附加数据库SQL语句
        /// @dbname:附加数据库名
        /// @filename1:附加数据文件名称
        /// @filename2:附加数据日志名称
        /// </summary>
        private string SQL_SP_ATTACH_DB = "EXEC SP_ATTACH_DB @dbname = '{0}', @filename1 = '{1}',@filename2='{2}'";

        /**//// <summary>
        /// 分离数据库SQL语句
        /// {0}:分离数据库名
        /// </summary>
        private string SQL_SP_DETACH_DB = "EXEC SP_DETACH_DB {0}";

        #endregion

        添加删除用户 SQL#region 添加删除用户 SQL
        /**//// <summary>
        /// 添加登录用户
        /// {0}:用户名
        /// {1}:密码
        /// {2}:默认数据库
        /// </summary>
        private string SQL_SP_AddLOGIN = "EXEC SP_AddLOGIN '{0}', '{1}', '{2}'";

        /**//// <summary>
        /// 删除登录用户
        /// {0}:用户名
        /// </summary>
        private string SQL_SP_DROPLOGIN = "EXEC SP_DROPLOGIN '{0}'"; 

        /**//// <summary>
        /// 使其成为当前数据库的合法用户（必须在当前库中操作）
        /// {0}:用户名
        /// </summary>
        private string SQL_SP_GRANTDBACCESS = "EXEC SP_GRANTDBACCESS  N'{0}'";

        /**//// <summary>
        /// 授予对自己数据库的所有权限（必须在当前库中操作）
        /// {0}:用户名
        /// </summary>
        private string SQL_SP_ADDROLEMEMBER = "EXEC  SP_ADDROLEMEMBER  N'db_owner', N'{0}'";

        /**//// <summary>
        /// 移除对数据库的访问权限（必须在当前库中操作）
        /// {0}:用户名
        /// </summary>
        private string SQL_SP_REVOKEDBACCESS = "EXEC SP_REVOKEDBACCESS  N'{0}'";

        /**//// <summary>
        /// 当前库是否存在该用户（必须在当前库中操作）
        /// {0}:用户名
        /// </summary>
        private string SQL_USEREXIT = "SELECT count(*) FROM dbo.sysusers where  [name] = '{0}'";

        /**//// <summary>
        /// 是否存在该用户（必须在当前库中操作）
        /// </summary>
        private string SQL_MASTER_USEREXIT = "SELECT count(*) FROM dbo.sysxlogins where  [name] = '{0}'";

        #endregion

        #endregion

        数据库成员#region 数据库成员
        /**//// <summary>
        /// 数据库地址
        /// </summary>
        private string m_DB_DataSource;

        /**//// <summary>
        /// 数据库地址
        /// </summary>
        public string DB_DataSource
        {
            get { return m_DB_DataSource; }
            set { m_DB_DataSource = value; }
        }

        /**//// <summary>
        /// 用户名
        /// </summary>
        private string m_UserID;

        /**//// <summary>
        /// 用户名
        /// </summary>
        public string UserID
        {
            get { return m_UserID; }
            set { m_UserID = value; }
        }

        /**//// <summary>
        /// 密码
        /// </summary>
        private string m_Pwd;

        /**//// <summary>
        /// 密码
        /// </summary>
        public string Pwd
        {
            get { return m_Pwd; }
            set { m_Pwd = value; }
        }

        #endregion

        #endregion

        Construtors#region Construtors
        public SQL_DBCommon()
        {

        }
        public SQL_DBCommon(string dbsource,string userid,string pwd)
        {
            this.m_DB_DataSource = dbsource;
            this.m_UserID = userid;
            this.m_Pwd = pwd;
        }
        #endregion

        Methods#region Methods

        Private Methods#region Private Methods

        附加分离数据库#region 附加分离数据库
        /**//// <summary>
        /// 附加数据库
        /// </summary>
        /// <returns></returns>
        private bool m_AttachDB(string DBName,string MDBFile,string LOGFile)
        {
            try
            {
                string sql = string.Empty;

                if (File.Exists(MDBFile))
                {
                    sql = string.Format(SQL_SP_ATTACH_DB, DBName, MDBFile, LOGFile);
                    SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User ID={1};PWD={2}", m_DB_DataSource, m_UserID, m_Pwd));
                    using (conn)
                    {
                        lock (conn)
                        {
                            conn.Open();
                            SqlCommand cmd = new SqlCommand(sql, conn);
                            cmd.ExecuteNonQuery();
                            conn.Close();
                        }
                    }
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        /**//// <summary>
        /// 分离数据库
        /// </summary>
        /// <param name="DBName"></param>
        /// <returns></returns>
        private bool m_DetachDB(string DBName)
        {
            try
            {
                string sql = string.Empty;


                sql = string.Format(SQL_SP_DETACH_DB, DBName);
                SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User ID={1};PWD={2}", m_DB_DataSource, m_UserID, m_Pwd));
                using (conn)
                {
                    lock (conn)
                    {
                        conn.Open();
                        SqlCommand cmd = new SqlCommand(sql, conn);
                        cmd.ExecuteNonQuery();
                        conn.Close();
                    }
                }

                return true;
            }
            catch
            {
                return false;
            }
        }

        #endregion

        登陆用户判断#region 登陆用户判断
        /**//// <summary>
        /// 登陆用户是否存在
        /// -1:异常失败
        /// 0:不存在
        /// 1:存在
        /// </summary>
        /// <param name="LoginName"></param>
        /// <returns></returns>
        private int m_Master_UserExit(string LoginName)
        {
            try
            {

                SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User ID={1};PWD={2}", m_DB_DataSource, m_UserID, m_Pwd));
                using (conn)
                {
                    string execSQL = string.Format(SQL_MASTER_USEREXIT, LoginName);

                    conn.Open();
                    SqlCommand cmd = new SqlCommand(execSQL, conn);
                    object obj = cmd.ExecuteScalar();
                    conn.Close();

                    return (int)obj;
                }
            }
            catch
            {
                return -1;
            }
        }

        /**//// <summary>
        /// 数据用户是否存在
        /// -1:异常失败
        /// 0:不存在
        /// 1:存在
        /// </summary>
        /// <param name="LoginName">用户名</param>
        /// <param name="DB">数据库</param>
        /// <returns></returns>
        private int m_DB_UserExit(string LoginName,string DB)
        {
            try
            {
                SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog={1};User ID={2};PWD={3}", m_DB_DataSource, DB, m_UserID, m_Pwd));
                using (conn)
                {
                    string execSQL = string.Format(SQL_MASTER_USEREXIT, LoginName);
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(execSQL, conn);
                    object obj = cmd.ExecuteScalar();
                    conn.Close();

                    return (int)obj;
                }
            }
            catch
            {
                return -1;
            }
        }

        #endregion

        添加删除登录用户#region 添加删除登录用户
        /**//// <summary>
        /// 添加登陆用户
        /// </summary>
        /// <param name="LoginName">新增登陆用户名</param>
        /// <param name="pwd">新增登陆用户密码</param>
        /// <param name="DBName">数据库名</param>
        /// <returns></returns>
        private bool m_Add_Login(string LoginName, string pwd, string DBName)
        {
            try
            {
                SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User ID={1};PWD={2}", m_DB_DataSource, m_UserID, m_Pwd));
                using (conn)
                {
                    string execSQL = string.Format(SQL_SP_AddLOGIN, LoginName, pwd, DBName);

                    conn.Open();
                    SqlCommand cmd = new SqlCommand(execSQL, conn);
                    cmd.ExecuteNonQuery(); ;
                    conn.Close();
                    return true;
                }
            }
            catch
            {
                return false;
            }

        }

        /**//// <summary>
        /// 删除登陆用户
        /// </summary>
        /// <param name="LoginName">登陆用户名</param>
        /// <returns></returns>
        private bool m_Del_Login(string LoginName)
        {
            try
            {
                SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User ID={1};PWD={2}", m_DB_DataSource, m_UserID, m_Pwd));
                using (conn)
                {
                    string execSQL = string.Format(SQL_SP_DROPLOGIN, LoginName);

                    conn.Open();
                    SqlCommand cmd = new SqlCommand(execSQL, conn);
                    cmd.ExecuteNonQuery(); ;
                    conn.Close();
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        #endregion

        分配移除登陆用户权限#region 分配移除登陆用户权限

        /**//// <summary>
        /// 分配登录用户权限
        /// </summary>
        /// <param name="LoginName">登陆用户名</param>
        /// <param name="DBName">数据库</param>
        /// <returns></returns>
        private bool m_GrantDBAccess(string LoginName,string DBName)
        {
            try
            {
                SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog={1};User ID={2};PWD={3}", m_DB_DataSource, DBName, m_UserID, m_Pwd));
                using (conn)
                {
                    string execSQL1 = string.Format(SQL_SP_GRANTDBACCESS, LoginName);
                    string execSQL2 = string.Format(SQL_SP_ADDROLEMEMBER, LoginName);
                    conn.Open();

                    SqlCommand cmd1 = new SqlCommand(execSQL1, conn);
                    cmd1.ExecuteNonQuery(); 

                    SqlCommand cmd2 = new SqlCommand(execSQL2, conn);
                    cmd2.ExecuteNonQuery(); 

                    conn.Close();
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        /**//// <summary>
        /// 移除对数据库的访问权限
        /// </summary>
        /// <param name="LoginName">登陆用户名</param>
        /// <param name="DBName">数据库</param>
        /// <returns></returns>
        private bool m_RevokeDBAccess(string LoginName, string DBName)
        {
            try
            {
                SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog={1};User ID={2};PWD={3}", m_DB_DataSource, DBName, m_UserID, m_Pwd));
                using (conn)
                {
                    string execSQL = string.Format(SQL_SP_REVOKEDBACCESS, LoginName);
       
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(execSQL, conn);
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        #endregion

        #endregion

        Public Methods#region Public Methods

        /**//// <summary>
        /// 附加数据库
        /// </summary>
        /// <param name="DBName">string:附加的数据库名称</param>
        /// <param name="MDBFile">string:附加数据库全址</param>
        /// <param name="LOGFile">string:附加数据库日志文件全址</param>
        /// <returns></returns>
        public bool AttachDB(string DBName, string MDBFile, string LOGFile)
        {
            return m_AttachDB(DBName, MDBFile, LOGFile);
        }

        /**//// <summary>
        /// 分离数据库
        /// </summary>
        /// <param name="DBName">分离的数据库名称</param>
        /// <returns></returns>
        public bool DetachDB(string DBName)
        {
            return m_DetachDB(DBName);
        }

        /**//// <summary>
        /// 给附加的数据库添加新的登陆用户
        /// </summary>
        /// <param name="DBName">string:附加的数据库名称</param>
        /// <param name="LoginName">string:用户名称</param>
        /// <param name="Pwd">string:密码</param>
        /// <returns></returns>
        public bool AddUser(string DBName, string LoginName, string Pwd)
        {
            //取用户是否创建
            int masterUserFlag = m_Master_UserExit(LoginName);
          
            if (masterUserFlag == 0)//没有该用户
            {
                //新增登录用户
                m_Add_Login(LoginName, Pwd, DBName);
                //分配用户数据库及权限
                m_GrantDBAccess(LoginName, DBName);

                return true;
            }
            else if (masterUserFlag == -1)//数据库异常
            {
                return false;
            }
            else
            {
                int userFlag = m_DB_UserExit(LoginName, DBName);
                if (userFlag == 1)//如果存在移除法重新分配
                {
                    //移除对数据库的访问权限
                    m_RevokeDBAccess(LoginName, DBName);

                }
                else 
                {
                    if (userFlag == -1)
                        return false;
                }

                //分配用户数据库及权限
                m_GrantDBAccess(LoginName, DBName);

                return true;
            }
        }
        #endregion

        #endregion
    }