SQL common database object compatible with Access, PostgreSQL, SQLite, MySQL, MSSQL, Sybase...

//App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
      <add connectionString="server=localhost;database=Inventory;user id=sa;password=SQLPassword"
           name="InventoryConnection"
           providerName="System.Data.SqlClient"/>
    </connectionStrings>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
    </startup>
</configuration>

---------------------------------------------------------------------------------
// database layer

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Common;
using System.Data;
using System.Configuration;

namespace CPRG254.Framework.Data
{
    public class DBObject
    {
        DbConnection Connection { get; set; }
        DbCommand Command { get; set; }
        DbDataReader Reader { get; set; }
        String ConnectionString { get; set; }

        DbProviderFactory Factory { get; set; }

        public DBObject(string connectionString, string providerName)
        {
            Factory = DbProviderFactories.GetFactory(providerName);
            ConnectionString = connectionString;
        }

        public IDataParameter CreateParameter
        {
            get
            {
                return Factory.CreateParameter();
            }
        }

        public IDataReader Query(string commandText,
                                        CommandType commandType,
                                        IDataParameter[] parameters)
        {
         
            try
            {
                //create connection
                Connection = Factory.CreateConnection();
                Connection.ConnectionString = ConnectionString;

                //create the command and set properties
                Command = Connection.CreateCommand();
                Command.CommandText = commandText;
                Command.CommandType = commandType;
                //add any parameters to the command object
                if (parameters != null)
                {
                    Command.Parameters.AddRange(parameters);
                }

                //open the connection
                Connection.Open();

                //execute the reader and return the reader
                Reader = Command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch(Exception ex)
            {
                //log the error
                if (Reader != null) Reader.Close();
                if(Connection != null && Connection.State == ConnectionState.Open) Connection.Close();
            }
            return Reader;
        }

        public bool NonQuery(string commandText,
                                    CommandType commandType,
                                    IDataParameter[] parameters)
        {
         
            try
            {
                using (Connection = Factory.CreateConnection())
                {
                    Connection.ConnectionString = ConnectionString;

                    //instantiate the command
                    Command = Factory.CreateCommand();
                    Command.CommandText = commandText;
                    Command.Connection = Connection;

                    Command.CommandType = commandType;

                    //add any parameters to the command object
                    if (parameters != null)
                    {
                        Command.Parameters.AddRange(parameters);
                    }

                    //open the connection
                    Connection.Open();

                    //execute the command
                    Command.ExecuteNonQuery();

                    return true;
                } //closes the connection implicitly/automatically
            }
            catch (Exception ex)
            {
                //log the exception

                return false;
            }
        }

    }
}

---------------------------------------------------------------------------------

//business layer

using System;

//using System.Data.SqlClient;

using System.Collections.Generic;

using System.Configuration; //used for ConfigurationManager class

using System.Data;

using CPRG254.Framework.Data;

namespace InventoryDomain

{

    public class SupplierManager

    {

      

        public static bool Add(Supplier supplier)

        {

            //connection string retrieved from config file

            var connectionString =

                ConfigurationManager.ConnectionStrings["InventoryConnection"].ConnectionString;

            var providerName =

                ConfigurationManager.ConnectionStrings["InventoryConnection"].ProviderName;

            DBObject db = new DBObject(connectionString, providerName);

            //sql command text

            var sql = "prSupplierInsert";

            //create a SqlParameter object and and include it in an array

            var par = db.CreateParameter;

            par.ParameterName = "@Name";

            par.Value = supplier.Name;

            par.DbType = DbType.String; //optional

            var par2 = db.CreateParameter;

            par2.ParameterName = "@ID";

            par2.DbType = DbType.Int32;

            par2.Direction = ParameterDirection.Output;

            var pars = new IDataParameter[] { par, par2 };

            return db.NonQuery(sql, CommandType.StoredProcedure, pars);

        }

        public static bool Update(Supplier supplier)

        {

            //connection string retrieved from config file

            var connectionString =

                ConfigurationManager.ConnectionStrings["InventoryConnection"].ConnectionString;

            var providerName =

                ConfigurationManager.ConnectionStrings["InventoryConnection"].ProviderName;

            DBObject db = new DBObject(connectionString, providerName);

            //sql command text

            var sql = "UPDATE Supplier SET Name = @name WHERE ID = @id";

            //create a SqlParameter object and and include it in an array

            var par = db.CreateParameter;

            par.ParameterName = "@name";

            par.Value = supplier.Name;

            par.DbType = DbType.String; //optional

            var par2 = db.CreateParameter;

            par2.ParameterName = "@id";

            par2.DbType = DbType.Int32;

            par2.Value = supplier.Id;

            var pars = new IDataParameter[] { par, par2 };

            return db.NonQuery(sql, CommandType.Text, pars);

        }

        public static List<Supplier> GetAll()

        {

            //connection string retrieved from config file

            var connectionString =

                ConfigurationManager.ConnectionStrings["InventoryConnection"].ConnectionString;

            var providerName =

                ConfigurationManager.ConnectionStrings["InventoryConnection"].ProviderName;

            DBObject db = new DBObject(connectionString, providerName);

            var suppliers = new List<Supplier>();

            Supplier supplier; //used in the while loop below

         try

            {

                //execute the command

                using (var reader = db.Query("SELECT * FROM Supplier", CommandType.Text, null))

                {

                    var idOrdinal = reader.GetOrdinal("ID");

                    var nameOrdinal = reader.GetOrdinal("Name");

                    while (reader.Read())

                    {

                        //instantiate and set the state of a Supplier

                        supplier = new Supplier();

                        supplier.Id = reader.GetInt32(idOrdinal);

                        supplier.Name = reader.GetString(nameOrdinal);

                        //add the supplier to the suppliers collection

                        suppliers.Add(supplier);

                    }

                }//closes the data reader implicitly  - which also closes the connection because of CommandBehavior enum set in ExecuteReader in DBObject                  

               

            }

            catch (Exception ex)

            {

                //log the exception                

            }

            return suppliers;

        }


//copy to access
public static void Synchronize()
        {
            var suppliers = GetAll();
            var connstring = ConfigurationManager.
                               ConnectionStrings["MyCompanyConnection"].
                               ConnectionString;
            var provname = ConfigurationManager.
                               ConnectionStrings["MyCompanyConnection"].
                               ProviderName;
            var db = new DBObject(connstring, provname);
         

            string sql = null;
            foreach(var supp in suppliers)
            {
                var par = db.CreateParameter;
                var par2 = db.CreateParameter;

                par.Value = supp.Id;
                par2.Value = supp.Name;

                var pars = new IDataParameter[] { par, par2};

                sql = "INSERT INTO Companies VALUES(?,?)";
                db.NonQuery(sql, CommandType.Text, pars);
            }

        }

---------------------------------------------------------------------------------
//App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
      <add connectionString="server=DESKTOP-EUFGQAP;database=Inventory;Trusted_Connection=Yes"
           name="InventoryConnection"
           providerName="System.Data.SqlClient"/>
      <add connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\chuanshuoge\Documents\Database1.accdb;
Persist Security Info=False;"
           name="MyCompanyConnection"
           providerName="System.Data.OleDb"/>
    </connectionStrings>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
    </startup>
</configuration>