Adding, Updating, and Deleting records using either OLE or SQL

Question

I don't know how I would describe this every time I get runtime errors.

What I want to know is should I use SQL or OLEDB libraries while using Visual Studio IDE?

Which one works best?

Answer 1

Your errors

In regards I get runtime errors, to help with this we need to know what the errors are and do they occur both inside and outside of Visual Studio.

Recommendation for a provider

My recommendation is to use Dapper which simplifies data operations using OLEDB under the covers. Below is a class responsible for common data operations.

For each operation a new connection is created (some developers use a single connection and keep it open which is incorrect).

When not use Dapper a developer needs to create a connection and command objects while with Dapper, only a connection is required.

No DataTable or DataSet are used, instead Dapper works off classes.

Simple example

The project is a console project but can be used in other project types. Each time the project runs the database is copied to the executable folder for learning purposes.

Source code is located on GitHub in this project.

Class

public class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateOnly BirthDate { get; set; }
    public bool Active { get; set; }
}

Data operation class

using System.Data;
using System.Data.OleDb;
using Dapper;
using DapperForAccessDatabase.Models;
using kp.Dapper.Handlers;

#pragma warning disable CA1416

namespace DapperForAccessDatabase.Repositories;
public class PersonRepository
{
    private IDbConnection _cn;

    /// <summary>
    /// Initializes a new instance of the <see cref="PersonRepository"/> class.
    /// - Setup for DateOnly handler and for creating a connection
    /// - Connection string is stored in appsettings.json amd uses ConfigurationLibrary NuGet package
    /// </summary>
    public PersonRepository()
    {
        _cn = new OleDbConnection(ConnectionString());
        SqlMapper.AddTypeHandler(new SqlDateOnlyTypeHandler());
    }

    /// <summary>
    /// Retrieves all the people from the database.
    /// </summary>
    /// <returns>A list of all the people.</returns>
    public List<Person> GetAll()
        => _cn.Query<Person>("SELECT * FROM Person").ToList();

    /// <summary>
    /// Adds a person to the database.
    /// </summary>
    /// <param name="person">The person to add.</param>
    public void Add(Person person)
    {
        const string statement =
            """
             INSERT INTO Person
             (
                 FirstName,LastName,BirthDate,Active
             )
             VALUES
             (
                 @FirstName,@LastName,@BirthDate,@Active
             )
             """;
        _cn.Execute(statement, new { person.FirstName, person.LastName, person.BirthDate, person.Active });
    }

    public void Insert(Person person)
    {
        const string statement =
            """
             INSERT INTO Person
             (
                 FirstName,LastName,BirthDate,Active
             )
             VALUES
             (
                 @FirstName,@LastName,@BirthDate,@Active
             )
             """;

        _cn.Open();
        _cn.Execute(statement, new { person.FirstName, person.LastName, person.BirthDate, person.Active });
        person.Id = _cn.QueryFirst<int>("SELECT @@IDENTITY");
        _cn.Close();
    }

    /// <summary>
    /// Adds a range of people to the database.
    /// </summary>
    /// <param name="people">The list of people to add.</param>
    public void AddRange(List<Person> people)
    {
        foreach (var person in people)
        {
            Add(person);
        }
    }

    /*
     * Basic/one property update. Add another method for say only updating First/Last name
     * The idea is usually all properties need not be updated.
     */
    /// <summary>
    /// Updates the birthdate of a person in the database.
    /// </summary>
    /// <param name="id">The ID of the person.</param>
    /// <param name="birthDate">The new birthdate.</param>
    /// <returns>True if the birthdate was successfully updated, otherwise false.</returns>
    public bool UpdateBirthDate(int id, DateOnly birthDate)
    {
        const string statement =
            """
                    UPDATE Person
                    SET BirthDate = @BirthDate 
                    WHERE Id = @Id
                    """;

        return _cn.Execute(statement, new { BirthDate = birthDate, Id = id }) == 1;
    }

    /// <summary>
    /// Retrieves a person from the database based on the specified ID.
    /// </summary>
    /// <param name="id">The ID of the person to retrieve.</param>
    /// <returns>The person with the specified ID.</returns>
    public Person Get(int id) =>
        _cn.QueryFirst<Person>(
            """
             SELECT Id, FirstName, LastName, BirthDate, Active 
             FROM Person 
             WHERE Id = @Id
             """,
            new { Id = id });

    /// <summary>
    /// Removes a person from the database based on the specified ID.
    /// </summary>
    /// <param name="id">The ID of the person to remove.</param>
    /// <returns>True if the person was successfully removed, otherwise false.</returns>
    public bool Remove(int id)
    {
        const string statement =
            """
            DELETE FROM Person
            WHERE Id = @Id
            """;

        return _cn.Execute(statement, new { Id = id }) == 1;
    }
}