C# Cơ bản .NET Core §1) Cài đặt, chương trình C# đầu tiên §2) Biến, kiểu dữ liệu và nhập/xuất §3) Toán tử số học và gán §4) So sánh, logic và lệnh if, switch §5) Vòng lặp for, while §6) Phương thức - Method §7) Phương thức - Delegate §8) Lớp - Class §9) Kiểu vô danh và dynamic §10) Biểu thức lambda §11) Event §12) Hàm hủy - Quá tải toán tử - thành viên tĩnh - indexer §13) Lớp lồng nhau - namespace §14) null và nullable §15) Mảng §16) Chuỗi ký tự §17) Tính kế thừa §18) Tính đa hình - abstract - interface §19) Struct và Enum §20) Ngoại lệ Exeption §21) IDisposable - using §22) File cơ bản §23) FileStream §24) Generic §25) Collection - List §26) SortedList §27) Queue / Stack §28) Linkedlist §29) Dictionary - HashSet §30) Phương thức mở rộng §31) ObservableCollection §32) LINQ §33) (Multithreading) async - bất đồng bộ §34) Type §35) Attribute Annotation §36) DI Dependency Injection §37) (Multithreading) Parallel §38) (Networking) HttpClient §39) (Networking) HttpMessageHandler §40) (Networking) HttpListener §41) (Networking) Tcp TcpListenerr/TcpClient §42) (ADO.NET) SqlConnection §43) (ADO.NET) SqlCommand §44) (EF Core) Tổng quan §45) (EF Core) Tạo Model §46) (Asp.net core) Hello World! §47) (Asp.net Core) Map - Request - Response §48) (Asp.net Core) IServiceCollection - MapWith §49) (Asp.net Core) Session - ISession §50) (Asp.net Core) Middleware §51) (Asp.net Core) Configuration
Lập trình C# (C Sharp)

Lớp SqlCommand

Lớp SqlCommand cho phép tạo ra đối tượng mà từ đó có thể thi hành các lệnh SQL như các lệnh UPDATE|INSERT|CREATE TABLE|SELECT ... cũng như cho phép thi hành các hàm, các stored procedure của Database.

Để tạo và thi hành được SqlCommand thì cần thiết lập cho nó câu lệnh SQL (truy vấn), và các tham số cho lệnh SQL đó, đồng thời phải thiết lập SqlConnection vào đối tượng SqlCommand.

Ví dụ, khởi tạo SqlCommand - thiết lập ngay câu query và kết nối

string sqlconnectStr     = "Data Source=localhost,1433;Initial Catalog=xtlab;User ID=SA;Password=Password123";
SqlConnection connection = new SqlConnection(sqlconnectStr);
connection.Open();

string queryString =  "SELECT [CategoryID],[CategoryName] FROM Categories";
using (SqlCommand cmd = new SqlCommand(queryString, connection)) {
    // Thiết lập tham số cmd cmd.Parameters nếu cần thiết
    // Thi hành lệnh  (như ) cmd.ExecuteNonQuery();
}
connection.Close();

Cũng có thể tạo đối tượng SqlCommand, sau đó thiết lập câu truy vấn và kết nối ...

string queryString =  "SELECT [CategoryID],[CategoryName] FROM Categories";
SqlCommand  cmd    = new SqlCommand();
cmd.CommandText    = queryString;
cmd.Connection     = connection;
/. thực hiện lệnh  ...

cmd.Dispose();

Từ đối tượng SqlConnection cũng có thể tạo ngay SqlCommand gắn với kết nối đó:

string queryString    =  "SELECT [CategoryID],[CategoryName] FROM Categories";
using (SqlCommand cmd = connection.CreateCommand())
{
    cmd.CommandText   = queryString;
    /. thực hiện lệnh
}

Như vậy để thi hành lệnh SQL với SqlCommand, thì cần có một kết nối trước (SqlConnection), rồi tạo ra đối tượng SqlCommand, gán cho nó kết nối, câu lệnh SQL sau đó mới thi hành được. Để thi hành, gọi một trong các phương thức như ExecuteScalar, ExecuteNonQuery, ExecuteReader ...

Thiết lập các tham số cho SqlCommmand

Các câu lệnh SQL có thể viết chứa tên tham số trong nó, sau đó giá trị thực của tham số này được SqlCommand gán thay vào. Tham số trong chuỗi câu lệnh SQL ký hiệu là @tenthamso (nhớ có ký hiệu @ ), ví dụ:

string queryString =  "SELECT [CategoryID],[CategoryName] FROM Categories Where CategoryID > @CateID";

Thì có một tham số tên @CateID. Tham số này được thêm vào/ thiết lập qua Parameters của SqlCommand. Ví dụ thêm bằng Parameters.AddWithValue

string queryString =  "SELECT [CategoryID],[CategoryName] FROM Categories Where CategoryID > @CateID";
using (SqlCommand cmd = connection.CreateCommand()) {
    cmd.CommandText = queryString;
    cmd.Parameters.AddWithValue("@CateID", 5);  // Thiết lập @CateID = 5
    /.. thực hiện lệnh
}

Cũng có thể tạo ra đối tượng SqlParameter với tên tham số sau đó thêm vào bằng cách gọi Parameters.Add(CateIDParam)

string queryString =  "SELECT [CategoryID],[CategoryName] FROM Categories Where CategoryID > @CateID";
SqlParameter CateIDParam = new SqlParameter("@CateID", SqlDbType.Int);
using (SqlCommand cmd = connection.CreateCommand()) {
    cmd.CommandText = queryString;
    cmd.Parameters.Add(CateIDParam); // Thêm tham số

    /.. thực hiện lệnh
}

Các cách thi hành SqlCommand

Có các phương thức khác nhau để thi hành SqlCommand tùy theo ngữ cảnh với mục đích khác nhau, gồm có các phương thức như:

  • ExecuteNonQuery() thi hành loại lệnh - không cần trả về dữ liệu gì
  • ExecuteReader() thi hành lệnh - trả về IDataReader từ đó đọc được dữ liệu
  • ExecuteScalar() thì hành và trả về một giá trị duy nhất - ở hàng đầu tiên, cột đầu tiên

Thi hành bằng ExecuteScalar()

Nếu thi hành SqlCommand bằng phương thức ExecuteScalar thì nó sẽ thi hành lệnh SQL và trả về 1 giá trị là cột đầu tiên của dòng đầu tiên. Lưu ý: giá trị có độ dài tối đa 2033 ký tự

Ví dụ sau sẽ chèn một dòng mới vào bảng và trả về giá trị định danh của dòng mới chèn vào (ID)

string sqlconnectStr     = "Data Source=localhost,1433;Initial Catalog=xtlab;User ID=SA;Password=Password123";
SqlConnection connection = new SqlConnection(sqlconnectStr);
connection.Open();

string queryString = "INSERT INTO Shippers (ShipperName, Phone) VALUES (@Name, @Phone);" + //Chèn dữ liệu mới
                     "SELECT CAST(scope_identity() AS int)";                               //Lấy định danh dòng mới chèn vào

using (SqlCommand cmd = connection.CreateCommand()) {
    cmd.CommandText = queryString;
    cmd.Parameters.AddWithValue("@Name", "ABC");
    cmd.Parameters.AddWithValue("@Phone", 123456);
    var id = cmd.ExecuteScalar();
    Console.WriteLine($"ID dữ liệu: {id}");

}

connection.Close();

Thi hành với ExecuteNonQuery

Thi hành SqlCommand bằng phương thức ExecuteNonQuery nó chỉ trả về kết quả là số dòng dữ liệu bị ảnh hưởng (số dòng xóa, số dòng update ...). Thường dùng cách này để thi hành các truy vấn UPDATE, INSERT, DELETE. Tuy nhiên, nếu là gọi Procedure thì có kết quả trả về.

string sqlconnectStr     = "Data Source=localhost,1433;Initial Catalog=xtlab;User ID=SA;Password=Password123";
SqlConnection connection = new SqlConnection(sqlconnectStr);
connection.Open();

string queryString = "INSERT INTO Shippers (ShipperName, Phone) VALUES (@Name, @Phone);"
using (SqlCommand cmd = connection.CreateCommand()) {
    cmd.CommandText = queryString;
    cmd.Parameters.AddWithValue("@Name", "XYZ");
    cmd.Parameters.AddWithValue("@Phone", 223344);
    var rows = cmd.ExecuteNonQuery();
    Console.WriteLine($"Số dòng ảnh hưởng: {rows}");

}

connection.Close();

Thi hành với ExecuteReader

Thi hành SqlCommand với phương thức thì nó sẽ tạo ra đối tượng SqlDataReader được mở sẵn, từ đối tượng đó giúp đọc từng dòng kết quả trả về.

Một số phương thức trong SqlDataReader

  • SqlDataReader.HasRows() cho biết có dòng dữ liệu nào không
  • SqlDataReader.Read() nạp dữ liệu dòng tiếp theo, nếu trả về true là có dòng dữ liệu nạp về thành công, nếu false là đã hết dữ liệu nạp về. Sau khi gọi phương thực này, thì các cột của dòng có thể đọc bằng các toán tử [cột], hoặc các hàm đọc dữ liệu như .GetInt32(cột), .GetString(cột) ...
  • SqlDataReader.Close() đóng Reader sau khi đọc xong dữ liệu
string sqlconnectStr     = "Data Source=localhost,1433;Initial Catalog=xtlab;User ID=SA;Password=Password123";
SqlConnection connection = new SqlConnection(sqlconnectStr);
connection.Open();

using (SqlCommand command = new SqlCommand("SELECT CategoryID, CategoryName FROM Categories;", connection))
using (SqlDataReader reader = command.ExecuteReader())
{
    if (reader.HasRows)
    {
        // Đọc kết quả
        while (reader.Read())
        {
            Console.WriteLine("{0}\t{1}", reader[0].ToString(), reader.GetString(1));
        }
    }
    else  Console.WriteLine("No rows found.");
}

Ngoài ra khi có được đối tượng SqlDataReader, có thể lấy toàn bộ kết quả trả về của SqlCommand đưa vào DataTable

//...
SqlCommand command = new SqlCommand("SELECT CategoryID, CategoryName FROM Categories;", connection);
using (SqlDataReader reader = command.ExecuteReader())
{
    DataTable myTable = new DataTable();

    if (reader.HasRows)
    {
        myTable.Load(reader);
    }
    else
    {
        //No rows
    }
}
//...

ExecuteXmlReader

Thi hành SqlCommand với phương thức thì nó sẽ tạo ra đối tượng System.Xml.XmlReader, từ đối tượng đó giúp đọc từng dòng kết quả trả về theo cấu trúc XML.

Gọi Procedure của DB

Mặc định SqlCommand sẽ coi nội dung trong thuộc tính CommandText là câu lệnh SQL vì nó đã thiết lập CommandType bằng CommandType.Text (xem ví dụ trên). Nếu muốn gọi đến Procedure thì thiết lập nó bằng CommandType.StoredProcedure. Giả sử muốn gọi đến stored procedure có tên myprocedure có một tham số tên @id

//...
SqlCommand cmd   = new SqlCommand("myprocedure", connection);
cmd.CommandType  = CommandType.StoredProcedure;
cmd.Parameters.Add(
    new SqlParameter() {
        ParameterName   = "@id",
        SqlDbType       = SqlDbType.Int,
        Value           = 123
    }
);

using (SqlDataReader reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        var col = (int)reader["colname"];
        /..
    }
}
//..

Chú ý, các phương thức của thi hành của SqlCommand đều có phương thức bất đồng bộ tương ứng như ExecuteNonQueryAsync(), ExecuteReaderAsync() ... để thích thì áp dụng kỹ thuật async, kể các SqlDataReader với ReadAsync()

Đăng ký theo dõi ủng hộ kênh