Quan hệ C# và Database :: SqlCommand

Ở phần một của loạt bài quan hệ C# và Database đã giới thiệu Kết nối C# với Database. Trong bài tiếp theo này sẽ giới thiệu về SqlCommand tầm quan trong và cách sử dụng SqlCommand

Có thể hiểu ngắn gọn là Lớp SqlCommand tạo một đối tượng để nắm giữ thông tin về mệnh lệnh. Nó phải đi kèm với một kết nối tồn tại để tạo và thi hành mệnh lệnh được viết ra. Xem ví dụ sau:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class Database : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        // Khai báo chuỗi kết nối
        string connectString = @"Server =.\SQL2005;Initial Catalog=BaiTap;User ID=sa;Password=******";
        // Khai báo câu truy vấn
        string sql = @"SELECT  count(*) FROM Customers";
        // Tạo một connection tới máy chủ
        SqlConnection conn = new SqlConnection(connectString);
        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            Label1.Text="Số bản ghi:= "+cmd.ExecuteScalar();

        }
        catch (SqlException ex)
        {
            Console.WriteLine("Error: " + ex);
        }
        finally
        {
            conn.Close();
        }
    }
}

Trong trang aspx bạn tạo một Label (<asp:Label ID="Label1" runat="server" />) để hiển thị kết quả. Khi chạy sẽ cho kết quả là: Số bản ghi:= 91 ( 91 là tổng số bản ghi trong bảng Customers)
Lưu ý:
- Một SqlCommand thông thường được khởi tạo như trên nó phải đi với một query và một connection, cụ thể ở đây là sql và conn. Nó nên đặt trong khối lệnh try vì nếu đặt ở ngoài khi kết nối không thành công thì chương trình ngưng hoạt động, ở tình trạng treo mà vẫn tiêu hao tài nguyên.
- Các cách thực thi của SqlCommand

Phương thức (Method)	Giá trị trả về (Return Value)
ExecuteNonQuery()	Trả về số hàng bị ảnh hưởng bởi câu lệnh SQL. Thường được sử dụng với các câu lệnh không trả về dữ liệu như Insert, delete, update,...
ExecuteScalar()	Trả về hàng đầu tiên, cột đầu tiên của (một tập hợp) kết quả, các hàng/cột còn lại (nếu có) sẽ bị bỏ qua. Thường được sử dụng với các câu lệnh chỉ trả về 1 hàng, 1 cột kết quả (vd đếm số lượng nhân viên trong công ty).
ExecuteReader()	Trả về đối tượng SqlDataReader - thường dùng cho việc đọc kết quả trả về của câu lệnh SQL là 1 tập hợp gồm nhiều hàng, nhiều cột - đối tượng này sẽ được giới thiệu kỹ hơn trong phần sau.
ExecuteXmlReader()	Trả về đối tượng XmlReader - thường dùng để đọc kết quả trả về của câu lệnh SQL được lưu trữ ở dạng XML.

Trong ví dụ trên câu lệnh truy vấn chỉ trả về một giá trị nên ta sử dụng ExecuteScalar() Trường hợp câu truy vấn trả về nhiều giá trị (Dữ liệu dạng bảng) khi đó ta sẽ sử dụng ExecuteReader() xem ví dụ sau:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class Database : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string strHTML = "";
        strHTML+="<table border=\"0\" width=\"550\" cellspacing=\"1\" cellpadding=\"0\" bgcolor=\"#999966\" id=\"table2\">";
        strHTML+=" <tr>";
        strHTML += "  <td bgcolor=\"#CC3300\" align=\"center\"><font color=\"#FFFFFF\">CustomerID</font></td>";
        strHTML += "  <td bgcolor=\"#CC3300\" align=\"center\"><font color=\"#FFFFFF\">CompanyName</font></td>";
        strHTML += "  <td bgcolor=\"#CC3300\" align=\"center\"><font color=\"#FFFFFF\">ContactName</font></td>";
        strHTML += "  <td bgcolor=\"#CC3300\" align=\"center\"><font color=\"#FFFFFF\">Country</font></td>";
        strHTML+=" </tr>";
        // Khai báo chuỗi kết nối
        string connectString = @"Server =.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=******";
        // Khai báo câu truy vấn
        string sql = @"SELECT TOP 10 CustomerID, CompanyName, ContactName, Country FROM Customers ";
        // Tạo một connection tới máy chủ
        SqlConnection conn = new SqlConnection(connectString);
        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                strHTML += " <tr>";
                strHTML += "  <td bgcolor=\"#FFFFFF\">" + reader.GetValue(0) + "</td>";
                strHTML += "  <td bgcolor=\"#FFFFFF\">" + reader.GetValue(1) + "</td>";
                strHTML += "  <td bgcolor=\"#FFFFFF\">" + reader.GetValue(2) + "</td>";
                strHTML += "  <td bgcolor=\"#FFFFFF\">" + reader.GetValue(3) + "</td>";
                strHTML += " </tr>";
            }
            reader.Close();//Đóng SqlDataReader
        }

        catch (SqlException ex)
        {
            Console.WriteLine("Error: " + ex);
        }
        finally
        {
            conn.Close();
        }
        strHTML += "</table>";
        Literal1.Text = strHTML;
    }
}

Trong trang aspx bạn cần tạo <asp:Literal ID="Literal1" runat="server" /> để hiển thị dữ liệu

protected void Page_Load(object sender, EventArgs e)
    {
        string connectString = @"Server =.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=******";
        string queryInsert = @"INSERT INTO Employees (firstname,lastname) VALUES ('Pete','Houston')";
        string queryDelete = @"DELETE FROM Employees WHERE firstname = 'Pete' AND lastname = 'Houston'";
        // Tạo một kết nối tới máy chủ
        SqlConnection conn = new SqlConnection(connectString);
        try
        {
            conn.Open();
            // Thực thi câu lệnh Insert
            SqlCommand cmdInsert = new SqlCommand(queryInsert, conn); 
            cmdInsert.ExecuteNonQuery();
            // Thực thi lệnh Delete
            SqlCommand cmdDelete = new SqlCommand(queryDelete, conn);
            cmdDelete.ExecuteNonQuery();
            
        }
        catch (SqlException ex)
        {
            Console.WriteLine("Error: " + ex);
        }
        finally
        {
            conn.Close();
        }
    }

Khi thực thi code như ví dụ trên nó sẽ lần lượt thêm và xóa một bản ghi trong bảng Employees bằng 2 lệnh
cmdInsert.ExecuteNonQuery();
cmdDelete.ExecuteNonQuery();
Tương tự như vậy bạn có thể viết câu lệnh cho việc update 1 bản ghi
Tiếp theo chúng ta sẽ tìm hiều về cách chuyền tham số cho PARAMETERS (khá quan trọng): Không lẽ mỗi lần thay đổi lệnh ta lại thay đổi các query? Từ bất cập mà parameters ra đời, nó cho phép ta truyền vào các tham số một cách linh động và hiệu quả. Bạn xem ví dụ sau để hiểu về  PARAMETERS:

string connectString = @"Server =.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=******";
        string queryInsert = @"INSERT INTO Employees (firstname,lastname) VALUES (@firstname,@lastname)";
        // Tạo một connection tới máy chủ
        SqlConnection conn = new SqlConnection(connectString);
        try
        {
            conn.Open();
            SqlCommand cmdInsert = new SqlCommand(queryInsert, conn);
            //Khởi tạo
            cmdInsert.Parameters.Add("@firstname", SqlDbType.NVarChar, 10);
            cmdInsert.Parameters.Add("@lastname ", SqlDbType.NVarChar, 20);
            //Truyền giá trị
            cmdInsert.Parameters["@firstname"].Value = "Bui ";
            cmdInsert.Parameters["@lastname "].Value = "Hung";
            //Thực thi lệnh
            cmdInsert.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            Console.WriteLine("Error: " + ex);
        }
        finally
        {
            conn.Close();
        }

Như vậy là trong bảng Employees bạn đã có thêm 1 Employee với fristname = "Bui " và lastname ="Hung"

Nội dung về SqlCommand được giới thiệu sơ qua như vậy, Hy vọng qua bài này bạn có thể hiểu hơn về SqlCommand.