Kết nối SQL Server với ADO.NET trong C# – Phần 2

3.5/5 - (2 bình chọn)

Trong phần 1, mình đã đề cập đến ADO.NET, cách mở và đóng kết nối SQL Server. Đối với phần 2, mình sẽ tập trung vào việc xây dựng các lớp hỗ trợ cho việc kết nối SQL Server. Các lớp mà xây dựng sẽ giúp đơn giản hóa việc kết nối SQL Server với ADO.NET. Bên cạnh đó, mình sẽ thực hiện một đoạn code mẫu để cho bạn hiểu cách sử dụng các lớp mà mình đã xây dựng.

Xây dựng các lớp (class) hỗ trợ kết nối SQL Server với ADO.NET

Lớp SqlConfig

Lớp này sẽ có phương thức tĩnh void ReadConfigFile(string connectionStringName), phương thức này sẽ đọc thông tin chuỗi kết nối từ tập tin cấu hình. Sau đó thông tin chuỗi kết nối sẽ được lưu trong trường tĩnh (static field) là static string ConnnecionString. Tập tin cấu hình sẽ là App.config hoặc Web.config.

using System.Configuration;

namespace SQLServer_Demo
{
    public class SqlConfig
    {
        public static string ConnnecionString;

        public static void ReadConfigFile(string connectionStringName)
        {
            ConnnecionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
        }
    }
}

– Tập tin cấu hình (App.config hoặc Web.config).

Thông tin chuỗi kết nối SQL Server sẽ nằm trong <connectionStrings></connectionStrings>.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
    </startup>
    <connectionStrings>
		<add name="DefaultConnectionString" connectionString="Server=...;Database=...;User Id=...;Password=;..."/>
	</connectionStrings>
</configuration>

Lớp SqlExecution

Lớp SqlExecution mình sẽ là Partial Class và sẽ được chia thành 2 tập tin là SqlExecution.csSqlExecutionAsync.cs.

Để hiểu thêm về Partial Class, bạn có thể đọc thêm tại đây: Partial Classes and Methods (C# Programming Guide).

Tập tin SqlExecution.cs

Tập tin này sẽ chứa các trường (fields), thuộc tính (properties), các phương thức khởi tạo và các phương thức đồng bộ (synchronous).

  • Phương thức void Connect(): Mở kết nối SQL Server.
  • Phương thức void Disconnect(): Đóng kết nối SQL Server.
  • Phương thức void SetCommand(string commandText, CommandType commandType, params SqlParameter[] sqlParameters): Đựa chuỗi truy vấn và các tham số vào SqlCommand. Có chức năng hoàn thiện chuỗi truy vấn và kiểm tra tránh tình trạng bị Sql Injection.
  • Phương thức int ExecuteNonQuery(string commandText, CommandType commandType, params SqlParameter[] sqlParameters): Thực hiện các truy vấn như insert, update. Trả về là số bản ghi (dòng dữ liệu) đã được thay đổi.
  • Phương thức object ExecuteScalar(string commandText, CommandType commandType, params SqlParameter[] sqlParameters): Thực hiện các truy vấn chỉ trả về một giá trị (cột đầu tiên của dòng đầu tiên).
  • Phương thức SqlDataReader ExecuteReader(string commandText, CommandType commandType, params SqlParameter[] sqlParameters): Thực hiện các truy vấn trả về một hoặc nhiều bản ghi (dòng dữ liệu).
  • Phương thức DataSet GetDataSet(string commandText, string srcTable, CommandType commandType, params SqlParameter[] sqlParameters): Thực hiện câu truy vấn và trả về một DataSet.
  • Phương thức
  • Phương thức int UpdateDataSet(DataSet dataSet): Cập nhật dữ liệu trong DataSet xuống cơ sở dữ liệu.
  • Phương thức int UpdateDataTable(DataTable dataTable): Cập nhật dữ liệu trong DataTable xuống cơ sở dữ liệu.
using System;
using System.Data;
using System.Data.SqlClient;

namespace SQLServer_Demo
{
    public partial class SqlExecution : IDisposable
    {
        private SqlConnection sqlConnection;
        private SqlCommand sqlCommand;
        private SqlDataAdapter sqlDataAdapter;
        private bool _disposed;

        public SqlExecution()
        {
            _disposed = false;
            sqlConnection = new SqlConnection(SqlConfig.ConnnecionString);
            sqlCommand = sqlConnection.CreateCommand();
        }

        private void Connect()
        {
            if (sqlConnection.State == ConnectionState.Closed)
                sqlConnection.Open();
        }

        public void Disconnect()
        {
            if (sqlConnection.State == ConnectionState.Open)
                sqlConnection.Close();
        }

        private void SetCommand(string commandText, CommandType commandType, params SqlParameter[] sqlParameters)
        {
            sqlCommand.Parameters.Clear();

            sqlCommand.CommandType = commandType;
            sqlCommand.CommandText = commandText;
            if (sqlParameters != null)
                sqlCommand.Parameters.AddRange(sqlParameters);
        }

        public int ExecuteNonQuery(string commandText, CommandType commandType, params SqlParameter[] sqlParameters)
        {
            if (string.IsNullOrEmpty(commandText))
                throw new Exception("@'commandText' must not be null or empty");

            Connect();
            SetCommand(commandText, commandType, sqlParameters);
            return sqlCommand.ExecuteNonQuery();
        }

        public object ExecuteScalar(string commandText, CommandType commandType, params SqlParameter[] sqlParameters)
        {
            if (string.IsNullOrEmpty(commandText))
                throw new Exception("@'commandText' must not be null or empty");

            Connect();
            SetCommand(commandText, commandType, sqlParameters);
            return sqlCommand.ExecuteScalar();
        }

        public SqlDataReader ExecuteReader(string commandText, CommandType commandType, params SqlParameter[] sqlParameters)
        {
            if (string.IsNullOrEmpty(commandText))
                throw new Exception("@'commandText' must not be null or empty");

            Connect();
            SetCommand(commandText, commandType, sqlParameters);
            return sqlCommand.ExecuteReader();
        }

        public DataSet GetDataSet(string commandText, string srcTable, CommandType commandType, params SqlParameter[] sqlParameters)
        {
            if (string.IsNullOrEmpty(commandText))
                throw new Exception("@'commandText' must not be null or empty");

            SetCommand(commandText, commandType, sqlParameters);
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            DataSet dataSet = new DataSet();
            sqlDataAdapter.Fill(dataSet, srcTable);
            return dataSet;
        }

        public DataTable GetDataTable(string tableName, string commandText, CommandType commandType, params SqlParameter[] sqlParameters)
        {
            DataSet dataSet = GetDataSet(commandText, tableName, commandType, sqlParameters);
            return dataSet.Tables[tableName];
        }

        public int UpdateDataSet(DataSet dataSet)
        {
            if (dataSet == null)
                throw new Exception("@'dataSet' must not be null or empty");

            SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);
            return sqlDataAdapter.Update(dataSet);
        }

        public int UpdateDataTable(DataTable dataTable)
        {
            if (dataTable == null)
                throw new Exception("@'dataTable' must not be null or empty");

            SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);
            return sqlDataAdapter.Update(dataTable);
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!this._disposed)
            {
                if (disposing)
                {
                    sqlCommand.Dispose();
                    sqlConnection.Dispose();
                }
            }
            _disposed = true;
        }
    }
}

Tập tin SqlExecutionAsync.cs

Tập tin này sẽ chứa các phương thức bất đồng bộ (asynchronous).

using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace SQLServer_Demo
{
    public partial class SqlExecution
    {
        private async Task ConnectAsync()
        {
            if (sqlConnection.State == ConnectionState.Closed)
                await sqlConnection.OpenAsync();
        }

        public async Task<int> ExecuteNonQueryAsync(string commandText, CommandType commandType, params SqlParameter[] sqlParameters)
        {
            if (string.IsNullOrEmpty(commandText))
                throw new Exception("@'commandText' must not be null or empty");

            await ConnectAsync();
            SetCommand(commandText, commandType, sqlParameters);
            return await sqlCommand.ExecuteNonQueryAsync();
        }

        public async Task<object> ExecuteScalarAsync(string commandText, CommandType commandType, params SqlParameter[] sqlParameters)
        {
            if (string.IsNullOrEmpty(commandText))
                throw new Exception("@'commandText' must not be null or empty");

            await ConnectAsync();
            SetCommand(commandText, commandType, sqlParameters);
            return await sqlCommand.ExecuteScalarAsync();
        }

        public async Task<SqlDataReader> ExecuteReaderAsync(string commandText, CommandType commandType, params SqlParameter[] sqlParameters)
        {
            if (string.IsNullOrEmpty(commandText))
                throw new Exception("@'commandText' must not be null or empty");

            await ConnectAsync();
            SetCommand(commandText, commandType, sqlParameters);
            return await sqlCommand.ExecuteReaderAsync();
        }
    }
}

Cách sử dụng các lớp (class) hỗ trợ kết nối SQL Server

Cơ sở dữ liệu mẫu

Để thực hiện được các ví dụ về cách sử dụng các lớp hỗ trợ kết nối SQL Server với ADO.NET. Mình sẽ sử dụng một cơ sở dữ liệu mẫu, cụ thể đó là AdventureWorks2019. Bạn có thể tải nó tại đây: AdventureWorks sample databases.

– Mình sẽ sử dụng bảng Person.CountryRegion để thực hiện ví dụ.

Dữ liệu của bảng Person.CountryRegion của CSDL AdventureWorks2019.

Đoạn code mẫu

Đây là một Console Application đơn giản, nhằm mục đích cho bạn hiểu cách sử dụng các phương thức có trong các lớp mà mình đã xây dựng.

Lưu ý: Phải thêm thông tin SQL Server vào trong tập tin cấu hình.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace SQLServer_Demo
{
    class Program
    {
        static void Main(string[] args)
        {
            // Đọc thông tin chuỗi kết nối từ tập tin cấu hình (App.config hoặc Web.config).
            SqlConfig.ReadConfigFile("DefaultConnectionString");

            Console.OutputEncoding = Encoding.UTF8;

            using(SqlExecution sqlExecution = new SqlExecution())
            {
                // Đếm số bản ghi có trong bảng.

                string commandText = "Select count(*) from Person.CountryRegion";
                int result = (int)sqlExecution.ExecuteScalar(commandText, CommandType.Text);

                Console.WriteLine($"Số bản ghi của bảng Person.CountryRegion là {result}");
                Console.WriteLine("\n\n");

                // Xác định sự tồn tại của bản ghi có trong bảng, với điều kiện được truyền vào các thuộc tính:
                // + CountryRegionCode.
                // +  Name.
                // Ví dụ được thực hiện với CountryRegionCode = US và Name = United States.

                commandText = @"Select count(*) from Person.CountryRegion 
                        where CountryRegionCode = @countryRegionCode and Name = @name";
                result = (int)sqlExecution.ExecuteScalar(
                    commandText, CommandType.Text,
                    new SqlParameter("@countryRegionCode", "US"),
                    new SqlParameter("@name", "United States")
                );

                if (result != 0)
                    Console.WriteLine("Có tồn tại!");
                else
                    Console.WriteLine("Không tồn tại!");

                Console.WriteLine("\n\n");


                // Lấy ra toàn bộ bản ghi hiện có của các bảng.

                commandText = "Select * from Person.CountryRegion";
                using (SqlDataReader sqlDataReader = sqlExecution.ExecuteReader(commandText, CommandType.Text))
                {
                    while (sqlDataReader.Read())
                    {
                        Console.WriteLine($"CountryRegionCode: {sqlDataReader["CountryRegionCode"]} ----- Name: {sqlDataReader["Name"]}");
                    }
                }
                Console.WriteLine("\n\n");

                // Lấy ra bản ghi có điều kiện như sau: CountryRegionCode có giá trị là VN.

                commandText = "Select * from Person.CountryRegion where CountryRegionCode = 'VN'";
                using (SqlDataReader sqlDataReader = sqlExecution.ExecuteReader(commandText, CommandType.Text))
                {
                    if (sqlDataReader.Read())
                    {
                        Console.WriteLine($"CountryRegionCode: {sqlDataReader["CountryRegionCode"]} ----- Name: {sqlDataReader["Name"]}");
                    }
                }
                Console.WriteLine("\n\n");

                // Cập nhật bản ghi với điều kiện được truyền vào các thuộc tính:
                // + CountryRegionCode có giá trị là VN.
                // Name được thay đổi giá trị từ Vietnam thành Việt Nam.

                commandText = "Update Person.CountryRegion set Name = 'Việt Nam' where CountryRegionCode = @countryRegionCode";
                int affected = sqlExecution
                    .ExecuteNonQuery(commandText, CommandType.Text, new SqlParameter("@countryRegionCode", "VN"));

                if (affected > 0)
                    Console.WriteLine("Đã cập nhật thành công!");
                else
                    Console.WriteLine("Cập nhật thất bại!");

                Console.WriteLine("\n\n");

            }

            Console.ReadKey();
        }
    }
}

– Kết quả sau khi chạy chương trình.

kết nối SQL Server với ADO.NET
Kết quả sau khi chạy chương trình.

Các bài viết liên quan

Danh sách bài viết về Kết nối SQL Server với ADO.NET trong C#:

Bạn có thế đọc thêm các bài viết về chủ đề CNTT tại đây nhé: Công nghệ thông tin.

Lời kết

Tiếp nối phần 1, mình đã chia sẻ cách mà bản thân mình đã vận dụng những lớp kết nối SQL Server sẵn có trong C#. Qua đó, mình đã xây dựng các lớp hỗ trợ việc kết nối SQL Server với ADO.NET thuận tiện hơn. Bài viết chưa đề cập đến việc xử lý DataSet và DataTable. Vì vậy, trong các phần tiếp theo, mình sẽ làm rõ vấn đề này. Bên cạnh đó, mình sẽ tiếp tục bổ sung thêm các phương thức mới để hỗ trợ việc kết nối SQL Server ngày càng tốt hơn.

Cuối cùng, cảm ơn bạn đã đọc hết bài viết này, nếu bạn có vấn đề cần giải đáp, hãy để lại bình luận cho mình nhé!

Hits: 45

Leave a Reply