QBFxWJH3

1using System;
2using System.Collections.Generic;
3using System.Data.SQLite;
4using System.IO;
5using System.Linq;
6
7namespace WebStore.Db
8{
9    public class SQLiteDatabase : IDatabase
10    {
11        //private const string DatabaseDirectory = "c:\\sqlite";
12        //private const string DatabaseName = "MyDatabase.sqlite";
13        //private const string DatabasePath = DatabaseDirectory + "\\" + DatabaseName;
14
15        private SQLiteConnection Database { get; set; }
16
17        public void Run()
18        {
19            //if (!Directory.Exists(DatabaseDirectory)) Directory.CreateDirectory(DatabaseDirectory);
20            //if (!File.Exists(DatabasePath)) SQLiteConnection.CreateFile(DatabasePath);
21
22            Database = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
23            Database.Open();
24        }
25
26        public void CreateTable<T>() where T : class
27        {
28            var type = typeof(T);
29            var cmd = new SQLiteCommand($"CREATE TABLE IF NOT EXISTS {type.Name} (" +
30                                        $"{PropertiesToColumns(type)});", Database);
31            cmd.ExecuteNonQuery();
32            //string createTableQuery = @"CREATE TABLE IF NOT EXISTS [User] (
33            //                  [ID] INTEGER NOT NULL,
34            //                  [FirstName] NVARCHAR(2048)  NOT NULL,
35            //                  [LastName] VARCHAR(2048)  NOT NULL
36            //                  )";
37        }
38
39        private static string PropertiesToColumns(Type t) => string.Join(", ",
40            t.GetProperties().Select(e => $"[{e.Name}] {ToSQLType(e.PropertyType)} NON NULL"));
41
42        private const string INTEGER = nameof(INTEGER);
43        private const string TEXT = nameof(TEXT);
44
45        private static string ToSQLType(Type t)
46        {
47            switch (Type.GetTypeCode(t))
48            {
49                case TypeCode.Byte:
50                case TypeCode.SByte:
51                case TypeCode.UInt16:
52                case TypeCode.UInt32:
53                case TypeCode.UInt64:
54                case TypeCode.Int16:
55                case TypeCode.Int32:
56                case TypeCode.Int64:
57                case TypeCode.Decimal:
58                case TypeCode.Double:
59                case TypeCode.Single:
60                    return INTEGER;
61                default:
62                    return TEXT;
63            }
64        }
65
66        public T Select<T>(int id) where T : class
67        {
68            new SQLiteCommand($"SELECT * FROM {typeof(T).Name} " +
69                              $"WHERE id={id};");
70            return default;
71        }
72
73        public IEnumerable<T> SelectAll<T>() where T : class
74        {
75            var com = new SQLiteCommand($"SELECT * FROM {typeof(T).Name};");
76            return null;
77        }
78
79        public void Insert<T>(T entity) where T : class
80        {
81            var type = typeof(T);
82            var cmd = new SQLiteCommand(
83                $"INSERT INTO {type.Name}({GetPropertyNames(type)}) " + // INSERT INTO User(Id, FirstName, LastName, ....) 
84                $"VALUES('{GetPropertyValues(entity)}');", Database); // VALUES (1, John, Doe, ...)
85            cmd.ExecuteNonQuery();
86        }
87
88        public void Update<T>(T entity) where T : class
89        {
90            var type = typeof(T);
91            var com = new SQLiteCommand(
92                $"UPDATE {type.Name} " + // UPDATE User
93                $"SET {GetPropertyValuePair(entity)} " + // SET Id='5', FirstName='John', LastName='Doe'
94                $"WHERE Id={GetPropertyId(entity)};", Database); // WHERE Id=5
95        }
96
97        public void Delete<T>(T entity) where T : class
98        {
99            //DELETE FROM Students WHERE StudentId = 11 OR StudentId = 12;
100            var type = typeof(T);
101            var com = new SQLiteCommand(
102                $"DELETE FROM {type.Name} " + // DELETE FROM User
103                $"WHERE Id={GetPropertyId(entity)};", Database); // WHERE Id=5 
104        }
105
106        private static string GetPropertyNames(Type type) =>
107            string.Join(", ", type.GetProperties().Select(e => e.Name));
108
109        private static string GetPropertyValues<T>(T entity) where T : class =>
110            string.Join("', '", entity.GetType().GetProperties().Select(e => e.GetValue(entity)));
111
112        private static string GetPropertyValuePair<T>(T entity) where T : class => string.Join(", ",
113            entity.GetType().GetProperties().Select(e => $"{e.Name}='{e.GetValue(entity)}'"));
114
115        private static string GetPropertyId<T>(T entity) where T : class =>
116            entity.GetType().GetProperty("Id").GetValue(entity).ToString();
117    }
118}