Goffo.SqlAccess 3.0.0

READ ME

Goffo.SqlAccess

Library to help with SQL Server data access utilizing Dapper

Version Updates

<u>v3.0.0</u>

1. Updated the namespace from SqlDataAccess to Goffo.SqlAccess (you will need to make this change in your using statements)
2. Added methods LoadFirstData and LoadFirstDataAsync

How to use

1. Class Library
2. Dependency Injection

Class Library

appsettings.json
{
  "ConnectionStrings": {
    "Default": "Your connection string goes here"
  }
}

namespace Goffo.SqlAccess.Library.Employees;
public class EmployeeModel
{
    public int Id { get; set; }
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public string? EmailAddress { get; set; }
    public bool Active { get; set; }
    public string UserCreated { get; set; } = string.Empty;
    public DateTime DateTimeCreated { get; set; }
    public string? UserUpdated { get; set; }
    public DateTime? DateTimeUpdated { get; set; }
}

using Dapper;
namespace Goffo.SqlAccess.Library.Employees;
public class EmployeesRepository : IEmployeesRepository
{
    private readonly ISqlDataAccess _sqlDataAccess;

    public EmployeesRepository(ISqlDataAccess sqlDataAccess)
    {
        _sqlDataAccess = sqlDataAccess;
    }

    public string ConnectionStringName { get; set; } = "Default";

    public async Task<EmployeeModel> CreateAsync(EmployeeModel employee)
    {
        string sql = "dbo.CreateEmployee";

        DynamicParameters parameters = new();
        parameters.Add("@FirstName", employee.FirstName, System.Data.DbType.String, System.Data.ParameterDirection.Input, 50);
        parameters.Add("@LastName", employee.LastName, System.Data.DbType.String, System.Data.ParameterDirection.Input, 50);
        parameters.Add("@EmailAddress", employee.EmailAddress, System.Data.DbType.String, System.Data.ParameterDirection.Input, 200);
        parameters.Add("@UserCreated", employee.UserCreated, System.Data.DbType.String, System.Data.ParameterDirection.Input, 50);
        parameters.Add("@Id", 0, System.Data.DbType.Int32, System.Data.ParameterDirection.Output);

        await _sqlDataAccess.SaveDataAsync(sql, parameters, ConnectionStringName, true);

        int id = parameters.Get<int>("@Id");

        return await GetByIdAsync(id);
    }

    public async Task<IEnumerable<EmployeeModel>> GetAsync()
    {
        string sql = "SELECT Id, FirstName, LastName, EmailAddress, " +
                            "Active, UserCreated, DateTimeCreated, UserUpdated, DateTimeUpdated " +
                     "FROM dbo.Employees";

        DynamicParameters parameters = new();

        IEnumerable<EmployeeModel> output = await _sqlDataAccess.LoadDataAsync<EmployeeModel, DynamicParameters>(sql,
            ConnectionStringName, false, parameters);

        return output ?? [];
    }

    public async Task<EmployeeModel> GetByIdAsync(int id)
    {
        string sql = "SELECT Id, FirstName, LastName, EmailAddress, " +
                            "Active, UserCreated, DateTimeCreated, UserUpdated, DateTimeUpdated " +
                     "FROM dbo.Employees " +
                     "WHERE Id = @Id";
        DynamicParameters parameters = new();
        parameters.Add("@Id", id, System.Data.DbType.Int32, System.Data.ParameterDirection.Input);

        EmployeeModel? output = await _sqlDataAccess.LoadSingleDataAsync<EmployeeModel, DynamicParameters>(sql,
            ConnectionStringName, false, parameters);

        return output ?? new EmployeeModel();
    }

    public async Task UpdateAsync(EmployeeModel employee)
    {
        string sql = "UPDATE dbo.Employees " +
                     "SET FirstName = @FirstName, LastName = @LastName, EmailAddress = @EmailAddress, " +
                         "Active = @Active, UserUpdated = @UserUpdated, DateTimeUpdated = GETDATE() " +
                     "WHERE Id = @Id";

        DynamicParameters parameters = new();
        parameters.Add("@Id", employee.Id, System.Data.DbType.Int32, System.Data.ParameterDirection.Input);
        parameters.Add("@FirstName", employee.FirstName, System.Data.DbType.String, System.Data.ParameterDirection.Input, 50);
        parameters.Add("@LastName", employee.LastName, System.Data.DbType.String, System.Data.ParameterDirection.Input, 50);
        parameters.Add("@EmailAddress", employee.EmailAddress, System.Data.DbType.String, System.Data.ParameterDirection.Input, 200);
        parameters.Add("@Active", employee.Active, System.Data.DbType.Boolean, System.Data.ParameterDirection.Input);
        parameters.Add("@UserUpdated", employee.UserUpdated, System.Data.DbType.String, System.Data.ParameterDirection.Input, 50);

        await _sqlDataAccess.SaveDataAsync(sql, parameters, ConnectionStringName, false);
    }

    public async Task DeleteAsync(int id)
    {
        string sql = "DELETE FROM dbo.Employees WHERE Id = @Id";
        DynamicParameters parameters = new();
        parameters.Add("@Id", id, System.Data.DbType.Int32, System.Data.ParameterDirection.Input);
        await _sqlDataAccess.SaveDataAsync(sql, parameters, ConnectionStringName, false);
    }

}

Dependency Injection

using Goffo.SqlAccess.Library.Employees;
using Microsoft.Extensions.DependencyInjection;

namespace Goffo.SqlAccess.Library.Extensions;
public static class DependencyInjection
{
    public static IServiceCollection RegisterLibrary(this IServiceCollection services)
    {
        services.AddTransient<ISqlDataAccess, SqlDataAccess>();
        services.AddTransient<IEmployeesRepository, EmployeesRepository>();
        return services;
    }
}

namespace Goffo.SqlAccess.Console;
public class App
{
    private readonly IHost _host;

    public App()
    {
        _host = Host.CreateDefaultBuilder()
            .ConfigureServices((context, services) =>
            {
                services.RegisterLibrary();
            })
            .Build();
    }

    internal IEmployeesRepository EmployeesRepository => _host.Services.GetRequiredService<IEmployeesRepository>();

    public async Task<IEnumerable<EmployeeModel>> GetEmployeesAsync()
    {
        IEnumerable<EmployeeModel> employees = await EmployeesRepository.GetAsync();
        return employees;
    }

    public async Task<EmployeeModel> GetEmployeeByIdAsync(int id)
    {
        EmployeeModel employee = await EmployeesRepository.GetByIdAsync(id);
        return employee;
    }

    public async Task<EmployeeModel> CreateEmployeeAsync(EmployeeModel employee)
    {
        return await EmployeesRepository.CreateAsync(employee);
    }

    public async Task UpdateEmployeeAsync(EmployeeModel employee)
    {
        await EmployeesRepository.UpdateAsync(employee);
    }

    public async Task DeleteEmployeeAsync(int id)
    {
        await EmployeesRepository.DeleteAsync(id);
    }
}