Lập trình thực thi thủ tục SQL trong ADO.NET – Phần 1

Bạn đã làm quen với ADO.NET và đã biết cách Thêm, Sửa, Xóa bằng lệnh T-SQL thông thường với đối tượng SqlCommand. Tuy nhiên để giảm thiểu việc viết lệnh T-SQL trong mã code C#, người ta có thể tạo ra các thủ tục trong Hệ quản trị CSDL. Với cách này bạn có thể dễ dàng bảo trì các mã T-SQL và Code C# của bạn cũng trở lên ngắn gọn hơn. Đặc biệt là khi bạn phải thực thi 1 thủ tục có thể lên đến hàng trang giấy hay vài trang giấy thì thực thi một thủ tục sẽ là giải pháp hữu hiệu trong lập trình với ADO.NET.

Trong bài viết này, tôi vẫn sử dụng CSDL SQL Server có tên HRM, có 1 bảng đơn giản là: Departments.

Bước 1: Thiết kế CSDL với bảng Departments như dưới đây

Lưu ý: Trường DepartmentID ở đây tôi thiết lập là khóa chính, tự động tắng

create table Departments(

DepartmentID int NOT NULL IDENTITY(1,1) PRIMARY KEY,

DepartmentName varchar(250),

Description varchar(250)

)

Bước 2: Viết các thủ tục cho phép thêm, sửa, xóa một phòng ban

/* Thủ tục thêm mới phòng ban*/

CREATE PROC [dbo].[SP_InsertDepartment]
(
     @Name nvarchar(250),
     @Description nvarchar(250)
)AS INSERT INTO Departments VALUES(@Name,@Description);

/* Thủ xóa một phòng ban*/

CREATE PROC [dbo].[SP_DeleteDepartment]
(
     @ID int
) AS DELETE Departments WHERE DepartmentID= @ID;

/* Thủ tục sửa thông tin 1 phòng ban*/

CREATE PROC [dbo].[SP_UpdateDepartment]
(
     @ID int,
     @Name nvarchar(250),
     @Description nvarchar(250)
)AS UPDATE Departments
     SET DepartmentName = @Name,
     Description = @Description
     WHERE DepartmentID= @ID;

Bước 3: Bây giờ bạn cần thiết kế giao diện như sau:

Cách thiết kế và gán ràng buộc dữ liệu với DataGridView tôi đã trình bày trong bài viết trước. Trong ví dụ này bạn ràng buộc dữ liệu với 2 trường: DepartmentID, DepartmentName, Description. Tuy nhiên cột Mã phòng ban của bạn sẽ ẩn đi bằng cách cho thuộc tính Visible của cột đó = False. Tại sao lại ẩn đi? Nhằm trong suốt với người dùng, đây là trường tự động tăng nên người dùng không cần nhập, không cần quan tâm, nhưng lại là trường mà người lập trình cần thiết kế để thuận tiện trong thao tác cập nhật.

Sau đó bạn bổ sung vào giao diện 2 ô TextBox (txtName và txtDescripton), 3 Button: btnAdd, btnUpdate, btnDelete.

Ta được giao diện như hình sau:

Bước 4: Lập trình hiển thị dữ liệu lên DataGridView

Đầu tiên bạn hãy khai báo và khởi tạo đối tượng Connection. Sau đó bạn nên viết một hàm LoadData() dùng để load dữ liệu lên DataGridView vì hàm này còn được sử dụng lại khi ta thêm, sửa, xóa 1 bản ghi.

Viết code như sau:

string strConn = @"Server=.\SQLEXPRESS; Database=HRM; Integrated Security=True";
SqlConnection conn; 

private void LoadData()
       {

           da = new SqlDataAdapter("SELECT * FROM Departments", conn);

           DataTable dt = new DataTable();

           da.Fill(dt);

           dgvDeparts.DataSource = dt;
       }

  private void frmDepartment_Load(object sender, EventArgs e)
       {

           conn = new SqlConnection(strConn);
           conn.Open();
           LoadData();
       }

Khi viết xong đoạn lệnh trên, bạn chạy lên thì kết quả  có thể đã hiển thị nhưng cột STT vẫn trống vì không có ràng buộc với trường này. Bởi vậy bạn hãy viết trong sự kiện RowPrePaint của điều khiển DataGridView như sau:

private void dgvDeparts_RowPrePaint(object sender, DataGridViewRowPrePaintEventArgs e)
    {
        dgvDeparts.Rows[e.RowIndex].Cells["clNo"].Value = e.RowIndex + 1;
    }

Lúc này cột số thứ tự của bạn sẽ điền số tự động như mong muốn.

Hãy chuyển sang bước 5.

Bước 5: Hiển thị dữ liệu lên TextBox tương ứng khi chọn 1 dòng trong DataGridView

private void dgvDeparts_CellClick(object sender, DataGridViewCellEventArgs e)
    {
        if (e.RowIndex >= 0 && e.ColumnIndex >= 0)
        {
            txtName.Text = Convert.ToString(dgvDeparts.CurrentRow.Cells["clName"].Value);
            txtDescription.Text = Convert.ToString(dgvDeparts.CurrentRow.Cells["clDescription"].Value);
        }
    }

Bước 6: Thực thi thủ tục thêm mới một phòng ban: SP_InsertDepartment.

private void btnAdd_Click(object sender, EventArgs e)
    {
        // Khai báo và khởi tạo đối tượng Command, truyền vào tên thủ tục tương ứng

        SqlCommand cmd = new SqlCommand("SP_InsertDepartment",conn);

        // Khai báo kiểu thực thi là Thực thi thủ tục

        cmd.CommandType = CommandType.StoredProcedure;

        // Khai báo và gán giá trị cho các tham số đầu vào của thủ tục

        // Khai báo tham số thứ nhất @Name - là tên tham số được tạo trong thủ tục

        SqlParameter p = new SqlParameter("@Name", txtName.Text);

        cmd.Parameters.Add(p);

        // Khởi tạo tham số thứ 2 trong thủ tục là @Description

        p = new SqlParameter("@Description",txtDescription.Text);

        cmd.Parameters.Add(p);

        // Thực thi thủ tục

        int count = cmd.ExecuteNonQuery();

        if (count > 0)
        {
            MessageBox.Show("Thêm mới thành công");
            LoadData();
        }
        else MessageBox.Show("Không thể thêm mới");

    }

Bạn có thể chạy chương trình và thử nghệm!

Bước 7: Tương tự cho việc thực thi các thủ tục sửa và xóa như sau:

private void btnUpdate_Click(object sender, EventArgs e)
    {

        SqlCommand cmd = new SqlCommand("SP_UpdateDepartment", conn);

        cmd.CommandType = CommandType.StoredProcedure;

        int id = (int)dgvDeparts.CurrentRow.Cells["clID"].Value;

        SqlParameter p = new SqlParameter("@ID", id);

        cmd.Parameters.Add(p);

        p = new SqlParameter("@Name", txtName.Text);

        cmd.Parameters.Add(p);

        p = new SqlParameter("@Description", txtDescription.Text);

        cmd.Parameters.Add(p);

        int count = cmd.ExecuteNonQuery();

        if (count > 0)

        {

            MessageBox.Show("Sửa thành công!");

            LoadData();

        }

        else MessageBox.Show("Không sửa được!");

    }

    private void btnDelete_Click(object sender, EventArgs e)
    {

        if (MessageBox.Show("Bạn có chắc chắn muôn xóa bản ghi đang chọn không?", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)

        {

            SqlCommand cmd = new SqlCommand("SP_DeleteDepartment", conn);

            cmd.CommandType = CommandType.StoredProcedure;

            int id = (int)dgvDeparts.CurrentRow.Cells["clID"].Value;

            SqlParameter p = new SqlParameter("@ID", id);

            cmd.Parameters.Add(p);

            int count = cmd.ExecuteNonQuery();

            if (count > 0)

            {

                MessageBox.Show("Xóa thành công!");

                LoadData();

            }

            else MessageBox.Show("Không thể xóa bản ghi hiện thời!");

        }

    }

Bạn có thể làm được như trên! Hãy thử nhé!

Trong ví dụ trên bạn có thể thấy là công việc gọi tên thủ tục, truyền tham số là tương tự nhau ở 3 thao tác thêm, sửa, xóa. Vậy thì có cách nào để viết ngắn gọn hơn không!? Và lập trình trong mô hình 3 lớp thì thực thi thủ tục như thế nào? Tôi sẽ trình bày với các bạn ở bài viết sau, có tên: “Lập trình thực thi thủ tục SQL trong ADO.NET & Mô hình 3 lớp” – Phần 2.

Chúc bạn thành công!

Bài 1: Xây dựng cơ sở dữ liệu có tên là QLHS. Có cấu trúc gồm 2 bảng như sau:

Quản lý:

• Viết store procedure thêm, xóa sửa một lớp học vào csdl.
• Viết store procedure thêm, xóa sửa một học sinh vào csdl.