C#、WinForm、ASP.NET - SQLHelper.cs

標籤：

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.Windows.Forms;namespace Car.Command{    public static class SQLHelper    {        //擷取連接字串        public static string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;        #region ExecuteTable        /// <summary>        /// 返回相應的資料表（無參數）        /// </summary>        /// <param name="text">要執行的語句，SQL語句或者是預存程序</param>        /// <param name="type">要執行的類型，SQL語句或者是預存程序</param>        /// <returns></returns>        public static DataTable ExecuteTable(string text, CommandType type)        {            DataTable dt = new DataTable();            using (SqlConnection con = new SqlConnection(ConString))            {                try                {                    con.Open();                    SqlDataAdapter adp = new SqlDataAdapter(text, con);                    adp.SelectCommand.CommandType = type;                    adp.Fill(dt);                }                catch (Exception ex)                {                    MessageBox.Show(ex.Message, "提示");                }            }            return dt;        }        /// <summary>        /// 返回相應的資料表（重載，使用參數）        /// </summary>        /// <param name="text">要執行的語句，SQL語句或者是預存程序</param>        /// <param name="para">使用的參數</param>        /// <param name="type">要執行的類型，SQL語句或者是預存程序</param>        /// <returns></returns>        public static DataTable ExecuteTable(string text, SqlParameter[] para, CommandType type)        {            DataTable dt = new DataTable();            using (SqlConnection con = new SqlConnection(ConString))            {                try                {                    con.Open();                    SqlDataAdapter adp = new SqlDataAdapter(text, con);                    adp.SelectCommand.CommandType = type;                    adp.SelectCommand.Parameters.AddRange(para);                    adp.Fill(dt);                }                catch (Exception ex)                {                    MessageBox.Show(ex.Message, "提示");                }            }            return dt;        }         #endregion        #region ExecuteNonQuery        /// <summary>        /// 返回受影響的行數（不使用參數）        /// </summary>        /// <param name="text">要執行的SQL語句或者是預存程序</param>        /// <param name="type">執行SQL語句或者是預存程序</param>        /// <returns></returns>        public static int ExecuteNonQuery(string text, CommandType type)        {            int i = 0;            using (SqlConnection con = new SqlConnection(ConString))            {                try                {                    con.Open();                    SqlCommand cmd = new SqlCommand();                    cmd.Connection = con;                    cmd.CommandText = text;                    cmd.CommandType = type;                    i = cmd.ExecuteNonQuery();                }                catch (Exception ex)                {                    MessageBox.Show(ex.Message, "提示");                }            }            return i;        }        /// <summary>        /// 返回受影響的行數（重載，使用參數）        /// </summary>        /// <param name="text">要執行的SQL語句或者是預存程序</param>        /// <param name="para">要使用的參數</param>        /// <param name="type">執行SQL語句或者是預存程序</param>        /// <returns></returns>        public static int ExecuteNonQuery(string text, SqlParameter[] para, CommandType type)        {            int i = 0;            using (SqlConnection con = new SqlConnection(ConString))            {                try                {                    con.Open();                    SqlCommand cmd = new SqlCommand();                    cmd.Connection = con;                    cmd.CommandText = text;                    cmd.Parameters.AddRange(para);                    cmd.CommandType = type;                    i = cmd.ExecuteNonQuery();                }                catch (Exception ex)                {                    MessageBox.Show(ex.Message, "提示");                }            }            return i;        }        #endregion        #region ExcuteReader        /// <summary>        /// 返回SqlDataReader資料集（不使用參數）        /// </summary>        /// <param name="text"></param>        /// <param name="type"></param>        /// <returns></returns>        public static SqlDataReader ExcuteReader(string text, CommandType type)        {            SqlConnection con = new SqlConnection(ConString);            using (SqlCommand cmd = new SqlCommand(text, con))            {                con.Open();                cmd.CommandType = type;                SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);                return sdr;            }        }        /// <summary>        /// 重載，返回SqlDataReader資料集（使用參數）        /// </summary>        /// <param name="text"></param>        /// <param name="para"></param>        /// <param name="type"></param>        /// <returns></returns>        public static SqlDataReader ExcuteReader(string text, SqlParameter[] para, CommandType type)        {            SqlConnection con = new SqlConnection(ConString);            using (SqlCommand cmd = new SqlCommand(text, con))            {                cmd.CommandType = type;                cmd.Parameters.AddRange(para);                SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);                return sdr;            }        }         #endregion        #region ExcuteScalar        /// <summary>        /// 返回資料集第一行第一列的值        /// </summary>        /// <param name="text"></param>        /// <param name="type"></param>        /// <returns></returns>        public static object ExcuteScalar(string text, CommandType type)        {            object obj = null;            try            {                using (SqlConnection con = new SqlConnection(ConString))                {                    con.Open();                    SqlCommand cmd = new SqlCommand(text, con);                    cmd.CommandType = type;                    obj = cmd.ExecuteScalar();                }            }            catch (Exception ex)            {                MessageBox.Show(ex.Message, "提示");            }            return obj;        }        /// <summary>        /// 重載（返回資料集合第一行第一列的值）        /// </summary>        /// <param name="text"></param>        /// <param name="para"></param>        /// <param name="type"></param>        /// <returns></returns>        public static  object ExcuteScalar(string text, SqlParameter[] para, CommandType type)        {            object obj = null;            try            {                using (SqlConnection con = new SqlConnection(ConString))                {                    con.Open();                    SqlCommand cmd = new SqlCommand(text, con);                    cmd.CommandType = type;                    cmd.Parameters.AddRange(para);                    obj = cmd.ExecuteScalar();                }            }            catch (Exception ex)            {                MessageBox.Show(ex.Message, "提示");            }            return obj;        }         #endregion    }}

 

C#、WinForm、ASP.NET - SQLHelper.cs