ADO.NET

1. ADO.NET Seminar Hệ Quản trị CSDL – Triển khai ứng dụng trên .NET

2. Nội dung • ADO.NET • Kiến trúc ADO.NET • Đối tượng SqlConnection • Đối tượng SqlCommand & SqlParameter • Đối tượng SqlDataReader • Đối tượng DataSet & SqlDataAdapter • Sử dụng stored procedure

3. Nội dung • ADO.NET • Kiến trúc ADO.NET • Đối tượng SqlConnection • Đối tượng SqlCommand & SqlParameter • Đối tượng SqlDataReader • Đối tượng DataSet & SqlDataAdapter • Sử dụng stored procedure

4. Kiến trúc ADO.NET .NET Data Provider ADO.NET SQL Server Oracle OLE DB OLE DB Relational Data Application ODBC ODBC Non-Relational Data

5. ADO.NET Namespace • System.Data System.Data.Common • System.Data.SqlClientSystem.Data.OleDB • System.Data.SqlTypes • System.XMLSystem.XML.Schema

6. Sử dụng ADO.NET cho Ứng dụng • Nếu ứng dụng .NET cần truy xuất dữ liệu  Phải khai báo namespace ADO.NET tương ứng với dữ liệu cho ứng dụng • Ví dụ: • using System.Data; • using System.Data.OleDb; • using System.Data.SqlClient;

7. Nội dung • ADO.NET • Kiến trúc ADO.NET • Đối tượng SqlConnection • Đối tượng SqlCommand & SqlParameter • Đối tượng SqlDataReader • Đối tượng DataSet & SqlDataAdapter • Sử dụng stored procedure

8. Nội dung • ADO.NET • Kiến trúc ADO.NET • Đối tượng SqlConnection • Đối tượng SqlCommand & SqlParameter • Đối tượng SqlDataReader • Đối tượng DataSet & SqlDataAdapter • Sử dụng stored procedure

9. Đối tượng SqlConnection • Dùng để tạo kết nối đến các CSDL Sql Server • Thuộc tính ConnectionString : Lưu chuỗi kết nối đến hệ quản trị CSDL SQL Server • Kết nối được mở thông qua 2 cách • Qua phương thức Open() • Qua việc thực thi đối tượng SqlDataAdapter • Ngắt kết nối thông qua phương thức Close() • Thuộc tính .State : cho biết tình trạng của kết nối • Quản lý các giao tác của kết nối này

10. Các tham số của Sqlconnection

11. Các phương thức:

12. Connection String • using System.Data.SqlClient;string sConnectionString = "InitialCatalog=Northwind; Data Source=localhost;user=sa;password=sa;”; • SqlConnection cnn = newSqlConnection(); • cnn.ConnectionString = sConnectionString; • cnn.Open(); • cnn.Close();

13. Đối tượng SqlConnection - Demo

14. Nội dung • ADO.NET • Mô hình sử dụng ADO.NET với Phần mềm • Đối tượng SqlConnection • Đối tượng SqlCommand & SqlParameter • Đối tượng SqlDataReader • Đối tượng DataSet & SqlDataAdapter • Sử dụng stored procedure

15. Đối tượng SqlCommand • Truyền thông tin của một câu truy vấn cho đối tượng SqlConnection • Hỗ trợ tham số vào, tham số ra, và giá trị trả về • Kết quả trả về được lưu trữ dưới dạng luồng thông qua 2 đối tượng • DataReader • DataSet thông qua một đối tượng SqlDataAdapter

16. Đối tượng SqlCommand string str= @"insert into Category(Name) values (N'Thể thao')"; SqlCommand cmd = new SqlCommand(insertString,conn); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery();

17. Các phương thức - SqlCommand

18. SqlParameter • Đối tượng tham số truyền vào cho đối tượng SqlCommand • Có các thuộc tính sau :

19. Truyền tham số vào sqlcommand Khai báo đối tượng command với tham số SqlCommand cmd = new SqlCommand( "select * from Category where CategoryID = @CategoryID ", conn); Định nghĩa các tham số được dùng ở đối tượng command: SqlParameter param = new SqlParameter(); param.ParameterName = "@CategoryID "; param.Value = categoryID; Thêm mới tham số vào đối tượng command cmd.Parameters.Add(param);

20. Ví dụ 1: SqlCommand cmd = new SqlCommand(); cmd.CommandText = "INSERT INTO Category(Name) VALUES(@Name)"; SqlParameter param = new SqlParameter(); param.ParameterName = "@Name"; param.Value = TextBox.Text; cmd.Parameters.Add(param); cmd.Connection = conn; – //Thực thi câu lệnh và đóng kết nối. cmd.ExecuteNonQuery(); conn.Close();

21. Ví dụ 2: truyền nhiều tham số String CnStr = "Server=localhost;Database=DBTest;UID=sa;PWD=sa"; SqlConnection = new SqlConnection(CnStr); String cmdText = "INSERT INTO Student(Name,Address, Age) VALUES(@Name, @Address, @Age)"; SqlCommand cmd = new SqlCommand(cmdText,Cn); cmd.Parameters.Add(“@Name", SqlDbType.NVarChar); cmd.Parameters.Add(“@Address”, SqlDbType.NVarChar); cmd.Parameters.Add(“@Age”, SqlDbType.Int) cmd.Parameters[“@Name”].Value = txtName.Text; cmd.Parameters[“@Address”].Value = txtAddress.Text; cmd.Parameters[“@Age”].Value = ConVert.ToInt32(txtAge.Text); Cn.Open(); – //Thực thi câu lệnh và đóng kết nối. cmd.ExecuteNonQuery(); Cn.Close();

22. Nội dung • ADO.NET • Mô hình sử dụng ADO.NET với Phần mềm • Đối tượng SqlConnection • Đối tượng SqlCommand & SqlParameter • Đối tượng SqlDataReader • Đối tượng DataSet & SqlDataAdapter • Sử dụng stored procedure

23. Đối tượng SqlDataReader • Có tốc độ xử lý dữ liệu nhanh • Chỉ lưu lại 1 record kết quả trong bộ nhớ với mỗi lần truy xuất (buffer mạng) • Không thực hiện các thao tác phức tạp trên DataReader (Sắp xếp, bỏ qua record, ..) • Chỉ đọc, không thay đổi dữ liệu

24. Đối tượng SqlDataReader • Một số thuộc tính & hàm :

25. Ví dụ: SqlConnection cn = new SqlConnection(“server=localhost; database=Northwind; user id=sa; password=sa”); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = “select * from Products”; cmd.CommandType = CommandType.Text; cn.Open(); SqlDataReader dr = cmd.executeReader(); while (dr.Read()) { MessageBox.Show(dr[“ProductID”].ToString()); } dr.Close(); cn.Close();

26. Nội dung • ADO.NET • Mô hình sử dụng ADO.NET với Phần mềm • Đối tượng SqlConnection • Đối tượng SqlCommand & SqlParameter • Đối tượng SqlDataReader • Đối tượng DataSet & SqlDataAdapter • Sử dụng stored procedure

27. Đối tượng DataSet • Lưu trữ dữ liệu của DB trong bộ nhớ • Mọi thao tác thay đổi dữ liệu được thực hiện trên DataSet, không làm ảnh hưởng đến DB • Theo vết các thay đổi trên dữ liệu và có thể cập nhật dữ liệu ngược vào DB thông qua SqlDataAdapter • Gồm các đối tượng : DataTable, DataRelationship, Constraint DataSet DataTable DataTable DataRow DataColumn

28. Đối tượng DataSet • Một số thuộc tính và phương thức

29. Đối tượng SqlDataAdapter • Cung cấp các phương thức và thuộc tính để lấy và lưu dữ liệu giữa DataSet và CSDL • Sử dụng DataSet để lưu trữ dữ liệu, đồng thời, cho cập nhật dữ liệu ngược lại vào Database

30. Đối tượng SqlDataAdapter DataSet Data source DataAdapter DataTable Fill Update DataAdapter DataTable Fill Update

31. Đối tượng SqlDataAdapter • Một số thuộc tính và phương thức

32. Ví dụ: SqlConnection cnn = new SqlConnection(“server=localhost; database=Northwind; user id=sa; password=sa”); SqlDataAdapter da = new SqlDataAdapter(“select * from Products”, cnn) DataSet ds = new DataSet(); da.Fill(ds); foreach (DataRow row in ds.Tables[0].Rows) if (row[“ProductID”].ToString() == “Nguyễn Văn A”) row.Delete(); SqlCommandBuilder cmd = new SqlCommandBuilder(da); da.Update(ds);

33. Nội dung • ADO.NET • Mô hình sử dụng ADO.NET với Phần mềm • Đối tượng SqlConnection • Đối tượng SqlCommand & SqlParameter • Đối tượng SqlDataReader • Đối tượng DataSet & SqlDataAdapter • Sử dụng stored procedure

34. Sử dụng stored procedure để truy xuất dữ liệu • Tạo stored procedure trong sqlServer • Tạo Sqlcommand với giá trị command text là tên Stored procedure • SqlCommand cmd = new SqlCommand(“Ten stored”,cn) • Gán commandType: • cmd.CommandType = CommandType.StoredProcedure • Truyền các tham số vào command nếu StoredProcedure có yêu cầu • Thực thi command

35. Ví dụ 1: Stored procedure trả về kết quả của lệnh Select • string procName = "sp_LoadAccType"; • string CnStr = "Server=.;Database=AgribankDB;UID=sa;PWD=hoangvy"; • SqlConnection Cn = new SqlConnection(CnStr); • SqlDataAdapter da = new SqlDataAdapter(procName, Cn); • DataTable dt = new DataTable(); • da.Fill(dt); • return dt;

36. Ví dụ 2: Stored procedure trả về kết quả của lệnh Insert/Delete/Update • string CnStr = "Server=.;Database=AgriBankDB;UID=sa;PWD=hoangvy"; • SqlConnection Cn = new SqlConnection(CnStr); • string procName = "sp_InsertAccTypes"; • SqlCommand cmd = new SqlCommand(procName, Cn); • cmd.CommandType = CommandType.StoredProcedure; • cmd.Parameters.Add("@AccTypeName", SqlDbType.NVarChar); • cmd.Parameters.Add("@Note", SqlDbType.NVarChar); • cmd.Parameters["@AccTypeName"].Value = txtAccTypeName.Text; • cmd.Parameters["@Note"].Value = txtNote.Text; • cmd.Connection.Open(); • cmd.ExecuteNonQuery(); • cmd.Connection.Close();

37. Ví dụ 3: Stored procedure có dùng tham số output • CREATE PROC [dbo].[sp_InsertAccTypes] • @AccTypeName nvarchar(50), • @Note nvarchar(255), • @AccID numeric output, • @err nvarchar(255) output • AS • BEGIN TRAN • INSERT INTO AccTypes(AccTypeName,Note) VALUES(@AccTypeName,@Note) • SET @AccID = @@IDENTITY • SET @err = '' • COMMIT TRAN

38. string CnStr = "Server=.;Database=AgriBankDB;UID=sa;PWD=hoangvy"; SqlConnection Cn = new SqlConnection(CnStr); string procName = "sp_InsertAccTypes"; SqlCommand cmd = new SqlCommand(procName, Cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@AccTypeName", SqlDbType.NVarChar); cmd.Parameters.Add("@Note", SqlDbType.NVarChar); cmd.Parameters.Add("@AccID", SqlDbType.Int).Direction = ParameterDirection.Output; cmd.Parameters["@AccTypeName"].Value = txtAccTypeName.Text; cmd.Parameters["@Note"].Value = txtNote.Text; cmd.Parameters["@AccID"].Value = -1 cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Connection.Close(); string err = cmd.Parameters["@err"].Value.ToString(); int AccId = (int)cmd.Parameters["@AccId"].Value.;

39. Ví dụ 4: Stored procedure có giá trị trả về • Create Proc TinhTong2 • @soA int, • @soB int • AS • BEGIN • declare @c int • set @c= @soA + @SoB • return @c • END

40. Ví dụ 4: Stored procedure có giá trị trả về string procName = "TinhTong2"; string CnStr = "Server=.;Database=AgribankDB;UID=sa;PWD=hoangvy"; SqlConnection Cn = new SqlConnection(CnStr); SqlCommand cmd = new SqlCommand(procName, Cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@SoA", SqlDbType.Int); cmd.Parameters.Add("@SoB", SqlDbType.Int); cmd.Parameters["@SoA"].Value = 2; cmd.Parameters["@SoB"].Value = 3; SqlParameter p = new SqlParameter("@c", SqlDbType.Int); p.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(p); Cn.Open(); cmd.ExecuteNonQuery(); Cn.Close(); MessageBox.Show(cmd.Parameters["@c"].Value.ToString());

41. Demo load dữ liệu lên gridview • Cách 1: Viết store ở hệ quản trị + gọi thực hiện store Public DataTable LoadAccTypes() { string procName = "sp_DsLoaiTaiKhoan"; string CnStr = "Server=.;Database=DBTest;UID=sa;PWD=sa"; SqlConnection Cn = new SqlConnection(CnStr); SqlDataAdapter da = new SqlDataAdapter(procName, Cn); DataTable dt = new DataSet(); da.Fill(dt); return dt; } DataTabledtAccTypes = LoadAccTypes(); if (dtAccTypes != null && dtAccTypes.Rows.Count > 0) { grdLoaiTK.DataSource = dtAccTypes; } CREATE PROC sp_DsLoaiTaiKhoan AS BEGIN TRAN SELECT * FROM LoaiTaiKhoan COMMIT TRAN GO

42. Public bool InsertAccTypes() { string CnStr = "Server=.;Database=DBTest;UID=sa;PWD=sa"; SqlConnection Cn = new SqlConnection(CnStr); string procName = "sp_ InsertAccTypes"; SqlCommand cmd = new SqlCommand(procName, Cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@AccTypeName“, SqlDbType. NVarchar); cmd.Parameters.Add("@Note", SqlDbType.NVarchar); cmd.Parameters.Add("@err", SqlDbType.NVarChar).Direction = ParameterDirection.Output; cmd.Parameters[“@AccTypeName”].Value = txtAccTypeName.Text; cmd.Parameters[“@ Note”].Value = txtNote.Text; cmd.Parameters[“@ err”].Size = 255; cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Connection.Close(); err = cmd.Parameters["@err"].Value.ToString(); if (err == "") return true; return false; } Ví dụ 2: CREATE PROC sp_InsertAccTypes @AccTypeID @AccTypeName nvarchar(50), @Note nvarchar(255), @err nvarchar(255) OUTPUT AS BEGIN TRAN UPDATE AccTypes SET AccTypeName = @AccTypeName, Note = @Note WHERE accTypeID = @accTypeID SET @err = '' COMMIT TRAN GO 43

43. Cám ơn các bạn đã dự buổi seminar.