[ADO.NET Tutorial] Lesson 03: Đối tượng SqlCommand Lesson này giới thiệu về đối tượng SqlCommand và cách sử dụng để thao tác với database. Qua bài viết này, bạn sẽ biết cách sử dụng các phương thức ExecuteReader(), ExecuteNonQuery() và ExecuteScalar() của đối tượng command. Giới thiệu Đối tượng SqlCommand cho phép bạn chọn kiểu tương tác mà bạn muốn thực hiện với database. Ví dụ, bạn có thể thực hiện các lệnh select, insert, modify, và delete các dòng trong một table của database. Đối tượng này có thể được dùng để hỗ trợ mô hình quản lý dữ liệu ngắt kết nối (disconnected), nhưng trong bài học này chúng ta chỉ dùng đối tượng SqlCommand làm việc độc lập. Bài học này cũng cho bạn thấy cách để lấy một giá trị đơn từ database, như là số dòng của một table. Tạo một đối tượng SqlCommand Tương tự như các đối tượng C# khác, bạn tạo đối tượng SqlCommand bằng cách khai báo một thể hiện của nó: SqlCommand cmd = new SqlCommand(“select CategoryName from Categories”, conn); Dòng trên là điển hình để tạo một đối tượng SqlCommand. Nó lấy một tham số là chuỗi lệnh bạn cần thực thi và một tham chiếu đến đối tượng SqlConnection. SqlCommand có một vài overload, mà bạn sẽ thấy trong các ví dụ của tutorial này. Querying Data – Truy vấn dữ liệu Khi dùng một lệnh SQL select, bạn lấy được một dữ liệu từ database để hiển thị. Để làm được điều này với SqlCommand, bạn cần dùng phương thức ExecuteReader() để trả về một đối tượng SqlDataReader. Chúng ta sẽ thảo luận về SqlDataReader trong bài tiếp theo. Ví dụ sau cho thấy cách dùng SqlCommand để lấy được một đối tượng SqlDataReader: // 1. Instantiate a new command with a query and connectionSqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);// 2. Call Execute reader to get query resultsSqlDataReader rdr = cmd.ExecuteReader(); Trong ví dụ trên, chúng ta tạo một đối tượng SqlCommand, truyền chuỗi lệnh và đối tượng connection vào constructor. Sau đó chúng ta lấy về đối tượng SqlDataReader bằng cách gọi phương thức ExecuteReader() của đối tượng SqlCommand, cmd. Inserting Data – Chèn dữ liệu Để chèn dữ liệu vào database, dùng phương thức ExecuteNonQuery() của đối tượng SqlCommand. Đoạn code sau cho thấy cách chèn dữ liệu vào một bảng trong database: // prepare command stringstring insertString = @" insert into Categories (CategoryName, Description) values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";// 1. Instantiate a new command with a query and connectionSqlCommand cmd = new SqlCommand(insertString, conn);// 2. Call ExecuteNonQuery to send commandcmd.ExecuteNonQuery(); Lưu ý hai dấu nháy đơn (‘’) trong chuỗi insertString của từ “doesn’’t” (không phải dấu nháy kép). Đây là cách để bạn chèn một dấu nháy đơn vào chuỗi trong trường hợp chuỗi đó được bao bởi hai dấu nháy đơn (‘) thay vì nháy kép (“). SQL sẽ xem hai dấu nháy đơn đứng sát nhau là một dấu nháy đơn, tương tự như cách viết (\’) trong C#. Một điểm cần chú ý nữa là chúng ta xác định rõ các cột CategoryName và Description. Bảng Categories có một primary key là CategoryID. Chúng ta không cần chèn dữ liệu vào cột này bởi vì SQL Server sẽ tự động thêm vào. Nếu bạn thử thêm giá trị vào cột primary key, như CategoryID, một ngoại lệ sẽ được tạo ra. Để thực thi lệnh này, chỉ cần đơn giản gọi phương thức ExecuteNonQuery() của đối tượng SqlCommand, cmd. Updating Data – Cập nhật dữ liệu Phương thức ExecuteNonQuery cũng được dùng để cập nhật dữ liệu, như đoạn mã sau: // prepare command string string updateString = @" update Categories set CategoryName = 'Other' where CategoryName = 'Miscellaneous'"; // 1. Instantiate a new command with command text only SqlCommand cmd = new SqlCommand(updateString); // 2. Set the Connection property cmd.Connection = conn; // 3. Call ExecuteNonQuery to send command cmd.ExecuteNonQuery(); Một lần nữa, chúng ta đặt câu SQL trong một biến string, nhưng lần này chúng ta dùng một constructor khác của SqlCommand, chỉ có một tham số là câu lệnh SQL. Trong bước 2, chúng ta gán đối tượng SqlConnection, conn, cho property Connection của đối tượng SqlCommand, cmd. Thay vì cách này, bạn có thể sử dụng constructor với hai tham số tương tự như cho đoạn mã insert phần trên. Nó cho thấy rằng bạn có thể thay đổi đối tượng connection gán cho một command bất kì lúc nào. Bước cuối cùng, phương thức ExecuteNonQuery() thực thi lệnh update. Deleting Data – Xóa dữ liệu Bạn cũng có thể xóa dữ liệu bằng phương thức ExecuteNonQuery(). Ví dụ sau cho thấy cách xóa một dòng từ database với phương thức ExecuteNonQuery(): // prepare command string string deleteString = @" delete from Categories where CategoryName = 'Other'"; // 1. Instantiate a new command SqlCommand cmd = new SqlCommand(); // 2. Set the CommandText property cmd.CommandText = deleteString; // 3. Set the Connection property cmd.Connection = conn; // 4. Call ExecuteNonQuery to send command cmd.ExecuteNonQuery(); Ví dụ này sử dụng constructor không có tham số của SqlCommand. Thay vào đó, nó gán cho hai property CommandText và Connection của đối tượng SqlCommand, cmd. Chúng ta cũng có thể dùng hai overload constructor trước đó của SqlCommand, dùng để insert và update, với kết quả tương tự. Điều này cho thấy rằng bạn có thể thay đôi cả chuỗi lệnh và đối tượng connection bất kì lúc nào. Lời gọi phương thức ExecuteNonQuery() gửi lệnh đến database. Lấy một giá trị đơn Đôi lúc tất cả những gì bạn cần từ database chỉ là một giá trị đơn, đó có thể là một giá trị đếm, tổng, trung bình, hoặc các giá trị kết hợp khác từ dữ liệu. Thực thi phương thức ExecuteReader() để lấy về một đối tượng SqlDataReader và tính toán kết quả trong mã lệnh của bạn không phải cách tốt để làm điều này. Cách tốt nhất là để database làm công việc này và trả về giá trị bạn cần. Ví dụ sau cho thấy cách làm điều này với phương thức ExecuteScalar(): // 1. Instantiate a new commandSqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);// 2. Call ExecuteNonQuery to send commandint count = (int)cmd.ExecuteScalar(); Câu truy vấn trong constructor của SqlCommand sẽ lấy về giá trị đếm của tất cả dòng trong bảng Categories. Câu truy vấn này chỉ trả về một giá trị đơn. Phương thức ExecuteScalar() trong bước 2 trả về giá trị này. Bởi vì kiểu trả về của ExecuteScalar() là object, chúng ta cần ép kiểu để chuyển giá trị sang int. Kết hợp tất cả với nhau Để đơn giản, tôi cho thấy các đoạn mã ngắn trong những phần trước để minh họa các kĩ thuật tương ứng. Nó cũng rất hữu ích để xem cách mà mã lệnh thực hiện trong một đoạn mã hoàn chỉnh của chương trình. Listing 1 cho thấy tất cả mã lệnh được dùng trong ví dụ này, cùng với phương thức Main() để thực thi và hiển thị kết quả xuất ra: Listing 1. SqlConnection Demousing System; using System.Data; using System.Data.SqlClient; ///<summary> /// Demonstrates how to work with SqlCommand objects /// </summary> class SqlCommandDemo { SqlConnection conn; public SqlCommandDemo() { // Instantiate the connection conn = new SqlConnection( "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI"); } // call methods that demo SqlCommand capabilities static void Main() { SqlCommandDemo scd = new SqlCommandDemo(); Console.WriteLine(); Console.WriteLine("Categories Before Insert"); Console.WriteLine("------------------------"); // use ExecuteReader method scd.ReadData(); // use ExecuteNonQuery method for Insert scd.Insertdata(); Console.WriteLine(); Console.WriteLine("Categories After Insert"); Console.WriteLine("------------------------------"); scd.ReadData(); // use ExecuteNonQuery method for Update scd.UpdateData(); Console.WriteLine(); Console.WriteLine("Categories After Update"); Console.WriteLine("------------------------------"); scd.ReadData(); // use ExecuteNonQuery method for Delete scd.DeleteData(); Console.WriteLine(); Console.WriteLine("Categories After Delete"); Console.WriteLine("------------------------------"); scd.ReadData(); // use ExecuteScalar method int numberOfRecords = scd.GetNumberOfRecords(); Console.WriteLine(); Console.WriteLine("Number of Records: {0}", numberOfRecords); } ///<summary> /// use ExecuteReader method /// </summary> public void ReadData() { SqlDataReader rdr = null; try { // Open the connection conn.Open(); // 1. Instantiate a new command with a query and connection SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn); // 2. Call Execute reader to get query results rdr = cmd.ExecuteReader(); // print the CategoryName of each record while (rdr.Read()) { Console.WriteLine(rdr[0]); } } finally { // close the reader if (rdr != null) { rdr.Close(); } // Close the connection if (conn != null) { conn.Close(); } } } ///<summary> /// use ExecuteNonQuery method for Insert /// </summary> public void Insertdata() { try { // Open the connection conn.Open(); // prepare command string string insertString = @" insert into Categories (CategoryName, Description) values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')"; // 1. Instantiate a new command with a query and connection SqlCommand cmd = new SqlCommand(insertString, conn); // 2. Call ExecuteNonQuery to send command cmd.ExecuteNonQuery(); } finally { // Close the connection if (conn != null) { conn.Close(); } } } ///<summary> /// use ExecuteNonQuery method for Update /// </summary> public void UpdateData() { try { // Open the connection conn.Open(); // prepare command string string updateString = @" update Categories set CategoryName = 'Other' where CategoryName = 'Miscellaneous'"; // 1. Instantiate a new command with command text only SqlCommand cmd = new SqlCommand(updateString); // 2. Set the Connection property cmd.Connection = conn; // 3. Call ExecuteNonQuery to send command cmd.ExecuteNonQuery(); } finally { // Close the connection if (conn != null) { conn.Close(); } } } ///<summary> /// use ExecuteNonQuery method for Delete /// </summary> public void DeleteData() { try { // Open the connection conn.Open(); // prepare command string string deleteString = @" delete from Categories where CategoryName = 'Other'"; // 1. Instantiate a new command SqlCommand cmd = new SqlCommand(); // 2. Set the CommandText property cmd.CommandText = deleteString; // 3. Set the Connection property cmd.Connection = conn; // 4. Call ExecuteNonQuery to send command cmd.ExecuteNonQuery(); } finally { // Close the connection if (conn != null) { conn.Close(); } } } ///<summary> /// use ExecuteScalar method /// </summary> /// number of records public int GetNumberOfRecords() { int count = -1; try { // Open the connection conn.Open(); // 1. Instantiate a new command SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn); // 2. Call ExecuteScalar to send command count = (int)cmd.ExecuteScalar(); } finally { // Close the connection if (conn != null) { conn.Close(); } } return count; } } Trong Lising 1, đối tượng SqlConnection được khởi tạo trong lớp SqlCommandDemo. Điều này hợp lệ vì đối tượng sẽ được dọn dẹp khi CLR garbage collector làm việc. Điều quan trọng là ta đóng connection sau khi sử dụng nó. Chương trình mở connection trong một khổi try và đóng nó trong một khối finally cho mỗi phương thức. Phương thức ReadData() hiển thị nội dung của cột CategoryName của bảng Categories. Chúng ta dùng nó vài lần trong phương thức Main() để hiển thị trạng thái hiện tại của bảng Categories. Bảng này sẽ bị thay đổi khi mỗi lệnh insert, update, delete được thực hiện. Tổng kết Đối tượng SqlCommand cho phép bạn truy vấn và gửi lệnh đến một database. Nó có các phương thức sử dụng cho các lệnh khác nhau. Phương thức ExecuteReader() trả về một đối tượng SqlDataReader để hiển thị kết quả của câu truy vấn. Cho các lệnh insert, update và delete, bạn dùng phương thức ExecuteNonQuery(). Nếu bạn chỉ cần một giá trị đơn từ một câu truy vấn, phương thức ExecuteScalar() là lựa chọn tốt nhất. Tôi hi vọng bạn thích lesson này và chào mừng bạn đến bài tiếp theo trong series này, Lesson 04: Đọc dữ liệu với SqlDataReader. Tags: