Mô hình kết nối cơ sở dữ liệu ADO.NET

MÔ HÌNH KẾT NỐI CƠ SỞ DỮ LIỆU ADO.NET I. Giới thiệu về ADO.NET - ADO.NET là một trong các lớp nằm trong bộ thư viện lớp cơ sở của NET Framework để cho phép các ứng dụng Windows(như c#, VB.net) hay các ứng dụng Web(như ASP.Net) thao tác dễ dàng với các nguồn dữ liệu. - Mục tiêu chính của ADO.NET là: o Cung cấp các lớp để thao tác dữ liệu trong cả hai môi trường là phi kết nối (Disconnected data) và kết nối (Connected data). o Tích hợp chặt chẽ với XML (Extensible Markup Language) o Tương tác với nhiều nguồn dữ liệu thông qua mô tả chung o Tối ưu truy cập nguồn dữ liệu (OLE DB & SQL server) o Làm việc trên môi trường Internet - Các lớp của ADO.NET được đặt trong Namespase là System.Data - ADO.NET bao gồm 2 provider để thao tác với các cơ sở dữ liệu là OLEDB provider (nằm trong System.Data.OLEDB) dùng để truy xuất đến bất kỳ CSDL có hỗ trợ OLEDB; SQL Provider dữ liệu (nằm trong System.Data.SQLClient) chuyên dùng để truy xuất đến CSDL SQL Server (Không qua OLE DB nên nhanh hơn). - Vị trí ADO.NET trong kiến trúc của .NET Framework Từ kiến trúc ta tây rằng ADO.NET là một phần nội tại của .NET Framework, do vậy nó có thể được sử dụng trong tất cả các ngôn ngữ hộ trợ .NET như C#, VB.Net… mà không có sự khác biệt nào (Tức là các chức năng cũng như cách sử dụng hoàn toàn giống nhau). - ADO.NET được thiết kế để kết nối với cả dữ liệu phi kết nối trong môi trường đa tầng (Multi – Tier). Nó sử dụng XML để trao đổi dữ liệu phi kết nối do vậy dễ dàng khi giao tiếp giữa các ứng dụng không phải trên nền Windows. ASP.NET Windows Forms Drawing XML Data Comman language Runtime System Services ADO.NET Class Framework Vị trí của ADO.NET trong kiến trúc của .Net Framework - ADO.NET hỗ trợ hoàn toàn XML, nghĩa là chúng ta có thể nạp dữ liệu từ một tệp XML và thao tác như một CSDL, sau đó cũng có thể lưu kết quả ngược trở lại tệp XML do vậy có thể đi qua FireWall một cách dễ dàng. 1. Các thành phần chính của ADO.NET 1. Connection 2. Command 3. Datareader 4. DataAdapter 5. DataSet Các tầng kiến trúc của ADO.NET 2.1. Connection  Chức năng: Là đối tượng có nhiệm vụ thực hiện nhiệm vụ kết nối đến CSDL để các đối tượng như Command thao tác với CSDL thông qua Connection này.  Khai báo ()  Mở kết nối: Thi hành phưng thức Open() để mở kết nối. con.Open(); using System.Data.OleDb; public partial class Form1 : Form { String str=("Provider = SQLOLEDB.1 ; Data source = KCNTT- 386D64051; Initial Catalog=QLSVien;; User ID=MinhHue; PassWord=123456"); public OleDbConnection con=new OleDbConnection(st); private void Form1_Load(object sender, EventArgs e) { con.Open(); if (con.State.ToString() =="Open" ) { MessageBox.Show("Ket noi thanh cong" ); } } Data DataReader Connection Command Connection Command DataSet Data Related Components Managed Provider Compnent DataStore DataAdapter  Kiểm tra kết nối: Sau khi gọi phương thức Open, có thể xem đã kết nối thành công hay không thông qua thuộc tính State của Connection: if (con.State.ToString() =="Open" )  Đóng kết nối: Thi hành phương thức Close() để đóng kết nối Để tránh lỗi ta nên kiểm tra trạng thái kết nối 2.2 Command  Công dụng: Dùng để thực hiện các câu lệnh SQL thao tác với CSDL như: Insert, Update, Select, Delete…  Có 2 cách để tạo đối tượng Command  Có 4 cách để thực thi một lệnh thông qua đối tượng Command 2.2.1 Tạo Command từ phương thức tạo dựng public OleDbCommand dc = new OleDbCommand("select * from sinhvien", con); 2.2.2 Tạo command từ phương thức CreateCommand của đối tượng Connection OleDbCommand command = con.CreateCommand(); command.CommandText = "select * from sinhvien"; 2.2.3 Tạo Command bằng cách đặt các thuộc tính sau khi khai báo OleDbCommand com = new OleDbCommand(); com.CommandType = CommandType.Text com.CommandText ="Select * from sinhvien"; com.Connection = con;  Phương thức ExcuteReader: Phương thức này sẽ trả về một tập các bản ghi, nó tương đương với một Recordset và thường được sử dụng để thực thi các câu lệnh truy vấn như Select. Kết quả có thể lưu trữ trong đối tượng DataReader để thao tác. Cú pháp: Biến_DataReader=com.ExcuteReader(); //com biến OleDbCommand.  Phương thức ExcuteScalar(): Phương thức này sẽ trả về phần tử cột đầu tiên hàng đầu tiên trong bảng kết quả. Phương thức này thường được sử dụng thực hiện câu lệnh truy vấn Select mà kết quả trả về chỉ có một hàng và một cột (Select Count(*) from sinhvien).  Phương thức ExcuteNonQuery: Được sử dụng để thực thi các câu lệnh truy vấn hành động: Insert, Update, Delete…  Phương thức ExcuteXMLReader: Tạo bộ đọc từ File XML, phương thức này không có trong oleCommand chỉ có trong sqlCommand. 2.3 DataReader Công dụng: Dùng để đón nhận kết quả trả về từ đối tượng Command. Nó tương tự một RecordSet của ADO. Tuy nhiên dữ liệu là chỉ đọc theo chiều tiến (Readoly). private void Form1_FormClosing(object sender, FormClosingEventArgs e) { if (con.State.ToString() == "Open") { con.Close(); } }  Khai báo và lấy dữ liệu từ Command. Đối tượng DataReader không có phương thức khởi tạo 2.4 DataAdapter  Công dụng: Có chức năng như một cầu nối giữa nguồn (tệp) dữ liệu và các bảng được cached trong bộ nhớ (đối tượng DataSet). DataAdapter điền dữ liệu vào một DataSet hay DataTable từ một nguồn dữ liệu sử dụng phương thức Fill(). Còn khi cập nhật dữ liệu ngược trở lại nguồn dữ liệu thì sử dụng phương thức Update() của đối tượng DataAdapter. Tạo một DataAdapter ta có thể tạo từ một đối tượng connection đang mở hoặc từ một chuỗi kết nối (connection chưa được mở). private void button1_Click(object sender, EventArgs e) { StringBuilder st = new StringBuilder(); dc = new OleDbCommand("select * from sinhvien", con); OleDbDataReader dr ; // Khai báo DataReader dr = dc.ExecuteReader(); // Lấy dữ liệu từ Command while (dr.Read() == true) // Duyệt qua các bản ghi { st.AppendLine(dr.GetValue(2).ToString() ); } MessageBox.Show(st.ToString()); dr.Close(); dc.Dispose(); } private void cmdShow_Click(object sender, EventArgs e) { da = new OleDbDataAdapter("select * from Sinhvien", con); // Hoặc là //String st="Provider = SQLOLEDB.1 ; Data source = KCNTT- 386D64051; Initial Catalog=QLSVien;; User ID=MinhHue; PassWord=123456"; //da = new OleDbDataAdapter("select * from Sinhvien",st); da.Fill(ds, "SinhVien"); // Lưu trữ vào đối tượng DataSet this.dataGridView1.DataSource = ds; this.dataGridView1.DataMember = "Sinhvien"; this.cmdadd.Enabled = true; this.cmdShow.Enabled = false; } 2.5 Đối tượng DataSet. Đối tượng DataSet được coi như một kho chứa các bảng (Table). Người sử dụng có thể thay đổi dữ liệu trong các bảng này và khi muốn cập nhật vào cơ sở dữ liệu thì thi hành phương thức Update của đối tượng DataAdapter. - Các bảng trong DataSet có thể do DataAdapter Fill vào hoặc cũng có thể là các bảng được tạo thành từ đối tượng DataTable. - Các bảng này được quản lý bởi tập hợp Tables của lớp DataSet Ví dụ: Thêm một bảng vào DataSet và đặt tên bảng đó là bảng “SinhVien”. II. Sử dụng ADO.NET Trong ADO.NET có 2 mô hình kết nối và phi kết nối. 1. Sử dụng mô hình kết nối để thao tác với cơ sở dữ liệu Để thao tác với cơ sở dữ liệu trong mô hình kết nối chúng ta cần sử dụng đến các đối tượng: - DataConnection: Dùng để mở kết nối đến cơ sở dữ liệu. - DataCommand: Dùng để thực hiện các câu lệnh truy vấn SQL để lấy về dữ liệu (Select) hoặc cập nhật dữ liệu (Insert, Update, Delete). - DataReader. Dùng để duyệt các bản ghi theo chiều tiến và chỉ đọc. 1.1 Lấy dữ liệu về. b1. Cần khai báo một biến đối tượng DataConnection, và mở kết nối đến cơ sở dữ liệu. b2. Khi cần lấy dữ liệu thì tạo một biến DataCommand gắn với biến DataConnection đã mở, với câu lệnh Select truy vấn dữ liệu cần lấy về. b3. Khai báo biến DataReader b4. Thi hành phương thức ExcuteReader() của biến đối tượng DataCommand và gán cho biến đối tượng DataReader. b5. Đọc và trình bày dữ liệu b6. Đóng và giải phóng các biến đối tượng nếu không cần thiết. 001 …. …. 002 …. …. …. 001 …. …. 002 …. …. …. ……. DataSet DataSet ds = new DataSet(); da = new OleDbDataAdapter("select * from Sinhvien", con); da.Fill(ds, "SinhVien"); Ví dụ: 1.2 Thực hiện các thao tác trên cơ sở dữ liệu Các thao tác trên cơ sở dữ liệu có thể là thêm mới, sửa, xóa. b1. Cần khai báo một biến đối tượng DataConnection, và mở kết nối đến cơ sở dữ liệu. b2. Khi cần lấy dữ liệu thì tạo một biến DataCommand gắn với biến DataConnection đã mở. b3. Gán giá trị cho thuộc tính CommandType của biến đối tượng DataCommand b4. Gán câu lệnh truy vấn (insert, update, delete, gọi store procedure…) cho thuộc tính CommandText của biến đối tượng DataCommand public partial class HuongKetNoi : Form { //public OleDbConnection con=new OleDbConnection() private OleDbConnection con = new OleDbConnection("Provider = SQLOLEDB.1 ; Data source = KCNTT-386D64051; Initial Catalog=QLSVien;; User ID=MinhHue; PassWord=123456"); // b1. Khai báo biến ĐT DataConection private OleDbCommand dc; private void HuongKetNoi_Load(object sender, EventArgs e) { con.Open(); //b1. Mở kết nối } private void button1_Click(object sender, EventArgs e) { StringBuilder st = new StringBuilder(); // b2. tạo một biến DataCommand dc = new OleDbCommand("select * from sinhvien", con); OleDbDataReader dr ; // b3. Tạo biến DataReader dr = dc.ExecuteReader(); // b4. //if (con.State.ToString() == "Open") //{ // Chú ý nếu đoạn mã này được thực thi // con.Close(); // thì đoạn chương trình này sẽ lỗi //} // Tại sao? //b5. while (dr.Read() == true) { st.AppendLine(dr.GetValue(2).ToString() ); } MessageBox.Show(st.ToString()); // b6. dr.Close(); dc.Dispose(); } b5. Thi hành phương thức ExcuteNonquery() của biến đối tượng DataCommand b6. Đóng và giải phóng các biến đối tượng nếu không cần thiết. Ví dụ 1: Insert Ví dụ 2: Update Ví dụ 3: Delete private void cmdcapnhat_Click(object sender, EventArgs e) { dc = new OleDbCommand(); // b2. dc.Connection = con; // b3. có thể b3 không cần thiết nếu gt là CommandType.Text; dc.CommandType = CommandType.Text; // Giá trị này là gt mặc định // b4. dc.CommandText = "Insert into sinhvien values('" + this.Txtmalop.Text + "','" + this.TxtmaSV.Text + "','" + this.txtTen.Text + "')"; dc.ExecuteNonQuery(); // b5. dc.Dispose(); // b6. } private void cmdupdate_Click(object sender, EventArgs e) { dc = new OleDbCommand("select * from sinhvien", con); dc.CommandText = "Update sinhvien set malop='" + this.Txtmalop.Text + "',masvien='" + this.TxtmaSV.Text + "', tensvien='" + this.txtTen.Text + "' where masvien='" + this.TxtmaSV.Text + "'" ; dc.ExecuteNonQuery(); dc.Dispose(); } private void cmddel_Click(object sender, EventArgs e) { dc = new OleDbCommand("select * from sinhvien", con); dc.CommandText = "delete from sinhvien where masvien='" + this.TxtmaSV.Text + "'"; dc.ExecuteNonQuery(); dc.Dispose(); } Ví dụ 4: Gọi thủ tục lưu trữ nội (Stored procedure). 2. Sử dụng mô hình phi kết nối để thao tác với cơ sở dữ liệu Để thao tác với cơ sở dữ liệu trong mô hình phi kết nối chúng ta cần sử dụng đến các đối tượng: - DataConnection: Dùng để mở kết nối đến cơ sở dữ liệu. (Có thể có hoặc không) - DataAdapter - DataSet private void CmdStoredProc_Click(object sender, EventArgs e) { dc = new OleDbCommand(); dc.Connection = con; dc.CommandType = CommandType.StoredProcedure; dc.CommandText = "NhapSinhVien"; // Thủ tục lưu trữ nội // Tạo đối tượng Parameter để tạo ra các tham số và truyền vào // giá trị cho các tham số trong thủ tục lưu trữ nội // NhapSinhVien OleDbParameter p; p = new OleDbParameter("@malop", OleDbType.VarChar, 20); p.Value = "01.1"; dc.Parameters.Add(p); p = new OleDbParameter("@MaSv", OleDbType.VarChar, 20); p.Value = "01.1.190"; dc.Parameters.Add(p); p = new OleDbParameter("@TenSV", OleDbType.VarChar); p.Value = "Chu van quenh"; dc.Parameters.Add(p); // gọi phương thức Excute để thi hành. dc.ExecuteNonQuery(); } Data Base DataConnecton DataAdapter .NET Data Provider DataSet Table Table - Ngoài ra chúng ta còn sử dụng một số các đối tượng khác như DataTable, DataRow… 2.1 Lấy dữ liệu về để thao tác. b1. Cần khai báo một biến đối tượng DataConnection, và mở kết nối đến cơ sở dữ liệu. (Bước này có thể không cần thiết) b2. Tạo biến DataAdapter, Chỉ rõ nguồn dữ liệu cần lấy gắn với một đối tượng DataConnection đã mở hoặc một xâu kết nối đến cơ sở dữ liệu. b3. Thi hành phương thức Fill để điền dữ liệu lấy về vào DataSet. b4. Sử dụng tập hợp Tables của đối tượng DataSet để làm việc với dữ liệu đã lấy về. b5. Đóng hoặc giải phóng các đối tượng nếu không cần thiết. Ví dụ: 2.2 Thực hiện các thao tác trên cơ sở dữ liệu Các thao tác trên cơ sở dữ liệu có thể là thêm mới, sửa, xóa. b1. Khai báo một biến đối tượng DataConnection, và mở kết nối đến cơ sở dữ liệu. (Bước này có thể không cần thiết). b2. Khai báo và tạo đối tượng DataAdapter b3. Thay đổi dữ liệu trên bảng dữ liệu nằm trong DataSet. b4. Tạo đối tượng CommandBuilder gắn với biến đối tượng DataAdapter. b5. Thi hành phương thức Update để cập nhật sự thay đổi dữ liệu trong bảng lên cơ sở dữ liệu. b6. Đóng và giải phóng các biến đối tượng nếu không cần thiết. private void cmdShow_Click(object sender, EventArgs e) { // B2. Cách này cần có b1. //da = new OleDbDataAdapter("select * from Sinhvien", con); // Cách này không cần có b1. da = new OleDbDataAdapter("select * from Sinhvien", "Provider = SQLOLEDB.1 ; Data source = KCNTT-386D64051; Initial Catalog=QLSVien;; User ID=MinhHue; PassWord=123456"); da.Fill(ds, "SinhVien"); // Lấy dl thông qua tập hợp Tables của đối tượng DataSet //this.dataGridView1.DataSource = ds.Tables["SinhVien"] ; this.dataGridView1.DataSource = ds; this.dataGridView1.DataMember = "Sinhvien"; this.cmdadd.Enabled = true; this.cmdShow.Enabled = false; } 3. DataGridview và ADO.NET Đối tượng DataGridView dùng để trình bày dữ liệu trong DataSet, DataTable, DataView dưới hình thức khác nhau. 3.1. Hiển thị dữ liệu  Sử dụng thuộc tính DataSource và thuộc tính DataMember để gán đối tượng DataTable, DataSet, DataView o Nếu là DataTable, DataView, thì ta chỉ cần gán giá trị cho thuộc tính DataSource. o Nếu là DataSet thì trong thuộc tính DataMember cần chỉ rõ tên bảng cần hiển thị dữ liệu trong DataSet. private void cmdShow_Click(object sender, EventArgs e) { // b2. da = new OleDbDataAdapter("select * from Sinhvien", con); da.Fill(ds, "SinhVien"); } private void cmdadd_Cl ck(object sender, EventArgs e) { DataRow dr; DataTable dt; dt = ds.Tables[0]; dr = dt.NewRow(); // b3. dr[0] = this.Txtmalop.Text; dr[1] = this.TxtmaSV.Text; dr[2] = this.txtTen.Text; dt.Rows.Add(dr); // b4. OleDbCommandBuilder cb = new OleDbCommandBuilder(da); // b5. int i = da.Update(ds.Tables[0]); } private void cmdShow_Click(object sender, EventArgs e) { try { da = new OleDbDataAdapter("select * from Sinhvien", con); da.Fill(ds, "SinhVien"); //this.dataGridView1.DataSource = ds.Tables["SinhVien"] ; this.dataGridView1.DataSource = ds; this.dataGridView1.DataMember = "Sinhvien"; } catch (Exception Er) { MessageBox.Show(Er.Message); } } 3.2. Cập nhật dữ liệu từ DataGrid vào cơ sở dữ liệu. Khi người sử dụng thay đổi dữ liệu trên DataGrid thực ra là thay đổi dữ liệu trên DataTable. 4. Lớp DataTable, DataRow (Đọc tài liệu). private void cmdcapnhat_Click(object sender, EventArgs e) { // da là đối tượng DataAdapter OleDbCommandBuilder cb = new OleDbCommandBuilder(da); int i=da.Update(ds.Tables[0] ); }