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 Namespace §10 Partial, Nested §11 Kiểu giá trị, tham chiếu §12 Kiểu vô danh và dynamic §13 Biểu thức lambda §14 Event §15 Hàm hủy - Quá tải toán tử - thành viên tĩnh - indexer §16 null và nullable §17 Mảng §18 Chuỗi ký tự §19 Tính kế thừa §20 Phương thức khởi tạo §21 Tính đa hình - abstract - interface §22 Struct và Enum §23 Ngoại lệ Exeption §24 IDisposable - using §25 File cơ bản §26 FileStream §27 Generic §28 Collection - List §29 SortedList §30 Queue / Stack §31 Linkedlist §32 Dictionary - HashSet §33 Phương thức mở rộng §34 ObservableCollection §35 LINQ §36 (Multithreading) async - bất đồng bộ §37 Type §38 Attribute Annotation §39 DI Dependency Injection §40 (Multithreading) Parallel §41 Thư viện lớp §42 (Networking) HttpClient §43 (Networking) HttpMessageHandler §44 (Networking) HttpListener §45 (Networking) Tcp TcpListenerr/TcpClient §46 (ADO.NET) SqlConnection §47 (ADO.NET) SqlCommand §48 (EF Core) Tổng quan §49 (EF Core) Tạo Model §50 (EF Core) Fluent API §51 (EF Core) Query §52 (EF Core) Scaffold §53 (EF Core) Migration §54 (ASP.NET CORE) Hello World! §55 (ASP.NET CORE) Middleware §56 (ASP.NET CORE) Map - Request - Response §57 (ASP.NET CORE) IServiceCollection - MapWhen §58 (ASP.NET CORE) Session - ISession §59 (ASP.NET CORE) Configuration §60 (ASP.NET CORE) Gửi Mail §61 (ASP.NET CORE) SASS/SCSS §62 (ASP.NET CORE) LibMan §63 (ASP.NET RAZOR) Khởi tạo và Route §64 (ASP.NET RAZOR) Cú pháp Razor §65 (ASP.NET RAZOR) Layout trong ASP.NET Core §66 (ASP.NET RAZOR) Partial §67 (ASP.NET RAZOR) ViewComponent §68 (ASP.NET RAZOR) TagHelper §69 (ASP.NET RAZOR) PageModel §70 (ASP.NET RAZOR) Model Binding §71 (ASP.NET RAZOR) HTML Form, Validation §72 (ASP.NET RAZOR) Upload File §73 (ASP.NET RAZOR) HtmlHelper §74 (ASP.NET RAZOR) Entity Framework §75 (ASP.NET RAZOR) Paging §76 (ASP.NET RAZOR) Identity (1) - Register, Login, Logout §77 (ASP.NET RAZOR) Identity (2) Lockout, Reset Password §78 (ASP.NET RAZOR) Identity (3) Google Login §79 (ASP.NET RAZOR) Identity (4) Facebook Login §80 (ASP.NET RAZOR) Identity (5) profile, password, email ... §81 (ASP.NET RAZOR) Identity (6) Role §82 (ASP.NET RAZOR) Identity (7) Role-based Authorization §83 (ASP.NET RAZOR) Identity (8) RoleClaim §84 (ASP.NET RAZOR) Identity (9) Authorization Handler §85 (ASP.NET RAZOR) IAuthorizationService §86 (ASP.NET MVC) Controller - View §87 (ASP.NET MVC) Route §88 (ASP.NET MVC) EF, Identity §89 (ASP.NET MVC) Binding, Validation §90 (ASP.NET MVC) Xây dựng Website(1) §91 (ASP.NET MVC) Xây dựng Website(2) §92 (ASP.NET MVC) Xây dựng Website(3) §93 (ASP.NET MVC) Xây dựng Website(4) §94 (ASP.NET MVC) Giỏ hàng - Cart (5) §95 (ASP.NET MVC) elFinder (5) §96 (ASP.NET MVC) SB Admin (6) §97 (ASP.NET MVC) Kestrel, publish
Lập trình C# (C Sharp)

Lớp SqlCommand - Khởi tạo đối tượng 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 tương tác với MS SQL Server 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 thông tin kết nối đến SQL Server SqlConnection vào đối tượng SqlCommand.

Để nhanh chóng có một MS SQL Server kèm dữ liệu mẫu, hãy dùng Docker và thực hiện theo hướng dẫn: MS SQL Server trên Docker

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

SqlCommand cmd = new SqlCommand (queryString, connection);
using System;
using System.Data;
using System.Data.SqlClient;

namespace SqlCommandExam {
  public partial class Program {

    public static void CreateSqlCommand01 () {

      // Mở kết nối đến SQL Server
      string sqlconnectStr = "Data Source=localhost,1433;Initial Catalog=xtlab;User ID=SA;Password=Password123";
      SqlConnection connection = new SqlConnection (sqlconnectStr);
      connection.Open ();

      string queryString = "SELECT TenDanhMuc, MoTa FROM Danhmuc";
      using (SqlCommand cmd = new SqlCommand (queryString, connection)) {

        // Mã sử dụng SqlCommand ở đây
        // Thiết lập tham số cmd cmd.Parameters nếu cần thiết
        // Thi hành lệnh  (như ) cmd.ExecuteReader(); ...

      }

      // Đóng kết nối khi không còn dùng
      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 TenDanhMuc, MoTa FROM Danhmuc";
SqlCommand  cmd    = new SqlCommand();
cmd.CommandText    = queryString;
cmd.Connection     = connection;

        // thực hiện lệnh, tác vụ  ...

cmd.Dispose();

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

string queryString    =  "SELECT TenDanhMuc, MoTa FROM Danhmuc";
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 ... trình bày ở mục phía dưới

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 để có câu lệnh SQL thực sự. 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 DanhmucID, TenDanhMuc, MoTa FROM Danhmuc where DanhmucID > @DanhmucID";

Thì có một tham số tên @DanhmucID: Giá trị thực sự của tham số này trước khi chạy lệnh được đưa vào SqlCommand bằng một số phương thức, Ví dụ thêm bằng Parameters.AddWithValue

string queryString =  "SELECT DanhmucID, TenDanhMuc, MoTa FROM Danhmuc where DanhmucID > @DanhmucID";";
using (SqlCommand cmd = connection.CreateCommand()) {
    cmd.CommandText = queryString;

    // Thiết lập @DanhmucID = 5, có nghĩa câu lệnh thực sự sẽ là:
    // "SELECT DanhmucID, TenDanhMuc, MoTa FROM Danhmuc where DanhmucID > > 5"
    cmd.Parameters.AddWithValue("@DanhmucID", 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(DanhmucID)

string queryString =  "SELECT DanhmucID, TenDanhMuc, MoTa FROM Danhmuc where DanhmucID > @DanhmucID";
// Tạo SqlParameter với tên tham số DanhmucID
SqlParameter DanhmucID = new SqlParameter("@DanhmucID", SqlDbType.Int);
DanhmucID.Value = 5;
using (SqlCommand cmd = connection.CreateCommand()) {
    cmd.CommandText = queryString;
    cmd.Parameters.Add(DanhmucID); // Thêm tham số

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

Các cách thi hành SqlCommand và lấy kết quả truy vấn

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 truy vấn - không cần trả về dữ liệu gì, phù hợp thực hiện các truy vấn như Update, Delete ...
  • ExecuteReader() thi hành lệnh - trả về đối tượng giao diện IDataReader như SqlDataReader, từ đó đọc được dữ liệu trả về
  • ExecuteScalar() thì hành và trả về một giá trị duy nhất - ở hàng đầu tiên, cột đầu tiên

Sau đây áp dụng gọi các hàm trên

Thi hành SqlCommand bằng phương thức ExecuteScalar()

Nếu thi hành SqlCommand bằng phương thức ExecuteScalar thì nó sẽ thi hành câu lệnh SQL và trả về 1 giá trị là cột đầu tiên của dòng đầu tiên. (Cho dù câu lệnh SQL thực tế trả về tập kết quả nhiều dòng nhiều cột). 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). Ở đây chèn một Shipper mới thông tin HoTen và Sodienthoai vào bảng Shippers

insertShipper.cs

// Mở kết nối MS SQL Server
string sqlconnectStr     = "Data Source=localhost,1433;Initial Catalog=xtlab;User ID=SA;Password=Password123";
SqlConnection connection = new SqlConnection(sqlconnectStr);
connection.Open();

// Câu truy vấn gồm: chèn dữ liệu vào và lấy định danh(Primary key) mới chèn vào
string queryString = @"INSERT INTO Shippers (Hoten, Sodienthoai) VALUES (@Hoten, @Sodienthoai);
                       SELECT CAST(scope_identity() AS int)";

using (SqlCommand cmd = connection.CreateCommand()) {
    cmd.CommandText = queryString;
    cmd.Parameters.AddWithValue("@Hoten", "ABC");
    cmd.Parameters.AddWithValue("@Sodienthoai", 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ề.

insertShipper2.cs

// Mở kết nối MS SQL Server
string sqlconnectStr     = "Data Source=localhost,1433;Initial Catalog=xtlab;User ID=SA;Password=Password123";
SqlConnection connection = new SqlConnection(sqlconnectStr);
connection.Open();

// Câu truy vấn gồm: chèn dữ liệu vào và lấy định danh(Primary key) mới chèn vào
string queryString = @"INSERT INTO Shippers (Hoten, Sodienthoai) VALUES (@Hoten, @Sodienthoai)";

using (SqlCommand cmd = connection.CreateCommand()) {
    cmd.CommandText = queryString;
    cmd.Parameters.AddWithValue("@Hoten", "XYZ");
    cmd.Parameters.AddWithValue("@Sodienthoai", 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

Các câu lệnh SELECT có thể dùng cách náy

readCate.cs

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

  // Truy vấn lấy các danh mục mặt hàng
  using (SqlCommand command = new SqlCommand("SELECT DanhmucID, TenDanhMuc FROM Danhmuc;", connection))
  using (SqlDataReader reader = command.ExecuteReader())
  {
      // Kiểm tra có kết quả trả về
      if (reader.HasRows)
      {
          // Đọc từng dòng kết quả cho đế hết
          while (reader.Read())
          {
              // Mỗi lần thực hiện read.Read() con trỏ sẽ dịch chuyển đến dòng dữ liệu tiếp
              // theo nếu có.
              // Đọc dòng dữ liệu dòng hiện tại dùng ký hiệu [chỉ-số-cột] hoặc các hàmg lấy dữ liệu
              // từng cột như reader.GetInt21(chỉ-số-cột)
              Console.WriteLine("{0}\t{1}", reader[0].ToString(), reader.GetString(1));
          }
      }
      else  Console.WriteLine("No rows found.");
  }

  connection.Close();

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 DanhmucID, TenDanhMuc FROM Danhmuc;", 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.

Bạn có thể chạy câu lệnh T-SQL để tạo ra một StoredProcedure mẫu có tên getproduct với một tham số @id, thủ tục này đơn giản lấy thông tin của sản phẩm theo ID

CREATE PROCEDURE [dbo].[getproduct](@id int)
AS
BEGIN
	SET NOCOUNT ON;
	SELECT TenSanpham, Gia FROM Sanpham Where SanphamID = @id
END

Thực hành gọi thủ tục SQL Server

callProcedure.cs

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

    // Thi hành thủ tục PROCEDURE [dbo].[getproduct](@id int) trong MS SQL Server
    SqlCommand cmd   = new SqlCommand("getproduct", connection);
    cmd.CommandType  = CommandType.StoredProcedure;
    // Tham số của procedure
    cmd.Parameters.Add(
        new SqlParameter() {
            ParameterName   = "@id",
            SqlDbType       = SqlDbType.Int,
            Value           = 10
        }
    );

    // Đọc kết quả trả về
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            var ten = reader["TenSanpham"];
            var gia = reader["Gia"];

            Console.WriteLine($"{ten} \t {gia}");
        }
    }


  connection.Close();
}
//..

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()

Mã nguồn: ADO_02_SqlCommand (git) hoặc tải về

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