SQLite Database example

namespace Database
{
    using System.Collections.Generic;
    using System.Data.SQLite;
    using System.Linq;

    public class DatabaseManager
    {
        public string DatabaseName { get; set; }
        private SQLiteConnection Connection;
        public DatabaseManager(string databasename = "base")
        {
            Connection = new SQLiteConnection($"Data Source={databasename}.s3db");
            Connection.Open();
            Connection.Close();
        }

        public void CreateTable(Table table)
        {
            string query = $"CREATE TABLE IF NOT EXISTS {table.TableName}({string.Join(", ", table.Columns.Select(column => column.GetCreateQuery()))});";
            SingleQuery(query);
        }
        public void CreateTable(Table table, string settings)
        {
            string query = $"CREATE TABLE IF NOT EXISTS {table.TableName}({string.Join(", ", table.Columns.Select(column => column.GetCreateQuery()))},{settings});";
            SingleQuery(query);
        }
        public void CreateTables(Table[] tables)
        {
            foreach (Table table in tables) {
                string query = $"IF NOT EXISTS CREATE TABLE {table.TableName}({string.Join(", ", table.Columns.Select(column => column.GetCreateQuery()))});";
                SingleQuery(query);
            }
        }

        public void Insert(Table table, (Column column, string value)[] values)
        {
            string query = $"INSERT INTO {table.TableName}({string.Join(", ", values.Select(item => item.column.Name))}) VALUES ({string.Join(", ", values.Select(item => item.value))});";
            SingleQuery(query);
        }
        public void Inserts(Table table,List<(Column column, string value)[]> values)
        {
            foreach (var items in values) {
                string query = $"INSERT INTO {table.TableName}({string.Join(", ", items.Select(item => item.column.Name))}) VALUES ({string.Join(", ", items.Select(item => item.value))});";
                SingleQuery(query);
            }

        }
        public List<(Column column, string value)[]> Select(Table table, Column[] columns)
        {
            string query = $"SELECT {string.Join(",", columns.Select(item => item.Name))} FROM {table.TableName};";
            SQLiteDataReader reader = ReaderQuery(query);
            return GetReaderReponse(columns, reader);
        }
        public List<(Column column, string value)[]> Select(Table table, Column[] columns, string order)
        {
            string query = $"SELECT {string.Join(",", columns.Select(item => item.Name))} FROM {table.TableName} {order};";
            SQLiteDataReader reader = ReaderQuery(query);
            return GetReaderReponse(columns, reader);
        }
        private void SingleQuery(string query)
        {
            lock (Connection) {
                Connection.Open();
                SQLiteCommand command = Connection.CreateCommand();
                command.CommandText = query;
                command.ExecuteNonQuery();
                command.Dispose();
                Connection.Close();
            }
        }
        private SQLiteDataReader ReaderQuery(string query)
        {
            SQLiteDataReader reader = null;
            lock (Connection) {
                Connection.Open();
                SQLiteCommand command = Connection.CreateCommand();
                command.CommandText = query;
                reader = command.ExecuteReader();
                command.Dispose();
                Connection.Close();
            }
            return reader;
        }
        private List<(Column column, string value)[]> GetReaderReponse(Column[] columns, SQLiteDataReader reader)
        {
            List<(Column column, string value)[]> items = new List<(Column column, string value)[]>();
            while (reader.Read()) {
                (Column column, string value)[] acc = new(Column column, string value)[columns.Length];
                for (int i = 0; i < columns.Length; i += 1) {
                    Column cal = columns[i];
                    acc[i] = (cal, reader[cal.Name].ToString());
                }
                items.Add(acc);
            }
            return items;
        }
    }

    public class Table
    {
        public string TableName { get; private set; }
        public List<Column> Columns { get; private set;}
        public Table(string tablename,params Column[] columns)
        {
            TableName = tablename;
            Columns = new List<Column>();
            Columns.AddRange(columns);
        }

        public void Addcolumn(Column column)
        {
            Columns.Add(column);
        }
        public void Addcolumns(Column[] columns)
        {
            Columns.AddRange(columns);
        }
    }

    public class Column
    {
        public string Name { get; private set; }
        public string Typ { get; private set; }

        public Column(string name, string typ)
        {
            Name = name;
            Typ = typ;
        }

        public string GetCreateQuery() => $"{Name} {Typ}";
    }
}