Kết nối tới SQL Server, Connection string, lớp SqlConnection, Connection pool

    1

    Trong bài học này chúng ta sẽ xem xét chi tiết vấn đề đầu tiên của lập trình ADO.NET: cách kết nối chương trình với cơ sở dữ liệu SQL Server. Đây là thao tác đầu tiên khi lập trình với cơ sở dữ liệu, dù sử dụng trực tiếp ADO.NET hay Entity Framework. Chúng ta sẽ làm quen với chuỗi kết nối (Connection String), lớp SqlConnection, Connection Pool.

    Trong các bài học trước bạn đã thực hiện một số ví dụ về lập trình ADO.NET cơ bản và đã biết kiến trúc cơ bản của ADO.NET. Trong bài học này (và các bài học tiếp sau), chúng ta sẽ lần lượt đi sâu vào các vấn đề cụ thể của lập trình ADO.NET.

    [signinlocker id=”16252″]

    Thực hành: kết nối đến cơ sở dữ liệu Sql Server

    Trước hết chúng ta sẽ cùng làm một bài thực hành đơn giản: kết nối chương trình với cơ sở dữ liệu Sql Server.

    1. Tạo một solution trống đặt tên là LearnAdoNet;
    2. Thêm project mới thuộc kiểu ConsoleApp (.NET framework) vào solution này và đặt tên là ConnectToSql
    3. Mở file Program.cs và viết code như sau:
    using System;
    using System.Data.SqlClient;
    using System.Data;
    namespace ConnectToSql
    {
        class Program
        {
            static void Main(string[] args)
            {
                var connectionString = @"Data Source=.\sqlexpress;Initial Catalog=Contacts;Integrated Security=True";
                var connection = new SqlConnection
                {
                    ConnectionString = connectionString
                };
                try
                {
                    connection.Open();
                    if(connection.State == ConnectionState.Open)
                    {
                        Console.WriteLine("Connection opened successfully!");
                    }
                }
                catch (Exception)
                {
                    if(connection.State != ConnectionState.Open)
                    {
                        Console.WriteLine("Failed to open the connection");
                    }
                }
                finally
                {
                    if(connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                    connection.Dispose();                
                }
                Console.ReadLine();
            }
        }
    }

    Dịch và chạy thử chương trình để xem kết quả.

    Chương trình kết nối thành công tới Sql Server Express
    Chương trình kết nối thành công tới Sql Server Express

    Trong bài thực hành này chúng ta tạo một object của class SqlConnection và cung cấp một chuỗi ký tự cho thuộc tính ConnectionString của nó. SqlConnection là class hỗ trợ kết nối tới cơ sở dữ liệu Sql Server. ConnectionString là thuộc tính chứa chuỗi tham số dành cho việc kết nối này.

    var connection = new SqlConnection
    {
        ConnectionString = @"Data Source=.\sqlexpress;Initial Catalog=Contacts;Integrated Security=True"
    };

    Phương thức Open của lớp SqlConnection thực hiện việc mở kết nối. Khi kết thúc phiên làm việc với cơ sở dữ liệu cần gọi phương thức Close để đóng kết nối.

    connection.Open();
    connection.Close();

    Thuộc tính State của SqlConnection trả về thông tin về trạng thái của kết nối ở thời điểm hiện tại. Thuộc tính này có giá trị thuộc kiểu liệt kê ConnectionState (nằm trong không gian tên System.Data).

    if(connection.State == ConnectionState.Open) {...}

    ConnectionString – chuỗi tham số hỗ trợ kết nối

    Yêu cầu kết nối tới nguồn dữ liệu là một yêu cầu cơ bản của bất kỳ kiến trúc truy xuất dữ liệu nào. Để kết nối tới cơ sở dữ liệu SQL Server (cũng như các nguồn dữ liệu khác), chúng ta cần hai thứ: (1) một object của class hỗ trợ kết nối tới nguồn dữ liệu; (2) một chuỗi kết nối chứa tham số để tạo kết nối. Object sẽ sử dụng chuỗi kết nối để thực hiện nhiệm vụ của mình.

    Class hỗ trợ kết nối tới nguồn dữ liệu của Sql Server trong ADO.NET là SqlConnection. Bạn sẽ học về SqlConnection ở cuối bài.

    Thông tin hỗ trợ kết nối trong ADO.NET được lưu dưới dạng chuỗi ký tự gọi là connection string (tạm dịch là chuỗi kết nối).

    Connection string là một chuỗi ký tự chứa các cặp khóa – giá trị. Mỗi cặp khóa – giá trị là một tham số phục vụ kết nối. Nói chung, chuỗi kết nối có dạng như sau:

    "parametername1=parametervalue1;parametername2=parametervalue2;..."  

    Để mở kết nối chúng ta phải cung cấp những thông tin nhất định mà nguồn dữ liệu đó yêu cầu. Ví dụ, có thể phải cung cấp tên server, user, password. Các thông tin này không giống nhau đối với các loại nguồn dữ liệu.

    Dưới đây chúng ta sẽ xem xét những thành phần quan trọng nhất của một connectionstring cho Sql Server.

    Phương pháp xác thực

    Ở dạng tối giản, connection string cho Sql Server chỉ cần chứa thông tin về cách xác thực. Có hai cách xác thực: windows authentication và Sql server authentication. Các phương pháp xác thực được xác định khi cài đặt sql server express.

    // chuỗi connectionstring chứa thông tin về cách xác thực sử dụng windows authentication
    var connectionString1 = "Integrated Security=true";
    var connectionString2 = "Integrated Security=SSPI";
    // chuỗi connectionstring chứa user id và mật khẩu (phương pháp xác thực Sql Server)
    // sa là tên tài khoản super admin, nhớ thay bằng mật khẩu của mình
    var connectionString3 = "User ID=sa; Password=1234567890";

    Bạn có thể thay các giá trị này vào code của ví dụ bên trên để chạy thử. Kết quả sẽ luôn là kết nối thành công.

    Chuỗi connection string này sẽ luôn kết nối tới instance mặc định của sql server trên máy cục bộ.

    Chỉ định host và instance của Sql Server

    Để chỉ định instance cần kết nối tới, trong connection string chúng ta dùng khóa Data Source hoặc Server. Hai khóa này là tương đương. Giá trị cần cung cấp chứa tên host (nơi sql server cài đặt) và tên instance (không bắt buộc). Nếu không chỉ định tên instance thì instance mặc định sẽ được sử dụng.

    Nhắc lại: SQL Server cho phép có nhiều bản cài đặt trên cùng một máy. Mỗi bản cài đặt được gọi là một instance. Để phân biệt, mỗi instance có một tên gọi riêng. Tên gọi của instance được chỉ định trong lúc cài đặt.

    Lưu ý rằng, nếu Sql Server chạy trên máy cục bộ thì tên host có thể ghi là (local) hoặc . (dấu chấm). Cũng có thể sử dụng hostname của máy cục bộ hoặc trực tiếp sử dụng tên localhost. Tên instance viết cách hostname bằng dấu \ (slash).

    // kết nối tới instance mặc định trên máy cục bộ
    var connectionString4 = @"Server=.; Integrated Security=SSPI";
    var connectionString5 = @"Server=(local); Integrated Security=SSPI";
    var connectionString6 = @"Data Source=localhost; Integrated Security=SSPI";
    var connectionString7 = @"Data Source=local-pc; Integrated Security=SSPI";//nhớ thay local-pc bằng hostname của mình
    // kết nối tới instance sqlexpress trên máy cục bộ
    var connectionString8 = @"Server=.\sqlexpress; Integrated Security=SSPI";
    var connectionString9 = @"Server=(local)\sqlexpress; Integrated Security=SSPI";
    var connectionString10 = @"Data Source=localhost\sqlexpress; Integrated Security=SSPI";
    var connectionString11 = @"Data Source=local-pc\sqlexpress; Integrated Security=SSPI";//nhớ thay local-pc bằng hostname của mình

    Chỉ định cơ sở dữ liệu

    Để chỉ định cơ sở dữ liệu sẽ được sử dụng ta dùng từ khóa Initial Catalog. Giá trị của nó là tên của một cơ sở dữ liệu đã được tạo từ trước.

    var connectionString12 = @"Data Source=.\SqlExpress; Integrated Security=SSPI; Initial Catalog=Contacts";

    Lưu ý:
    (1) thứ tự của các cặp tham số trong connectionstring không quan trọng;
    (2) không phân biệt chữ hoa thường trong từ khóa và giá trị;
    (3) danh sách tất cả các khóa của cơ sở dữ liệu Sql Server có thể xem ở đây.

    Connection string để kết nối tới file cơ sở dữ liệu sử dụng LocalDb

    Ở phần trên bạn đã xem cách xây dựng connectionstring để kết nối tới SQL Server hoặc SQL Express. Nếu bạn sử dụng LocalDb, connectionstring cũng xây dựng theo cách tương tự nhưng có một số điểm khác biệt nhỏ.

    Thứ nhất, khóa Data Source (hoặc Server) cần nhận giá trị (localdb)\MSSQLLocalDB.

    Thứ hai, thay vì dùng Initial Catalog, bạn sử dụng khóa AttachDbFilename để chỉ định file mdf (file CSDL của SQL Server) cần kết nối. Giá trị của khóa này là đường dẫn tới file mdf.

    Về phương pháp xác thực luôn sử dụng Integrated Security=True.

    Dưới đây là một ví dụ về connectionstring để kết nối tới file Library.mdf sử dụng LocalDb:

    Data Source=(localdb)\MSSQLLocalDB; AttachDbFilename=C:\Users\Laptop\Library.mdf; Integrated Security=True

    Trong phần tiếp theo chúng ta sẽ xem xét một số cách tạo và lưu trữ chuỗi kết nối trong chương trình C#.

    Tạo – lưu trữ – truy xuất ConnectionString cho chương trình

    Qua phần nội dung trước chúng ta có thể thấy việc xây dựng các chuỗi liên kết là tương đối đơn giản. Chúng ta có thể viết trực tiếp (hardcode) trong chương trình như trên. .NET framework hỗ trợ một số phương pháp khác để tạo ra và lưu trữ connection string.

    Lớp ConnectionStringBuilder – tạo connection string

    Nếu không thể nhớ được tên các từ khóa tạo ra chuỗi connection string, bạn có thể sử dụng lớp ConnectionStringBuilder theo cách sau:

    SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
    connectionStringBuilder.DataSource = "(local)";
    connectionStringBuilder.InitialCatalog = "Contacts";
    connectionStringBuilder.IntegratedSecurity = true;
    var connectionString = connectionStringBuilder.ToString();

    Lưu ý rằng, khi sử dụng lớp ConnectionStringBuilder bạn vẫn phải biết cách viết giá trị cho từng tham số. Mỗi từ khóa của connection string giờ được thay bằng một property tương ứng.

    Tạo connection string bằng file udl

    Có một cách khác đơn giản hơn rất nhiều để tạo ra connection string thông qua file “universal data link” (.udl). Hãy thực hiện theo các bước sau để lấy connection string:

    1. Tạo một file text bất kỳ trên ổ cứng và đổi đuôi file thành udl
    2. Click đúp vào file này và điều chỉnh các tham số cần thiết. Ấn OK khi hoàn thành. Các tham số điền ở bước này chúng ta đã xem xét đầy đủ ở phần trên. Sự khác biệt là giờ chúng ta có một giao diện đồ họa.
    3. Mở file uld vừa rồi bằng một chương trình xử lý văn bản bất kỳ (như notepad chẳng hạn). Bạn sẽ nhìn thấy ngay chuỗi connection string được tạo ra từ các tham số đã chọn.
    Hộp thoại Universal Data Link (udl)

    Có điều sau cần lưu ý: chuỗi connectionstring được tạo ra từ hộp thoại udl chứa thêm tham số Provider=... Đối với provider chuyên dụng cho Sql Server thì thông tin này dư thừa (và còn không chính xác). Bạn xóa tham số này khi sử dụng connectionstring trong chương trình.

    Lưu và truy xuất connection string từ file cấu hình

    Ở trên chúng ta đã xem cách tạo và sử dụng chuỗi connectionstring trực tiếp trong code (hardcode). Tuy nhiên, hardcode chuỗi liên kết tuy đơn giản nhưng lại rất bất tiện khi deploy ứng dụng. Hãy tưởng tượng khi triển khai ứng dụng cho khách hàng, do cấu hình máy của họ hoàn toàn khác, tên install của sql server, hostname, phương pháp xác thực, user id, mật khẩu sẽ phụ thuộc vào máy của khách. Không lẽ lúc đó phải build lại ứng dụng? Hoặc khi người dùng thay đổi thông tin truy cập, chúng ta sẽ phải build lại ứng dụng!

    Một cách thức đơn giản để lưu trữ và truy xuất connection string là sử dụng file cấu hình.

    Mỗi project khi tạo ra sẽ có sẵn một file xml chứa thông tin cấu hình (App.config). Đây là một file xml và sẽ được copy vào thư mục hoạt động của ứng dụng khi cài đặt. Thông tin cấu hình lưu ở đây có thể được truy xuất khi chạy ứng dụng.

    File cấu hình (App.Config) của project

    Để sử dụng App.config với connection string chúng ta thực hiện các bước sau:

    Bước 1. Thay đổi file App.config bằng cách thêm node <connectionStrings>, trong đó thêm node <add /> như dưới đây

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
      <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
      </startup>
      <connectionStrings>
        <add name="my connection string" connectionString="Data Source=.\sqlexpress;Initial Catalog=Contacts;Integrated Security=True"/>
      </connectionStrings>
    </configuration>

    Bước 2. Tham chiếu project tới thư viện System.Configuration (click phải node References => Add Reference => tìm System.Configuration trong nhóm Assemblies => OK).

    Bước 3. Truy xuất chuỗi liên kết từ code C# như sau:

    var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["my connection string"].ConnectionString;

    Sử dụng SqlConnection để xử lý kết nối tới Sql Server

    Class hỗ trợ kết nối – lớp SqlConnection

    ADO.NET hỗ trợ kết nối tới nguồn dữ liệu bằng cách sử dụng một object của class được thiết kế riêng để làm việc với cơ sở dữ liệu tương ứng. Class này là một phần của data provider được tạo riêng dành cho nguồn dữ liệu đó.

    Để kết nối tới cơ sở dữ liệu SQL Server cần sử dụng một object từ lớp SqlConnection. Class này là một phần của data provider dành cho SQL Server. Lớp SqlConnection nằm trong namespace System.Data.SqlClient.

    Có một số cách để khởi tạo object của class này:

    var connection1 = new SqlConnection();            
    var connection2 = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=Contacts;Integrated Security=True");
    var connection3 = new SqlConnection { ConnectionString = @"Data Source=.\sqlexpress;Initial Catalog=Contacts;Integrated Security=True" };

    Các cách thức khởi tạo này đem lại cùng một kết quả. Ở cách khởi tạo thứ hai và thứ ba chúng ta cung cấp một chuỗi ký tự chứa tham số phục vụ kết nối. Nên lưu ý rằng, trước khi mở liên kết bắt buộc phải cung cấp thông tin về chuỗi kết nối này cho object của SqlConnection.

    Trong phần tiếp theo chúng ta sẽ xem xét các phương thức và thuộc tính quan trọng của lớp SqlConnection.

    Phương thức của lớp SqlConnection

    Open: đây là phương thức quan trọng hàng đầu của lớp SqlConnection. Phương thức này mở kết nối vật lý tới cơ sở dữ liệu. Object chỉ sẵn sàng để sử dụng với cơ sở dữ liệu sau khi gọi phương thức Open. Trước khi gọi phương thức này bắt buộc phải cung cấp một connectionstring phù hợp.

    Close: phương thức này thực hiện công việc ngược lại so với Open – đóng kết nối tới cơ sở dữ liệu nếu kết nối đang mở. Một khi gọi phương thức Close, kết nối vật lý sẽ không bị xóa bỏ thực sự mà đưa vào một “kho lưu tạm” gọi là Connection Pool để có thể tái sử dụng về sau. Một connection sau khi gọi Close sẽ có thể được sử dụng lại.

    Lưu ý, một connection nên mở càng muộn càng tốt, và nên đóng càng sớm càng tốt.

    Dispose: tương tự như Close, Dispose sẽ đóng kết nối và đưa nó vào pool. Dispose tự nó sẽ gọi tới Close. Sự khác biệt là Dispose đồng thời xóa bỏ tất cả các trạng thái và thông tin của connection (như ConnectionString). Một connection khi bị Dispose thì chương trình không thể tái sử dụng.

    ChangeDatabase: cho phép chuyển đổi cơ sở dữ liệu sẽ được sử dụng. Như ở trên chúng ta đã biết, khi tạo connectionstring, chúng ta có thể kết nối tới bất kỳ cơ sở dữ liệu nào ngay từ đầu hoặc chỉ kết nối tới server. Để thực hiện các thao tác với dữ liệu, ta phải chọn cơ sở dữ liệu cụ thể bằng phương thức ChangeDatabase. Dĩ nhiên, chúng ta có thể chuyển đổi cơ sở dữ liệu bằng cách tạo liên kết mới. Tuy nhiên, ChangeDatabase hiệu quả hơn nhiều.

    CreateCommand: giúp tạo ra một object của lớp SqlCommand – class giúp thực hiện các truy vấn trên cơ sở dữ liệu. Lớp SqlCommand sẽ được xem xét chi tiết ở bài tiếp theo.

    Thuộc tính của lớp SqlConnection

    ConnectionString: đây là thuộc tính quan trọng hàng đầu của SqlConnection. Thuộc tính này chứa chuỗi liên kết mà chúng ta đã xem xét rất kỹ ở các phần trên. Thuộc tính này bắt buộc phải có giá trị phù hợp trước khi gọi phương thức Open.

    ConnectionTimeout: chứa giá trị thời gian (tính bằng giây) tối đa để thử kết nối tới server. Quá thời gian này mà chưa kết nối thành công, việc kết nối sẽ bị hủy bỏ và phát ra exception.

    Database: chứa thông tin (chỉ đọc) về cơ sở dữ liệu hiện tại đang được sử dụng (nếu liên kết tới cơ sở dữ liệu đang mở).

    State: chứa thông tin về trạng thái của hiện tại của liên kết. Thuộc tính này có giá trị thuộc kiểu liệt kê ConnectionState (nằm trong không gian tên System.Data). Hai giá trị quan trọng mà chúng ta đã biết là ConnectionState.Open và ConnectionState.Closed.

    Connection Pooling

    Connection pooling là một loại kỹ thuật cho phép tạo ra và duy trì một số các kết nối sử dụng chung để tăng hiệu suất cho các ứng dụng. Việc tăng hiệu suất này được thực hiện thông qua tái sử dụng các kết nối khi có yêu cầu thay vì việc tạo kết nối mới.

    Tại sao lại cần Connection Pooling? Lý do là vì việc tạo và hủy các kết nối tới cơ sở dữ liệu mất rất nhiều thời gian. Thông thường phải mất từ 1 đến 3s để thiết lập kết nối tới cơ sở dữ liệu vì trong đó phải thực hiện các bước như giao tiếp với server, chứng thực, và nhiều tác vụ khác. Do đó, việc đóng mở kết nối liên tục sẽ ảnh hưởng rất lớn tới hiệu năng của ứng dụng và tải của server.

    Trong ADO.NET, Connection Pooling được sử dụng mặc định. Đối với SqlConnection, khi gọi một trong hai phương thức Close và Dispose (đã trình bày ở trên) sẽ tự động đưa kết nối đó vào pool (tạm dịch là vùng lưu trữ liên kết tạm thời) mà không hủy bỏ hoàn toàn liên kết. Khi gặp lệnh Open tiếp theo, một kết nối trong pool sẽ được sử dụng (nếu có) thay vì tạo ra liên kết mới.

    Kết luận

    Trong bài học này chúng ta đi sâu vào vấn đề đầu tiên và quan trọng hàng đầu khi làm việc với ADO.NET (và cả Entity Framework): kết nối tới cơ sở dữ liệu Sql Server Express. Để thực hiện kết nối chúng ta cần sử dụng lớp SqlConnection (của .NET data provider dành cho Sql Server) và xây dựng một connectionstring phù hợp. Chúng ta cũng đã học cách lưu trữ và sử dụng connectionstring từ file cấu hình.

    Lưu ý rằng, connection string sẽ còn được sử dụng tiếp trong nội dung về Entity Framework. Vì vậy, hãy cố gắng nắm bắt bài học này.

    Chúc bạn học tốt!

    [wpdm_package id=’10281′]

    + Nếu bạn thấy site hữu ích, trước khi rời đi hãy giúp đỡ site bằng một hành động nhỏ để site có thể phát triển và phục vụ bạn tốt hơn.
    + Nếu bạn thấy bài viết hữu ích, hãy giúp chia sẻ tới mọi người.
    + Nếu có thắc mắc hoặc cần trao đổi thêm, mời bạn viết trong phần thảo luận cuối trang.
    Cảm ơn bạn!

    [/signinlocker]

    Theo dõi
    Thông báo của
    guest

    1 Thảo luận
    Cũ nhất
    Mới nhất
    Phản hồi nội tuyến
    Xem tất cả bình luận
    Quang Thuận

    hay