repos_util/SQLHelper/SQLHelper/ConnectionParameters.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Newtonsoft.Json;

namespace SQLHelper
{
    /// <summary>
    /// 连接参数
    /// </summary>
    [Serializable]
    public sealed class ConnectionParameters
    {
        #region 连接串
        // 无端口号;
        private const string _msdbconnect = "Data Source={0};Initial Catalog={1};User ID={2};Password={3}";
        private const string _msdbconnect_master = "Data Source={0};Initial Catalog=master;User ID={1};Password={2}";
        // 有端口号;
        private const string _msdbconnect_port = "Data Source={0},{1};Initial Catalog={2};User ID={3};Password={4}";
        private const string _msdbconnect_master_port = "Data Source={0},{1};Initial Catalog=master;User ID={2};Password={3}";
        // windows信任连接;
        private const string _msdbconnect_trusted_master = "server={0};database=master;Trusted_Connection=SSPI";
        #endregion

        #region 私有变量;
        /// <summary>
        /// 数据库连接串;
        /// </summary>
        private string connectionString = "";
        private string masterConnectionString = "";
        private string trustedConnectionString = "";

        private string databaseServer = "127.0.0.1";
        private string databaseAccount = "sa";
        private string databasePassword = "";
        private string databaseName = "";
        private uint databasePort = 0;
        private uint maxPoolSize = 100;
        private uint minPoolSize = 5;
        private uint connectTimeout = 3000;
        #endregion

        #region 公共属性;
        /// <summary>
        /// 数据库服务器;
        /// </summary>
        public string DatabaseServer
        {
            get { return databaseServer; }
            set
            {
                databaseServer = value;
                if (IsSetParameters) InitConnectParameters();
            }
        }
        /// <summary>
        /// 数据库账号;
        /// </summary>
        public string DatabaseAccount
        {
            get { return databaseAccount; }
            set
            {
                databaseAccount = value;
                if (IsSetParameters) InitConnectParameters();
            }
        }
        /// <summary>
        /// 数据库密码;
        /// </summary>
        public string DatabasePassword
        {
            get { return databasePassword; }
            set
            {
                databasePassword = value;
                if (IsSetParameters) InitConnectParameters();
            }
        }
        /// <summary>
        /// 数据库名称;
        /// </summary>
        public string DatabaseName
        {
            get { return databaseName; }
            set
            {
                databaseName = value;
                if (IsSetParameters) InitConnectParameters();
            }
        }
        /// <summary>
        /// 数据库端口号;
        /// </summary>
        public uint DatabasePort
        {
            get { return databasePort; }
            set
            {
                databasePort = value;
                if (IsSetParameters) InitConnectParameters();
            }
        }

        /// <summary>
        /// 池最大值;
        /// </summary>
        public uint MaxPoolSize { get; set; } = 100;

        /// <summary>
        /// 池最小值;
        /// </summary>
        public uint MinPoolSize { get; set; } = 5;

        /// <summary>
        /// 连接超时值;
        /// </summary>
        public uint ConnectTimeout { get; set; } = 3000;

        [JsonIgnore]
        /// <summary>
        /// 是否设置了数据库连接参数;
        /// </summary>
        public bool IsSetParameters { get; private set; } = false;

        [JsonIgnore]
        /// <summary>
        /// 数据库连接串;
        /// </summary>
        public string ConnectionString
        {
            get
            {
                if (IsSetParameters)
                    return connectionString;
                else
                    return "";
            }
        }

        [JsonIgnore]
        public string MasterConnectionString
        {
            get
            {
                if (IsSetParameters)
                    return masterConnectionString;
                else
                    return "";
            }
        }

        [JsonIgnore]
        public string TrustedConnectionString
        {
            get
            {
                if (IsSetParameters)
                    return trustedConnectionString;
                else
                    return "";
            }
        }
        #endregion

        public ConnectionParameters() { }

        public ConnectionParameters(string databaseServer, string databaseName, string databaseAccount, string databasePassword, uint databasePortNumber)
        {
            try
            {
                // 标记连接参数已设置;
                IsSetParameters = true;

                // 生成默认的连接串;
                this.connectionString = DatabasePort== 0
                    ? string.Format(_msdbconnect, DatabaseServer, DatabaseName, DatabaseAccount, DatabasePassword)
                    : string.Format(_msdbconnect_port, DatabaseServer, DatabasePort, DatabaseName, DatabaseAccount, DatabasePassword);

                this.masterConnectionString = DatabasePort == 0
                    ? string.Format(_msdbconnect_master, DatabaseServer, DatabaseAccount, DatabasePassword)
                    : string.Format(_msdbconnect_master_port, DatabaseServer, DatabasePort, DatabaseAccount, DatabasePassword);

                this.trustedConnectionString = string.Format(_msdbconnect_trusted_master, DatabaseServer);
            }
            catch
            {
                throw;
            }
        }

        public void InitConnectParameters()
        {
            try
            {
                // 标记连接参数已设置;
                IsSetParameters = true;

                // 生成默认的连接串;
                this.connectionString = DatabasePort == 0
                    ? string.Format(_msdbconnect, DatabaseServer, DatabaseName, DatabaseAccount, DatabasePassword)
                    : string.Format(_msdbconnect_port, DatabaseServer, DatabasePort, DatabaseName, DatabaseAccount, DatabasePassword);

                this.masterConnectionString = DatabasePort == 0
                    ? string.Format(_msdbconnect_master, DatabaseServer, DatabaseAccount, DatabasePassword)
                    : string.Format(_msdbconnect_master_port, DatabaseServer, DatabasePort, DatabaseAccount, DatabasePassword);

                this.trustedConnectionString = string.Format(_msdbconnect_trusted_master, DatabaseServer);
            }
            catch
            {
                throw;
            }
        }

        public void InitConnectParameters(
           string databaseServer,
           string databaseName,
           string databaseAccount,
           string databasePassword,
           uint databasePortNumber)
        {
            DatabaseServer = databaseServer;
            DatabaseName = databaseName;
            DatabaseAccount = databaseAccount;
            DatabasePassword = databasePassword;
            DatabasePort = databasePortNumber;

            // 标记连接参数已设置;
            IsSetParameters = true;

            try
            {
                // 生成默认的连接串;
                this.connectionString = DatabasePort == 0
                    ? string.Format(_msdbconnect, DatabaseServer, DatabaseName, DatabaseAccount, DatabasePassword)
                    : string.Format(_msdbconnect_port, DatabaseServer, DatabasePort, DatabaseName, DatabaseAccount, DatabasePassword);

                this.masterConnectionString = DatabasePort == 0
                    ? string.Format(_msdbconnect_master, DatabaseServer, DatabaseAccount, DatabasePassword)
                    : string.Format(_msdbconnect_master_port, DatabaseServer, DatabasePort, DatabaseAccount, DatabasePassword);

                this.trustedConnectionString = string.Format(_msdbconnect_trusted_master, DatabaseServer);
            }
            catch
            {
                throw;
            }
        }

        [Obsolete("this function unfinished")]
        /// <summary>
        /// 设置连接参数;(未完成)
        /// </summary>
        /// <param name="serverIp"></param>
        /// <param name="database"></param>
        /// <param name="account"></param>
        /// <param name="password"></param>
        /// <param name="port"></param>
        public void InitConnectParameters(
            string databaseServer,
            string databaseName,
            string databaseAccount,
            string databasePassword,
            uint databasePortNumber,
            uint maxPoolSize = 100,
            uint minPoolSize = 5,
            uint connectTimeout = 3000)
        {
            DatabaseServer = databaseServer;
            DatabaseName = databaseName;
            DatabaseAccount = databaseAccount;
            DatabasePassword = databasePassword;
            DatabasePort = databasePortNumber;
            MaxPoolSize = maxPoolSize;
            MinPoolSize = minPoolSize;
            ConnectTimeout = connectTimeout;

            // 标记连接参数已设置;
            IsSetParameters = true;

            try
            {
                // 生成默认的连接串;
                this.connectionString = DatabasePort == 0
                    ? string.Format(_msdbconnect, DatabaseServer, DatabaseName, DatabaseAccount, DatabasePassword)
                    : string.Format(_msdbconnect_port, DatabaseServer, DatabasePort, DatabaseName, DatabaseAccount, DatabasePassword);

                this.masterConnectionString = DatabasePort == 0
                    ? string.Format(_msdbconnect_master, DatabaseServer, DatabaseAccount, DatabasePassword)
                    : string.Format(_msdbconnect_master_port, DatabaseServer, DatabasePort, DatabaseAccount, DatabasePassword);

                this.trustedConnectionString = string.Format(_msdbconnect_trusted_master, DatabaseServer);
            }
            catch
            {
                throw;
            }
        }

        #region 特殊的SQL执行函数
        public bool CreateDatabaseUser(string userName, string password)
        {
            try
            {
                StringBuilder sqlScript = new StringBuilder();
                sqlScript.Append("IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'" + userName + "')\r\n");
                sqlScript.Append("DROP LOGIN [" + userName + "]\r\n");
                sqlScript.Append("CREATE LOGIN [" + userName + "] WITH PASSWORD=N'" + password + "', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON\r\n");
                sqlScript.Append("EXEC master..sp_addsrvrolemember @loginame = N'" + userName + "', @rolename = N'bulkadmin'\r\n");
                sqlScript.Append("EXEC master..sp_addsrvrolemember @loginame = N'" + userName + "', @rolename = N'dbcreator'\r\n");
                sqlScript.Append("EXEC master..sp_addsrvrolemember @loginame = N'" + userName + "', @rolename = N'diskadmin'\r\n");
                sqlScript.Append("EXEC master..sp_addsrvrolemember @loginame = N'" + userName + "', @rolename = N'processadmin'\r\n");
                sqlScript.Append("EXEC master..sp_addsrvrolemember @loginame = N'" + userName + "', @rolename = N'securityadmin'\r\n");
                sqlScript.Append("EXEC master..sp_addsrvrolemember @loginame = N'" + userName + "', @rolename = N'serveradmin'\r\n");
                sqlScript.Append("EXEC master..sp_addsrvrolemember @loginame = N'" + userName + "', @rolename = N'setupadmin'\r\n");
                sqlScript.Append("EXEC master..sp_addsrvrolemember @loginame = N'" + userName + "', @rolename = N'sysadmin'\r\n");
                SqlHelper.ExecuteNonQuery(this.MasterConnectionString, CommandType.Text, sqlScript.ToString());
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                return false;
            }

            return true;
        }

        public bool DeleteDatabaseUser(string userName)
        {
            try
            {
                System.Text.StringBuilder sqlScript = new System.Text.StringBuilder();
                sqlScript.Append("IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'" + userName + "')\r\n");
                sqlScript.Append("ALTER LOGIN [" + userName + "] DISABLE\r\n");
                sqlScript.Append("IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'" + userName + "')\r\n");
                sqlScript.Append("DROP LOGIN [" + userName + "]\r\n");
                SqlHelper.ExecuteNonQuery(this.MasterConnectionString, CommandType.Text, sqlScript.ToString());
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                return false;
            }

            return true;
        }

        public bool ForcedDisconnect(string databaseName)
        {
            System.Text.StringBuilder RestoreSql = new System.Text.StringBuilder();
            RestoreSql.Append("/* 结束所有对当前数据库的连接 */\r\n");
            RestoreSql.Append("if exists(select 1 from sys.sysprocesses where dbid=db_id(@databaseName)) begin\r\n");
            RestoreSql.Append("declare #cs_spid cursor -- 声明游标\r\n");
            RestoreSql.Append("for\r\n");
            RestoreSql.Append("select #cs_spid=convert(varchar,spid) from sys.sysprocesses where dbid=db_id(@databaseName)\r\n");
            RestoreSql.Append("open #cs_spid\r\n");
            RestoreSql.Append("declare @spid varchar(20)\r\n");
            RestoreSql.Append("fetch next from #cs_spid into @spid -- 赋值并前进到下一条\r\n");
            RestoreSql.Append("while(@@fetch_status=0) begin -- 在fetch失败前执行\r\n");
            RestoreSql.Append("exec ('kill '+@spid) -- 结束对操作库的连接(exec执行SQL语句1)\r\n");
            RestoreSql.Append("fetch next from #cs_spid into @spid\r\n");
            RestoreSql.Append("end\r\n");
            RestoreSql.Append("close #cs_spid\r\n");
            RestoreSql.Append("deallocate #cs_spid -- 释放游标\r\n");
            RestoreSql.Append("end");
            List<System.Data.SqlClient.SqlParameter> parameterlist = new List<System.Data.SqlClient.SqlParameter>();

            parameterlist.Add(new System.Data.SqlClient.SqlParameter("@databaseName", databaseName));

            System.Data.SqlClient.SqlParameter[] parameters = parameterlist.ToArray();
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            try
            {
                SqlHelper.ExecuteNonQuery(RestoreSql.ToString(), CommandType.Text, this.masterConnectionString, parameters);
            }
            catch
            {
                return false;
            }

            return true;
        }

        public bool IsDatabaseExists(string databaseName, out string dbFilePath)
        {
            List<System.Data.SqlClient.SqlParameter> parameterlist = new List<System.Data.SqlClient.SqlParameter>();
            parameterlist.Add(new System.Data.SqlClient.SqlParameter("@databaseName", databaseName));
            string sql = "select dbid,name,filename From sysdatabases WHERE NAME=@databaseName";
            System.Data.SqlClient.SqlParameter[] parameters = parameterlist.ToArray();
            // System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            dbFilePath = "";
            try
            {
                System.Data.DataSet ds = SqlHelper.ExecuteDataset(this.MasterConnectionString, CommandType.Text, sql, parameters);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    dbFilePath = ds.Tables[0].Rows[0]["filename"].ToString();
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }
        }

        public void DatabaseLogRecoveryMode(string databaseName, string mode = "SIMPLE")
        {
            string databasePath;
            if (IsDatabaseExists(databaseName, out databasePath))
            {
                List<System.Data.SqlClient.SqlParameter> parameterlist = new List<System.Data.SqlClient.SqlParameter>();
                parameterlist.Add(new System.Data.SqlClient.SqlParameter("@databaseName", databaseName));
                parameterlist.Add(new System.Data.SqlClient.SqlParameter("@SIMPLE", databaseName));
                string sql = "ALTER DATABASE @databaseName SET RECOVERY @SIMPLE ;";
                System.Data.SqlClient.SqlParameter[] parameters = parameterlist.ToArray();
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                try
                {
                    SqlHelper.ExecuteNonQuery(this.MasterConnectionString, CommandType.Text, sql.ToString(), parameters);
                }
                catch
                {
                }
            }
        }

        /// <summary>
        /// 分离数据库;
        /// 注:如果有外部连接了数据库, 是没办法分离的;
        /// </summary>
        /// <param name="databaseName"></param>
        /// <returns></returns>
        public bool DetachDatabase(string databaseName)
        {
            bool bl = false;
            string databasePath;
            if (IsDatabaseExists(databaseName, out databasePath))
            {
                List<System.Data.SqlClient.SqlParameter> parameterlist = new List<System.Data.SqlClient.SqlParameter>();
                parameterlist.Add(new System.Data.SqlClient.SqlParameter("@databaseName", databaseName));
                string sql = "EXEC master.dbo.sp_detach_db @dbname = @databaseName";
                System.Data.SqlClient.SqlParameter[] parameters = parameterlist.ToArray();
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                try
                {
                    ForcedDisconnect(databaseName);
                    SqlHelper.ExecuteNonQuery(this.MasterConnectionString, CommandType.Text, sql.ToString(), parameters);
                    if (!IsDatabaseExists(databaseName, out databasePath))
                    {
                        bl = true;
                    }
                    else
                    {
                        bl = false;
                    }
                }
                catch (Exception e)
                {
                    bl = false;
                }
            }
            return bl;
        }

        /// <summary>
        /// 附加数据库;
        /// </summary>
        /// <param name="databaseFilePath"></param>
        /// <param name="databaseLogsFilePath"></param>
        /// <param name="databaseName"></param>
        /// <returns></returns>
        public bool AttachDatabase(string databaseName, string databaseFilePath, string databaseLogsFilePath)
        {
            bool bl = false;
            string databasePath;
            if (!IsDatabaseExists(databaseName, out databasePath))
            {
                List<System.Data.SqlClient.SqlParameter> parameterlist = new List<System.Data.SqlClient.SqlParameter>();
                string sql = "CREATE DATABASE [" + databaseName + "] ON ( FILENAME ='" + databaseFilePath + "'  ),( FILENAME ='" + databaseLogsFilePath + "' ) FOR ATTACH";
                System.Data.SqlClient.SqlParameter[] parameters = parameterlist.ToArray();
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                try
                {
                    SqlHelper.ExecuteNonQuery(this.MasterConnectionString, CommandType.Text, sql.ToString(), parameters);
                    if (IsDatabaseExists(databaseName, out databasePath))
                    {
                        DatabaseLogRecoveryMode(databaseName);
                        bl = true;
                    }
                    else
                    {
                        bl = false;
                    }
                }
                catch (Exception e)
                {
                    //e.Message;
                    bl = false;
                }
            }
            return bl;
        }

        public bool EnableDatabaseUser(string userName, bool disable)
        {
            try
            {
                SqlHelper.ExecuteNonQuery(this.TrustedConnectionString, CommandType.Text, string.Format("alter login [{0}] {1}", userName, disable ? "DISABLE" : "ENABLE"));
            }
            catch
            {
                return false;
            }
            return true;
        }
        #endregion
    }
}