ZiYangSftpDataUpload/TimedUpload/utils/SqlHelper.cs

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

namespace TimedUpload.Util
{
    class SqlHelper
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="configConnStrKey">XmlConfig 中 database.config 数据库连接key</param>
        public SqlHelper(string configConnStrKey)
        {
            connStr = ConfigurationManager.AppSettings[configConnStrKey];
        }

        //连接字符串
        private readonly string connStr;

        //private static readonly string connStr = ConfigurationManager.ConnectionStrings["PipeNetworkDb"].ConnectionString;

        ////IP地址
        //private static String SqlServerId = Wujian_Jm.C_DesDecrypt(GetConfigValue("SqlServerId"), "WWKJ");
        ////用户名
        //private static String SqlUserId = Wujian_Jm.C_DesDecrypt(GetConfigValue("SqlUserId"), "WWKJ");
        ////用户密码
        //private static String PassWord = Wujian_Jm.C_DesDecrypt(GetConfigValue("PassWord"), "WWKJ");
        //数据库名
        //private static String DbaseName = GetConfigValue("DbaseName");

        //拼接链接字符串
        //  private static readonly string connStrN = "data source=" + SqlServerId + ";initial catalog=" + DbaseName + ";user id=" + SqlUserId + ";password=" + PassWord + ";";

        //1.执行insert/delete/update的方法
        #region 配置文件字段的读取函数
        public string GetConfigValue(string key)
        {
            string value = "";
            try
            {
                //string assemblyConfigFile = Assembly.GetEntryAssembly().Location;
                //Configuration config = ConfigurationManager.OpenExeConfiguration(assemblyConfigFile);
                //AppSettingsSection app = config.AppSettings;
                //value = app.Settings[key].Value;
                return null;
            }
            catch
            {
                return value;
            }
        }

        public int ExecuteNonQuery(string sql, CommandType cmdType, params SqlParameter[] pms)
        {

            using (SqlConnection con = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    //设置当前执行的是存储过程还是带参数的Sql语句
                    cmd.CommandType = cmdType;
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }

                    try
                    {
                        con.Open();
                        return cmd.ExecuteNonQuery();
                    }
                    catch
                    {
                        con.Close();
                        con.Dispose();
                        throw;
                    }
                }
            }
        }
        #endregion


        public int ExecuteNoQuery(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;

                    if (parameters != null)
                    {
                        conn.Open();
                        foreach (SqlParameter parameter in parameters)
                        {
                            cmd.Parameters.Add(parameter);
                        }
                    }

                    return cmd.ExecuteNonQuery();
                }

            }
        }
        //带事物的执行语句
        public int ExecuteNoQueryWithTransaction(string sql, CommandType cmdType, params SqlParameter[] param)
        {
            int num = 0;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlTransaction tran = conn.BeginTransaction();
                try
                {
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        cmd.CommandType = cmdType;
                        cmd.Transaction = tran;
                        if (param != null)
                        {
                            cmd.Parameters.AddRange(param);
                        }
                        //conn.Open();
                        num = cmd.ExecuteNonQuery();
                        tran.Commit();                  //提交事物
                    }
                }
                catch
                {
                    tran.Rollback();                      //事物回滚
                }

            }
            return num;
        }

        //2.执行返回单个值的方法
        public object ExecuteScalar(string sql, CommandType cmdType, params SqlParameter[] pms)
        {
            ////IP地址
            //String SqlServerIP = Wujian_Jm.C_DesDecrypt(GetConfigValue("SqlServerId"), "WWKJ");
            ////用户名
            //String userId = Wujian_Jm.C_DesDecrypt(GetConfigValue("SqlUserId"), "WWKJ");
            ////用户密码
            //String pwd = Wujian_Jm.C_DesDecrypt(GetConfigValue("PassWord"), "WWKJ");
            ////数据库名
            //String dbName = GetConfigValue("DbaseName");

            ////拼接链接字符串
            //string connStrNew = "data source=" + SqlServerIP + ";initial catalog=" + dbName + ";user id=" + userId + ";password=" + pwd + ";";
            using (SqlConnection con = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    cmd.Parameters.Clear();
                    //设置当前执行的是存储过程还是带参数的Sql语句
                    cmd.CommandType = cmdType;
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }

                    con.Open();
                    Object value = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return value;
                }
            }
        }


        //3.返回SqlDataReader的方法
        public SqlDataReader ExecuteReader(string sql, CommandType cmdType, params SqlParameter[] pms)
        {
            SqlConnection con = new SqlConnection(connStr);

            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                cmd.CommandType = cmdType;
                if (pms != null)
                {
                    cmd.Parameters.AddRange(pms);
                }
                try
                {
                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); //修改 刘学铸 by 2017-06-02
                    cmd.Parameters.Clear();
                    return dr;//cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
                catch
                {
                    con.Close();
                    con.Dispose();
                    throw;
                }
            }

        }

        //4.返回DataTable
        public DataTable ExecuteDataTable(string sql, CommandType cmdType, params SqlParameter[] pms)
        {
            try
            {
                DataTable dt = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter(sql, connStr))
                {
                    //清空参数集合
                    adapter.SelectCommand.Parameters.Clear();
                    adapter.SelectCommand.CommandType = cmdType;
                    if (pms != null)
                    {


                        adapter.SelectCommand.Parameters.AddRange(pms);
                    }
                    adapter.Fill(dt);
                    return dt;
                }
            }

            catch (Exception ex)
            {
                return null;
            }
        }


        /// <summary>
        /// 执行sql语句无参数
        /// </summary>
        public int ExecuteNoParams(string sql, CommandType cmdType)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {

                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = cmdType;
                    try
                    {
                        conn.Open();
                        return cmd.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                        conn.Close();
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// 判断表是否存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public bool DBTableIsExists(string tableName)
        {
            return ExecuteDataTable("select * from sysobjects where name='" + tableName + "'", CommandType.Text, null).Rows.Count > 0;
        }

        /// <summary>
        /// 带事务的存储过程 
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">存储过程参数-不能为空</param>
        /// <returns>0:执行成功 异常抛出</returns>
        public int ExecStoredProcedure(string procName, params SqlParameter[] parameters)
        {
            int rtn = 0;
            using (SqlConnection conn = new SqlConnection(connStr))
            {

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    SqlTransaction st = conn.BeginTransaction();
                    cmd.Transaction = st;
                    try
                    {
                        cmd.CommandText = procName;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddRange(parameters);
                        conn.Open();
                        rtn = cmd.ExecuteNonQuery();
                        st.Commit();
                        cmd.Parameters.Clear();//修改 刘学铸 at 2017-07-13
                        return rtn;
                    }
                    catch (SqlException sqlex)
                    {
                        st.Rollback();
                        throw sqlex;
                    }
                }
            }
        }

        /// <summary>
        /// 带输出参数的存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public object ExecStoredPro(string procName, string name, params SqlParameter[] paras)
        {

            int rtn = 0;
            using (SqlConnection conn = new SqlConnection(connStr))
            {

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    SqlTransaction st = conn.BeginTransaction();
                    cmd.Transaction = st;
                    try
                    {
                        cmd.CommandText = procName;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddRange(paras);
                        conn.Open();
                        rtn = cmd.ExecuteNonQuery();
                        st.Commit();
                        return cmd.Parameters[name].Value;  //返回输出参数

                    }
                    catch (SqlException sqlex)
                    {
                        st.Rollback();
                        throw sqlex;
                    }
                    //finally
                    //{
                    //    cmd.Parameters.Clear();//修改 刘学铸 at 2017-07-13

                    //}
                }
            }

        }

        /// <summary>
        /// 带输出参数的存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public void ExecStoredPro1(string procName, params SqlParameter[] paras)
        {

            int rtn = 0;
            using (SqlConnection conn = new SqlConnection(connStr))
            {

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    //SqlTransaction st = conn.BeginTransaction();
                    //cmd.Transaction = st;
                    try
                    {
                        cmd.CommandText = procName;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddRange(paras);
                        conn.Open();
                        rtn = cmd.ExecuteNonQuery();
                        //st.Commit();
                        //return cmd.Parameters[name].Value;  //返回输出参数

                    }
                    catch (SqlException sqlex)
                    {
                        //st.Rollback();
                        throw sqlex;
                    }
                    finally
                    {
                        cmd.Parameters.Clear();//修改 刘学铸 at 2017-07-13
                        if (conn != null) conn.Close();
                    }
                }
            }

        }

        /// <summary>
        /// 存储过程返回DataTable
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public DataTable ExecProcedureDatatable(string procName, params SqlParameter[] paras)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connStr))
            {

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    cmd.CommandText = procName;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Clear();
                    if (paras != null)
                    {

                        foreach (SqlParameter parameter in paras)
                        {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                    conn.Open();
                    try
                    {
                        //执行               
                        adapter.Fill(dt);
                    }
                    catch (System.Data.Common.DbException ex)
                    {
                        //PublicUtility.HandException("DbBase.cs", ex);
                    }
                    finally
                    {
                        //关闭连接
                        cmd.Parameters.Clear();

                        if (conn != null) conn.Close();
                    }
                    //return dt;
                }

            }
            return dt;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="strSql">查询sql</param>
        /// <param name="dbParameter">查询参数</param>
        /// <param name="orderField">排序字段</param>
        /// <param name="isAsc">排序方式</param>
        /// <param name="pageSize">显示的页数</param>
        /// <param name="pageIndex">当前页数</param>
        /// <param name="total">总共条数</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string strSql, string orderField, bool isAsc, int pageSize, int pageIndex, out int total, params SqlParameter[] parameter)
        {
            using (SqlConnection dbConnection = new SqlConnection(connStr))
            {
                StringBuilder sb = new StringBuilder();
                if (pageIndex == 0)
                {
                    pageIndex = 1;
                }
                int num = (pageIndex - 1) * pageSize;
                int num1 = (pageIndex) * pageSize;
                string OrderBy = "";

                if (!string.IsNullOrEmpty(orderField))
                {
                    if (orderField.ToUpper().IndexOf("ASC") + orderField.ToUpper().IndexOf("DESC") > 0)
                    {
                        //OrderBy = "Order By " + orderField ;
                        OrderBy = "Order By " + orderField;
                    }
                    else
                    {
                        //OrderBy = "Order By " + orderField + " " + (isAsc ? "ASC" : "DESC");
                        OrderBy = "Order By " + orderField + "  " + (isAsc ? "ASC" : "DESC");
                    }
                }
                else
                {
                    OrderBy = "order by (select 0)";
                }
                sb.Append("Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
                sb.Append(" As rowNum, * From (" + strSql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");

                total = Convert.ToInt32(ExecuteScalar("Select Count(1) From (" + strSql + ") As t", CommandType.Text, parameter));
                //total = Convert.ToInt32(new DbHelper(dbConnection).ExecuteScalar(CommandType.Text, "Select Count(1) From (" + strSql + ") As t", dbParameter));
                //var IDataReader = new DbHelper(dbConnection).ExecuteReader(CommandType.Text, sb.ToString(), dbParameter);
                //return ConvertExtension.IDataReaderToList<T>(IDataReader);

                return ExecuteDataTable(sb.ToString(), CommandType.Text, parameter);
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="strSql">查询sql</param>
        /// <param name="dbParameter">查询参数</param>
        /// <param name="orderField">排序字段</param>
        /// <param name="isAsc">排序方式</param>
        /// <param name="pageSize">每页行数</param>
        /// <param name="pageIndex">当前页数</param>
        /// <param name="total">总共条数</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string strSql, string orderField, string isAsc, int pageSize, int pageIndex, out int total, params SqlParameter[] parameter)
        {
            using (SqlConnection dbConnection = new SqlConnection(connStr))
            {
                StringBuilder sb = new StringBuilder();
                if (pageIndex == 0)
                {
                    pageIndex = 1;
                }
                int num = (pageIndex - 1) * pageSize;
                int num1 = (pageIndex) * pageSize;
                string OrderBy = "";

                if (!string.IsNullOrEmpty(orderField))
                {
                    if (orderField.ToUpper().IndexOf("ASC") + orderField.ToUpper().IndexOf("DESC") > 0)
                    {
                        OrderBy = "Order By " + orderField;
                    }
                    else
                    {
                        OrderBy = "Order By " + orderField + " " + isAsc;
                    }
                }
                else
                {
                    OrderBy = "order by (select 0)";
                }
                sb.Append("Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
                sb.Append(" As rowNum, * From (" + strSql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");

                total = Convert.ToInt32(ExecuteScalar("Select Count(1) From (" + strSql + ") As t", CommandType.Text, parameter));
                //total = Convert.ToInt32(new DbHelper(dbConnection).ExecuteScalar(CommandType.Text, "Select Count(1) From (" + strSql + ") As t", dbParameter));
                //var IDataReader = new DbHelper(dbConnection).ExecuteReader(CommandType.Text, sb.ToString(), dbParameter);
                //return ConvertExtension.IDataReaderToList<T>(IDataReader);

                return ExecuteDataTable(sb.ToString(), CommandType.Text, parameter);
            }
        }


        #region 返回 sqlDataReader
        public SqlDataReader ReturnSqlDataReader(String sql)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(sql, conn);

            conn.Open();

            try
            {
                SqlDataReader reader = cmd.ExecuteReader();
                return reader;
            }
            catch (Exception ex)
            {
                conn.Close();
                throw ex;
            }


        }
        #endregion

        public int ScalarToInt(string sql)
        {
            return Convert.ToInt32(ExecuteScalar(sql, CommandType.Text, null));
        }

        public int Query(string sql)
        {
            return ExecuteNonQuery(sql, CommandType.Text, null);
        }

        public DataTable ExecuteDataTable(String sql)
        {
            return this.ExecuteDataTable(sql, CommandType.Text, null);
        }

        public Int32 QueryTran(String sql)
        {
            Int32 num = 0;

            using (SqlConnection con = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    con.Open();
                    SqlTransaction st = con.BeginTransaction();
                    cmd.Transaction = st;
                    //设置当前执行的是存储过程还是带参数的Sql语句
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;

                    try
                    {

                        num = cmd.ExecuteNonQuery();
                        st.Commit();
                    }
                    catch
                    {
                        st.Rollback();
                        con.Close();
                        con.Dispose();
                    }
                }
            }

            return num;
        }
    }
}