数据库访问工具类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
namespace StudentDLL
{
    public class Datebase
    {
        private static string _sqlserver = ConfigurationManager.ConnectionStrings["sqlserver"].ToString();
        private SqlConnection conn;
        /// <summary>
        /// 初始化conn并且打开sqlconnection
        /// </summary>
        public SqlConnection Conn
        {
            get
            {
                if (conn == null)
                {
                    conn = new SqlConnection(_sqlserver);
                }
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                else if (conn.State == ConnectionState.Broken)
                {
                    conn.Close();
                    conn.Open();
                }
                return conn;
            }
        }
        #region 关闭数据库连接
        public void CloseSqlconnection()
        {
            if (conn.State == ConnectionState.Open || conn.State == ConnectionState.Broken)
            {
                conn.Close();
            }
        }
        #endregion
        #region 查询
        /// <summary>
        /// 使用T-SQL语句查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet executeReader(string sql)
        {
            DataSet ds = new DataSet();
            using(SqlDataAdapter da=new SqlDataAdapter(sql,conn)){da.Fill(ds);}
            CloseSqlconnection();
            return ds;
        }
        /// <summary>
        /// T-Sql参数化查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="par"></param>
        /// <returns></returns>
        public DataSet executeReader(string sql, params SqlParameter[] par)
        {
            DataSet ds = new DataSet();
            using (SqlDataAdapter da = new SqlDataAdapter(sql, Conn))
            {
                da.SelectCommand.Parameters.AddRange(par);
                da.Fill(ds);
            }
            CloseSqlconnection();
            return ds;
        }
        /// <summary>
        /// 不带参数存储过程查询
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns>DataSet表</returns>
        public DataSet procExecuteReader(string procName)
        {
            DataSet ds = new DataSet();
            using (SqlDataAdapter dar = new SqlDataAdapter(procName, Conn))
            {
                dar.SelectCommand.CommandType = CommandType.StoredProcedure;
                dar.Fill(ds);
            }
            CloseSqlconnection();
            return ds;
        }
        /// <summary>
        /// 参数化存储过程查询
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="par"></param>
        /// <returns>DataSet</returns>
        public DataSet procExecuteReader(string procName,params SqlParameter [] par)
        {
            DataSet ds = new DataSet();
            using (SqlDataAdapter dar = new SqlDataAdapter(procName,Conn))
            {
                dar.SelectCommand.CommandType = CommandType.StoredProcedure;
                dar.SelectCommand.Parameters.AddRange(par);
                dar.Fill(ds);
            }
            CloseSqlconnection();
            return ds;
        }
        #endregion
        #region 增删改
        /// <summary>
        /// 使用sql语句增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int executeNoQuery(string sql)
        {
            int dr = 0;
            using (SqlCommand comm = new SqlCommand(sql, Conn))
            {
                dr = comm.ExecuteNonQuery();
            }
            CloseSqlconnection();
            return dr;
        }
        /// <summary>
        /// 使用参数化增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int executeNoQuery(string sql,params SqlParameter [] par)
        {
            int dr = 0;
            using (SqlCommand comm = new SqlCommand(sql, Conn))
            {
                comm.Parameters.AddRange(par);
                dr = comm.ExecuteNonQuery();
            }
            CloseSqlconnection();
            return dr;
        }
        /// <summary>
        /// 使用存储过程增删改
        /// </summary>
        /// <returns></returns>
        public int procExecuteNoQuery(string procName,params SqlParameter[] par)
        {
            int result = 0;
            using (SqlCommand comm = new SqlCommand(procName,Conn))
            {
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddRange(par);
                result = comm.ExecuteNonQuery();
            }
            CloseSqlconnection();
            return result;
        }
        #endregion
    }
}

来自为知笔记(Wiz)