Giới thiệu về ADO.NET

ADO.NET (ActiveX Data Object) là tập hợp các thư viện lớp qua đó cho phép ứng dụng tương tác (lấy về, cập nhật, xóa) với các nguồn dữ liệu (Như SQLServer, XML, MySQL, Oracle Database ...).

Kiến trúc để truy cập dữ liệu với ADO.NET được phân ra nhiều phần rời rạc, mỗi phần có thể sử dụng độc lập hay đồng thời nhiều thành phần được sử dụng. Cơ bản thì nó phân chia ra hai khu vực như hình dưới:

Kiến trúc ADO.NET

• Phần thứ nhất gọi là Data Provider: là các thư viện lớp cung cấp chức năng tạo kết nối đến nguồn dữ liệu, thi hành các lệnh trên nguồn dữ liệu đó inset, update, delete, read.

  ---

  SQL Server: Loại Data Provider mặc định trong .NET Core là SqlClient ở namespace System.Data.SqlClient cung cấp khả năng kết nối đến SQL Server

  MySQL: Nếu muốn có loại Data Provider truy cập đến MySQL thì cài đặt package MySql.Data, sẽ có Data Provider MySql.Data.MySqlClient

  SQLite thì cài đặt Data Provider Microsoft.Data.SQLite

• Phần thứ 2 gọi là DataSet là các thư viện lớp (độc lập với Data Provider) tạo ra các đối tượng để quản lý dữ liệu không phụ thuộc ngồn dữ liệu đến từ đâu, đã ở trong ứng dụng (local) hay từ nguồn XML.

  DataSet thường gồm nhiều DataTable, trong DataTable lại gồm DataColumn, các dàng buộc, các khóa chính ... Vậy DataSet là sự trừu tượng hóa một CSDL thực.

Trước tiên tìm hiểu sử dụng Data Provider với trường hợp cụ thể là SqlClient để truy cập đến CSDL MS SQL Server. Hãy gõ lệnh sau để thêm package SqlClient vào dự án:

 dotnet add package System.Data.SqlClient

Sau đó trong code sử dụng thêm namespace:

 using System.Data;
 using System.Data.SqlClient;
 

Chuẩn bị SQL Server với dữ liệu mẫu

Do thực hành sử dụng SqlClient kết nối SQL Server, nên nếu chưa có hệ quản trị CSDL này thì có thể tạo một SQL Server với dữ liệu mẫu - chạy trong một Container Docker, hãy làm theo hướng dẫn phần MSSQL Container, nếu đã có SQL Server - bạn có thể phục hồi dữ liệu mẫu từ file bak xtlab.bak vào CSDL xtlab để thực hành.

Hoặc bạn có thể tải về docker-compose.yml và dữ liệu mẫu tại SQLDocker, sau đó giải nén, từ dòng lệnh vào thư mục MSSQL rồi chạy lệnh:

 docker-compose up -d
 docker exec sqlserver-xtlab /var/opt/mssql/backup/restore.sh
 

Bằng cách như vậy bạn sẽ có một SQL Server với dữ liệu mẫu có tên xtlab có cấu trục như tại Công cụ SQL Online , lắng nghe ở cổng 1433, tài khoản sa với password là Password123

Tạo kết nối với DbConnection

DbConnection là lớp biểu diễn sự kết nối tới máy chủ CSDL. Từ lớp này các thư viện triển khai cụ thể cho từng loại CSDL như SQL Server có lớp triển khai (kế thừa) DbConnection là SqlConnection.

SqlConnection sử dụng với các bước cơ bản sau:

• Tạo đối tượng SqlConnection từ một chuỗi kết nối tới SQL Server
• Mở kết nối với phương thức Open()
• Thực hiện các truy vấn bằng cách sử dụng các lớp như SqlCommand, SqlDataAdapter, SqlDataReader ...
• Không còn sử dụng đến kết nối nữa thì cần đóng lại bằng phương thức Close

Ví dụ, có SQL Server ở địa chỉ localhost (hoặc 127.0.0.1), cổng kết nổi 1433, tên tài khoản là SA, password là Password123, thì tạo và mở kết nối như sau:

 string sqlconnectStr = "Data Source=localhost,1433;Initial Catalog=xtlab;User ID=SA;Password=Password123";
 DbConnection connection = new SqlConnection(sqlconnectStr);
 
 connection.Open();                      // Mở kết nối - hoặc  connection.OpenAsync(); nếu dùng async
 
 //..                                    // thực hiện cá tác  vụ truy vấn CSDL (CRUD - Insert, Select, Update, Delete)
 
 connection.Close();                     // Đóng kết nối
 
 

Tạo chuỗi kết nối với SQL Server

Chuỗi kết nối (connection string) là tham số để khởi tạo ra đối tượng SqlConnection, chuỗi này chứa các thông tin cơ bản để thực hiện kết nối đến một SQL Server, các thông tin được chứa theo cặp key=value; ví dụ chuỗi "key 1=value1;key 2=value2", dưới dây tham khảo một số key

Tạo chuỗi kết nối cơ bản

Chuỗi có dạng như sau:

 "Server=ServerAddress;Database=DataBaseName;User Id=Username;Password=myPassword;"

Hoặc

 "Data Source=ServerAddress;Database=DataBaseName;User Id=Username;Password=myPassword;"

Ví dụ

SQL Server ở máy có địa chỉ IP 192.168.1.10, có CSDL tên exampledb, cho phép kết nối với User/Password là testuser/testpass, thì tạo ra chuỗi kết nối và đối tượng SqlConnection như sau:

 String connectionString = "Server=192.168.1.10;Database=exampledb;User Id=testuser;Password=testpass;";
 var sqlConnection = new SqlConnection(connectionString);
 sqlConnection.Open();   //Mở kết nối
 //...Code truy vấn, cập nhật dữ dữ liệu ở đây
 sqlConnection.Close();  //Đóng kết nối sau khi sử dụng
 

Nếu sử dụng using thì kết nối sẽ tự động đóng lại ở cuối khối using, nên không cần phải gọi Close

 String connectionString = "Server=192.168.1.10;Database=exampledb;User Id=testuser;Password=testpass;";
 using (var sqlConnection = new SqlConnection(connectionString))
 {
     sqlConnection.Open();//Mở kết nối
     //...Code truy vấn, cập nhật dữ dữ liệu ở đây
 }
 

Chú ý, trong một phạm vi phương thức có thể sử dụng using mà không cần tạo khối lệnh

 void myfunction()
 {
     using var sqlConnection = new SqlConnection(connectionString);
     sqlConnection.Open();//Mở kết nối
     //...
 }
 

SqlConnectionStringBuilder

SqlConnectionStringBuilder giúp tạo ra chuỗi kết nối, bằng cách thiết lập từng loại key ở trên, sau đó nó phát sinh chuỗi kết nối giúp bạn. Ví dụ:

 var DbCStringBuilder = new SqlConnectionStringBuilder();
 DbCStringBuilder["Server"] = "127.0.0.1,1433";
 DbCStringBuilder["Database"] = "xtlab";
 DbCStringBuilder["User Id"] = "SA";
 DbCStringBuilder["Password"] = "Password123";
 
 string sqlConnectStr = DbCStringBuilder.ToString();
 using var connection = new SqlConnection(sqlConnectStr);
 
 connection.Open();
 // Thực hiện  các tác vụ ...
 
 

Thông tin kết nối SqlConnection

Ở phần trên đã biết tạo ra một chuỗi kết nối, từ đó sinh ra đối tượng SqlConnection, đối tượng này được sử dụng bởi các lớp khác nhau như SqlCommand, SqlDataAdapter ... để thực hiện các truy vấn đến dữ liệu. Những đối tượng này sẽ tìm hiều ở phần sau, ở đây nói thêm một chút về SqlConnection

Một số phương thức, thuộc tính SqlConnection

 if ((connection.State.HasFlag(ConnectionState.Open))
     && (connection.State.HasFlag(ConnectionState.Fetching)))
     {
         Console.WriteLine("Kết nối mở và đang nhận dữ liệu");
     }

Ví dụ: Ví dụ sau có tạo ra một chuỗi kết nối tới SQL Server ở địa chỉ 127.0.0.1, sau đó bắt thông tin cho biết mỗi khi trạng thái kết nối thay đổi (open, close), có thực hiện một câu truy vấn SQL lấy dữ liệu về, cuối cùng là cho biết các thông tin thông kế đã thực hiện trên kết nối

 public class Exam1
 {
   public static void Test()
   {
 
     // TẠO CHUỖI KẾT NỐI bằng SqlConnectionStringBuilder
     var stringBuilder = new SqlConnectionStringBuilder();
     stringBuilder["Server"] = "127.0.0.1,1433";
     stringBuilder["Database"] = "xtlab";
     stringBuilder["User Id"] = "SA";
     stringBuilder["Password"] = "Password123";
     String sqlConnectionString = stringBuilder.ToString();
 
     var connection = new SqlConnection(sqlConnectionString);
     // kích hoạt chế độ thu thập thông tin thống kê khi truy vấn
     connection.StatisticsEnabled = true;
 
     Console.WriteLine($"{"ConnectionString ",17} : {stringBuilder}");
     Console.WriteLine($"{"DataSource ",17} : {connection.DataSource}");
 
     // Bắt sự kiện trạng thái kết nối thay đổi
     connection.StateChange += (object sender, StateChangeEventArgs e) =>
     {
       Console.WriteLine($"Kết nối thay đổi: {e.CurrentState}, trạng thái trước: " + $"{e.OriginalState}");
     };
 
     // mở kết nối
     connection.Open();
 
     // Dùng SqlCommand thi hành SQL  - sẽ  tìm hiểu sau
     using (DbCommand command = connection.CreateCommand())
     {
       // Câu truy vấn SQL
       command.CommandText = "select top(5) * from Sanpham";
       var reader = command.ExecuteReader();
       // Đọc kết quả truy vấn
       Console.WriteLine("
 CÁC SẢN PHẨM:");
       Console.WriteLine($"{"SanphamID ",10} {"TenSanpham "}");
       while (reader.Read())
       {
         Console.WriteLine($"{reader["SanphamID"],10} {reader["TenSanpham"]}");
       }
     }
 
     // Lấy thống kê và in số liệu thống kê
     Console.WriteLine("Thông tin thống kê các tương tác đã thực hiện trên kết nôis");
     var dicStatics = connection.RetrieveStatistics();
     foreach (var key in dicStatics.Keys)
     {
       Console.WriteLine($"{key,17} : {dicStatics[key]}");
     }
 
     // Không dùng đến kết nối thì phải đóng lại (giải phóng)
     connection.Close();
   }
 }
 

Khi chạy trong Main

 static void Main(string[] args)
 {
     Exam1.Test();
 }
 

Đọc thông tin kết nối từ file config

Bạn có thể lưu chuỗi kết nối ở một cấu hình sau đó khi chạy chương trình nó sẽ đọc vào tạo thông tin kết nối, có thể sử dụng kỹ thuật Configuration lưu thông tin kết nối ở các định dạng file như json, ini, xml ... giả sử dùng định dạng json, hãy thêm các package như hướng dẫn tại config với json

 dotnet add package Microsoft.Extensions.Configuration
 dotnet add package Microsoft.Extensions.Options.ConfigurationExtensions
 dotnet add package Microsoft.Extensions.Configuration.Json

Giả sử tạo file config có tên là appconfig.json có lưu chuỗi kết nối như sau:

 {
     "csdl" :  {
         "ketnoi1"  : "Data Source=127.0.0.1,1433;Initial Catalog=xtlab;User ID=SA;Password=Password123",
         "ketnoi2"  : "Data Source=localhost,1433;Initial Catalog=xtlab;User ID=SA;Password=Password123"
     }
 }
 

Ví dụ:

 using System;
 using System.Data;
 using System.Data.SqlClient;
 using Microsoft.Extensions.Configuration;
 using Microsoft.Extensions.Configuration.Json;
 using System.IO;
 
 namespace ADO_01_SqlConnection
 {
     class Program
     {
         // Lấy chuỗi kết nối từ file config định dạng json,
         // Điểm lưu: csl:ketnoi2
         public static string GetConnectString() {
             var configBuilder = new ConfigurationBuilder()
                        .SetBasePath(Directory.GetCurrentDirectory())      // file config ở thư mục hiện tại
                        .AddJsonFile("appconfig.json");                    // nạp config định dạng JSON
             var configurationroot = configBuilder.Build();                // Tạo configurationroot
             return configurationroot["csdl:ketnoi2"];
 
         }
 
         static void Main(string[] args)
         {
             // Exam1.Test();
 
             String sqlConnectString = GetConnectString();
             var connection = new SqlConnection(GetConnectString());
             connection.StatisticsEnabled = true;
             connection.FireInfoMessageEventOnUserErrors = true;
 
             connection.StateChange += (object  sender, StateChangeEventArgs e) => {
                     Console.WriteLine($"Trạng thái hiện tại: {e.CurrentState}, trạng thái trước: " + $"{e.OriginalState}");
             };
 
             // Mở kết nối
             connection.Open();
 
             // Thực hiện các truy vấn tại đây ...
 
             connection.Close();
 
         }
     }
 }
 

Sử dụng ADO.NET với MySQL Server

Để nhanh chóng, sử dụng docker tạo ra một dịch vụ MySQL Server, tạo ra file docker-compose.yaml có nội dung như sau:

 version: "3"
 
 services:
     netcore-mysql:
       image: "mysql:5.7"
       container_name: mysql-net
       restart: always
       hostname: mysqlnet
       networks:
         - my-network
       environment:
         MYSQL_ROOT_PASSWORD: abc123   #Thiết lập password cho root
       volumes:
         - storedb:/var/lib/mysql
       ports:
         - "3307:3306"                # Ánh xạ cổng 3307 vào cổng mặc định 3306 SQL
 
 networks:                            # TẠO NETWORK
   my-network:
     driver: bridge
 
 volumes:                              # TẠO Ổ ĐĨA LƯU DB
   storedb:
 

Với file trên, sử dụng MySQL bản 5.7, cổng mặc định của MySQL 3306 khi expose ra máy host là cổng 3307 (localhost:3307 là truy cập vào MySQL), sau đó thực hiện lệnh tạo dịch vụ

 docker-compose up -d

Sau khi dịch vụ chạy, có thể sử dụng bất kỳ công cụ quản lý MySQL nào để quản trị nó, ví dụ hãy cài MySQL Workbench, rồi tạo kết nối đến MySQL (chú ý cổng 3307)

Nếu muốn nhanh chóng có CSDL ví dụ giống trên, hãy tạo một db tên xtlab và thực thi trong cửa sổ truy vấn các câu SQL script_create_db_mysql

Tích hợp thư viện MySql.Data

Thư viện MySql.Data trên Nuget cho phép ứng dụng .NET sử dụng ADO.NET để truy vấn MySQL, hãy thực hiện lệnh

 dotnet add package MySql.Data

Thư viện này triển khai các Interface, Abstract ... của ADO.NET, ví dụ:

• DbConnectionStringBuilder triển khai thành MySqlConnectionStringBuilder
• DbConnection triển khai thành MySqlConnection
• DbCommand triển khai thành MySqlCommand
• ...

Đoạn code kết nối, truy vấn đến SQL Server có thể sửa lại để truy vấn MySQL như sau:

   class Program
   {
     public class Exam1
     {
       public static void Test()
       {
 
         // TẠO CHUỖI KẾT NỐI bằng SqlConnectionStringBuilder
         var stringBuilder = new MySqlConnectionStringBuilder();
         stringBuilder["Server"] = "127.0.0.1";
         stringBuilder["Database"] = "xtlab";
         stringBuilder["User Id"] = "root";
         stringBuilder["Password"] = "abc123";
         stringBuilder["Port"] = "3307";
 
 
         String sqlConnectionString = stringBuilder.ToString();
 
         var connection = new MySqlConnection(sqlConnectionString);
 
         Console.WriteLine($"{"ConnectionString ",17} : {stringBuilder}");
         Console.WriteLine($"{"DataSource ",17} : {connection.DataSource}");
 
         // Bắt sự kiện trạng thái kết nối thay đổi
         connection.StateChange += (object sender, StateChangeEventArgs e) =>
         {
           Console.WriteLine($"Kết nối thay đổi: {e.CurrentState}, trạng thái trước: " + $"{e.OriginalState}");
         };
 
         // mở kết nối
         connection.Open();
 
         // Dùng SqlCommand thi hành SQL  - sẽ  tìm hiểu sau
         using (DbCommand command = connection.CreateCommand())
         {
           // Câu truy vấn SQL
           command.CommandText = "select * from Sanpham Limit 5";
           var reader = command.ExecuteReader();
           // Đọc kết quả truy vấn
           Console.WriteLine("
 CÁC SẢN PHẨM:");
           Console.WriteLine($"{"SanphamID ",10} {"TenSanpham "}");
           while (reader.Read())
           {
             Console.WriteLine($"{reader["SanphamID"],10} {reader["TenSanpham"]}");
           }
         }
         // Không dùng đến kết nối thì phải đóng lại (giải phóng)
         connection.Close();
 
       }
     }
 
     static void Main(string[] args)
     {
         Exam1.Test();
     }
   }
 
 // Kết quả chạy
 /*
 ConnectionString  : server=127.0.0.1;database=xtlab;user id=root;password=abc123;port=3307
       DataSource  : 127.0.0.1
 Kết nối thay đổi: Connecting, trạng thái trước: Closed
 Kết nối thay đổi: Open, trạng thái trước: Open
 
 CÁC SẢN PHẨM:
 SanphamID  TenSanpham
          1 Bia 333
          2 Nước ngọt Coca cola
          3 Tương Ớt Chin-Su (250g)
          4 Dầu Đậu Nành Simply
          5 Bột cần tây sấy lạnh
 Kết nối thay đổi: Closed, trạng thái trước: Open
 */
 

Chú ý sử dụng namespace MySql.Data.MySqlClient

Source Code

Mã nguồn ADO_01_SqlConnection hoặc tải về ex039

Nguồn tin: XuanThuLab