اسنیپت‌ها، ترفندها و کدهای کاربردی

using System.Data;
using Microsoft.Data.SqlClient;

namespace SqlForDevelopers.Data;

public class SqlDataContext
{
    private readonly IConfiguration _config;

    public SqlDataContext(IConfiguration config)
    {
        _config = config;
    }

    private SqlConnection CreateConnection()
    => new SqlConnection(_config.GetConnectionString("DefaultConnection"));

    // DELETE , INSERT, UPDATE 
    public async Task<int> ExecuteNonQueryAsync(string cmdText, CommandType type, params SqlParameter[] parameters)
    {
        await using var connection = CreateConnection();
        await using var cmd = new SqlCommand(cmdText, connection) { CommandType = type }; // it determined whether it is Raw Sql or Stored Procedure
        if (parameters?.Length>0) cmd.Parameters.AddRange(parameters);
        await connection.OpenAsync();
        return await cmd.ExecuteNonQueryAsync(); 
    }

    // Return a field of table (1 row , 1 column)
    public async Task<object> ExecuteScalarAsync(string cmdText, CommandType type, params SqlParameter[] parameters)
    {
        await using var connection = CreateConnection();
        await using var cmd = new SqlCommand(cmdText, connection) { CommandType = type }; // it determined whether it is Raw Sql or Stored Procedure
        if (parameters?.Length > 0) cmd.Parameters.AddRange(parameters);
        await connection.OpenAsync();
        return await cmd.ExecuteScalarAsync(); // SELECT
    }

    // Return several row/column 
    public async Task<List<T>> ExecuteReaderAsync<T>(string cmdText, CommandType type,
        Func<SqlDataReader, T> map, params SqlParameter[] parameters)
    {
        var list = new List<T>();
        await using var connection = CreateConnection();
        await using var cmd = new SqlCommand(cmdText, connection) { CommandType = type };
        if (parameters?.Length > 0) cmd.Parameters.AddRange(parameters);
        await connection.OpenAsync();
        await using var reader= await cmd.ExecuteReaderAsync();

        while (await reader.ReadAsync())
        {
            list.Add(map(reader));
        }

        return list;
    }
}

ExecuteNonQueryAsync → INSERT / UPDATE / DELETE → تعداد ردیف تحت تأثیر (int)

ExecuteScalarAsync → SELECT با یک مقدار تکی → یک object

ExecuteReaderAsync<T> → SELECT با چند ردیف → List<T>