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

Thảo luận trong 'C#.NET, Windows Form' bắt đầu bởi nhatlectv, 7/5/16.

  1. nhatlectv

    nhatlectv Stanford - Nâng tầm tri thức Thành viên BQT

    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.

    [​IMG]

    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.
     
  2. nhatlectv

    nhatlectv Stanford - Nâng tầm tri thức Thành viên BQT

    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.
     

Chia sẻ trang này