[ADO.NET Tutorial] Lesson 03: Đối tượng SqlCommand

(The C# Station ADO.NET Tutorial)

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 connection
SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);

// 2. Call Execute reader to get query results
SqlDataReader 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 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();

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 command
SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);

// 2. Call ExecuteNonQuery to send command
int 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 Demo
 using 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.

Nguồn tham khảo: http://www.csharp-station.com/Tutorials/AdoDotNet/lesson03.aspx

https://yinyangit.wordpress.com

2 thoughts on “[ADO.NET Tutorial] Lesson 03: Đối tượng SqlCommand

  1. Pingback: [ADO.NET Tutorial] Lesson 02: Đối tượng SqlConnection | fishgold192

  2. Pingback: [ADO.NET Tutorial] Lesson 02: Đối tượng SqlConnection – Tiểu Lam

Gửi phản hồi

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s