QikafJ23

1using System;
2using System.Collections.Generic;
3using System.Linq;
4using System.Text;
5using System.Threading.Tasks;
6using System.Data.SQLite;
7using System.IO;
8using System.Data;
9
10
11namespace Haushaltsbuch
12{
13    class SqlHandler
14    {
15        private SQLiteConnection go_db;
16        private string gv_sql;
17        private SQLiteCommand go_command;
18        private SQLiteDataReader go_reader;
19
20        public SqlHandler( )
21        {
22            Setting setting = new Setting();
23                        
24            if (!File.Exists(setting.getPathForSqlDatabase()))
25            {
26                SQLiteConnection.CreateFile(setting.getPathForSqlDatabase());
27            }
28
29            go_db = new SQLiteConnection("Data Source=" + setting.getPathForSqlDatabase() + ";Version=3;");
30            go_db.Open();
31        }
32
33        //ergaenze bool als return für check if succesfull
34        private void executeSql(string lv_sql_statement)
35        {
36            go_command = new SQLiteCommand(lv_sql_statement, go_db);
37            go_command.ExecuteNonQuery();
38        }
39
40        public void kontoTypenAdd(string konto)
41        {
42            //Check if table exists, create if not
43            gv_sql = "CREATE TABLE IF NOT EXISTS kontotypen(id INTEGER PRIMARY KEY AUTOINCREMENT, text nvarchar(100));";
44            executeSql(gv_sql);
45
46            //check if entry exists, only add if its a new text
47            gv_sql = "select * from kontotypen where text = '" + konto + "'";
48            go_command = new SQLiteCommand(gv_sql, go_db);
49            go_reader = go_command.ExecuteReader();
50
51            //insert new value
52            if (!go_reader.HasRows)
53            {
54                gv_sql = "insert into kontotypen (text) values ('" + konto + "')";
55                executeSql(gv_sql);
56            }            
57        }
58
59        //ToDO: evtl. erweitern um Filter
60        public DataTable kontoTypenRead()
61        {
62            DataTable ldt_kontoTypen = new DataTable("kontoTypen");
63            
64            gv_sql = "select id, text from kontotypen order by text asc";
65            go_command = new SQLiteCommand(gv_sql, go_db);
66            go_reader = go_command.ExecuteReader();
67            ldt_kontoTypen.Load(go_reader);
68            return ldt_kontoTypen;
69               
70        }
71    }
72}