ClassSQL.cs

出自ProgWiki
跳至導覽 跳至搜尋

用途

  • ADO.NET的SqlClient操作封裝

程式碼

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Data.SqlClient;
 
// GetConnectionStrings 裡使用的 IEnumerator
using System.Collections;
 
 
 
//namespace MyDB
//{
    /// <summary>
    /// ClassSQL 為 System.Data.SqlClient 的操作類別
    /// </summary>
    public class ClassSQL
    {
		//private int m_nConnStrSel;
		private string m_strConnection;
 
 
		/// <summary>
		/// ClassSQL() 建構函式
		/// </summary>
		public ClassSQL()
		{
			// TODO: ClassSQL()
 
			//m_strConnection = ConfigurationManager.AppSettings["ConSQL"];
			m_strConnection = ConfigurationManager.ConnectionStrings["ConSQL"].ConnectionString;
		}
 
 
/*
		/// <summary>
		/// 取得連線用字串 (放進 m_strConnection 供 OpenConnection() 使用)
		/// </summary>
		/// <param name="strFilter">過濾條件</param>
		/// <returns>使否取得連線用字串</returns>
		public bool GetConnectionStrings(string strFilter)
		{
			// TODO: GetConnectionStrings(string strFilter)
 
			// Get the connectionStrings.
			ConnectionStringSettingsCollection connectionStrings =
					  ConfigurationManager.ConnectionStrings;
 
			// Get the collection enumerator.
			IEnumerator connectionStringsEnum = connectionStrings.GetEnumerator();
 
			int i = 0;
			bool IsConnStrSel = false;
 
			while (connectionStringsEnum.MoveNext())
			{
				string strName = connectionStrings[i].Name;
 
				if (m_nConnStrSel != i)
				{
					if (strFilter.Equals(""))
					{
						IsConnStrSel = true;
					}
					else if (strName.IndexOf(strFilter) != -1)
					{
						IsConnStrSel = true;
					}
				}
 
				if (IsConnStrSel == true)
				{  
					m_nConnStrSel = i;
					m_strConnection = connectionStrings[strName].ToString();
					return IsConnStrSel;
				}
 
				i += 1;
			}
 
			if (IsConnStrSel == false)
			{
				m_nConnStrSel = -1;
				m_strConnection = "";
			}
			return IsConnStrSel;
		}
*/
 
		/// <summary>
		/// 開啟SQL連線
		/// </summary>
		/// <returns>SqlConnection</returns>
		public SqlConnection OpenConnection()
		{
			//TODO: OpenConnection()
 
			SqlConnection objConn = new SqlConnection();
 
			objConn.ConnectionString = m_strConnection;
			objConn.Open();
 
			return objConn;
		}
 
 
		/// <summary>
		/// SqlCommand.ExecuteNonQuery 操作
		/// </summary>
		/// <param name="strSQL">SQL命令字串</param>
		/// <returns>int</returns>
		public int ExecuteNonQuery(string strSQL)
		{
			//TODO: ExecuteNonQuery(string strSQL)
 
			using (SqlConnection objConn = OpenConnection())
			{
				using (SqlCommand objCommand = new SqlCommand())
				{
					objCommand.CommandText = strSQL;
					objCommand.CommandType = CommandType.Text;
					return ExecuteNonQuery(objConn, objCommand);
				}
			}
		}
 
		/// <summary>
		/// SqlCommand.ExecuteNonQuery 操作
		/// </summary>
		/// <param name="objConn">已開啟連線的SqlConnection</param>
		/// <param name="strSQL">SQL命令字串</param>
		/// <returns>int</returns>
		public int ExecuteNonQuery(SqlConnection objConn, string strSQL)
		{
			//TODO: ExecuteNonQuery(SqlConnection objConn, string strSQL)
 
			using (SqlCommand objCommand = new SqlCommand())
			{
				objCommand.CommandText = strSQL;
				objCommand.CommandType = CommandType.Text;
				return ExecuteNonQuery(objConn, objCommand);
			}
		}
 
 
		/// <summary>
		/// SqlCommand.ExecuteNonQuery 操作
		/// </summary>
		/// <param name="objConn">已開啟連線的SqlConnection</param>
		/// <param name="objCommand">SqlCommand</param>
		/// <returns>int</returns>
		public int ExecuteNonQuery(SqlConnection objConn, SqlCommand objCommand)
		{
			//TODO: ExecuteNonQuery(SqlConnection objConn, SqlCommand objCommand)
 
			objCommand.Connection = objConn;
			int retval = objCommand.ExecuteNonQuery();
			objCommand.Parameters.Clear();
			return retval;
		}
 
 
		/// <summary>
		/// SqlCommand.ExecuteReader 操作
		/// </summary>
		/// <param name="objConn">已開啟連線的SqlConnection</param>
		/// <param name="strSQL">SQL命令字串</param>
		/// <returns>SqlDataReader</returns>
		public SqlDataReader ExecuteReader(SqlConnection objConn, string strSQL)
		{
			//TODO: ExecuteReader(SqlConnection objConn, string strSQL)
 
			using (SqlCommand objCommand = new SqlCommand())
			{
				objCommand.CommandText = strSQL;
				objCommand.CommandType = CommandType.Text;
				return ExecuteReader( objConn, objCommand);
			}
		}
 
 
		/// <summary>
		/// SqlCommand.ExecuteReader 操作
		/// </summary>
		/// <param name="objConn">已開啟連線的SqlConnection</param>
		/// <param name="objCommand">SqlCommand</param>
		/// <returns>SqlDataReader</returns>
		public SqlDataReader ExecuteReader(SqlConnection objConn, SqlCommand objCommand)
		{
			//TODO: ExecuteReader(SqlConnection objConn, SqlCommand objCommand)
 
			objCommand.Connection = objConn;
			SqlDataReader objRet = objCommand.ExecuteReader();
			objCommand.Parameters.Clear();
			return objRet;
		}
 
 
        /// <summary>
        /// SqlCommand.ExecuteScalar 操作
        /// </summary>
        /// <param name="strSQL">SQL命令字串</param>
        /// <returns>object</returns>
        public object ExecuteScalar(string strSQL)
        {
            //TODO: ExecuteScalar(string strSQL)
 
            using (SqlConnection objConn = OpenConnection())
            {
                using (SqlCommand objCommand = new SqlCommand())
                {
                    objCommand.CommandText = strSQL;
                    objCommand.CommandType = CommandType.Text;
                    return ExecuteScalar(objConn, objCommand);
                }
            }
        }
 
 
		/// <summary>
		/// SqlCommand.ExecuteScalar 操作
		/// </summary>
		/// <param name="objConn">已開啟連線的SqlConnection</param>
		/// <param name="strSQL">SQL命令字串</param>
		/// <returns>object</returns>
		public object ExecuteScalar(SqlConnection objConn, string strSQL)
		{
			//TODO: ExecuteScalar(SqlConnection objConn, string strSQL)
 
			using (SqlCommand objCommand = new SqlCommand())
			{
				objCommand.CommandText = strSQL;
				objCommand.CommandType = CommandType.Text;
				return ExecuteScalar(objConn, objCommand);
			}
		}
 
 
		/// <summary>
		/// SqlCommand.ExecuteScalar 操作
		/// </summary>
		/// <param name="objConn">已開啟連線的SqlConnection</param>
		/// <param name="objCommand">SqlCommand</param>
		/// <returns>object</returns>
		public object ExecuteScalar(SqlConnection objConn, SqlCommand objCommand)
		{
			//TODO: ExecuteScalar(SqlConnection objConn, SqlCommand objCommand)
 
			objCommand.Connection = objConn;
			object obj = objCommand.ExecuteScalar();
			objCommand.Parameters.Clear();
			return obj;
		}
 
 
		/// <summary>
		/// SqlCommand.ExecuteDataTable 操作
		/// </summary>
		/// <param name="objConn">已開啟連線的SqlConnection</param>
		/// <param name="strSQL">SQL命令字串</param>
		/// <returns>DataTable</returns>
		public DataTable ExecuteDataTable(SqlConnection objConn, string strSQL)
		{
			//TODO: ExecuteDataTable(SqlConnection objConn, string strSQL)
 
			using (SqlCommand objCommand = new SqlCommand())
			{
				objCommand.CommandText = strSQL;
				objCommand.CommandType = CommandType.Text;
				return ExecuteDataTable(objConn, objCommand);
			}
		}
 
 
		/// <summary>
		/// SqlCommand.ExecuteDataTable 操作
		/// </summary>
		/// <param name="objConn">已開啟連線的SqlConnection</param>
		/// <param name="objCommand">SqlCommand</param>
		/// <returns>DataTable</returns>
		public DataTable ExecuteDataTable(SqlConnection objConn, SqlCommand objCommand)
		{
			//TODO: ExecuteDataTable(SqlConnection objConn, SqlCommand objCommand)
 
			DataTable ret = new DataTable();
 
			using (SqlDataAdapter objDA = new SqlDataAdapter())
			{
				objCommand.Connection = objConn;
				objDA.SelectCommand = objCommand;
				objDA.Fill(ret);
				objCommand.Parameters.Clear();
				return ret;
			}
		}
 
 
		/// <summary>
		/// SqlCommand.ExecuteDataSet操作
		/// </summary>
		/// <param name="objConn">已開啟連線的SqlConnection</param>
		/// <param name="strSQL">SQL命令字串</param>
		/// <returns>DataSet</returns>
		public DataSet ExecuteDataSet(SqlConnection objConn, string strSQL)
		{
			//TODO: ExecuteDataSet(SqlConnection objConn, string strSQL)
 
			using (SqlCommand objCommand = new SqlCommand())
			{
				objCommand.CommandText = strSQL;
				objCommand.CommandType = CommandType.Text;
				return ExecuteDataSet(objConn, objCommand);
			}
		}
 
 
		/// <summary>
		/// SqlCommand.ExecuteDataSet 操作
		/// </summary>
		/// <param name="objConn">已開啟連線的SqlConnection</param>
		/// <param name="objCommand">SqlCommand</param>
		/// <returns>DataSet</returns>
		public DataSet ExecuteDataSet(SqlConnection objConn, SqlCommand objCommand)
		{
			//TODO: ExecuteDataSet(SqlConnection objConn, SqlCommand objCommand)
 
			DataSet objDS = new DataSet();
 
			using (SqlDataAdapter objDA = new SqlDataAdapter())
			{
				objCommand.Connection = objConn;
				objDA.SelectCommand = objCommand;
				objDA.Fill(objDS);
				objCommand.Parameters.Clear();
				return objDS;
			}
		}
 
 
		/// <summary>
		/// SqlCommand.ExecuteDataSet操作
		/// </summary>
		/// <param name="objConn">已開啟連線的SqlConnection<</param>
		/// <param name="strSQL">SQL命令字串</param>
		/// <param name="objDS">ref DataSet</param>
		/// <param name="strTableName">資料表名稱</param>
		/// <param name="bFillSchema">是否填入資料表的格式(true或false)</param>
		/// <returns>int</returns>
		public int ExecuteDataSet(SqlConnection objConn, string strSQL, ref DataSet objDS, string strTableName, bool bFillSchema)
		{
			//TODO: ExecuteDataSet(SqlConnection objConn, string strSQL, ref DataSet objDS, string strTableName, bool bFillSchema)
 
			using (SqlCommand objCommand = new SqlCommand())
			{
				objCommand.CommandText = strSQL;
				objCommand.CommandType = CommandType.Text;
				return ExecuteDataSet(objConn,  objCommand, ref objDS, strTableName, bFillSchema);
			}
		}
 
 
		/// <summary>
		/// SqlCommand.ExecuteDataSet操作
		/// </summary>
		/// <param name="objConn">已開啟連線的SqlConnection</param>
		/// <param name="objCommand">SqlCommand</param>
		/// <param name="objDS">ref DataSet</param>
		/// <param name="strTableName">資料表名稱</param>
		/// <param name="bFillSchema">是否填入資料表的格式(true或false)</param>
		/// <returns>int</returns>
		public int ExecuteDataSet(SqlConnection objConn, SqlCommand objCommand, ref DataSet objDS, string strTableName, bool bFillSchema)
		{
			//TODO: ExecuteDataSet(SqlConnection objConn, SqlCommand objCommand, ref DataSet objDS, string strTableName, bool bFillSchema)
 
			int nRet = 0;				
 
			using (SqlDataAdapter objDA = new SqlDataAdapter())
			{
				objCommand.Connection = objConn;
				objDA.SelectCommand = objCommand;
 
				if (bFillSchema)
					objDA.FillSchema(objDS, SchemaType.Source);
 
				if ((strTableName != null) && (strTableName != ""))
					nRet = objDA.Fill(objDS, strTableName);
				else
					nRet = objDA.Fill(objDS);
 
				objCommand.Parameters.Clear();
 
				return nRet;
			}
		}
	}
//}

使用範例

  • 範例1，ExecuteScalar （一定有值回傳）
string strSql = "SELECT count(*) FROM table1";
 
ClassSQL objSql = new ClassSQL();
int count = objSql.ExecuteScalar(strSqlSelect);
  • 範例2，ExecuteScalar （不一定有值回傳）
string strSql = "SELECT top x1 FROM table1";
 
ClassSQL objSql = new ClassSQL();
Object o = objSql.ExecuteScalar(strSql);
int x1 = 0;   //因為不一定有值回傳, 所以要先給預設值
if ((o != null) && (o != DBNull.Value))     x1 = o;
  • 範例3，ExecuteDataSet，使用foreach
string strSql = "SELECT * FROM table1";
 
ClassSQL objSql = new ClassSQL();
using (SqlConnection conn = objSql.OpenConnection()) {
	using (DataSet ds = objSql.ExecuteDataSet(conn, strSql)) {
	    if (!((ds.Tables.Count == 0) || (ds.Tables[0].Rows.Count == 0))) {
                //有資料
                foreach (DataRow dr in ds.Tables[0].Rows) {
                    //使用dr()取得資料, dr(id) 或 dr("欄位名稱")
                }
            } else {
	        //無資料
            }
	}
}
  • 範例4，ExecuteDataSet，使用ds.Tables[0].Rows(0)回傳第一列的資料
string strSql = "SELECT top 1 * FROM table1";
 
ClassSQL objSql = new ClassSQL();
using (SqlConnection conn = objSql.OpenConnection()) {
	using (DataSet ds = objSql.ExecuteDataSet(conn, strSql)) {
	    if (!((ds.Tables.Count == 0) || (ds.Tables[0].Rows.Count == 0))) {
                //有資料
                DataRow dr = ds.Tables[0].Rows(0);
                //使用dr()取得資料, dr(id) 或 dr("欄位名稱")                
            } else {
	        //無資料
            }
	}
}
取自「https://www.player.idv.tw/prog/index.php?title=ClassSQL.cs&oldid=10904」