SQL 数据库学习05 DBHelper

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	<startup>
		<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
	</startup>

	<connectionStrings>
		<add name="connStr" connectionString="server=.;database=TestBase;uid=sa;pwd='123456'" providerName="System.Data.SqlClient"/>
	</connectionStrings>

	<appSettings>
		<add key="connStr" value="server=.;database=TestBase;uid=sa;pwd='123456'"/>
	</appSettings>
</configuration>

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 _15_DBHelper
{
    public class DBHelper
    {
        private static readonly string ConStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

        SqlConnection conn = null;

        //private SqlConnection GetConn()
        //{
        //    conn = new SqlConnection(ConStr);
        //    return conn;
        //}

        /// <summary>
        /// 执行T-SQL ，返回受影响的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        private static int ExcuteNonQuery(string sql,int cmdType,params SqlParameter[]paras)
        {
            int count = 0;
           using (SqlConnection con = new SqlConnection(ConStr))
            {
                SqlCommand cmd = CmdSet(new SqlCommand(sql, con), cmdType, paras);

                con.Open();
                count = cmd.ExecuteNonQuery();
                con.Close();
                
            }
           return count;
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        private static object ExcuteScalar(string sql, int cmdType, params SqlParameter[] paras)
        {
            object o = null;
            using (SqlConnection con = new SqlConnection(ConStr))
            {
                SqlCommand cmd = CmdSet(new SqlCommand(sql, con), cmdType, paras);

                con.Open();
                o = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                con.Close();

            }
            return o;
        }

        private static SqlDataReader ExcuteReader(string sql, int cmdType, params SqlParameter[] paras)
        {
            SqlDataReader dr = null;
            SqlConnection con = new SqlConnection(ConStr);

            SqlCommand cmd = CmdSet(new SqlCommand(sql, con), cmdType, paras);

            try
            {
                con.Open();
                dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
            }
            catch (Exception ex)
            {

                con.Close();
                throw new Exception("执行查询异常",ex);
            }

            return dr;
        }

        public static SqlCommand CmdSet(SqlCommand cmd, int cmdType, params SqlParameter[] paras)
        {
            if (cmdType == 2)
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
            }

            if (paras.Length > 0)
            {
                cmd.Parameters.AddRange(paras);
            }
            return cmd;
        }

        public static DataSet GetDataSet(string sql, int cmdType, params SqlParameter[] paras)
        {
            DataSet ds = null;
            using (SqlConnection con = new SqlConnection(ConStr))
            {
                SqlCommand cmd = CmdSet(new SqlCommand(sql, con), cmdType, paras);

                //数据库->adapter
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                con.Open();
                //adapter->临时表
                da.Fill(ds);
                con.Close();
            }
            return ds;
        }

        /// <summary>
        /// 单个表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql, int cmdType, params SqlParameter[] paras)
        {
            DataTable dt = new DataTable();
            using (SqlConnection con = new SqlConnection(ConStr))
            {
                SqlCommand cmd = CmdSet(new SqlCommand(sql, con), cmdType, paras);

                //数据库->adapter
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                con.Open();
                //adapter->临时表
                da.Fill(dt);
                con.Close();
            }
            return dt;
        }

        public static bool Executetrans(List<string> listSQL)
        {
            using (SqlConnection con = new SqlConnection(ConStr))
            {
                con.Open();
                SqlTransaction trans = con.BeginTransaction();
                SqlCommand cmd = con.CreateCommand();
                cmd.Transaction = trans;

                try
                {
                    for (int i = 0; i < listSQL.Count; i++)
                    {
                        cmd.CommandText = listSQL[i];
                        cmd.ExecuteNonQuery();
                    }
                    trans.Commit();
                    return true;
                }
                catch (SqlException ex)
                {
                    trans.Rollback();
                    throw new Exception("执行事务出现异常",ex);
                }
            }
                return false;
        }
    }
}