SQLHELPER

using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Linq;
using WHC.Framework.Commons;
using System.Xml;


namespace DX.method
{
    public class SQLHelper
    {
        //getSqlConnection a = new getSqlConnection();
        //static string strConn = a.G_Str_ConnectionString;
        //连接字符串
        //ConfigurationManager;
        public static string strConn = GetConnectionString("sqlserver");
        
        //static string strConn = "Data Source=132.147.128.82,23332;database=WinFramework;uid=sa;pwd=manager";


        /// <summary>
        ///  执行SQL语句，返回受影响的行数(用于insert,delete,update等)
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <returns>受影响的行数</returns>
        /// <summary>
        /// 读取程序的config文件Enterprise Library的数据库链接地址
        /// </summary>
        /// <param name="keyName">键名</param>
        /// <returns></returns>
        public static string GetConnectionString(string keyName)
        {
            string strReturn = string.Empty;
            try
            {
                XmlDocument document = new XmlDocument();
                document.Load(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile.Replace(".vshost", ""));


                XmlNodeList nodes = document.GetElementsByTagName("add");
                for (int i = 0; i < nodes.Count; i++)
                {
                    //获得将当前元素的key属性
                    XmlAttribute att = nodes[i].Attributes["name"];
                    //根据元素的第一个属性来判断当前的元素是不是目标元素
                    if (att != null && (att.Value == keyName))
                    {
                        att = nodes[i].Attributes["connectionString"];
                        if (att != null)
                        {
                            strReturn = att.Value;
                            break;
                        }
                    }
                }
            }
            catch
            {; }


            return strReturn;
        }
        public static int ExecuteSql(string strSQL)
        {
            //using: 释放资源
            //SqlConnection: 连接数据源
            //connectionString:用于连接数据源的字符串
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                //SqlCommand对数据源执行SQl命令
                //SqlCommand(CommandText, CommandString)：strSQL需执行的SQL命令字符串,conn指定执行SQL命令需连接的数据源
                using (SqlCommand cmd = new SqlCommand(strSQL, conn))
                {
                    try
                    {
                        conn.Open();//打开数据源连接
                        int rows = cmd.ExecuteNonQuery();//执行SQL语句,返回受影响的行数。如rows>0，说明执行成功
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        conn.Close();//出异常,关闭数据源连接
                        throw e;
                    }
                }
            }
        }


        /// <summary>
        /// 执行带参数的非查询SQL
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="values">参数</param>
        /// <returns>受影响行数</returns>
        public static int ExecuteCommand(string strSQL, params SqlParameter[] values)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                using (SqlCommand cmd = new SqlCommand(strSQL, conn))
                {
                    try
                    {
                        conn.Open();//打开数据源连接
                        cmd.Parameters.AddRange(values);
                        int rows = cmd.ExecuteNonQuery();//执行SQL语句,返回受影响的行数。如rows>0，说明执行成功
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        conn.Close();//出异常,关闭数据源连接
                        throw new Exception(string.Format("执行{0}失败:{1}", strSQL, ex.Message));
                    }
                }
            }
        }


        /// <summary>
        ///  执行查询SQL语句，返回SqlDataReader(只进记录集) ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string strSQL)
        {
            SqlConnection conn = new SqlConnection(strConn);
            {
                using (SqlCommand cmd = new SqlCommand(strSQL, conn))
                {
                    try
                    {
                        conn.Open();//打开数据源连接
                        //CommandBehavior.CloseConnection 能够保证当SqlDataReader对象被关闭时，其依赖的连接也会被自动关闭。
                        SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);


                        return myReader;
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        conn.Close();//出异常,关闭数据源连接
                        throw new Exception(string.Format("执行{0}失败:{1}", strSQL, ex.Message));
                    }
                }
            }


        }


        /// <summary>
        ///  执行带参数的查询SQL语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <param name="values">参数</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string strSQL, params SqlParameter[] values)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                using (SqlCommand cmd = new SqlCommand(strSQL, conn))
                {
                    try
                    {
                        conn.Open();//打开数据源连接
                        //CommandBehavior.CloseConnection 能够保证当SqlDataReader对象被关闭时，其依赖的连接也会被自动关闭。
                        cmd.Parameters.AddRange(values);
                        SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        return myReader;
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        conn.Close();//出异常,关闭数据源连接
                        throw new Exception(string.Format("执行{0}失败:{1}", strSQL, ex.Message));
                    }
                }
            }
        }




        ///DataReader和DataSet的区别
        ///1.获取数据的方式:
        ///    DataReader(在线-一直占用SqlConnection连接,其它操作就可以不可使用，占用内存较小)
        ///    DataSet(离线-将数据一次性读入内存，然后断开连接，其它操作就可以使用,比较消耗资源)
        ///2．获取数据的机制
        ///    DataReader是通过IDbCommand.ExecuteReader来读取数据。
        ///    DataSet则是通过DbDataAdapter.Fill来填充数据
        ///3．其它区别
        ///    DataReader读取速度快于DataSet。
        ///    DataReader是数据提供者类，DataSet是一般性类，借助于DbDataAdapter来填充数据。
        ///...............................等




        /// <summary>
        ///  执行查询SQL语句，返回离线记录集
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <returns>离线记录DataSet</returns>
        public DataSet getDataTablebySQL(string strSQL)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                using (SqlCommand cmd = new SqlCommand(strSQL, conn))
                {
                    try
                    {
                        conn.Open();//打开数据源连接
                        DataSet ds = new DataSet();
                        SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);
                        myAdapter.Fill(ds);
                        return ds;
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        conn.Close();//出异常,关闭数据源连接
                        throw new Exception(string.Format("执行{0}失败:{1}", strSQL, ex.Message));
                    }
                }
            }
        }


        /// <summary>
        ///  执行带参数的查询SQL，返回离线记录集
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public DataSet getDataTablebySQL(string strSQL, params SqlParameter[] values)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                using (SqlCommand cmd = new SqlCommand(strSQL, conn))
                {
                    try
                    {
                        conn.Open();//打开数据源连接
                        DataSet ds = new DataSet();
                        cmd.Parameters.AddRange(values);
                        SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);
                        myAdapter.Fill(ds);
                        return ds;
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        conn.Close();//出异常,关闭数据源连接
                        throw new Exception(string.Format("执行{0}失败:{1}", strSQL, ex.Message));
                    }
                }
            }
        }










        #region 执行查询，返回DataTable对象-----------------------






        public static DataTable GetTable(string strSQL)
        {
            return GetTable(strSQL, null);
        }
        public static DataTable GetTable(string strSQL, SqlParameter[] pas)
        {
            return GetTable(strSQL, pas, CommandType.Text);
        }
        /// <summary>
        /// 执行查询，返回DataTable对象
        /// </summary>
        /// <param name="strSQL">sql语句</param>
        /// <param name="pas">参数数组</param>
        /// <param name="cmdtype">Command类型</param>
        /// <returns>DataTable对象</returns>
        public static DataTable GetTable(string strSQL, SqlParameter[] pas, CommandType cmdtype)
        {
            DataTable dt = new DataTable(); ;
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);
                da.SelectCommand.CommandType = cmdtype;
                if (pas != null)
                {
                    da.SelectCommand.Parameters.AddRange(pas);
                }
                da.Fill(dt);
            }
            return dt;
        }






        #endregion








        #region 执行查询，返回DataSet对象-------------------------








        public static DataSet GetDataSet(string strSQL)
        {
            return GetDataSet(strSQL, null);
        }


        public static DataSet GetDataSet(string strSQL, SqlParameter[] pas)
        {
            return GetDataSet(strSQL, pas, CommandType.Text);
        }
        /// <summary>
        /// 执行查询，返回DataSet对象
        /// </summary>
        /// <param name="strSQL">sql语句</param>
        /// <param name="pas">参数数组</param>
        /// <param name="cmdtype">Command类型</param>
        /// <returns>DataSet对象</returns>
        public static DataSet GetDataSet(string strSQL, SqlParameter[] pas, CommandType cmdtype)
        {
            DataSet dt = new DataSet(); ;
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);
                da.SelectCommand.CommandType = cmdtype;
                if (pas != null)
                {
                    da.SelectCommand.Parameters.AddRange(pas);
                }
                da.Fill(dt);
            }
            return dt;
        }
        #endregion










        #region 执行非查询存储过程和SQL语句-----------------------------








        public static int ExcuteProc(string ProcName)
        {
            return ExcuteSQL(ProcName, null, CommandType.StoredProcedure);
        }


        public static int ExcuteProc(string ProcName, SqlParameter[] pars)
        {
            return ExcuteSQL(ProcName, pars, CommandType.StoredProcedure);
        }


        public static int ExcuteSQL(string strSQL)
        {
            return ExcuteSQL(strSQL, null);
        }


        public static int ExcuteSQL(string strSQL, SqlParameter[] paras)
        {
            return ExcuteSQL(strSQL, paras, CommandType.Text);
        }


        /// 执行非查询存储过程和SQL语句
        /// 增、删、改
        /// </summary>
        /// <param name="strSQL">要执行的SQL语句</param>
        /// <param name="paras">参数列表，没有参数填入null</param>
        /// <param name="cmdType">Command类型</param>
        /// <returns>返回影响行数</returns>
        public static int ExcuteSQL(string strSQL, SqlParameter[] paras, CommandType cmdType)
        {
            int i = 0;
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand(strSQL, conn);
                cmd.CommandType = cmdType;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                conn.Open();
                i = cmd.ExecuteNonQuery();
                conn.Close();
            }
            return i;


        }




        #endregion
















        #region 执行查询返回第一行，第一列---------------------------------








        public static int ExcuteScalarSQL(string strSQL)
        {
            return ExcuteScalarSQL(strSQL, null);
        }


        public static int ExcuteScalarSQL(string strSQL, SqlParameter[] paras)
        {
            return ExcuteScalarSQL(strSQL, paras, CommandType.Text);
        }
        public static int ExcuteScalarProc(string strSQL, SqlParameter[] paras)
        {
            return ExcuteScalarSQL(strSQL, paras, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 执行SQL语句，返回第一行，第一列
        /// </summary>
        /// <param name="strSQL">要执行的SQL语句</param>
        /// <param name="paras">参数列表，没有参数填入null</param>
        /// <returns>返回影响行数</returns>
        public static int ExcuteScalarSQL(string strSQL, SqlParameter[] paras, CommandType cmdType)
        {
            int i = 0;
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand(strSQL, conn);
                cmd.CommandType = cmdType;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                conn.Open();
                i = Convert.ToInt32(cmd.ExecuteScalar());
                conn.Close();
            }
            return i;


        }




        #endregion


















        #region 查询获取单个值------------------------------------








        /// <summary>
        /// 调用不带参数的存储过程获取单个值
        /// </summary>
        /// <param name="ProcName"></param>
        /// <returns></returns>
        public static object GetObjectByProc(string ProcName)
        {
            return GetObjectByProc(ProcName, null);
        }
        /// <summary>
        /// 调用带参数的存储过程获取单个值
        /// </summary>
        /// <param name="ProcName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static object GetObjectByProc(string ProcName, SqlParameter[] paras)
        {
            return GetObject(ProcName, paras, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 根据sql语句获取单个值
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public static object GetObject(string strSQL)
        {
            return GetObject(strSQL, null);
        }
        /// <summary>
        /// 根据sql语句 和 参数数组获取单个值
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static object GetObject(string strSQL, SqlParameter[] paras)
        {
            return GetObject(strSQL, paras, CommandType.Text);
        }


        /// <summary>
        /// 执行SQL语句，返回首行首列
        /// </summary>
        /// <param name="strSQL">要执行的SQL语句</param>
        /// <param name="paras">参数列表，没有参数填入null</param>
        /// <returns>返回的首行首列</returns>
        public static object GetObject(string strSQL, SqlParameter[] paras, CommandType cmdtype)
        {
            object o = null;
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand(strSQL, conn);
                cmd.CommandType = cmdtype;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);


                }


                conn.Open();
                o = cmd.ExecuteScalar();
                conn.Close();
            }
            return o;


        }






        #endregion




        /// <summary>
        /// md5加密
        /// </summary>
        /// <param name="strPwd"></param>
        /// <returns></returns>
        public static string GetMD5(string strPwd)
        {
            string pwd = "";
            //实例化一个md5对象
            MD5 md5 = MD5.Create();
            // 加密后是一个字节类型的数组
            byte[] s = md5.ComputeHash(Encoding.UTF8.GetBytes(strPwd));
            //翻转生成的MD5码        
            s.Reverse();
            //通过使用循环，将字节类型的数组转换为字符串，此字符串是常规字符格式化所得
            //只取MD5码的一部分，这样恶意访问者无法知道取的是哪几位
            for (int i = 3; i < s.Length - 1; i++)
            {
                //将得到的字符串使用十六进制类型格式。格式后的字符是小写的字母，如果使用大写（X）则格式后的字符是大写字符
                //进一步对生成的MD5码做一些改造
                pwd = pwd + (s[i] < 198 ? s[i] + 28 : s[i]).ToString("X");
            }
            return pwd;
        }




        #region 查询获取DataReader------------------------------------








        /// <summary>
        /// 调用不带参数的存储过程，返回DataReader对象
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns>DataReader对象</returns>
        public static SqlDataReader GetReaderByProc(string procName)
        {
            return GetReaderByProc(procName, null);
        }
        /// <summary>
        /// 调用带有参数的存储过程，返回DataReader对象
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="paras">参数数组</param>
        /// <returns>DataReader对象</returns>
        public static SqlDataReader GetReaderByProc(string procName, SqlParameter[] paras)
        {
            return GetReader(procName, paras, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 根据sql语句返回DataReader对象
        /// </summary>
        /// <param name="strSQL">sql语句</param>
        /// <returns>DataReader对象</returns>
        public static SqlDataReader GetReader(string strSQL)
        {
            return GetReader(strSQL, null);
        }
        /// <summary>
        /// 根据sql语句和参数返回DataReader对象
        /// </summary>
        /// <param name="strSQL">sql语句</param>
        /// <param name="paras">参数数组</param>
        /// <returns>DataReader对象</returns>
        public static SqlDataReader GetReader(string strSQL, SqlParameter[] paras)
        {
            return GetReader(strSQL, paras, CommandType.Text);
        }
        /// <summary>
        /// 查询SQL语句获取DataReader
        /// </summary>
        /// <param name="strSQL">查询的SQL语句</param>
        /// <param name="paras">参数列表，没有参数填入null</param>
        /// <returns>查询到的DataReader（关闭该对象的时候，自动关闭连接）</returns>
        public static SqlDataReader GetReader(string strSQL, SqlParameter[] paras, CommandType cmdtype)
        {
            SqlDataReader sqldr = null;
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand(strSQL, conn);
            cmd.CommandType = cmdtype;
            if (paras != null)
            {
                cmd.Parameters.AddRange(paras);
            }
            conn.Open();
            //CommandBehavior.CloseConnection的作用是如果关联的DataReader对象关闭，则连接自动关闭
            sqldr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return sqldr;
        }






        #endregion








        #region 批量插入数据---------------------------------------------








        /// <summary>
        /// 往数据库中批量插入数据
        /// </summary>
        /// <param name="sourceDt">数据源表</param>
        /// <param name="targetTable">服务器上目标表</param>
        public static void BulkToDB(DataTable sourceDt, string targetTable)
        {
            SqlConnection conn = new SqlConnection(strConn);
            SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);   //用其它源的数据有效批量加载sql server表中
            bulkCopy.DestinationTableName = targetTable;    //服务器上目标表的名称
            bulkCopy.BatchSize = sourceDt.Rows.Count;   //每一批次中的行数


            try
            {
                conn.Open();
                if (sourceDt != null && sourceDt.Rows.Count != 0)
                    bulkCopy.WriteToServer(sourceDt);   //将提供的数据源中的所有行复制到目标表中
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
                if (bulkCopy != null)
                    bulkCopy.Close();
            }


        }


        #endregion




    }
}