ADO.NET Mô Hình Kết Nối Với SQL Server – Phần 2.1.2

Phần 2: CÁCH THỨC SỬ DỤNG ADO.NET
 

2.1.2 Mô Hình Kết Nối - Sử Dụng Stored Procedure

Ở phần trước (2.1.1) các bạn đã biết được cách connect ứng dụng tới CSDL. Hôm này, chúng ta lặp lại các thao tác đó, nhưng các câu lệnh SQL sẽ được đặt trên SQL Server. Ta gọi đó là các thủ tục Stored Procedure và ở trong codebehind (C#) ta chỉ cần gọi đến nó SQL SV sẽ thực hiện các lệnh thủ tục này.

Nhưng trước hết, ta sẽ lướt qua lớp SqlCommand và một vài phương thức cơ bản của nó.

- Class SqlCommand có nhiệm vụ gửi các lệnh thao tác tới Server và gọi các phương thức cụ thể để đưa ra yêu cầu cần thiết cho lệnh sql tới Server. Bạn cần biết 3 phương thức chính, hay sử dụng là:

• ExecuteNonQuery(): Thực thi một câu lệnh SQL không cần trả về tập kết quả như INSERT, UPDATE, DELETE, CREATE, ALTER, DROP. Giá trị trả về của hàm này chính là số dòng bị ảnh hưởng khi thực thi truy vấn.


• ExecuteScalar():  Thực thi lệnh câu lệnh SQL và trả về giá trị cột đầu tiên của hàng đầu tiên, những giá trị thừa bị loại bỏ. Phương thức này thường được sử dụng khi thực thi một lệnh SELECT có sử dụng các hàm kết hợp như COUNT (), SUM () …


• ExecuteReader():  Thực hiện một truy vấn SELECT và trả về một đối tượng DataReader.


• Dispose(): Giải phóng tất cả tài nguyên đã chiếm giữ.

Xem thêm tại MSDN

- Class SqlReader có nhiệm vụ đọc các record trong table theo thứ tự.

• Read(): Chuyển DataReader đến dòng tiếp theo trong tập kết quả và đọc dữ liệu trên dòng đó. Trả về giá trị False khi không còn dòng để đọc.


• GetInt32(), GetChar(), GetDateTime(), Get…(): Lấy giá trị của một cột (theo chỉ số) của hàng hiện tại, trả về kiểu dữ liệu được quy định như tên các phương thức.


• Close(): Đóng DataReader

a.) Tạo một stored procedure

Bây giờ quay lại vấn đề chính, tạo một Stored Procedure. Bạn hãy mở SQL Server lên, vẫn dùng mẫu CSDL của ví dụ (2.1.1) và làm như sau:

Databases -> MyDataDemo -> Programmability -> Chuột phải lên Stored Procedure tạo mới.

 Sau khi chọn new, một trang biên tập query mở ra cho bạn. Xóa hết nội dung bên trong và gõ mới mã như bên dưới. Cuối cùng nhấn Execute để thực thi và lưu thủ tục (chú ý khi bạn nhấn Execute thì nếu lệnh sql của bạn không lỗi, nó sẽ được lưu hoàn tất. Mặc khác nếu nhấn Save thì file biên tập sẽ được lưu trên máy của bạn, chúng ta không cần quan tấm vấn đề này), bạn có thể refresh lại SQL Server để coi thủ tục của mình đã lưu chưa. Để biên tập tiếp thủ tục khác, gõ mã khác vào lại và tiếp tục nhấn Execute.

Kích chuột phải vào thủ tục vừa tạo chọn modify thì bạn sẽ thấy SQL SV đã generate cho bạn mã đầy đủ đúng chuẩn của nó. Bạn không cần quan tâm cái này.

Get tất cả dữ liệu từ table SinhVien:

CREATE PROCEDURE SinhVien_GetAll 
AS
SELECT * FROM SinhVien

 

Get tất cả dữ liệu từ table Khoa:

CREATE PROCEDURE Khoa_GetAll
AS
SELECT * FROM Khoa

 

 Insert dữ liệu:

CREATE PROCEDURE SinhVien_AddNew
@MaSV  nchar(10),
@HoTenSV nvarchar(50),
@NgaySinh  date,
@QueQuan  nvarchar(50),
@MaKhoa  nchar(10) 
AS
INSERT INTO SinhVien 
(MaSV, HoTenSV, NgaySinh, QueQuan,MaKhoa) 
VALUES (@MaSV, @HoTenSV, @NgaySinh, @QueQuan,@MaKhoa);

-- SET @MaSV = @@IDENTITY (Nếu bạn muốn mã nhảy số tự động)

 

Update dữ liệu:

CREATE PROCEDURE SinhVien_UpdateAgain
@MaSV  nchar(10),
@HoTenSV nvarchar(50),
@NgaySinh  date,
@QueQuan  nvarchar(50),
@MaKhoa  nchar(10) 
AS
UPDATE SinhVien 
SET HoTenSV = @HoTenSV,
NgaySinh = @NgaySinh,
QueQuan = @QueQuan,
MaKhoa=@MaKhoa
WHERE MaSV = @MaSV

 

Delete dữ liệu:

CREATE PROCEDURE SinhVien_DeleteForever
@MaSV  nchar(10)
AS
DELETE SinhVien WHERE MaSV = @MaSV

 

Tiếp tục tạo thêm một Stored Procedure để đếm số dòng trong table SinhVien

CREATE PROCEDURE SinhVien_CountAll
AS 
SELECT COUNT(MaSV) FROM SinhVien

 

Như vậy là các thủ tục để thao tác cơ bản nhất được viết xong. Các bạn cứ để nó đấy, giờ quay lại Visual Studio mở project (2.1.1) lên và thay đổi lại như sau

b.) Sử dụng các Stored Procedure

Để đơn giản hóa các bước sử dụng codebehind (C#) chúng ta sẽ bỏ các các Exception, các hình thức bẫy lỗi.

Hàm kết nối các bạn hãy giữ nguyên như ở bài trước.

Hàm get tất cả dữ liệu từ bảng SinhVien

/// <summary>
        /// Hàm đọc dữ liệu trên CSDL và thông qua đó lấy dữ liệu về datagridview mà ta đã tạo sẵn
        /// Sử dụng lớp SqlCommand gửi lệnh sql tới server
        /// Sử dụng lớp SqlDataReader để đọc từng hàng (Row) trên CSDL. 
        /// </summary>
        private void GetData()
        {
            _cmd = new SqlCommand("SinhVien_GetAll", _con)
            {
                CommandType = CommandType.StoredProcedure
            };
            _dr = _cmd.ExecuteReader();
            while (_dr.Read())
            {
                var masv = (string)_dr["MaSV"];
                var hoten = (string)_dr["HoTenSV"];
                var ngaysinh = _dr["NgaySinh"];
                var quequan = (string)_dr["QueQuan"];
                var makhoa = (string)_dr["MaKhoa"];
                dataGridView.Rows.Add(masv, hoten, ngaysinh.ToString(), quequan, makhoa);
            }
            _dr.Close();
            _cmd.Dispose();

        }

Hàm Insert

/// <summary>
        /// Hàm thêm mới dữ liệu vào CSDL
        /// </summary>
        private void InsertData()
        {
            //var sqlDate = dtpNgaysinh.Value.ToString("yyyy-MM-dd hh:mm:ss");

            /*Có thể viết như thế này*/
            //_cmd = _con.CreateCommand();
            //_cmd.CommandType = CommandType.StoredProcedure;
            //_cmd.CommandText = "SinhVien_AddNew";

            _cmd = new SqlCommand("SinhVien_AddNew", _con)
            {
                CommandType = CommandType.StoredProcedure                                           
            };
            _cmd.Parameters.Add("@MaSV", SqlDbType.NChar, 10).Value = txtMasv.Text; 
            _cmd.Parameters.Add("@HoTenSV", SqlDbType.NVarChar, 50).Value = txtHoten.Text; 
            _cmd.Parameters.Add("@NgaySinh", SqlDbType.Date).Value = dtpNgaysinh.Text;
            _cmd.Parameters.Add("@QueQuan", SqlDbType.NVarChar, 50).Value = txtQuequan.Text;
            _cmd.Parameters.Add("@MaKhoa", SqlDbType.NChar, 10).Value = cbbMakhoa.SelectedValue; 
            _cmd.ExecuteNonQuery(); 
            _cmd.Dispose();

        }

Hàm Update

/// <summary>
        /// Hàm Cập nhật, sửa lại một dòng dữ liệu. Lưu ý khóa chính không thể được cập nhật
        /// </summary>
        private void UpdateData()
        {
            //var sqlDate = dtpNgaysinh.Value.ToString("yyyy-MM-dd hh:mm:ss");
            _cmd = new SqlCommand("SinhVien_UpdateAgain", _con)
            {
                CommandType = CommandType.StoredProcedure
            };
            _cmd.Parameters.Add("@MaSV", SqlDbType.NChar, 10).Value = txtMasv.Text;
            _cmd.Parameters.Add("@HoTenSV", SqlDbType.NVarChar, 50).Value = txtHoten.Text;
            _cmd.Parameters.Add("@NgaySinh", SqlDbType.Date).Value = dtpNgaysinh.Text;
            _cmd.Parameters.Add("@QueQuan", SqlDbType.NVarChar, 50).Value = txtQuequan.Text;
            _cmd.Parameters.Add("@MaKhoa", SqlDbType.NChar, 10).Value = cbbMakhoa.SelectedValue; 
            _cmd.ExecuteNonQuery();
            _cmd.Dispose();

        }

Hàm Delete

 /// <summary>
        /// Tương tự, đây là hàm xóa 1 hàng dữ liệu. 
        /// </summary>
        private void DeleteData()
        {

            _cmd = new SqlCommand("SinhVien_DeleteForever", _con)
            {
                CommandType = CommandType.StoredProcedure
            };
            _cmd.Parameters.Add("@MaSV", SqlDbType.NChar, 10).Value = txtMasv.Text;
            _cmd.ExecuteNonQuery();

            _cmd.Dispose();

        }

Hàm Count

/// <summary>
        /// Hàm đếm số dòng dữ liệu trong bảng Sinh Viên
        /// </summary>
        private void CountAllSinhVien()
        {
            _cmd = new SqlCommand("SinhVien_CountAll", _con)
            {
                CommandType = CommandType.StoredProcedure
            };
            // Ở đây chúng ta sử dụng ExceteScalar
            lblTongsinhvien.Text = Convert.ToString(_cmd.ExecuteScalar());
            _cmd.Dispose();

        }

 

---

Hãy tải về mã nguồn của ví dụ 2.1.2 ở đầu bài viết
Nguồn: Tự Học IT Online - http://tuhocit.net