'C# generic insert method to insert object into Sqlite3 table?

I am trying to make a generic method where I can insert any object into a sqlite3 database.

User class:

public class Users : IClassModel<Users>
{
    public int Id { get; set; }
    public string UserName { get; set; }
    public string UserAddress { get; set; }
    public string OtherUserDetails { get; set; }
    public decimal AmountOfFine { get; set; }
    public string Email { get; set; }
    public string PhoneNumber { get; set; }
}

Interface class:

public interface IClassModel<T>
{
    public int Id { get; set; }
}

QueryBuilder class:

    public class queryBuilder : IDisposable
{
    private SqliteConnection _connection;

    public queryBuilder(string connectionString)
    {
        _connection = new SqliteConnection(connectionString);
        _connection.Open();
    }
    public void Dispose()
    {
        _connection.Close();
    }

    public void Create<T>(T obj) where T : IClassModel<T>
    {
        // insert into tableName values()
        
        Type myType = obj.GetType();
        IList<PropertyInfo> props = new List<PropertyInfo>(myType.GetProperties());
        ArrayList valueArray = new ArrayList();
        ArrayList nameArray = new ArrayList();
        var questionString = "";
        var nameString = "";
        foreach (PropertyInfo prop in props)
        {
            object propValue = prop.GetValue(obj, null);
            object propName = prop.Name;
            valueArray.Add(propValue);
            nameArray.Add(propName);
            questionString += "?, ";
            nameString += $"{propName}, " ;
        }
        var newNameString = nameString.Trim();
        var newerNameString = newNameString.TrimEnd(',');
        var newQuestionString = questionString.Trim();
        var newerQuestionString = newQuestionString.TrimEnd(',');
        SqliteCommand insertSQL = new SqliteCommand($"INSERT INTO {typeof(T).Name} ({newerNameString}) VALUES ({newerQuestionString})", _connection);


        foreach (var item in valueArray)
        {
            insertSQL.Parameters.Add(item);
        }


        insertSQL.ExecuteNonQuery();

        //Console.WriteLine("Successfully added the thing.");

    }
}

Driver:

using Microsoft.Data.Sqlite;
using QueryBuilder.Models;
using System.Reflection;

using (var query = new queryBuilder(@"Data Source=C:\path\to\database"))
{
// con

var user = new Users();
user.UserName = "username";
user.UserAddress = "some_address";
user.OtherUserDetails = "details";
user.AmountOfFine = 90;
user.Email = "[email protected]";
user.PhoneNumber = "5555555555";

query.Create<Users>(user);
}

I know my code is bit messy, but the idea is to somehow create an object and then be able to insert it into the already made table, no matter what object it is. I keep getting invalid cast exceptions.

I need to be able to iterate through the values and properties and add them to the sqlite insert command but it doesn't seem to be working. Any help is appreciated.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source