Khi học làm việc với cơ sở dữ liệu Sql Server, bạn hẳn đã biết vai trò và cách thực thi truy vấn. Tuy nhiên, trong lập trình C#, việc thực thi truy vấn Sql không hoàn toàn giống như vậy. Để thực thi truy vấn Sql trong chương trình C# cần sử dụng một class hỗ trợ của ADO.NET có tên là SqlCommand. Ngoài ra, để đọc được kết quả của truy vấn Sql cũng cần những kỹ thuật lập trình riêng. Show
Bài học này sẽ giới thiệu với bạn tất cả những gì cần biết về việc thực thi truy vấn Sql từ chương trình C# sử dụng lớp SqlCommand và các kỹ thuật đặc thù cho quá trình này. [signinlocker id=”16252″] Truy vấn SQL trong C#Trong bài học trước, bạn đã biết cách kết nối tới Sql Server. Sau khi kết nối thành công, việc tương tác với cơ sở dữ liệu cần hai thao tác: (1) Chỉ định những dữ liệu mà chương trình cần; (2) Nhận kết quả. Việc chỉ định những dữ liệu mà chương trình quan tâm thực hiện bằng cách gửi đi truy vấn viết bằng ngôn ngữ SQL. Truy vấn SQL thực chất đều là các chuỗi văn bản. Lưu ý rằng, ngôn ngữ SQL không hoàn toàn giống nhau trên các hệ quản trị cơ sở dữ liệu. SQL Server của Microsoft sử dụng T-SQL, trong khi Oracle sử dụng PL/SQL. ADO.NET hỗ trợ thực thi các truy vấn SQL (tới SQL Server) và đọc kết quả thông qua sử dụng object của class SqlCommand. Lớp SqlCommandSqlCommand (tên đầy đủ là Chúng ta có thể hình dung object của lớp SqlConnection tạo kết nối tới Sql Server tương tự như đặt một đường ống thông từ chương trình C# tới Sql Server. Object của SqlCommand giống như cái bơm đặt ở đầu ống phía chương trình. Nó có nhiệm vụ đẩy truy vấn về phía Sql Server và “hút” dữ liệu (kết quả của truy vấn) từ Sql Server về chương trình. Khởi tạo objectCó một số cách khởi tạo object của lớp SqlCommand: var command1 = new SqlCommand(); // cung cấp chuỗi truy vấn khi khởi tạo var commandText = "Select * from Contacts"; var command2 = new SqlCommand(commandText); // cung cấp chuỗi truy vấn + object connection khi khởi tạo var connection = new SqlConnection(); var command3 = new SqlCommand(commandText, connection); // cũng có thể tạo command trực tiếp từ connection var connection = new SqlConnection(); var command = connection.CreateCommand(); Một số thuộc tính của SqlCommandCommandText: chứa chuỗi truy vấn SQL cần thực thi Connection: chứa object của connection được sử dụng để kết nối tới cơ sở dữ liệu CommandType: xác định xem mình cần thực hiện truy vấn SQL (giá trị CommandType.Text) hay cần gọi hàm stored procedure (giá trị CommandType.StoredProcedure) qua object của SqlCommand. Giá trị mặc định là Text. Parameters: danh sách tham số được sử dụng trong truy vấn. Nội dung này sẽ trình bày ở một phần riêng cuối bài. Hai thông tin quan trọng nhất mà object SqlCommand cần biết là object của class SqlConnection và chuỗi truy vấn Sql. Hai thông tin này phải được cung cấp cho object SqlCommand trước khi gọi lệnh thực thi bất kỳ truy vấn nào. Một số phương thức quan trọng của SqlCommandExecuteNonQuery(): chuyên để thực thi các truy vấn không trả về dữ liệu (INSERT, UPDATE, DELETE). ExecuteScalar(): thực thi các truy vấn trả lại MỘT giá trị duy nhất, thường là kết quả của truy vấn Aggregate (SELECT COUNT|MIN|MAX|AVG). ExecuteReader(): thực thi các truy vấn trả lại TẬP HỢP giá trị, như các truy vấn SELECT thông thường. Xử lý kết quả truy vấn của SqlCommandĐối với việc nhận kết quả, tùy thuộc vào từng loại truy vấn sẽ những kỹ thuật khác nhau. Như bạn đã biết, ngôn ngữ SQL có 4 loại truy vấn dữ liệu chính: INSERT, UPDATE, DELETE, và SELECT. Trong đó, INSERT, UPDATE, DELETE không trả lại kết quả. Phương thức ExecuteNonQuery() dùng để thực thi các truy vấn này. Kết quả thực hiện của phương thức là số bản ghi chịu ảnh hưởng của truy vấn. Riêng đối với SELECT, tùy từng truy vấn phải sử dụng cách lưu trữ dữ liệu phù hợp. Đối với các truy vấn kiểu SELECT COUNT|MIN|MAX|AVG, kết quả trả về chỉ là một giá trị đơn. Phương thức ExecuteScalar sẽ lưu kết quả vào một biến kiểu Object. Bạn có thể cast nó sang kiểu phù hợp để sử dụng tiếp. Đối với các truy vấn SELECT khác, kết quả trả về là một tập hợp dữ liệu. Một phần kết quả của các truy vấn này được lưu (tạm) trong một object SqlDataReader hoạt động theo kiểu forward-only và read-only. SqlDataReader cho phép đọc qua từng hàng trong tập hợp dữ liệu theo một chiều từ đầu đến cuối (forward-only). Không thể đọc theo chiều ngược lại. SqlDataReader chỉ cho phép đọc dữ liệu ra mà không cho phép sửa dữ liệu (read-only). Do đó, nếu muốn lưu trữ kết quả để sử dụng lâu dài, bạn phải dùng một cơ chế lưu trữ khác. Cách thức làm việc cụ thể của SqlDataReader sẽ xem xét ở phần ví dụ dưới đây. Thực thi truy vấn Sql trong C# với SqlCommandThực thi truy vấn AggregateNhững dữ liệu đơn lẻ trả về từ các truy vấn loại Aggregate như SELECT COUNT|MIN|MAX|AVG được gọi là scalar value. Chúng ta sẽ xem xét cách thực thi và lấy kết quả cho loại truy vấn này thông qua thực hiện một ví dụ. Ví dụ minh họa này sử dụng cơ sở dữ liệu từ bài thực hành Lập trình ADO.NET cơ bản. Bước 1. Tạo một solution mới đặt tên là S02_SqlCommand. Trong solution này tạo một project (Console App) đặt tên là P01_Scalar. Bước 2. Mở file Program.cs và viết code: using System; using System.Data.SqlClient; namespace P01_Scalar { }Có thể thấy rằng, thực thi và đọc kết quả từ các truy vấn Aggregate với SqlCommand rất đơn giản: (1) sử dụng phương thức ExecuteScalar của SqlCommand; (2) biến đổi (ép) kiểu dữ liệu của kết quả về loại mong muốn. Phương thức ExecuteScalar của lớp SqlCommand chuyên để thực thi các truy vấn Sql có trả về một kết quả đơn. Giá trị được ExecuteScalar trả về luôn có kiểu Object. Do đó, nếu muốn dùng kết quả này trong các tính toán khác, bạn phải ép kiểu (type casting) về kiểu mình mong muốn. Thực thi truy vấn SelectBước 1. Tạo thêm project P02_DataReader trong solution. Thiết lập để project này làm StartUp project. Bước 2. Viết code như sau: using System; using System.Data; using System.Data.SqlClient; namespace P02_DataReader { }Ví dụ này đọc tất cả các bản ghi từ bảng Contacts và in ra màn hình. Trong ví dụ này sử dụng phương thức ExecuteReader và object của lớp SqlDataReader. Sử dụng phương thức ExecuteReaderĐể ý rằng phương thức ExecuteReader có một tham số thuộc kiểu CommandBehavior ( using System; using System.Data.SqlClient; namespace P01_Scalar { }
Nếu không đóng SqlDataReader sau khi đọc dữ liệu thì các truy vấn tiếp theo trên SqlCommand sẽ không thực thi được. Đóng SqlDataReader không đồng nghĩa với đóng kết nối. Một số giá trị khác của CommandBehavior bao gồm:
Mỗi truy vấn SELECT trả về một số dòng thuộc cùng một bảng. ADO.NET gọi nhóm dòng kết quả này là một tập kết quả (result set). SqlCommand cho phép thực hiện NHIỀU truy vấn cùng lúc. Khi đó một lệnh ExecuteReader có thể trả về NHIỀU tập kết quả. Đây là cách hoạt động mặc định (tham số Default). Ở chiều khác, nếu CommandBehavior được chỉ định là SingleResult thì SqlCommand chỉ lấy về một tập kết quả. Sử dụng SqlDataReaderKết quả trả về của ExecuteReader là một object của SqlDataReader. Object này hỗ trợ đọc dữ liệu theo kiểu forward-only và read-only như bạn đã biết. Nguyên tắc làm việc chung của SqlDataReader như sau:
Sử dụng phép toán indexer và số thứ tự của ô trên object của SqlDataReader: var id = (int) sqlDataReader[0]; var contactName = sqlDataReader[1] as string; var alias = sqlDataReader[2] as string; Sử dụng tên trường trong phép toán indexer: var id = (int) sqlDataReader["Id"]; var contactName = sqlDataReader["ContactName"] as string; var alias = sqlDataReader["Alias"] as string; Cần lưu ý rằng, khi đọc dữ liệu từ SqlDataReader thì cần duy trì mở connection. Chỉ khi nào hoàn thành đọc dữ liệu thì mới đóng connection. Lý do là vì SqlDataReader không chứa bản sao cục bộ của dữ liệu. Nó chỉ là công cụ giúp đọc kết quả truy vấn. Thực thi truy vấn INSERT – UPDATE – DELETEChúng ta xem xét tiếp cách thực hiện ba loại truy vấn INSERT – UPDATE – DELETE. Bước 1. Tạo thêm project P03_InsertUpdateDelete (Console App) trong solution. Bước 2. Viết code như sau: using System; using System.Data.SqlClient; namespace P03_InsertUpdateDelete { }Dịch và chạy chương trình sẽ thu được kết quả như sau: Như đã biết để thực hiện các loại truy vấn này chúng ta sử dụng phương thức ExecuteNonQuery() của SqlCommand. Phương thức này trả về số dòng chịu tác động của truy vấn tương ứng. Tham số trong truy vấn SQL, SqlParameterVấn đề tạo truy vấn từ dữ liệu người dùngTrong các ví dụ trên bạn đã biết cách thực thi các truy vấn SQL cơ bản trong C#. Tuy nhiên, các truy vấn này đều “tĩnh”, được “code cứng”. Vậy làm thế nào INSERT một bản ghi mới vào cơ sở dữ liệu mà các giá trị do người dùng nhập? Hẳn bạn có thể nghĩ ngay ra giải pháp. Vì truy vấn SQL thực chất chỉ là một chuỗi văn bản (kiểu using System; using System.Data.SqlClient; namespace P01_Scalar { }1). Vậy thì cứ để người dùng nhập dữ liệu vào. Bạn sẽ dùng các kỹ thuật định dạng chuỗi để tạo ra truy vấn từ dữ liệu người dùng nhập. Ví dụ: var contactName = Console.ReadLine(); var fistName = Console.ReadLine(); var lastName = Console.ReadLine(); var commandText = $"INSERT INTO [dbo].[Contacts] ([ContactName], [FirstName], [LastName]) VALUES ('{contactName}', '{firstName}', '{lastName}')"; // các thao tác còn lại bỏ qua Tuy đơn giản dễ hiểu nhưng đây là một giải pháp TỒI. Tại sao lại vậy? Bất kỳ thứ gì người dùng nhập vào qua các biến đều sẽ được đặt vào truy vấn SQL của bạn. Kể cả một chuỗi truy vấn khác cũng có thể bị chèn vào chuỗi truy vấn của bạn. Cách làm này dẫn đến một lỗi bảo mật rất phổ biến: SQL Injection. Do vậy, bạn KHÔNG BAO GIỜ được dùng cách này để tạo chuỗi truy vấn. ADO.NET cung cấp một giải pháp: sử dụng Parameter. Lớp SqlParameter – ví dụ minh họaĐể dễ hình dung, chúng ta cùng làm một ví dụ: using System; using System.Data.SqlClient; using System.Data; namespace P04_Parameter { }Dịch và chạy thử chương trình: Sử dụng SqlParameterQua ví dụ trên có thể thấy việc sử dụng tham số trong truy vấn Sql với SqlParameter rất đơn giản:
Khi này bất kỳ thứ gì được đặt vào một parameter sẽ được coi là dữ liệu. SqlCommand sẽ đặt đúng giá trị vào vị trí của tham số cho chúng ta cũng như sẽ thực hiện các thao tác xử lý phù hợp giúp truy vấn an toàn. Cách viết placeholder cho tham số có chút khác biệt giữa các provider. Sql Server sử dụng ký tự @, Oracle – dấu hai chấm :, OLEDB và ODBC sử dụng dấu chấm hỏi ?. Kết luậnBài học này đã giúp bạn hiểu rõ cách thức thực thi truy vấn Sql trong C# và kỹ thuật đọc dữ liệu từ kết quả của các truy vấn. Bạn cũng nên lưu ý rằng, các kỹ thuật chúng ta xem xét ở đây mang tính minh họa nhiều hơn là khả năng áp dụng thực tế. Các bài học thuộc phần này hướng tới giúp bạn hiểu nguyên lý của lập trình với cơ sở dữ liệu từ C#, thay vì cách vận dụng. Đây là bài học cuối của phần nội dung về lập trình ADO.NET. Nếu còn nhớ bài viết về kiến trúc ADO.NET bạn hẳn sẽ thắc mắc sao không nói tiếp về DataAdapter, DataSet, DataTable, tức là các thành phần disconnected của ADO.NET. Lý do là bản thân Microsoft đã khuyến nghị không tiếp tục sử dụng DataSet và các class liên quan do chúng được xem là công nghệ cũ. Thay vào đó nên sử dụng Entity Framework – nội dung chính của tập bài giảng này. Nếu vậy thì tại sao vẫn học SqlConnection, SqlCommand, SqlParameter, SqlDataReader? Lý do là Entity Framework xây dựng trên các thành phần này của ADO.NET. Bản thân Entity Framework phải sử dụng đến các thành phần này để làm việc với cơ sở dữ liệu. Trên thực tế, bạn có thể bắt đầu học thẳng Entity Framework. Tuy nhiên, việc nắm được các thành phần của ADO.NET có tác dụng tốt hơn khi học và làm việc cùng Entity Framework. Nó giúp bạn hiểu rõ các vấn đề nằm sâu bên dưới Entity Framework và cách thức làm việc với cơ sở dữ liệu từ chương trình C#. |