SqlHelper V1.0

//===========================
//SqlHelper V1.0
//日期:2013-05-14
//作者：PENNY
//===========================

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

using System.Configuration;
using System.Data.Common;
using System.Data.SqlClient;

using System.Security.Cryptography;

namespace PennyLibrary
{
    class SqlHelper
    {
        //配置文件解密后的数据库连接字符串
        private static String DB_CONN_STRING = GetConnectionString();
        
        //密钥
        private const string KEY = "TEST";
        private const int DBCOMMAND_TIMEOUT = 180;


        #region "BasicProcess"
        //从配置文件中得到并解密数据库连接字符串
        private static String GetConnectionString()
        {
            String connectionString;
            try
            {
                connectionString = Decrypt(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString, KEY);
            }
            catch (Exception e)
            {
                throw new Exception("连接数据库失败", e);
            }
            return connectionString;
        }        

        #endregion

        //============================
        //调用例子
        //SqlHelper.ExecuteNoQuery("INSERT INTO TB_USER (Name,Age) VALUES ('PENNY',36)");
        //带参数
        //DataTable dt = SqlHelper.ExecuteDataTable("SELECT * FROM TB_USER WHERE Name = @name OR Age = @age",
        //                                            new SqlParameter ("@name","PENNY"),
        //                                            new SqlParameter ("@age",35))
        //============================

        #region "ExecuteNoQuery"

        public static int ExecuteNoQuery(string strSQL , params SqlParameter [] parameters)
        {
            using (SqlConnection conn = new SqlConnection(DB_CONN_STRING))
            {
                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandTimeout = DBCOMMAND_TIMEOUT;
                    cmd.CommandText = strSQL;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
            }
        }


        #endregion

        #region "ExecuteScalar"

        public static object ExecuteScalar(string strSQL, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(DB_CONN_STRING))
            {
                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandTimeout = DBCOMMAND_TIMEOUT;
                    cmd.CommandText = strSQL;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteScalar();
                }
            }
        }
        #endregion

        #region "ExecuteReader"
        public static SqlDataReader ExecuteReader(string strSQL, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(DB_CONN_STRING))
            {
                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandTimeout = DBCOMMAND_TIMEOUT;
                    cmd.CommandText = strSQL;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteReader();
                }
            }
        }

        #endregion

        #region "ExecuteDataset"
        public static DataSet ExecuteDataset(string strSQL, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(DB_CONN_STRING))
            { 
                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandTimeout = DBCOMMAND_TIMEOUT;
                    cmd.CommandText = strSQL;
                    cmd.Parameters.AddRange(parameters);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    return ds;

                }
            }
        }

        #endregion

        #region "ExecuteDataTable"

        public static DataTable ExecuteDataTable(string strSQL, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(DB_CONN_STRING))
            {
                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandTimeout = DBCOMMAND_TIMEOUT;
                    cmd.CommandText = strSQL;
                    cmd.Parameters.AddRange(parameters);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet ();
                    da.Fill(ds);

                    return ds.Tables[0];
                }
            }
        }

        #endregion


        #region "ExecuteNoQueryProc"
        public static int ExecuteNoQueryProc(string strProcName, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(DB_CONN_STRING))
            {
                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandTimeout = DBCOMMAND_TIMEOUT;
                    cmd.CommandType = CommandType.StoredProcedure;//指定执行存储过程操作
                    cmd.CommandText = strProcName;//存储过程名称
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        #endregion

        #region "ExecuteDatasetProc"
        public static DataSet ExecuteDatasetProc(string strProcName, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(DB_CONN_STRING))
            {
                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandTimeout = DBCOMMAND_TIMEOUT;
                    cmd.CommandType = CommandType.StoredProcedure;//指定执行存储过程操作
                    cmd.CommandText = strProcName;//存储过程名称
                    cmd.Parameters.AddRange(parameters);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);

                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    return ds;

                }
            }
        }
        #endregion

        #region "ExecuteDataTableProc"
        public static DataTable ExecuteDataTable(string strProcName, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(DB_CONN_STRING))
            {
                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandTimeout = DBCOMMAND_TIMEOUT;
                    cmd.CommandType = CommandType.StoredProcedure;//指定执行存储过程操作
                    cmd.CommandText = strProcName;//存储过程名称
                    cmd.Parameters.AddRange(parameters);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    return ds.Tables[0];
                }
            }
        }
        #endregion

        
    }
}