Create Proper Database Management

Overview

Before we start creating connections, we need to establish efficient database management practices. This proposal outlines the steps to enhance database connectivity, prevent sensitive information leakage, and simplify querying using Dapper.

Steps To Implement

1. Database Connector Installation

Before proceeding with query implementation, we'll integrate the Connector package into our project.

2. Securing Connection String

To safeguard sensitive information and prevent accidental commits, we'll store our connection string securely in secrets.json.

3. Adding Database To Services

Next, we'll integrate the MySQLConnection Factory into our application services. This step involves adding the connection string retrieved from secrets.json to establish a connection with the database.

builder.Services.AddTransient(x =>
  new MySqlConnection(builder.Configuration.GetConnectionString("Default")));

4. Integrating Dapper

To simplify the querying process and enhance code readability, we'll integrate Dapper into our project. Dapper provides a concise and efficient approach to executing database queries. This is the code with Dapper:

products = connection.Query<Product>(sql).ToList();

This is the code without Dapper

using (var command = new SqlCommand(sql, connection))
{
    using (var reader = command.ExecuteReader())
    {
        var product = new Product
        {
            ProductId = reader.GetInt32(reader.GetOrdinal("ProductId")),
            ProductName = reader.GetString(reader.GetOrdinal("ProductName")),
            SupplierId = reader.GetInt32(reader.GetOrdinal("SupplierId")),
            CategoryId = reader.GetInt32(reader.GetOrdinal("CategoryId")),
            QuantityPerUnit = reader.GetString(reader.GetOrdinal("QuantityPerUnit")),
            UnitPrice = reader.GetDecimal(reader.GetOrdinal("UnitPrice")),
            UnitsInStock = reader.GetInt16(reader.GetOrdinal("UnitsInStock")),
            UnitsOnOrder = reader.GetInt16(reader.GetOrdinal("UnitsOnOrder")),
            ReorderLevel = reader.GetInt16(reader.GetOrdinal("ReorderLevel")),
            Discontinued = reader.GetBoolean(reader.GetOrdinal("Discontinued")),
            DiscontinuedDate = reader.GetDateTime(reader.GetOrdinal("DiscontinuedDate"))
        };
        products.Add(product);
    }
}

For more information, check out the documentation here.

TheDarkun / Elevators