封装 SqlHelper 数据访问类

 

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

namespace DAL.SqlHelper
{
    public static class SqlHelper
    {
        private static string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
        /// <summary>
        /// 增删改操作
        /// </summary>
        /// <param name="sql">数据库查询sql语句</param>
        /// <param name="pars">参数</param>
        /// <returns>int</returns>
        /// <exception cref="Exception"></exception>
        public static int UpDate(string sql, params SqlParameter[] pars)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.Clear();
            if (pars != null)
            {
                cmd.Parameters.AddRange(pars);
            }
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //记录日志

                //向上抛出错误信息
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 查询单一结果
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns>object</returns>
        /// <exception cref="Exception"></exception>
        public static object ExecuteScalar(string sql, params SqlParameter[] pars)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.Clear();
            if(pars != null)
            {
                cmd.Parameters.AddRange(pars);
            }
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }catch (Exception ex)
            {
                //记录到日志
                throw new Exception(ex.Message);
            }
            finally { conn.Close(); }
        }

        /// <summary>
        /// 获取DataTtable 数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns>DataTtable</returns>
        /// <exception cref="Exception"></exception>
        public static DataTable GetDataTable(string sql, params SqlParameter[] pars)
        {
            SqlConnection conn = new SqlConnection (connStr);
            SqlCommand cmd = new SqlCommand (sql, conn);
            DataSet dt = new DataSet();
            cmd.Parameters.Clear();
            if(pars != null)
            {
                cmd.Parameters.AddRange(pars);
            }
            try
            {
                SqlDataAdapter sa = new SqlDataAdapter(cmd);
                conn.Open();
                sa.Fill(dt);
                return dt.Tables[0];
            }catch (Exception ex)
            {
                //写入日志
                conn.Close();
                throw new Exception(ex.Message);
            }finally { conn.Close(); }
        }

        /// <summary>
        /// 获取 SqlDataReader 结果
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns>SqlDataReader</returns>
        /// <exception cref="Exception"></exception>
        public static SqlDataReader GetSqlDataReader(string sql, params SqlParameter[] pars)
        {
            SqlConnection conn = new SqlConnection (connStr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.Clear();
            if (pars != null)
            {
                cmd.Parameters.AddRange(pars);
            }
            try
            {
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }catch (Exception ex)
            {
                conn.Close();
                throw new Exception(ex.Message);
            }
        }
    }
}