SQL 自製類別

這個 SQL 類別為我個人設計並撰寫，可能設計的不夠完整，就請廣大的觀眾多多指教囉！

若要引用請註明來源，歡迎取用。







使用範例：

private SQL sql;

protected void Page_Load(object sender, EventArgs e)
{
    sql = new SQL("NorthwindConnectionString", 
        SQLConnectionType.SettingsName);

    Hashtable ht = new Hashtable();
    ht.Add("eID", "1");

    gv.DataSource = sql.ExecuteDataTable(
        "SELECT * FROM Employees WHERE EmployeeID=@eID ", ht);
    gv.DataBind();
    

}

類別程式碼如下：

public enum SQLConnectionType
{
    ConnectionString,
    SettingsName
};

/// <summary>
/// SQL 的摘要描述
/// </summary>
public class SQL
{
    private DataTable dt;

    private SqlConnection connection;
    private SqlCommand command;
    private SqlDataAdapter adapter;
    public SqlDataReader reader { get; set; }
    private SqlTransaction tran;
    public bool isError { get; set; }
    public string errorMessage { get; set; }

    /// <summary>
    /// 
    /// </summary>
    public SQL()
    {

    }

    /// <summary>
    /// 初始化資料庫連線
    /// </summary>
    public SQL(string strConnection, SQLConnectionType type)
    {
        Connection(strConnection, type);
    }

    /// <summary>
    /// 資料庫連線
    /// </summary>
    /// <param name="strConnection">連接字串</param>
    /// <param name="type">資料庫連線方式</param>
    public void Connection(string strConnection, SQLConnectionType type)
    {
        connection = new SqlConnection();
        command = new SqlCommand();

        switch (type)
        {
            case SQLConnectionType.ConnectionString:
                
                connection.ConnectionString = strConnection;
                command.Connection = connection;
                
                break;

            case SQLConnectionType.SettingsName:

                connection.ConnectionString =
                    ConfigurationManager.ConnectionStrings[strConnection].
                    ConnectionString;
                command.Connection = connection;

                break;
        }
    }

    /// <summary>
    /// 執行資料庫語法
    /// </summary>
    /// <param name="query">指令</param>
    public void Execute(string query)
    {
        command.CommandText = query;
        connection.Open();

        isError = false;
        try
        {
            // 開始執行資料庫交易
            tran = connection.BeginTransaction();
            command.Transaction = tran;
            command.ExecuteNonQuery();
            tran.Commit();

        }
        catch (Exception ex)
        {
            // 失敗則 Rollback
            tran.Rollback();

            errorMessage = ex.Message;
            isError = true;
        }
        finally
        {
            connection.Close();
        }
    }

    /// <summary>
    /// 執行資料庫語法
    /// </summary>
    /// <param name="query">指令</param>
    /// <param name="ht">參數</param>
    public void Execute(string query, Hashtable ht)
    {
        SetParameters(ht);
        Execute(query);
    }

    /// <summary>
    /// 傳回結果第一行第一列之資料
    /// </summary>
    /// <param name="query">指令</param>
    /// <returns></returns>
    public object ExecuteScalar(string query)
    {
        object obj = string.Empty;

        command.CommandText = query;
        connection.Open();

        isError = false;
        try
        {
            obj = command.ExecuteScalar();
        }
        catch (Exception ex)
        {
            errorMessage = ex.Message;
            isError = true;
        }
        finally
        {
            connection.Close();
        }

        return obj;
    }

    /// <summary>
    /// 傳回結果第一行第一列之資料
    /// </summary>
    /// <param name="query">指令</param>
    /// <param name="ht">參數</param>
    /// <returns></returns>
    public object ExecuteScalar(string query, Hashtable ht)
    {
        SetParameters(ht);

        return ExecuteScalar(query);
    }


    /// <summary>
    /// 傳回 DataTable
    /// </summary>
    /// <param name="query">指令</param>
    /// <returns></returns>
    public DataTable ExecuteDataTable(string query)
    {
        dt = new DataTable();
        command.CommandText = query;
        
        isError = false;
        try
        {
            adapter = new SqlDataAdapter(command);
            adapter.Fill(dt);
        }
        catch(Exception ex)
        {
            errorMessage = ex.Message;
            isError = true;
        }

        return dt;
    }

    /// <summary>
    /// 傳回 DataTable
    /// </summary>
    /// <param name="query">指令</param>
    /// <param name="ht">參數</param>
    /// <returns></returns>
    public DataTable ExecuteDataTable(string query, Hashtable ht)
    {
        SetParameters(ht);

        return ExecuteDataTable(query);
    }

    /// <summary>
    /// 傳回 SqlDataReader
    /// </summary>
    /// <param name="query">指令</param>
    public SqlDataReader ExecuteReader(string query)
    {
        command.CommandText = query;

        reader = command.ExecuteReader();

        return reader;
    }

    /// <summary>
    /// 傳回 SqlDataReader
    /// </summary>
    /// <param name="query">指令</param>
    /// <param name="ht">參數</param>
    /// <returns></returns>
    public SqlDataReader ExecuteReader(string query, Hashtable ht)
    {
        SetParameters(ht);
        return ExecuteReader(query);
    }

    /// <summary>
    /// 查詢資料是否存在
    /// </summary>
    /// <param name="query">指令</param>
    /// <returns></returns>
    public bool Exists(string query)
    {
        object obj;
        bool isExists = false;
        int iResult = 0;

        command.CommandText = query;
        connection.Open();

        isError = false;
        try
        {
            obj = command.ExecuteScalar();

            if (int.TryParse(Convert.ToString(obj), out iResult))
            {
                if (iResult > 0)
                    isExists = true;
            }
        }
        catch (Exception ex)
        {
            errorMessage = ex.Message;
            isError = true;
        }
        finally
        {
            connection.Close();
        }

        return isExists;
    }

    /// <summary>
    /// 查詢資料是否存在
    /// </summary>
    /// <param name="query">指令</param>
    /// <param name="ht">參數</param>
    /// <returns></returns>
    public bool Exists(string query, Hashtable ht)
    {
        SetParameters(ht);
        return Exists(query);
    }

    /// <summary>
    /// 將資料庫連線開啟或關閉
    /// </summary>
    /// <param name="strStatus">狀態字串(open or close)</param>
    public void CallConnection(string strStatus)
    {
        switch (strStatus.ToLower())
        {
            case "open":
                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                break;
            case "close":
                if (connection.State == ConnectionState.Open)
                    connection.Close();
                break;

        }
    }

    /// <summary>
    /// 設定參數
    /// </summary>
    /// <param name="ht">參數</param>
    private void SetParameters(Hashtable ht)
    {
        command.Parameters.Clear();
        foreach (DictionaryEntry de in ht)
            command.Parameters.AddWithValue(Convert.ToString(de.Key),
            Convert.ToString(de.Value));
    }
}

回C#目錄
回首頁