SQLHelper(for winForm)

SQLHelper.csusing System;<br />using System.Collections.Generic;<br />using System.Text;<br />using System.Collections;<br />using System.Data;<br />using System.Data.SqlClient;<br />using System.Configuration;<br />namespace HelloWinForm.DBUtility<br />{<br /> class SQLHelper<br /> {<br /> #region 通用方法<br /> // 資料連線池<br /> private SqlConnection con;<br /> /// <summary><br /> /// 返回資料庫連接字串<br /> /// </summary><br /> /// <returns></returns><br /> public static String GetSqlConnection()<br /> {<br /> String conn = ConfigurationManager.AppSettings["connectionString"].ToString();<br /> return conn;<br /> }<br /> #endregion<br /> #region 執行sql字串<br /> /// <summary><br /> /// 執行不帶參數的SQL語句<br /> /// </summary><br /> /// <param name="Sqlstr"></param><br /> /// <returns></returns><br /> public static int ExecuteSql(String Sqlstr)<br /> {<br /> String ConnStr = GetSqlConnection();<br /> using (SqlConnection conn = new SqlConnection(ConnStr))<br /> {<br /> SqlCommand cmd = new SqlCommand();<br /> cmd.Connection = conn;<br /> cmd.CommandText = Sqlstr;<br /> conn.Open();<br /> cmd.ExecuteNonQuery();<br /> conn.Close();<br /> return 1;<br /> }<br /> }<br /> /// <summary><br /> /// 執行帶參數的SQL語句<br /> /// </summary><br /> /// <param name="Sqlstr">SQL語句</param><br /> /// <param name="param">參數對象數組</param><br /> /// <returns></returns><br /> public static int ExecuteSql(String Sqlstr, SqlParameter[] param)<br /> {<br /> String ConnStr = GetSqlConnection();<br /> using (SqlConnection conn = new SqlConnection(ConnStr))<br /> {<br /> SqlCommand cmd = new SqlCommand();<br /> cmd.Connection = conn;<br /> cmd.CommandText = Sqlstr;<br /> cmd.Parameters.AddRange(param);<br /> conn.Open();<br /> cmd.ExecuteNonQuery();<br /> conn.Close();<br /> return 1;<br /> }<br /> }<br /> /// <summary><br /> /// 返回DataReader<br /> /// </summary><br /> /// <param name="Sqlstr"></param><br /> /// <returns></returns><br /> public static SqlDataReader ExecuteReader(String Sqlstr)<br /> {<br /> String ConnStr = GetSqlConnection();<br /> SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader時,是不可以用using()的<br /> try<br /> {<br /> SqlCommand cmd = new SqlCommand();<br /> cmd.Connection = conn;<br /> cmd.CommandText = Sqlstr;<br /> conn.Open();<br /> return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//關閉關聯的Connection<br /> }<br /> catch //(Exception ex)<br /> {<br /> return null;<br /> }<br /> }<br /> /// <summary><br /> /// 執行SQL語句並返回資料表<br /> /// </summary><br /> /// <param name="Sqlstr">SQL語句</param><br /> /// <returns></returns><br /> public static DataTable ExecuteDt(String Sqlstr)<br /> {<br /> String ConnStr = GetSqlConnection();<br /> using (SqlConnection conn = new SqlConnection(ConnStr))<br /> {<br /> SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);<br /> DataTable dt = new DataTable();<br /> conn.Open();<br /> da.Fill(dt);<br /> conn.Close();<br /> return dt;<br /> }<br /> }<br /> /// <summary><br /> /// 執行SQL語句並返回DataSet<br /> /// </summary><br /> /// <param name="Sqlstr">SQL語句</param><br /> /// <returns></returns><br /> public static DataSet ExecuteDs(String Sqlstr)<br /> {<br /> String ConnStr = GetSqlConnection();<br /> using (SqlConnection conn = new SqlConnection(ConnStr))<br /> {<br /> SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);<br /> DataSet ds = new DataSet();<br /> conn.Open();<br /> da.Fill(ds);<br /> conn.Close();<br /> return ds;<br /> }<br /> }<br /> #endregion<br /> #region 操作預存程序<br /> /// <summary><br /> /// 運行預存程序(已重載)<br /> /// </summary><br /> /// <param name="procName">預存程序的名字</param><br /> /// <returns>預存程序的傳回值</returns><br /> public int RunProc(string procName)<br /> {<br /> SqlCommand cmd = CreateCommand(procName, null);<br /> cmd.ExecuteNonQuery();<br /> this.Close();<br /> return (int)cmd.Parameters["ReturnValue"].Value;<br /> }<br /> /// <summary><br /> /// 運行預存程序(已重載)<br /> /// </summary><br /> /// <param name="procName">預存程序的名字</param><br /> /// <param name="prams">預存程序的輸入參數列表</param><br /> /// <returns>預存程序的傳回值</returns><br /> public int RunProc(string procName, SqlParameter[] prams)<br /> {<br /> SqlCommand cmd = CreateCommand(procName, prams);<br /> cmd.ExecuteNonQuery();<br /> this.Close();<br /> return (int)cmd.Parameters[0].Value;<br /> }<br /> /// <summary><br /> /// 運行預存程序(已重載)<br /> /// </summary><br /> /// <param name="procName">預存程序的名字</param><br /> /// <param name="dataReader">結果集</param><br /> public void RunProc(string procName, out SqlDataReader dataReader)<br /> {<br /> SqlCommand cmd = CreateCommand(procName, null);<br /> dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);<br /> }<br /> /// <summary><br /> /// 運行預存程序(已重載)<br /> /// </summary><br /> /// <param name="procName">預存程序的名字</param><br /> /// <param name="prams">預存程序的輸入參數列表</param><br /> /// <param name="dataReader">結果集</param><br /> public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)<br /> {<br /> SqlCommand cmd = CreateCommand(procName, prams);<br /> dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);<br /> }<br /> /// <summary><br /> /// 建立Command對象用於訪問預存程序<br /> /// </summary><br /> /// <param name="procName">預存程序的名字</param><br /> /// <param name="prams">預存程序的輸入參數列表</param><br /> /// <returns>Command對象</returns><br /> private SqlCommand CreateCommand(string procName, SqlParameter[] prams)<br /> {<br /> // 確定串連是開啟的<br /> Open();<br /> //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );<br /> SqlCommand cmd = new SqlCommand(procName, con);<br /> cmd.CommandType = CommandType.StoredProcedure;<br /> // 添加預存程序的輸入參數列表<br /> if (prams != null)<br /> {<br /> foreach (SqlParameter parameter in prams)<br /> cmd.Parameters.Add(parameter);<br /> }<br /> // 返回Command對象<br /> return cmd;<br /> }<br /> /// <summary><br /> /// 建立輸入參數<br /> /// </summary><br /> /// <param name="ParamName">參數名</param><br /> /// <param name="DbType">參數類型</param><br /> /// <param name="Size">參數大小</param><br /> /// <param name="Value">參數值</param><br /> /// <returns>新參數對象</returns><br /> public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)<br /> {<br /> return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);<br /> }<br /> /// <summary><br /> /// 建立輸出參數<br /> /// </summary><br /> /// <param name="ParamName">參數名</param><br /> /// <param name="DbType">參數類型</param><br /> /// <param name="Size">參數大小</param><br /> /// <returns>新參數對象</returns><br /> public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)<br /> {<br /> return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);<br /> }<br /> /// <summary><br /> /// 建立預存程序參數<br /> /// </summary><br /> /// <param name="ParamName">參數名</param><br /> /// <param name="DbType">參數類型</param><br /> /// <param name="Size">參數大小</param><br /> /// <param name="Direction">參數的方向(輸入/輸出)</param><br /> /// <param name="Value">參數值</param><br /> /// <returns>新參數對象</returns><br /> public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)<br /> {<br /> SqlParameter param;<br /> if (Size > 0)<br /> {<br /> param = new SqlParameter(ParamName, DbType, Size);<br /> }<br /> else<br /> {<br /> param = new SqlParameter(ParamName, DbType);<br /> }<br /> param.Direction = Direction;<br /> if (!(Direction == ParameterDirection.Output && Value == null))<br /> {<br /> param.Value = Value;<br /> }<br /> return param;<br /> }<br /> #endregion<br /> #region 資料庫連接和關閉<br /> /// <summary><br /> /// 開啟串連池<br /> /// </summary><br /> private void Open()<br /> {<br /> // 開啟串連池<br /> if (con == null)<br /> {<br /> //這裡不僅需要using System.Configuration;還要在引用目錄裡添加<br /> con = new SqlConnection(GetSqlConnection());<br /> con.Open();<br /> }<br /> }<br /> /// <summary><br /> /// 關閉串連池<br /> /// </summary><br /> public void Close()<br /> {<br /> if (con != null)<br /> con.Close();<br /> }<br /> /// <summary><br /> /// 釋放串連池<br /> /// </summary><br /> public void Dispose()<br /> {<br /> // 確定串連已關閉<br /> if (con != null)<br /> {<br /> con.Dispose();<br /> con = null;<br /> }<br /> }<br /> #endregion<br /> }<br />} 簡單用一下:using System;<br />using System.Collections.Generic;<br />using System.Text;<br />using System.Data;<br />using System.Data.SqlClient;<br />using System.Collections;<br />using HelloWinForm.DBUtility;<br />namespace HelloWinForm.DAL<br />{<br /> class Student<br /> {<br /> public string test()<br /> {<br /> string str = "";<br /> SqlDataReader dr = SQLHelper.ExecuteReader("select * from Student");<br /> while (dr.Read())<br /> {<br /> str += dr["StudentNO"].ToString();<br /> }<br /> dr.Close();<br /> return str;<br /> }<br /> }<br />}