using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Common;
using System.Collections.Generic;

namespace LYFZ.Helper
{
    /// <summary>
    /// 数据访问抽象基础类
    /// </summary>
    public abstract class SQLHelper
    {
        public SQLHelper()
        {

        }

        #region 公用方法



        /// <summary>
        /// 检查数据库连接是否正常
        /// </summary>
        /// <param name="connString"></param>
        /// <returns></returns>
        public static bool ExistsSqlConn(string connString)
        {
            bool bl = true;
           
                using (SqlConnection connection = new SqlConnection(connString))
                {
                    try
                    {

                        if (connection.State != ConnectionState.Open)
                        {
                            connection.Open();
                            connection.Close();
                        }
                        else
                        {
                            connection.Close();
                        }


                    }
                    catch
                    {

                        bl = false;

                    }
                }
            
            return bl;
        }


        /// <summary>
        /// 判断是否存在某表的某个字段
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="columnName">列名称</param>
        /// <returns>是否存在</returns>
        public static bool ColumnExists(string tableName, string columnName, string iGetConn)
        {
            string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";
            object res = GetSingle(sql,iGetConn);
            if (res == null)
            {
                return false;
            }
            return Convert.ToInt32(res) > 0;
        }

        public static int GetMaxID(string FieldName, string TableName, string iGetConn)
        {
            string strsql = "select max(" + FieldName + ")+1 from " + TableName;
            object obj = GetSingle(strsql,iGetConn);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }

        public static bool Exists(string strSql, string iGetConn)
        {
            object obj = GetSingle(strSql,iGetConn);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        /// <summary>
        /// 表是否存在
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public static bool TabExists(string TableName, string iGetConn)
        {
            string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
            //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
            object obj = GetSingle(strsql,iGetConn);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        /// <summary>
        /// 视图是否存在
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public static bool ViewsExists(string ViewsName, string iGetConn)
        {
            try
            {
                string strsql = "SELECT count([object_id]) as objCount FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[" + ViewsName + "]')";
                //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
                object obj = GetSingle(strsql,iGetConn);
                int cmdresult;
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    cmdresult = 0;
                }
                else
                {
                    cmdresult = int.Parse(obj.ToString());
                }
                if (cmdresult == 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch { return false; }
        }
        public static bool Exists(string strSql, string iGetConn, params SqlParameter[] cmdParms)
        {
            object obj = GetSingle(strSql,iGetConn, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #endregion

        #region  执行简单SQL语句

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, string iGetConn)
        {
           
                using (SqlConnection connection = new SqlConnection(iGetConn))
                {
                    using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                    {
                        try
                        {
                            connection.Open();
                            int rows = cmd.ExecuteNonQuery();
                            return rows;
                        }
                        catch (System.Data.SqlClient.SqlException e)
                        {
                            connection.Close();
                            throw e;
                        }
                    }
                }
            
        }

        public static int ExecuteSqlByTime(string SQLString, int Times, string iGetConn)
        {
           
                using (SqlConnection connection = new SqlConnection(iGetConn))
                {
                    using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                    {
                        try
                        {
                            connection.Open();
                            cmd.CommandTimeout = Times;
                            int rows = cmd.ExecuteNonQuery();
                            return rows;
                        }
                        catch (System.Data.SqlClient.SqlException e)
                        {
                            connection.Close();
                            throw e;
                        }
                    }
                }
            
        }

    
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>		
        public static int ExecuteSqlTran(List<String> SQLStringList, string iGetConn)
        {
           
                using (SqlConnection conn = new SqlConnection(iGetConn))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    SqlTransaction tx = conn.BeginTransaction();
                    cmd.Transaction = tx;
                    try
                    {
                        int count = 0;
                        for (int n = 0; n < SQLStringList.Count; n++)
                        {
                            string strsql = SQLStringList[n];
                            if (strsql.Trim().Length > 1)
                            {
                                cmd.CommandText = strsql;
                                count += cmd.ExecuteNonQuery();
                            }
                        }
                        tx.Commit();
                        return count;
                    }
                    catch
                    {
                        tx.Rollback();
                        return 0;
                    }
                }
            
        }
        /// <summary>
        /// 执行带一个存储过程参数的的SQL语句。
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, string content, string iGetConn)
        {
            System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
            myParameter.Value = content;

            return ExecuteSql(SQLString,iGetConn, new System.Data.SqlClient.SqlParameter[] { myParameter });
               
            
        }

        /// <summary>
        /// 执行带一个存储过程参数的的SQL语句。
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
        /// <returns>影响的记录数</returns>
        public static object ExecuteSqlGet(string SQLString, string content, string iGetConn)
        {
            System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
            myParameter.Value = content;

            return GetSingle(SQLString,iGetConn, new System.Data.SqlClient.SqlParameter[] { myParameter });
           
        }
        /// <summary>
        /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSqlInsertImg(string strSQL, byte[] fs, string iGetConn)
        {
            System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
            myParameter.Value = fs;
            return ExecuteSql(strSQL,iGetConn, new System.Data.SqlClient.SqlParameter[] { myParameter });
           
        }

        
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString,string connString)
        {
            
                using (SqlConnection connection = new SqlConnection(connString))
                {
                    using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                    {
                        try
                        {
                            connection.Open();
                            object obj = cmd.ExecuteScalar();
                            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                            {
                                return null;
                            }
                            else
                            {
                                return obj;
                            }
                        }
                        catch (System.Data.SqlClient.SqlException e)
                        {
                            connection.Close();
                            throw e;
                        }
                    }
                }
            
        }
        public static object GetSingle(string SQLString, int Times, string iGetConn)
        {
           
                using (SqlConnection connection = new SqlConnection(iGetConn))
                {
                    using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                    {
                        try
                        {
                            connection.Open();
                            cmd.CommandTimeout = Times;
                            object obj = cmd.ExecuteScalar();
                            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                            {
                                return null;
                            }
                            else
                            {
                                return obj;
                            }
                        }
                        catch (System.Data.SqlClient.SqlException e)
                        {
                            connection.Close();
                            throw e;
                        }
                    }
                }
            
        }
        ///// <summary>
        ///// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        ///// </summary>
        ///// <param name="strSQL">查询语句</param>
        ///// <returns>SqlDataReader</returns>
        //public static SqlDataReader ExecuteReader(string strSQL)
        //{
        //    SqlConnection connection = new SqlConnection(GetConnectionString.iGetConn);
        //    SqlCommand cmd = new SqlCommand(strSQL, connection);
        //    try
        //    {
        //        connection.Open();
        //        SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        //        return myReader;
        //    }
        //    catch (System.Data.SqlClient.SqlException e)
        //    {
        //        throw e;
        //    }

        //}

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, string iGetConn)
        {
            
                using (SqlConnection connection = new SqlConnection(iGetConn))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        connection.Open();
                        SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                        command.Fill(ds, "ds");
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            
        }
        public static DataSet Query(string SQLString, int Times, string iGetConn)
        {
           
                using (SqlConnection connection = new SqlConnection(iGetConn))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        connection.Open();
                        SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                        command.SelectCommand.CommandTimeout = Times;
                        command.Fill(ds, "ds");
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            
        }



        #endregion

        #region 执行带参数的SQL语句

        public static string dataBaseServer="127.0.0.1";
        public static string baseUser="sa";
        public static string basePass="1234";
        public static int port=0;

        /// <summary>
        /// 获取Master数据库连接串
        /// </summary>
        /// <returns></returns>
        public static string GetMasterConnString()
        {
          
            string vDataBaseServer = dataBaseServer;
            if (port != 0)
            {
                vDataBaseServer = vDataBaseServer + "," + port;
            }
            string connString = "Data Source=" + vDataBaseServer + ";Initial Catalog=master;User ID=" + baseUser + ";Password=" + basePass+ "";
            return connString;
        }

        static string _WindowsDataBaseServer = "127.0.0.1";
        /// <summary>
        /// windows 身份验证连接数据库时的服务器地址 如果有端口号 请加上端口号
        /// </summary>
        public static string WindowsDataBaseServer
        {
            get { return SQLHelper._WindowsDataBaseServer; }
            set { SQLHelper._WindowsDataBaseServer = value; }
        }

        /// <summary>
        /// 获取Master数据库的windows 身份验证连接串
        /// </summary>
        /// <returns></returns>
        public static string GetWindowsMasterConnString()
        {
            string vDataBaseServer = WindowsDataBaseServer;
            if (port != 0)
            {
                vDataBaseServer = vDataBaseServer + "," + port;
            }
            return "server=" + vDataBaseServer + ";database=master;Trusted_Connection=SSPI"; 
        }
        /// <summary>
        /// 数据库用户管理
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <param name="isDisable">是否禁用 true 为禁用 false 为启用</param>
        /// <returns></returns>
        public static string DBUserManagement(string userName = "sa", bool isDisable = true)
        {
            string ret = "操作失败";
            try
            {
                if (isDisable)
                {
                    ExecuteSqlWindowsToMaster("ALTER LOGIN [" + userName + "] DISABLE", null);
                    ret = "用户禁用成功";
                }
                else
                {
                    ExecuteSqlWindowsToMaster("ALTER LOGIN [" + userName + "] ENABLE", null);
                    ret = "用户启用成功";
                }
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                ret = "操作失败,原因：" + e.Message;
            }
            return ret;
        }
        /// <summary>
        /// 创建数据库登录用户
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <param name="password">密码</param>
        /// <returns></returns>
        public static string CreateDBUser(string userName, string password)
        {
            string ret = "创建成功";
            try
            {
                System.Text.StringBuilder RestoreSql = new System.Text.StringBuilder();
                RestoreSql.Append("IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'" + userName + "')\r\n");
                RestoreSql.Append("DROP LOGIN [" + userName + "]\r\n");
                RestoreSql.Append("CREATE LOGIN [" + userName + "] WITH PASSWORD=N'" + password + "', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON\r\n");
                RestoreSql.Append("EXEC master..sp_addsrvrolemember @loginame = N'" + userName + "', @rolename = N'bulkadmin'\r\n");
                RestoreSql.Append("EXEC master..sp_addsrvrolemember @loginame = N'" + userName + "', @rolename = N'dbcreator'\r\n");
                RestoreSql.Append("EXEC master..sp_addsrvrolemember @loginame = N'" + userName + "', @rolename = N'diskadmin'\r\n");
                RestoreSql.Append("EXEC master..sp_addsrvrolemember @loginame = N'" + userName + "', @rolename = N'processadmin'\r\n");
                RestoreSql.Append("EXEC master..sp_addsrvrolemember @loginame = N'" + userName + "', @rolename = N'securityadmin'\r\n");
                RestoreSql.Append("EXEC master..sp_addsrvrolemember @loginame = N'" + userName + "', @rolename = N'serveradmin'\r\n");
                RestoreSql.Append("EXEC master..sp_addsrvrolemember @loginame = N'" + userName + "', @rolename = N'setupadmin'\r\n");
                RestoreSql.Append("EXEC master..sp_addsrvrolemember @loginame = N'" + userName + "', @rolename = N'sysadmin'\r\n");
                ExecuteSqlWindowsToMaster(RestoreSql.ToString(),null);
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                ret = "用户创建失败,原因："+e.Message;
            }
            return ret;
        }
        /// <summary>
        /// 删除数据库登录用户
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns></returns>
        public static string DeleteDBUser(string userName)
        {
            string ret = "删除成功";
            try
            {
                System.Text.StringBuilder RestoreSql = new System.Text.StringBuilder();
                RestoreSql.Append("IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'" + userName + "')\r\n");
                RestoreSql.Append("ALTER LOGIN [" + userName + "] DISABLE\r\n");
                RestoreSql.Append("IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'" + userName + "')\r\n");
                RestoreSql.Append("DROP LOGIN [" + userName + "]\r\n");
                ExecuteSqlWindowsToMaster(RestoreSql.ToString(), null);
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                ret = "用户删除失败,原因：" + e.Message;
            }
            return ret;
        }
        /// <summary>
        /// 获取数据库登录用户状态
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns>返回 true 为启用 false 为禁用</returns>
        public static bool GetDBUserStatus(string userName="sa")
        {
            string connString = GetWindowsMasterConnString();
            using (SqlConnection connection = new SqlConnection(connString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, "SELECT is_disabled FROM sys.server_principals WHERE name ='" + userName + "'", null);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return false;
                        }
                        else
                        {
                            return !Convert.ToBoolean(obj);
                        }
                    }
                    catch 
                    {
                        return false;
                    }
                }
            }
        }
        /// <summary>
        /// windows 身份验证连接Master系统数据库，并执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSqlWindowsToMaster(string SQLString, params System.Data.SqlClient.SqlParameter[] cmdParms)
        {

            string connString = GetWindowsMasterConnString();
            return ExecuteSql(SQLString, connString, cmdParms);
          
        }
        /// <summary>
        /// 检查数据库是否存在
        /// </summary>
        /// <param name="databaseName">数据库名</param>
        /// <returns></returns>
        public static bool IsDatabaseExists(string databaseName)
        { 
            string dbFilePath="";
            return IsDatabaseExists(databaseName, out dbFilePath);
        }
        /// <summary>
        /// 检查数据库是否存在
        /// </summary>
        /// <param name="databaseName">数据库名</param>
        /// <param name="dbFilePath">返数据库文件所在完全路径</param>
        /// <returns></returns>
        public static 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
            {
                DataSet ds = LYFZ.Helper.SQLHelper.MasterQuery(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;
            }
        }

        /// <summary>
        /// 强制断开数据库连接
        /// </summary>
        /// <param name="databaseName"></param>
        /// <returns></returns>
        public static bool ForcedDisconnect(string databaseName)
        {
            //LYFZ.Helper.GetConnectionString conn = new Helper.GetConnectionString();
            //conn.GetConnInfo();
            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
            {
                LYFZ.Helper.SQLHelper.ExecuteSqlToMaster(RestoreSql.ToString(), parameters);
                return true;
            }
            catch 
            {
                return false;
            }
        }
        /// <summary>
        /// 分离数据库
        /// </summary>
        /// <param name="databaseName"></param>
        /// <returns></returns>
        public static bool DetachDatabase(string databaseName)
        {
            bool bl = false;
            if (IsDatabaseExists(databaseName))
            {

                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
                {
                    LYFZ.Helper.SQLHelper.ForcedDisconnect(databaseName);
                    LYFZ.Helper.SQLHelper.ExecuteSqlToMaster(sql.ToString(), parameters);
                    if (!IsDatabaseExists(databaseName))
                    {
                        bl = true;
                    }
                    else
                    {
                        bl = false;
                    }
                }
                catch
                {
                    bl = false;
                }
            }
            return bl;
        }
        /// <summary>
        ///创建数据库用户
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        /// <param name="databaseName"></param>
        /// <returns></returns>
        public static bool CreateDataBaseUser(string userName,string password,string databaseName,ref string msg)
        {
            bool bl = false;
            if (IsDatabaseExists(databaseName))
            {
                // HPSocketCS.Extended.SystemFileLogs.WriteLogs("“" + userName + "”“" + password + "”“" + databaseName + "”开始创建数据库用户...");
                System.Text.StringBuilder sql = new System.Text.StringBuilder();
                sql.Append(String.Format("IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'{0}')\r\n", userName));
                sql.Append(String.Format("DROP LOGIN [{0}]\r\n", userName));
                sql.Append(String.Format("CREATE LOGIN [{0}] WITH PASSWORD=N'{1}', DEFAULT_DATABASE=[{2}], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON\r\n", userName, password, databaseName));
                List<System.Data.SqlClient.SqlParameter> parameterlist = new List<System.Data.SqlClient.SqlParameter>();
                System.Data.SqlClient.SqlParameter[] parameters = parameterlist.ToArray();
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                try
                {
                    // HPSocketCS.Extended.SystemFileLogs.WriteLogs("创建数据库用户SQL：" + sql);
                    LYFZ.Helper.SQLHelper.ExecuteSqlToMaster(sql.ToString(), parameters);
                    if (IsDataBaseUserExists(userName))
                    {
                        // HPSocketCS.Extended.SystemFileLogs.WriteLogs("创建数据库用户OK：");
                        bl = true;
                    }
                    else
                    {
                        bl = false;
                        // HPSocketCS.Extended.SystemFileLogs.WriteLogs("创建数据库用户false：");
                    }
                }
                catch (Exception ex)
                {
                    msg = ex.Message;
                   // HPSocketCS.Extended.SystemFileLogs.WriteLogs("创建数据库用户出错：" + msg);
                    bl = false;
                }
            }
            else {
                msg = "没有找到名为“" + databaseName + "”的数据库";
              //  HPSocketCS.Extended.SystemFileLogs.WriteLogs("创建数据库用户失败" + msg);
            }
            return bl;
        }
        /// <summary>
        /// 检查数据库用户是否存在
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public static bool IsDataBaseUserExists(string userName)
        {
            List<System.Data.SqlClient.SqlParameter> parameterlist = new List<System.Data.SqlClient.SqlParameter>();

            string sql = String.Format("SELECT * FROM sys.server_principals WHERE name = N'{0}'", userName);
            System.Data.SqlClient.SqlParameter[] parameters = parameterlist.ToArray();

            try
            {
                DataSet ds = LYFZ.Helper.SQLHelper.MasterQuery(sql, parameters);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }

            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 设置数据库用户权限
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="databaseName"></param>
        /// <returns></returns>
        public static bool SetDataBaseUserPermissions(string userName, string databaseName,ref string msg)
        {
            bool bl = false;
            if (IsDatabaseExists(databaseName) && IsDataBaseUserExists(userName))
            {
                System.Text.StringBuilder sql = new System.Text.StringBuilder();
                sql.Append(String.Format("USE [{0}]\r\n", databaseName));
                sql.Append(String.Format("IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'{0}')\r\n", userName));
                sql.Append(String.Format("DROP USER [{0}]\r\n", userName));
                sql.Append(String.Format("CREATE USER [{0}] FOR LOGIN [{0}] WITH DEFAULT_SCHEMA=[dbo]\r\n", userName));
                sql.Append(String.Format("EXEC sp_addrolemember N'db_backupoperator', N'{0}'\r\n", userName));
                sql.Append(String.Format("EXEC sp_addrolemember N'db_datareader', N'{0}'\r\n", userName));
                sql.Append(String.Format("EXEC sp_addrolemember N'db_datawriter', N'{0}'\r\n", userName));
                sql.Append(String.Format("EXEC sp_addrolemember N'db_ddladmin', N'{0}'\r\n", userName));
                sql.Append(String.Format("EXEC sp_addrolemember N'db_owner', N'{0}'\r\n", userName));
                sql.Append(String.Format("EXEC sp_addrolemember N'db_securityadmin', N'{0}'\r\n", userName));

                List<System.Data.SqlClient.SqlParameter> parameterlist = new List<System.Data.SqlClient.SqlParameter>();
                System.Data.SqlClient.SqlParameter[] parameters = parameterlist.ToArray();
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                try
                {
                    LYFZ.Helper.SQLHelper.ExecuteSqlToMaster(sql.ToString(), parameters);
                    bl = true;
                }
                catch(Exception ex)
                {
                    msg = ex.Message;
                    bl = false;
                }
            }
            return bl;
        }

        /// <summary>
        /// 附加数据库
        /// </summary>
        /// <param name="databaseFilePath"></param>
        /// <param name="databaseLogsFilePath"></param>
        /// <param name="databaseName"></param>
        /// <returns></returns>
        public static bool AddDatabase(string databaseFilePath, string databaseLogsFilePath, string databaseName)
        {
            bool bl = false;
            if (!IsDatabaseExists(databaseName))
            {
                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
                {
                    LYFZ.Helper.SQLHelper.ExecuteSqlToMaster(sql.ToString(), parameters);
                    if (IsDatabaseExists(databaseName))
                    {
                        DatabaseLogRecoveryMode(databaseName);
                        bl = true;
                    }
                    else
                    {
                        bl = false;
                    }
                }
                catch
                {
                    bl = false;
                }
            }
            else
            {
                bl = true;
            }
            return bl;
        }
        /// <summary>
        /// 数据库日志恢复模式
        /// </summary>
        /// <param name="databaseName"></param>
        /// <param name="mode"></param>
        public static void DatabaseLogRecoveryMode(string databaseName, string mode = "SIMPLE")
        {
            if (IsDatabaseExists(databaseName))
            {
                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
                {
                    LYFZ.Helper.SQLHelper.ExecuteSqlToMaster(sql.ToString(), parameters);

                }
                catch
                {
                }
            }
        }
      
        /// <summary>
        /// 连接Master系统数据库，并执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSqlToMaster(string SQLString, params System.Data.SqlClient.SqlParameter[] cmdParms)
        {
            string connString =GetWindowsMasterConnString();
            return ExecuteSql(SQLString, connString, cmdParms);
          
        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>		
       /// <param name="msg"></param>
       /// <param name="backgroundWorker"></param>
       /// <param name="times"></param>
       /// <returns></returns>
        public static int ExecuteSqlTran(List<string> SQLStringList, string iGetConn, ref string msg, System.ComponentModel.BackgroundWorker backgroundWorker = null, int times = -1)
        {
            
                //总计数器
                float sumCount = SQLStringList.Count;
                //当前计数器
                int currentCount = 0;
                using (SqlConnection conn = new SqlConnection(iGetConn))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    SqlTransaction tx = conn.BeginTransaction();
                    cmd.Transaction = tx;
                    if (times > 0)
                    {
                        cmd.CommandTimeout = times;
                    }
                    try
                    {
                        int count = 0;
                        if (SQLStringList.Count > 0)
                        {
                            for (int n = 0; n < SQLStringList.Count; n++)
                            {
                                currentCount++;
                                string strsql = SQLStringList[n];
                                if (strsql.Trim().Length > 1)
                                {
                                    cmd.CommandText = strsql;
                                    count += cmd.ExecuteNonQuery();
                                }

                                if (backgroundWorker != null)
                                {
                                    try
                                    {
                                        int currentProgres = Convert.ToInt32(1000 / sumCount * currentCount);
                                        backgroundWorker.ReportProgress(currentProgres);
                                    }
                                    catch { }
                                }
                            }
                            tx.Commit();
                            msg = "成功!";
                            return SQLStringList.Count;
                        }
                        else
                        {
                            msg = "失败，错误原因：脚本内容为空！";
                            return 0;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        msg = "失败，错误原因：" + ex.Message;
                        tx.Rollback();
                        return 0;
                    }
                }
            
        }
        /// <summary>
        /// 执行SQL脚本文件 实现数据库事务。
        /// </summary>
        /// <param name="sqlScript">SQL脚本内容</param>
        /// <param name="msg">返回执行信息</param>
        /// <returns></returns>
        public static bool ExecuteSqlScriptTextTran(string sqlScript, string iGetConn, ref string msg, System.ComponentModel.BackgroundWorker backgroundWorker = null, int times = -1)
        {
            bool bl = false;
            List<string> SQLStringList = new List<string>();
            string[] sqlArray = System.Text.RegularExpressions.Regex.Split(sqlScript, "go\r\n", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            for (int i = 0; i < sqlArray.Length; i++)
            {
                string sql = System.Text.RegularExpressions.Regex.Split(sqlArray[i], "\r\ngo", System.Text.RegularExpressions.RegexOptions.IgnoreCase)[0];
                if (sql.Trim() != "")
                {
                        SQLStringList.Add(sql);
                }

            }
            int ret = ExecuteSqlTran(SQLStringList,iGetConn, ref msg, backgroundWorker, times);
            if (ret > 0) {
                bl = true;
            }
            return bl;
        }
         /// <summary>
        /// 执行SQL脚本文件 实现数据库事务。
        /// </summary>
        /// <param name="sqlScriptFilePath">SQL脚本文件路径</param>
        /// <param name="msg">返回执行信息</param>
        /// <returns></returns>
        public static bool ExecuteSqlScriptFileTran(string sqlScriptFilePath, string iGetConn, ref string msg, System.ComponentModel.BackgroundWorker backgroundWorker = null, int times = -1)
        {
            if (System.IO.File.Exists(sqlScriptFilePath))
            {
                string upgradeDatabaseSql = System.IO.File.ReadAllText(sqlScriptFilePath, System.Text.Encoding.UTF8);
                return ExecuteSqlScriptTextTran(upgradeDatabaseSql,iGetConn, ref msg, backgroundWorker, times);
            }
            else
            {
                msg = "要执行的SQL脚本文件不存在！";
                return false;
            }
        }
        /// <summary>
        /// 执行SQL脚本文件
        /// </summary>
        /// <param name="sqlScript">SQL脚本内容</param>
        /// <param name="msg">返回执行信息</param>
        /// <returns></returns>
        public static bool ExecuteSqlScriptFile(string sqlScript, string iGetConn, ref string msg, System.ComponentModel.BackgroundWorker backgroundWorker = null,int times=-1)
        {
            bool bl = false;
            string[] sqlArray = System.Text.RegularExpressions.Regex.Split(sqlScript, "go\r\n", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            //总计数器
            float sumCount = sqlArray.Length;
            //当前计数器
            int currentCount = 0;
            for (int i = 0; i < sqlArray.Length; i++)
            {
                currentCount++;
                string sql = System.Text.RegularExpressions.Regex.Split(sqlArray[i], "\r\ngo", System.Text.RegularExpressions.RegexOptions.IgnoreCase)[0];
                if (sql.Trim() != "")
                {
                    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                    try
                    {
                        List<System.Data.SqlClient.SqlParameter> parameterlist = new List<System.Data.SqlClient.SqlParameter>();
                        System.Data.SqlClient.SqlParameter[] parameters = parameterlist.ToArray();
                        if (times > 0)
                        {
                            LYFZ.Helper.SQLHelper.ExecuteSql(sql,times,iGetConn, parameters);
                        }
                        else
                        {
                            LYFZ.Helper.SQLHelper.ExecuteSql(sql,iGetConn, parameters);
                        }
                        msg = "成功!";
                        bl = true;
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        msg = "失败，错误原因：" + ex.Message;
                        bl = false;
                        break;
                    }
                }
                if (backgroundWorker != null)
                {
                    try
                    {
                        int currentProgres =Convert.ToInt32(1000 / sumCount * currentCount);
                        backgroundWorker.ReportProgress(currentProgres);
                    }
                    catch { }
                }
            }
            return bl;
        }
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="connString">连接字符串</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString,string connString, params SqlParameter[] cmdParms)
        {
           
                using (SqlConnection connection = new SqlConnection(connString))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        try
                        {
                            PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                            int rows = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                            return rows;
                        }
                        catch (System.Data.SqlClient.SqlException e)
                        {
                            throw e;
                        }
                    }
                }
            
        }
       
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, int times, string iGetConn, params SqlParameter[] cmdParms)
        {
           
                using (SqlConnection connection = new SqlConnection(iGetConn))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        try
                        {
                            PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                            cmd.CommandTimeout = times;
                            int rows = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                            return rows;
                        }
                        catch (System.Data.SqlClient.SqlException e)
                        {
                            throw e;
                        }
                    }
                }
            
        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static void ExecuteSqlTran(Hashtable SQLStringList, string iGetConn)
        {
            //循环
            System.Collections.Generic.List<CommandInfo> cmdList = new List<CommandInfo>();
            foreach (DictionaryEntry myDE in SQLStringList)
            {
                string cmdText = myDE.Key.ToString();
                SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                cmdList.Add(new CommandInfo(cmdText, cmdParms));
            }
             ExecuteSqlTran(cmdList,iGetConn);
            
        }



         /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList, string iGetConn)
        {
            return ExecuteSqlTran(cmdList,-1,iGetConn);
        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList, int times, string iGetConn)
        {
           
                using (SqlConnection conn = new SqlConnection(iGetConn))
                {
                    conn.Open();
                    using (SqlTransaction trans = conn.BeginTransaction())
                    {
                        SqlCommand cmd = new SqlCommand();
                        if (times > 0)
                        {
                            cmd.CommandTimeout = times;
                        }
                        try
                        {
                            int count = 0;
                            //循环
                            foreach (CommandInfo myDE in cmdList)
                            {
                                string cmdText = myDE.CommandText;
                                SqlParameter[] cmdParms = myDE.Parameters;
                                PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

                                if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
                                {
                                    if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
                                    {
                                        trans.Rollback();
                                        return 0;
                                    }

                                    object obj = cmd.ExecuteScalar();
                                    bool isHave = false;
                                    if (obj == null && obj == DBNull.Value)
                                    {
                                        isHave = false;
                                    }
                                    isHave = Convert.ToInt32(obj) > 0;

                                    if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
                                    {
                                        trans.Rollback();
                                        return 0;
                                    }
                                    if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
                                    {
                                        trans.Rollback();
                                        return 0;
                                    }
                                    continue;
                                }
                                int val = cmd.ExecuteNonQuery();
                                count += val;
                                if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
                                {
                                    trans.Rollback();
                                    return 0;
                                }
                                cmd.Parameters.Clear();
                            }
                            trans.Commit();
                            return count;
                        }
                        catch
                        {
                            trans.Rollback();
                            throw;
                        }
                    }
                }
            
        }
        
       

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString, string iGetConn, params SqlParameter[] cmdParms)
        {
           
                using (SqlConnection connection = new SqlConnection(iGetConn))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        try
                        {
                            PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                            object obj = cmd.ExecuteScalar();
                            cmd.Parameters.Clear();
                            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                            {
                                return null;
                            }
                            else
                            {
                                return obj;
                            }
                        }
                        catch (System.Data.SqlClient.SqlException e)
                        {
                            throw e;
                        }
                    }
                }
            
        }

        /// <summary>
        /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string SQLString, string iGetConn, params SqlParameter[] cmdParms)
        {
            SqlConnection connection = new SqlConnection(iGetConn);
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw e;
            }
            //			finally
            //			{
            //				cmd.Dispose();
            //				connection.Close();
            //			}	

        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet MasterQuery(string SQLString, params SqlParameter[] cmdParms)
        {
            return Query(SQLString, GetMasterConnString(), cmdParms);
        }
       
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, int times, string iGetConn, params SqlParameter[] cmdParms)
        {
           
                using (SqlConnection connection = new SqlConnection(iGetConn))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandTimeout = times;
                    PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        try
                        {
                            da.Fill(ds, "ds");
                            cmd.Parameters.Clear();
                        }
                        catch (System.Data.SqlClient.SqlException ex)
                        {
                            throw new Exception(ex.Message);
                        }
                        return ds;
                    }
                }
            
        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <param name="connString">数据连接字符串</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, string connString, params SqlParameter[] cmdParms)
        {
           
                using (SqlConnection connection = new SqlConnection(connString))
                {
                    SqlCommand cmd = new SqlCommand();
                    PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        try
                        {
                            da.Fill(ds, "ds");
                            cmd.Parameters.Clear();
                        }
                        catch (System.Data.SqlClient.SqlException ex)
                        {
                            throw new Exception(ex.Message);
                        }
                        return ds;
                    }
                }
            
        }
        public static void PublicPrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            PrepareCommand(cmd,conn,trans,cmdText,cmdParms);
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {


                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        #endregion

        #region 存储过程操作

       

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, string iGetConn)
        {
          
                using (SqlConnection connection = new SqlConnection(iGetConn))
                {
                    DataSet dataSet = new DataSet();
                    connection.Open();
                    SqlDataAdapter sqlDA = new SqlDataAdapter();
                    sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                    sqlDA.Fill(dataSet, tableName);
                    connection.Close();
                    return dataSet;
                }
            
        }
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times, string iGetConn)
        {
           
            
                using (SqlConnection connection = new SqlConnection(iGetConn))
                {
                    DataSet dataSet = new DataSet();
                    connection.Open();
                    SqlDataAdapter sqlDA = new SqlDataAdapter();
                    sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                    sqlDA.SelectCommand.CommandTimeout = Times;
                    sqlDA.Fill(dataSet, tableName);
                    connection.Close();
                    return dataSet;
                }
            
        }

        /// <summary>
        /// 执行存储过程，返回影响的行数		
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, string iGetConn, out int rowsAffected)
        {
            using (SqlConnection connection = new SqlConnection(iGetConn))
            {
                int result;
                connection.Open();
                SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                result = (int)command.Parameters["ReturnValue"].Value;
                //Connection.Close();
                return result;
            }
        }

        /// <summary>
        /// 创建 SqlCommand 对象实例(用来返回一个整数值)	
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand 对象实例</returns>
        private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }

        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }

            return command;
        }

       
        #endregion

    }

}