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

namespace WindowsFormsApplication1
{
    class DBHelper
    {
        //SQL連接字符串-SQL身份認(rèn)證方式登錄
        public static string connStr = "server=.;database=DBTEST;uid=sa;pwd=123456;";

        //SQL連接字符串-Windows身份認(rèn)證方式登錄
        //public static string connStr = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBTEST;Data Source=.";

        //讀取配置文件appSettings節(jié)點(diǎn)讀取字符串(需要添加引用System.Configuration)
        //public static string connStr = ConfigurationManager.AppSettings["DefaultConn"].ToString();
        //對(duì)應(yīng)的配置文件如下：
        //<appSettings>
        //  <add key="DefaultConn" value="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBTEST;Data Source=."/>
        //</appSettings>

        //讀取配置文件ConnectionStrings節(jié)點(diǎn)讀取字符串(需要添加引用System.Configuration)
        //public static string connStr = ConfigurationManager.ConnectionStrings["DefaultConn"].ConnectionString;
        //對(duì)應(yīng)配置文件如下：
        //<connectionStrings>
        //    <add name="DefaultConn" connectionString="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBTEST;Data Source=."/>
        //</connectionStrings>

        public static SqlConnection conn = null;
        public static SqlDataAdapter adp = null;

        #region 連接數(shù)據(jù)庫
        /// <summary>
        /// 連接數(shù)據(jù)庫
        /// </summary>
        public static void OpenConn()
        {
            if (conn == null)
            {
                conn = new SqlConnection(connStr);
                conn.Open();
            }
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }
            if (conn.State == System.Data.ConnectionState.Broken)
            {
                conn.Close();
                conn.Open();
            }
        }
        #endregion

        #region 執(zhí)行SQL語句前準(zhǔn)備
        /// <summary>
        /// 準(zhǔn)備執(zhí)行一個(gè)SQL語句
        /// </summary>
        /// <param name="sql">需要執(zhí)行的SQL語句</param>
        public static void PrepareSql(string sql)
        {
            OpenConn(); //打開數(shù)據(jù)庫連接
            adp = new SqlDataAdapter(sql, conn);
        }
        #endregion

        #region 設(shè)置和獲取sql語句的參數(shù)
        /// <summary>
        /// 設(shè)置傳入?yún)?shù)
        /// </summary>
        /// <param name="parameterName">參數(shù)名稱</param>
        /// <param name="parameterValue">參數(shù)值</param>
        public static void SetParameter(string parameterName, object parameterValue)
        {
            parameterName = "@" + parameterName.Trim();
            if (parameterValue == null)
                parameterValue = DBNull.Value;
            adp.SelectCommand.Parameters.Add(new SqlParameter(parameterName, parameterValue));
        }
        #endregion

        #region 執(zhí)行SQL語句
        /// <summary>
        /// 執(zhí)行非查詢SQL語句
        /// </summary>
        /// <returns>受影響行數(shù)</returns>
        public static int ExecNonQuery()
        {
            int result = adp.SelectCommand.ExecuteNonQuery();
            conn.Close();
            return result;
        }
        /// <summary>
        /// 執(zhí)行查詢SQL語句
        /// </summary>
        /// <returns>DataTable類型查詢結(jié)果</returns>
        public static DataTable ExecQuery()
        {
            DataTable dt = new DataTable();
            adp.Fill(dt);
            conn.Close();
            return dt;
        }
        /// <summary>
        /// 執(zhí)行查詢SQL語句
        /// </summary>
        /// <returns>SqlDataReader類型查詢結(jié)果,SqlDataReader需要手動(dòng)關(guān)閉</returns>
        public static SqlDataReader ExecDataReader()
        {
            return adp.SelectCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }
        /// <summary>
        /// 執(zhí)行查詢SQL語句
        /// </summary>
        /// <returns>查詢結(jié)果第一行第一列</returns>
        public static object ExecScalar()
        {
            object obj = adp.SelectCommand.ExecuteScalar();
            conn.Close();
            return obj;
        }
        #endregion
    }
}