Quan hệ C# và Database :: Xây dựng lớp thực thi lệnh SQL (DataAccsess Layer)

vào lúc 6:59 PM Nhãn: , , , , ,
Trong các bài trước của loạt bài về Quan hệ C# và Database mình đã giới thiệu cách kết nối SQL, SqlCommand, SqlDataReader & Dataset, Store Procedure, Function and Trigger. Trong bài này mình sẽ giới thiệu cách xây dựng lớp thực thi các câu truy vấn, các Store Procedure. Đây chính là lớp thứ nhất (DataAccsess Layer) trong mô hình 3 lớp
Đây chính là lớp thứ nhất (DataAccsess Layer) trong mô hình 3 lớp. Lớp này có nhiệm vụ thực hiện việc kết nối và thực thi các câu truy vấn dạng NoneQuery (Câu truy vấn không tra về giá trị - dạng Insert, Update, Delete), câu truy vấn trả về giá trị. Thực thi các  Store Procedure,

Sau khi tạo Project bạn chọn menu File/Add/New Project, Sau đó chọn Class Library.
Quan hệ C# và Database :: Xây dựng lớp thực thi lệnh SQL
Đặt tên cho Project. Khi đó VS sẽ tạo một file mặc định là class1.cs. Bạn có thể xóa nó đi và tạo một file class mới. Đặt tên cho class này và thực hiện các hàm sau. Ở đây mình sẽ giới thiệu các hàm cơ bản. Nếu cần bạn có thể download Class ở liên kết cuỗi bài viết.
 + Hàm mở và đóng kết nối
public SqlConnection MoKetNoi()
{
    SqlConnection sqlCn = new SqlConnection(ConnectionString);
    try
    {
        sqlCn.Open();
    }
    catch
    {
        return null;
    }
    return sqlCn;
}

public void DongKetNoi(SqlConnection sqlCn)
{
    if (sqlCn != null)
    {
        if (sqlCn.State == ConnectionState.Open)
            sqlCn.Close();
        sqlCn.Dispose();
    }
}
+ Hàm thực thi câu truy vấn không dạng Insert Update, Delete
/// <summary>
///Thực thi 1 câu lệnh SQL dạng select không trả lại giá trị
/// </summary>
public string ThucThiLenhSQL(string strSQL)
{
    SqlCommand sqlCommand = new SqlCommand();
    sqlCommand.CommandTimeout = 2000;
    sqlCn = MoKetNoi();
    sqlCommand.Connection = sqlCn;
    sqlCommand.Parameters.Clear();
    sqlCommand.CommandText = strSQL;
    sqlCommand.CommandType = CommandType.Text;
    try
    {
        intErrorNumber = sqlCommand.ExecuteNonQuery();
        mErrorCode = 0;
        mErrorMsg = "";
        DongKetNoi(sqlCn);
    }
    catch (SqlException ex)
    {
        intErrorNumber = ex.Number;
        mErrorMsg = ex.Message;
        System.Console.Write(ex.StackTrace);
    }
    finally
    {
        if (sqlCn.State == ConnectionState.Open)
            sqlCn.Close();
        sqlCn.Dispose();
    }
    return mErrorCode.ToString() + ":" + mErrorMsg;
}
 + Hàm thực thi câu truy vấn trả về một DataSet
/// <summary>
///Thực thi 1 câu lệnh SQL dạng select có trả về dữ liệu là DataSet
/// </summary>
public DataSet ThucThiCauTruyVan_TraVeDataSet(string strSQL)
{
    SqlDataAdapter Adapter = new SqlDataAdapter(strSQL, sqlCn);
    DataSet ds = new DataSet();
    try
    {
        Adapter.Fill(ds);
        DongKetNoi(sqlCn);
    }
    catch (SqlException E)
    {
        string strDescriptionError = E.Message;
    }
    return ds;
}
+ Hàm thực thi câu truy vấn có trả về dữ liệu là một bảng
/// <summary>
/// Thực thi 1 câu lệnh SQL dạng select có trả về dữ liệu là 1 bảng
/// </summary>
/// <return>
/// DataTable
/// </return>>
public DataTable ThucThiCauTruyVan_TraVeBang(string strSQL)
{
    sqlCn = MoKetNoi();
    SqlDataAdapter Adapter = new SqlDataAdapter(strSQL, sqlCn);
    DataTable ds = new DataTable();
    try
    {
        Adapter.Fill(ds);
        DongKetNoi(sqlCn);
    }
    catch (SqlException E)
    {
        string strDescriptionError = E.Message;
    }
    finally
    {
        if (sqlCn.State == ConnectionState.Open)
            sqlCn.Close();
        sqlCn.Dispose();
    }
    return ds;
}
+ Hàm thực thi 1 StoreProcedure không trả lại giá trị
/// <summary>
/// Thực thi 1 StoreProcedure không trả lại giá trị
/// </summary>
protected void ThucThiStore(
    string TenStoreProcedure, 
    SqlParameter[] sqlParam)
{
    try
    {
        SqlCommand sqlCmd = new SqlCommand();
        sqlCmd.CommandTimeout = 2000;
        sqlCn = MoKetNoi();
        sqlCmd.Connection = sqlCn;
        sqlCmd.CommandType = CommandType.StoredProcedure;
        sqlCmd.CommandText = TenStoreProcedure;
        for (int i = 0; i < sqlParam.Length; i++)
        {
            sqlCmd.Parameters.Add(sqlParam[i]);
        }
        sqlCmd.ExecuteNonQuery();
        DongKetNoi(sqlCn);
    }
    catch (SqlException sqlEx)
    {
        strErrorMessage = sqlEx.Message;
        intErrorNumber = sqlEx.Number;
    }
    finally
    {
        if (sqlCn.State == ConnectionState.Open)
            sqlCn.Close();
        sqlCn.Dispose();
    }
}
 + Hàm thực thi Store trả về dữ liệu là 1 Dataset
/// <summary>
/// Thực thi Store trả về dữ liệu là 1 Dataset
/// </summary>
/// <param name="TenStoreProcedure"></param>
/// <param name="sqlParam"></param>
/// <returns></returns>
protected DataSet ThucThiStoreTraVeDataSet(
    string TenStoreProcedure, 
    SqlParameter[] sqlParam)
{
    DataSet dtbTmp = new DataSet();
    try
    {
        sqlCn = MoKetNoi();
        SqlCommand sqlCmd = new SqlCommand();
        sqlCmd.CommandTimeout = 2000;
        sqlCmd.Connection = sqlCn;
        sqlCmd.CommandType = CommandType.StoredProcedure;
        sqlCmd.CommandText = TenStoreProcedure;
        for (int i = 0; i < sqlParam.Length; i++)
        {
            sqlCmd.Parameters.Add(sqlParam[i]);
        }
        SqlDataAdapter sqlDA = new SqlDataAdapter();
        sqlDA.SelectCommand = sqlCmd;
        sqlDA.Fill(dtbTmp);
        DongKetNoi(sqlCn);
    }
    catch (SqlException sqlEx)
    {
        strErrorMessage = sqlEx.Message;
        intErrorNumber = sqlEx.Number;
    }
    finally
    {
        if (sqlCn.State == ConnectionState.Open)
            sqlCn.Close();
        sqlCn.Dispose();
    }
    return dtbTmp;
}
+ Thực thi 1 StoreProcedure có trả về dữ liệu là 1 bảng
/// <summary>
/// Thực thi 1 StoreProcedure có trả về dữ liệu là 1 bảng
/// </summary>
/// <return>
/// DataTable
/// </return>>
protected DataTable ThucThiStoreTraVeBang(
    string TenStoreProcedure, 
    SqlParameter[] sqlParam)
{
    DataTable dtbTmp = new DataTable();
    try
    {
        sqlCn = MoKetNoi();
        SqlCommand sqlCmd = new SqlCommand();
        sqlCmd.CommandTimeout = 2000;
        sqlCmd.Connection = sqlCn;
        sqlCmd.CommandType = CommandType.StoredProcedure;
        sqlCmd.CommandText = TenStoreProcedure;
        for (int i = 0; i < sqlParam.Length; i++)
        {
            sqlCmd.Parameters.Add(sqlParam[i]);
        }
        SqlDataAdapter sqlDA = new SqlDataAdapter();
        sqlDA.SelectCommand = sqlCmd;
        sqlDA.Fill(dtbTmp);
        DongKetNoi(sqlCn);
    }
    catch (SqlException sqlEx)
    {
        strErrorMessage = sqlEx.Message;
        intErrorNumber = sqlEx.Number;
    }
    finally
    {
        if (sqlCn.State == ConnectionState.Open)
            sqlCn.Close();
        sqlCn.Dispose();
    }
    return dtbTmp;
}