Bài giảng Lập trình cơ sở dữ liệu - Chương 2: Lấy dữ liệu theo cách Connected

LẤY DỮ LIỆU THEO CÁCH CONNECTEDChương 2Nội dungCác bước lấy dữ liệu Lấy 1 giá trị đơnLấy tập giá trịTruy vấn tập giá trị và lưu lạiTruy vấn có tham sốGọi Store ProcedureCác bước lấy dữ liệu3 bước cơ bản Bước 1: Chỉ rõ dữ liệu mà ứng dụng cần Viết yêu cầu bằng câu lệnh SQLSQL:SelectDML, DDL, DCLBước 2: Gởi yêu cầu đến CSDLTạo đối tượng CommandBước 3: Nhận kết quảLấy 1 giá trị đơnMột số câu lệnh SQL trả về giá trị đơnTạo đối tượng CommandCung cấp văn bản câu lệnh SQL cho CommandThực thi câu lệnhLấy 1 giá trị đơnMột số câu lệnh SQL trả về giá trị đơnSelect min() From Select max() From Select avg() From Select count() From Select sum() From string strSQL =  "Select count(*)  From TenBang";Lấy 1 giá trị đơnTạo đối tượng CommandMột đối tượng Command cần 3 thông tinĐối tượng ConnectionVăn bản câu lệnh SQLLoại câu lệnh: CommadTypeLấy dữ liệu nguyên 1 Bảng: CommadType.TableDirectCâu lệnh SQL: CommadType.TextStored Procedure: CommadType.StoredProcedureLấy 1 giá trị đơnTạo đối tượng CommandCách 1:SqlCommand cmd = new SqlCommand();cmd.Connection = conn;cmd.CommandText = strSQL;cmd.CommandType = CommandType.Text;Cách 2:SqlCommand cmd = new SqlCommand(strSQL);cmd.Connection = conn;cmd.CommandType = CommandType.Text;Lấy 1 giá trị đơnTạo đối tượng CommandCách 3:SqlCommand cmd = new SqlCommand(strSQL, conn);cmd.CommandType = CommandType.Text;Cách 4:SqlCommand cmd = conn.CreateCommand();cmd.CommandText = strSQL;cmd.CommandType = CommandType.Text;Lấy 1 giá trị đơnThực thi câu lệnhPhương thức ExecuteScalar()ExecuteScalar trả về 1 giá trị kiểu objectDùng ép kiểu để chuyển sang kiểu dữ liệu tương ứngChú ý: Phải mở kết nối trước khi gọi lệnh ExecuteScalarconn.Open();kq = (Kieu)cmd.ExecuteScalar();conn.Close();Lấy tập giá trịCâu lệnh SQL trả về tập giá trịThực thi câu lệnhĐối tượng IDataReaderThuộc tính và phương thức của IDataReaderLấy tập giá trịCâu lệnh SQL trả về tập giá trịTập giá trị: Gồm nhiều dòng hoặc nhiều cộtSelect cột1, cột2, From string strSQL =  "Select cot1,  From TenBang";Lấy tập giá trịThực thi câu lệnhPhương thức ExecuteReader()ExecuteScalar trả về 1 đối tượng IDataReader (SqlDataReader hay OleDBDataReader)IDataReader cho phép chúng ta duyệt qua các dòng trong tập dữ liệu Chú ý: Phải mở kết nối trước khi gọi lệnh ExecuteReaderKhông được đóng kết nối khi còn đang sử dụng đối tượng IDataReaderconn.Open();SqlDataReader reader=cmd.ExecuteReader();conn.Close();Lấy tập giá trịĐối tượng IDataReaderĐặc điểm của đối tượng IDataReaderChứa một luồng dữ liệu, tại mỗi thời điểm chỉ truy cập 1 dòng Read-only, Forward-onlyLấy tập giá trịThuộc tính và phương thức của IDataReaderGet[DataType](int i): GetInt32(), GetString(), Tên đầy đủ: phụ thuộc vào kiểu dữ liệu cần lấyTham số của hàm: là số thứ tự của cột (Ordinal) muốn lấy dữ liệu string GetName(int i)Lấy tên cột có số thứ tự iint GetOrdinal(string name)Lấy số thứ tự của cột có tên namebool IsDbNull(int i)Kiểm tra giá trị cột I có là null/nothing hay không Lấy tập giá trịThuộc tính và phương thức của IDataReaderobject GetValue(int i)Lấy giá trị cột I mà không quan tâm kiểu dữ liệu Hai indexer có chức năng tương tự phương thức GetValueobject dataReader[int i]object dataReader[string name]Chú ý: Nên dùng số thứ tự thay vì tên cộtLấy tập giá trịThuộc tính và phương thức của IDataReaderbool HasRowtrue: cho biết IDataReader chứa 1 hay nhiều dòng dữ liệubool Read()Đọc dòng kế tiếpTrả về true nếu còn 1 hay nhiều dòng dữ liệu Lấy tập giá trịThuộc tính và phương thức của IDataReaderconn.Open();SqlDataReader reader=cmd.ExecuteReader();if (!reader.HasRows) while (reader.Read()){ value1 = reader.GetString(0); value2 = reader.GetInt32(1);}conn.Close();Truy vấn tập giá trị và lưu lạiĐiều kiện lưu dữ liệu vào bộ nhớTrong một số trường hợp cần lưu dữ liệu Data-bindingCần nhiều thời gian để xử lý dữ liệu Cần lưu dữ liệu lại để xử lý sauTruy vấn tập giá trị và lưu lạiDùng vòng lặp foreach Để lặp qua các dòng dữ liệu Mỗi dòng dữ liệu được gọi là DbDataRecordList nhanVienList = new List();conn.Open();SqlDataReader reader=cmd.ExecuteReader();foreach (DbDataRecord rec in reader) { nhanVienList.Add(new NhanVien(rec));}conn.Close();//Sử dụng nhanVienListTRUY VẤN CÓ THAM SỐNhu cầu cần có truy vấn có tham sốChúng ta thường viết các câu lệnh SQL đề tìm kiếm các dòng dữ liệu trong CSDL theo một điều kiện nào đó. Những giá trị trong điều kiện do người dùng nhập vàoVí dụ: Tìm kiếm nhân viên có trong CSDL không để 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ận xétBất kỳ những gì nhập vào txtPass, txtUser đều được đặt vào trong câu SQLHãy nhập vào username: ' or ('1'='1') or '1'='1 hayHãy nhập vào password: ' or '1'='1Nhu cầu cần có truy vấn có tham sốTruy vấn có tham sốNhững nơi trong câu SQL cần dữ liệu người dùng, chúng ta sẽ tạo những tham số tại những nơi đó (gọi là 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ố3 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ốBước 2: Khai báo đối tượng SqlParameter, gán giá trị tương ứngBước 3: Gán đối tượng SqlParameter cho thuộc tính Parameters của đối tượng SqlCommandCá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ốNhững nơi cần dữ liệu người dùng chúng ta tạo một parameter placeholderTên parameter placeholder: @tenstrSQL = "select where cot1 = @ten1 "Ví dụ:Cú pháp:strSQL = "select * from NhanVien where " + “manv=@user and matkhau=@pass";Parameter placeholderCá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 ứngMỗi parameter placeholder tương ứng với một đối tượng SqlParameterSqlParameter 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ú pháp: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ố: SqlDbTypeSố TinyInt = ByteSmallInt = Int16Int = Int32BigInt = Int64Real = SingleFloat = DoubleDecimal = DecimalMoney = DecimalChuỗiChar = StringNChar = StringText = StringNText = StringNVarChar = StringKiểu khácBit = BooleanDateTime = DateTimeCác bước tạo truy vấn có tham sốMột số property của SqlParameterstring param.ParameterNameSqlDbType param.SqlDbTypeobject param.Valueint param.SizeCá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 SqlCommandSqlCommand 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 PROCEDUREGọi Stored ProcedureKhái niệm Stored Procedure Định nghĩa Stored Procedure – SP: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 SPSQL Server biên dịch Stored ProcedureGọ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ơnGọi Stored ProcedureTạo Stored Procedure – SP Cú pháp tạo SPCREATE PROCEDURE usp_TenSP/* ( @parameter1 datatype = defaultvalue, @parameter2 datatype OUTPUT )*/AS /* SET NOCOUNT ON */RETURNGọi Stored ProcedureGọi SP không có tham số2 bước gọi SP không tham số:Bước 1: Chỉ rõ tên SP trong thuộc tính CommandText của đối tượng CommandSqlCommand 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 Procedurecmd.CommandType = CommandType.StoredProcedureGọi Stored ProcedureGọi SP không có tham sốVí dụ:create procedure usp_LayDSNhanVienas select * from NhanVien returnstring 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 ProcedureGọ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 Bước 3: Thêm đối tượng tham số SqlParameter vào đối tượng command (Tên tham số giống tên tham số trong SP)cmd.Parameters.Add(“@TenThamSo”, kieu, kichthuoc);cmd.Parameters[“@TenThamSo”].value = giatri;Gọi Stored ProcedureGọi SP có tham số input (vào)Ví dụ: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;create procedure usp_TimNhanVienTheoTen (@ten nvarchar(20))as select * from NhanVien  where ten = @ten returnGọi Stored ProcedureGọi SP có tham số output (ra)Tương tự SP có tham số inputNhưng bước 3 phải chỉ rõ tham số nào là output bằng thuộc tính Direction của đối tượng SqlParameterSqlParameter param;param.Direction = ParameterDirection.OutputGọi Stored ProcedureGọ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=@manvreturnGọi Stored ProcedureGọ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();Tóm tắt chương 2