Truy vấn cơ sở dữ liệu
Khi thực hiện kết nối thành công, chúng ta cần truy vấn cơ sở dữ liệu bằng cách sử dụng các đối tượng như SqlCommand, SqlDataReader, v.v. Chi tiết về các đối tượng dùng truy vấn cơ sở dữ liệu có tham khảo chi tiết tại https://ngocminhtran.com/2017/02/22/ado-net-connected-classes-phan-2-doc-va-ghi-du-lieu/ . Trong tài liệu này chỉ đề cập cách dùng một số đối tượng và phương thức cơ bản để truy vấn cơ sở dữ liệu.
Đọc dữ liệu với SqlDataReader
Để đọc dữ liệu từ cơ sở dữ liệu chúng ta thực hiện các bước sau:
- Kết nối đến cơ sở dữ liệu dùng lớp SqlConnection và phương thức Open()
- Thực thi truy vấn đến cơ sở dữ liệu dùng lớp SqlCommand và phương thức ExecuteReader()
- Nhận và xử lý kết quả từ truy vấn dùng lớp SqlDataReader và phương thức Read()
Giả sử chúng ta cần kết nối và truy vấn đến bảng Khoa từ cơ sở dữ liệu QuanLySinhVien. Lược đồ bảng Khoa:
Kết quả nhận được từ truy vấn bảng Khoa có thể được lưu trữ trong các đối tượng và căn cứ vào lược đồ bảng Khoa, các đối tượng này là các thể hiện của lớp Khoa gồm các thuộc tính sau:
public class Khoa
{
public string MaKhoa { get; set; }
public string TenKhoa { get; set; }
}
Đoạn mã tạo kết nối, truy vấn, nhận và lưu trữ kết quả tử bảng Khoa như sau:
try
{
List<Khoa> khoa = new List<Khoa>();
string connectionString = ConfigurationManager.ConnectionStrings["QLSV"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand
( "SELECT MaKhoa, TenKhoa FROM Khoa;", connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var kh = new Khoa();
kh.MaKhoa = reader.GetString(0);
kh.TenKhoa = reader.GetString(1);
khoa.Add(kh);
}
}
}
Console.WriteLine("Mo va dong co so du lieu thanh cong.");
foreach (Khoa kh in khoa) {
string sFormat = String.Format("Ma Khoa:{0} Ten Khoa: {1}",
kh.MaKhoa,kh.TenKhoa);
Console.WriteLine(sFormat);
}
}
catch (Exception ex)
{
Console.WriteLine("Loi khi mo ket noi:" + ex.Message);
}
Minh hoạ trong ứng dụng WPF:
Trong trường hợp kết quả nhận được chứa nhiều tập kết quả con (ví dụ truy vấn cùng lúc hai bảng Khoa và SinhVien, kết quả nhận được là hai tập chứa dữ liệu từ bảng Khoa và bảng SinhVien), chúng ta gọi phương thức NextResult() từ lớp SqlDataReader để di chuyển đến tập dữ liệu kế tiếp từ kết quả nhận được. Ví dụ sau truy vấn cùng lúc hai bảng Khoa và SinhVien từ cơ sở dữ liệu QuanLySinhVien:
Tạo lớp SinhVien:
public class SinhVien
{
public string MaSV { get; set; }
public string TenSV { get; set; }
public string Email { get; set; }
public string MaKhoa { get; set; }
}
kết nối, truy vấn, nhận và lưu trữ kết quả
try
{
List<Khoa> khoa = new List<Khoa>();
List<SinhVien> sinhvien = new List<SinhVien>();
string connectionString =
ConfigurationManager.ConnectionStrings["QLSV"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand("SELECT MaKhoa, TenKhoa FROM Khoa;" +
"SELECT MaSV, TenSV, Email, MaKhoa FROM SinhVien;",
connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
//xử lý tập dữ liệu từ bảng Khoa
while (reader.Read())
{
var kh = new Khoa();
kh.MaKhoa = reader.GetString(0);
kh.TenKhoa = reader.GetString(1);
khoa.Add(kh);
}
//di chuyển đến tập dữ liệu từ bảng SinhVien
reader.NextResult();
// xử lý tập dữ liệu từ bảng SinhVien
while (reader.Read())
{
var sv = new SinhVien();
sv.MaSV = reader.GetString(0);
sv.TenSV = reader.GetString(1);
sv.Email = reader.GetString(2);
sv.MaKhoa = reader.GetString(3);
sinhvien.Add(sv);
}
}
}
Console.WriteLine("Mo va dong co so du lieu thanh cong.");
Console.WriteLine("Du lieu tu bang Khoa:");
foreach (Khoa kh in khoa) {
string sFormat = String.Format("Ma Khoa:{0} Ten Khoa: {1}",
kh.MaKhoa,kh.TenKhoa);
Console.WriteLine(sFormat);
}
Console.WriteLine("Du lieu tu bang SinhVien:");
foreach (SinhVien sv in sinhvien)
{
string sFormat = String.Format("Ma Sinh Vien:{0} Ten Sinh Vien:
{1} Email: {2} Ma Khoa: {3}",
sv.MaSV,sv.TenSV,sv.Email,sv.MaKhoa);
Console.WriteLine(sFormat);
}
}
catch (Exception ex)
{
Console.WriteLine("Loi khi mo ket noi:" + ex.Message);
}
Nếu truy vấn của chúng ta trả về giá trị đơn (ví dụ một cột ) thì có thể dùng phương thức ExecuteScalar như ví dụ sau:
public string GetPersonName(int MaSV)
{
string connectionString = ConfigurationManager.ConnectionStrings["QLSV"]?.ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(
"SELECT TenSV FROM SinhVien WHERE MaSV = @MaSV", connection))
{
command.Parameters.Add("MaSV", SqlDbType. NChar).Value = MaSV;
connection.Open();
object result = command.ExecuteScalar();
string TenSV = null;
if (result != DBNull.Value)
{
TenSV = (string)result;
}
return TenSV;
}
}
Tham số hoá
Trong ví dụ về phương thức ExecuteScalar, chúng ta đã dùng dấu @ trước MaSV trong chuỗi truy vấn. Điều này ngụ ý rằng, @MaSV giống như một biến dùng để chứa các giá trị. Giá trị thực được gán cho @MaSV nhờ phương thức Add:
command.Parameters.Add("MaSV", SqlDbType. Nchar,10).Value = MaSV;
Chúng ta hãy nhìn lại lược đồ của bảng SinhVien và để ý cột MaSV (kiểu nchar(10)):
Tham số thứ nhất là tên cột của bảng dữ liệu, tham số thứ hai là kiểu dữ liệu của cột và cột thứ ba là kích cỡ dữ liệu.
Cách sử dụng dấu @ trong câu truy vấn gọi là tham số hoá. Tham số hoá giúp cho quá trình truy vấn dữ liệu an toàn hơn. Chúng ta sẽ hiểu hơn về tham số hoá qua truy vấn thêm, xoá, sửa.
Thêm dữ liệu
Khi thêm, xoá, hay sửa dữ liệu, thay vì dùng ExecuteReader chúng ta dùng phương thức ExecuteNonQuery trả về số các hàng bị thay đổi.
Phương thức InsertData dùng để thêm dữ liệu đến bảng Khoa có thể như sau:
public static int InsertData(Khoa khoa)
{
try
{
string connectionString =
ConfigurationManager.ConnectionStrings["QLSV"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand("INSERT INTO Khoa(MaKhoa,TenKhoa)" +
"VALUES(@MaKhoa,@TenKhoa)", connection))
{
command.Parameters.Add("MaKhoa", SqlDbType.NChar, 10).Value = khoa.MaKhoa;
object dbTenKhoa = khoa.TenKhoa;
if (dbTenKhoa == null)
{
dbTenKhoa = DBNull.Value;
}
command.Parameters.Add("TenKhoa", SqlDbType.NVarChar, 50).Value = dbTenKhoa;
connection.Open();
return command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine("Loi khi mo ket noi:" + ex.Message);
return -1;
}
}
Cập nhật dữ liệu
Phương thức UpdateData dùng để cập nhật dữ liệu đến bảng Khoa có thể như sau:
public static int UpdateData(Khoa khoa)
{
try
{
string connectionString =
ConfigurationManager.ConnectionStrings["QLSV"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand("UPDATE Khoa " +
"SET TenKhoa = @TenKhoa " +
"WHERE MaKhoa = @MaKhoa",
connection))
{
command.Parameters.Add("MaKhoa", SqlDbType.NChar, 10).Value = khoa.MaKhoa;
command.Parameters.Add("TenKhoa", SqlDbType.NVarChar, 50).Value = khoa.TenKhoa;
connection.Open();
return command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine("Loi khi mo ket noi:" + ex.Message);
return -1;
}
}
Xoá dữ liệu
Phương thức DeleteData dùng để xoá dữ liệu đến bảng Khoa có thể như sau:
public static int DeleteData(Khoa khoa)
{
try
{
string connectionString =
ConfigurationManager.ConnectionStrings["QLSV"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand("DELETE FROM Khoa" +
"WHERE MaKhoa = @MaKhoa",
connection))
{
command.Parameters.Add("MaKhoa", SqlDbType.NChar, 10).Value = khoa.MaKhoa;
connection.Open();
return command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine("Loi khi mo ket noi:" + ex.Message);
return -1;
}
}
Sử dụng thủ tục lưu trú (Stored Procedures)
Cách thực hành tốt nhất khi tương tác với cơ sở dữ liệu là dùng các thủ tục lưu trú. Sử dụng các thủ tục lưu trú bằng thuộc tính CommandType của đối tượng SqlCommand. Thủ tục InsertData sau được viết lại dùng thủ tục lưu trú (cũng tên InsertData). Mã của thủ tục lưu trú:
-- ============================================= -- Author: ngocminhtran.com -- Create date: 22/2/2018 -- Description: -- ============================================= CREATE PROCEDURE InsertData -- Add the parameters for the stored procedure here @MaKhoa nchar(10) = NULL, @TenKhoa nvarchar(50) = NULL AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here INSERT INTO Khoa(MaKhoa,TenKhoa) VALUES(@MaKhoa,@TenKhoa); END
Đoạn mã phương thức InsertData:
public static int InsertData(Khoa khoa)
{
try
{
string connectionString =
ConfigurationManager.ConnectionStrings["QLSV"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand("InsertData",connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("MaKhoa", SqlDbType.NChar, 10).Value = khoa.MaKhoa;
object dbTenKhoa = khoa.TenKhoa;
if (dbTenKhoa == null)
{
dbTenKhoa = DBNull.Value;
}
command.Parameters.Add("TenKhoa", SqlDbType.NVarChar, 50).Value = dbTenKhoa;
connection.Open();
command.ExecuteNonQuery();
return 1;
}
}
catch (Exception ex)
{
Console.WriteLine("Loi khi mo ket noi:" + ex.Message);
return -1;
}
}
Trần Ngọc Minh Notes 