Bài giảng Lập trình trên Windows: Chương 5.4

Lập trình Windows Chương 5.4. Truy vấn có tham số

Nội dung

• Nhu cầu cần có truy vấn có tham số

• Các bước tạo truy vấn có tham số

• Gọi Stored Procedure

Nhu cầu cần có truy vấn có tham số

• Tìm kiếm các dòng dữ liệu trong CSDL theo một điều kiện nào đó

do người dùng nhập vào

• Ví dụ: Tìm kiếm xem nhân viên có trong CSDL để cho phép đăng

nhập vào chương trình

string strUser, strPass; strUser = txtUser.Text; strPass = txtPass.Text;

string strSQL; strSQL = "select * from NhanVien where " + "username='" + strUser + "' and " + "password='" + strPass + "'";

Nhu cầu cần có truy vấn có tham số

• Những vị trí lệnh SQL cần dữ liệu người dùng (parameter

placeholders)

• Bất kỳ dữ liệu gì được truyền vào tham số sẽ được đối xử như

là dữ liệu thuần túy (không phải là 1 câu lệnh SQL)

Các bước tạo truy vấn có tham số

• Bước 1: Xây dựng câu SQL có tham số

• Bước 2: Khai báo đối tượng SqlParameter, gán giá trị tương ứng

• Bước 3: Gán đối tượng SqlParameter cho thuộc tính Parameters

của đối tượng SqlCommand

Các bước tạo truy vấn có tham số

• Bước 1:Xây dựng văn bản câu SQL có tham số

placeholder

• Những nơi cần dữ liệu người dùng chúng ta tạo một parameter

• Tên parameter placeholder: @ten

Parameter placeholder

§Cú pháp:

strSQL = "select … where cot1 = @ten1 …"

§Ví dụ:

strSQL = "select * from NhanVien where " + “manv=@user and matkhau=@pass";

Các bước tạo truy vấn có tham số

• Bước 2: Khai báo đối tượng SqlParameter, gán giá trị tương

ứng

SqlParameter

• Mỗi parameter placeholder tương ứng với một đối tượng

§Cú pháp:

SqlParameter param;

param = new SqlParameter(); param = new SqlParameter(string paramName, object value); param = new SqlParameter(string paramName, SqlDbType dbType); param = new SqlParameter(string paramName, SqlDbType dbType, int size);

Các bước tạo truy vấn có tham số

• Một số loại kiểu dữ liệu của tham số: SqlDbType

• Chuỗi

• TinyInt = Byte

• SmallInt = Int16

•

Int = Int32

• BigInt = Int64

• Real = Single

• Float = Double

– Char = String – NChar = String – Text = String – NText = String – NVarChar = String

• Decimal = Decimal

• Money = Decimal

• Kiểu khác

• Số

– Bit = Boolean – DateTime = DateTime

Các bước tạo truy vấn có tham số

• Một số property của SqlParameter

• string param.ParameterName

• SqlDbType param.SqlDbType

• object param.Value

• int param.Size

Các bước tạo truy vấn có tham số

• Ví dụ

SqlParameter paramMaNV, paramMatKhau;

paramMaNV = new SqlParameter(); paramMaNV.SqlDbType = SqlDbType.VarChar; paramMaNV.ParameterName = "@user"; paramMaNV.Value = strMaNV;

paramMatKhau = new SqlParameter(); paramMatKhau.SqlDbType = SqlDbType.VarChar; paramMatKhau.ParameterName = "@pass"; paramMatKhau.Value = strMatKhau;

Các bước tạo truy vấn có tham số

• Bước 3: Gán đối tượng SqlParameter cho thuộc tính

Parameters của đối tượng SqlCommand

SqlCommand cmd; SqlParameter param; … cmd.Parameters.Add(param); cmd.Parameters.Add(string paramName, SqlDbType dbType); cmd.Parameters.Add(string paramName, SqlDbType dbType, int size);

Gọi Stored Procedure

• Khái niệm Stored Procedure – SP

• Tạo Stored Procedure

• Gọi SP không có tham số

• Gọi SP có tham số input (vào)

• Gọi SP có tham số output (ra)

Gọi Stored Procedure Khái niệm Stored Procedure

• SP là 1 thủ tục được định nghĩa trước và được lưu trữ trong

CSDL để người dùng sẵn sàng dùng

• Đặc điểm của SP

• SQL Server biên dịch Stored Procedure

• Gọi SP chạy sẽ nhanh hơn việc xây dựng câu truy vấn trong code

• SP bảo mật hơn

Gọi Stored Procedure Tạo Stored Procedure – SP

• Cú pháp tạo SP

CREATE PROCEDURE usp_TenSP /* ( @parameter1 datatype = defaultvalue, @parameter2 datatype OUTPUT ) / AS / SET NOCOUNT ON */ RETURN

Gọi Stored Procedure Gọi SP không có tham số

• 2 bước gọi SP không tham số:

Command

• Bước 1: Chỉ rõ tên SP trong thuộc tính CommandText của đối tượng

SqlCommand cmd; … cmd.CommandText = “usp_TenStoredProcedure”

• Bước 2: Chỉ cho đối tượng Command biết chúng ta đang muốn thực thi 1 Stored Procedure

cmd.CommandType = CommandType.StoredProcedure

Gọi Stored Procedure Gọi SP không có tham số

• Ví dụ:

create procedure usp_LayDSNhanVien as

select * from NhanVien return

string strSP = “usp_LayDSNhanVien”; SqlCommand cmd = new SqlCommand(strSP, conn); cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter daNhanVien = new SqlDataAdapter(cmd); DataTable dtNhanVien = new DataTable(); daNhanVien.Fill(dtNhanVien);

// Cách 2 //conn.Open(); //SqlDataReader dr = cmd.ExecuteReader();

Gọi Stored Procedure Gọi SP có tham số input (vào)

• 3 bước gọi SP có tham số input:

• Bước 1, 2: giống như trước

command (Tên tham số giống tên tham số trong SP)

• Bước 3: Thêm đối tượng tham số SqlParameter vào đối tượng

cmd.Parameters.Add(“@TenThamSo”, kieu, kichthuoc); cmd.Parameters[“@TenThamSo”].value = giatri;

Gọi Stored Procedure Gọi SP có tham số input (vào)

• Ví dụ:

create procedure usp_TimNhanVienTheoTen

(@ten nvarchar(20))

as

select * from NhanVien where ten = @ten return

string sqlSP = "usp_TimNhanVienTheoTen";

conn = new SqlConnection(strConn);

SqlCommand cmd = new SqlCommand(sqlSP, conn); cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@Ten", SqlDbType.NVarChar, 20); cmd.Parameters["@Ten"].Value=giatri;

Gọi Stored Procedure Gọi SP có tham số output (ra)

• Tương tự SP có tham số input

Direction của đối tượng SqlParameter

• Nhưng bước 3 phải chỉ rõ tham số nào là output bằng thuộc tính

SqlParameter param; … param.Direction = ParameterDirection.Output

Gọi Stored Procedure Gọi SP có tham số output (ra)

• Ví dụ:

create procedure usp_TimTenNhanVienTheoMa

(@manv nvarchar(20), @ten nvarchar(20) output)

as

select @ten=ten from NhanVien where manv=@manv

return

Gọi Stored Procedure Gọi SP có tham số output (ra)

string sqlSP = "usp_TimTenNhanVienTheoMa";

conn = new SqlConnection(strConn); SqlCommand cmd = new SqlCommand(sqlSP, conn); cmd.CommandType = CommandType.StoredProcedure;

SqlParameter paramTen = new SqlParameter(); paramTen.Direction = ParameterDirection.Output; paramTen.ParameterName ="@ten"; paramTen.SqlDbType = SqlDbType.NVarChar; paramTen.Size = 20; cmd.Parameters.Add(paramTen);

cmd.Parameters.Add("@MaNV", SqlDbType.NVarChar, 20); cmd.Parameters["@MaNV"].Value = "NV001";

conn.Open(); cmd.ExecuteReader(); conn.Close(); string ten = cmd.Parameters["@ten"].Value.ToString();

Q&A

22 22

Bài giảng Lập trình trên Windows: Chương 5.4 - Trần Minh Thái