ALAgQiXk

1using System;
2using System.Collections.Generic;
3using System.Linq;
4using System.Text;
5using System.Threading.Tasks;
6using System.Data.SQLite;
7namespace KI_0._1
8{
9    public static class R
10    {
11        public static class Strg
12        {
13            public const string
14                ConnectStringBuilder = "Data Source={0}.s3db;",
15                CreateTableStringBuilder = "CREATE TABLE IF NOT EXISTS {0}({1});",
16                InsertIntoTableStringBuilder = "INSERT INTO {0}({1}) VALUES ({2});",
17                SelectDatasetStringBuilder = "SELECT {0} FROM {1}",
18                TablebaseName = "KiRon"
19                ;
20
21            public const string
22                ErrorToMutchTable = "0x0001",
23                ErrorNoTableSelct = "0x0002"
24                ;
25        }
26    }
27
28    public enum TableParameterTyp
29    {
30        INTEGER, TEXT
31    }
32
33    public struct TableParameter
34    {
35        private string Name, Typ;
36        private bool Autoincrement;
37
38        public TableParameter(string name, TableParameterTyp typ = TableParameterTyp.TEXT, bool autoincrement = false)
39        {
40            Name = name;
41            Typ = helper.EnumController.getEnumStrgValue(typeof(TableParameterTyp), typ);
42            Autoincrement = autoincrement;
43        }
44
45        public string ParameterName
46        {
47            get { return Name; }
48        }
49        public string ParameterTyp
50        {
51            get { return Autoincrement ? string.Format("{0} PRIMARY KEY AUTOINCREMENT", Typ) : Typ; }
52        }
53    }
54
55    public class Database
56    {
57        private SQLiteConnection connect;
58
59        public void ConnectToDatabase(string DatabaseName = "KiRon")
60        {
61            string DBConStrg = string.Format(R.Strg.ConnectStringBuilder, DatabaseName);
62            connect = new SQLiteConnection() { ConnectionString = DBConStrg };
63            connect.Open();
64            connect.Close();
65        }
66
67        public void CreateTable(string TableName, TableParameter[] prams)
68        {
69            connect.Open();
70            SQLiteCommand command = connect.CreateCommand();
71            command.CommandText = string.Concat(string.Format(R.Strg.CreateTableStringBuilder, TableName, string.Join(",", prams.Select(p => string.Format("{0} {1}", p.ParameterName, p.ParameterTyp)))), (!TableName.Equals(R.Strg.TablebaseName)) && (ValueCount("KiRon",string.Format("Name = '{0}'",TableName)) == 0)? string.Format(R.Strg.InsertIntoTableStringBuilder, R.Strg.TablebaseName, "Name,Prams", string.Join(",", new string[] { string.Format("'{0}'",TableName), string.Concat("'(",string.Join(",", prams.Select(x => x.ParameterName)),")'")})) : "");
72            try
73            {
74                command.ExecuteNonQuery();
75            }
76            catch {
77                connect.Close();
78                connect.Open();
79                command.ExecuteNonQuery();
80            }
81            
82            command.Dispose();
83            connect.Close();
84        }
85        public void InsertIntoDatabase(string TableName, string[] values)
86        {
87            connect.Open();
88            SQLiteCommand command = connect.CreateCommand();
89
90            dynamic vla =  GetDatasetDatabase(R.Strg.TablebaseName, string.Format("Name = '{0}'", TableName), "Prams");
91            if(vla is tableItem)
92            {
93                command.CommandText = string.Format(R.Strg.InsertIntoTableStringBuilder, TableName,string.Join(",",((tableItem)vla).Value.Split(',').Select(x => x.Split(' ')[0])).Replace(",ID",""),string.Concat("(",string.Join(",", values),")"));
94            }
95            if(vla is tableItem[][])
96            {
97                new Exception("tomutch reasons");
98            }
99            command.ExecuteNonQuery();
100            command.Dispose();
101            connect.Close();
102        }
103
104        public dynamic GetDatasetDatabase(string Tablename, string ArgString, string selection = "*",string limit = "")
105        {
106            connect.Open();
107            SQLiteCommand command = connect.CreateCommand();
108
109            //Tabellenname checken, denn main ist nicht verzeichnet
110            if (Tablename.Equals(R.Strg.TablebaseName))
111            {
112                command.CommandText = string.Format(R.Strg.SelectDatasetStringBuilder, selection, R.Strg.TablebaseName);
113
114                SQLiteDataReader reader = command.ExecuteReader();
115                tableItem[][] values = new tableItem[reader.FieldCount][].Select(x =>
116                {
117                    reader.Read();
118                    if (!selection.Contains(","))
119                        return new tableItem[] { new tableItem(selection, reader[selection].ToString()) };
120
121                    return selection.Split(',').Select(na => new tableItem(na, reader[na].ToString())).ToArray();
122                }).ToArray();
123                if(values[0].Length == 0)
124                {
125                    return (dynamic) values[0][0];
126                }
127                return (dynamic) values;
128            }
129            //"SELECT {0} FROM {1}"
130            command.CommandText = string.Concat(string.Format(R.Strg.SelectDatasetStringBuilder, selection, Tablename), string.IsNullOrWhiteSpace(ArgString) ? ";" : string.Concat(" WHERE ", ArgString,";"));
131            SQLiteDataReader dataReade = command.ExecuteReader();
132
133            tableItem[][] val = new tableItem[dataReade.FieldCount][].Select(x =>
134            {
135                dataReade.Read();
136                if(!selection.Contains(","))
137                    return new tableItem[] { new tableItem(selection, dataReade[selection].ToString())};
138               
139                return selection.Split(',').Select(na => new tableItem(na, dataReade[na].ToString())).ToArray();
140            }).ToArray();
141            if(val[0].Length == 0)
142            {
143                return (dynamic)val[0][0];
144            }
145
146            return (dynamic)val;
147        }
148
149        public int ValueCount(string tablename, string valueArg)
150        {
151            try
152            {
153            connect.Open();
154            } catch{};
155          
156            SQLiteCommand command = connect.CreateCommand();
157            command.CommandText = string.Format("SELECT COUNT(*) AS COUN FROM {0}{1}", tablename, string.IsNullOrEmpty(valueArg) ? ";" : string.Format(" WHERE {0};", valueArg));
158            SQLiteDataReader SDR = command.ExecuteReader();
159            SDR.Read();
160            command.Dispose();
161            return int.Parse(SDR["COUN"].ToString());
162        }
163
164        private struct tableItem
165        {
166            public string Itemname
167            {
168                get; private set;
169            }
170            public string Value
171            {
172                get; private set;
173            }
174
175            public tableItem(string itemname,string value)
176            {
177                Itemname = itemname;
178                Value = value;
179            }
180        }
181      
182
183    }
184
185
186    class KiController
187    {
188        //Contentanalyse 
189
190        /* Ist es ein Text
191         * o. Mathematische Aufgabe
192         * 
193         * Um wen geht es
194         * Was tut er
195         * Ist es gut oder böse
196         * Objectmapping
197         *     -> Tür hat geschlossen zu sein
198         *     _Tabelle: Tür=Zu
199         */
200        private Database DB;
201        public KiController()
202        {
203            DB = new Database();
204            DB.ConnectToDatabase();
205
206            DB.CreateTable(R.Strg.TablebaseName, new TableParameter[] {
207                new TableParameter("Name"),
208                new TableParameter("Prams"),
209                new TableParameter("ID", TableParameterTyp.INTEGER, true) });
210
211            DB.CreateTable("Wortarten", new TableParameter[] {
212                new TableParameter("Wortart"),
213                new TableParameter("ID", TableParameterTyp.INTEGER, true)});
214
215            DB.CreateTable("Wörter", new TableParameter[] {
216                new TableParameter("Word"),
217                new TableParameter("Wortart",TableParameterTyp.INTEGER),
218                new TableParameter("Benutzung"),
219                new TableParameter("ID", TableParameterTyp.INTEGER, true) });
220
221            
222        }
223
224
225    
226        //Ausgabe generierung
227
228
229    }
230}
231/*
232 * 
233Imports System.Data.SQLite
234Public Class Form1
235Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
236CreateDataBase()
237CreateTables()
238CreateData()
239SelectData()
240End Sub
241
242    Private Sub CreateDataBase()
243Dim connect As New SQLite.SQLiteConnection()
244connect.ConnectionString = "Data Source=user.s3db;"
245connect.Open()
246connect.Close()
247End Sub
248
249Private Sub CreateTables()
250Dim connect As New SQLite.SQLiteConnection()
251Dim command As SQLiteCommand
252connect.ConnectionString = "Data Source=user.s3db;"
253connect.Open()
254command = connect.CreateCommand
255command.CommandText = "CREATE TABLE user(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT);"
256command.ExecuteNonQuery()
257command.Dispose()
258connect.Close()
259End Sub
260
261Private Sub CreateData()
262Dim SQLconnect As New SQLite.SQLiteConnection()
263Dim SQLcommand As SQLiteCommand
264SQLconnect.ConnectionString = "Data Source=user.s3db;"
265SQLconnect.Open()
266SQLcommand = SQLconnect.CreateCommand
267SQLcommand.CommandText = "INSERT INTO user (id, name) VALUES (1, 'John')"
268'SQLcommand.CommandText = "UPDATE user SET name = 'Johnny' WHERE id = last_insert_rowid()"
269'SQLcommand.CommandText = "DELETE FROM user WHERE id = last_insert_rowid()"
270SQLcommand.ExecuteNonQuery()
271SQLcommand.Dispose()
272SQLconnect.Close()
273End Sub
274
275Private Sub SelectData()
276Dim SQLconnect As New SQLite.SQLiteConnection()
277Dim SQLcommand As SQLiteCommand
278SQLconnect.ConnectionString = "Data Source=user.s3db;"
279SQLconnect.Open()
280SQLcommand = SQLconnect.CreateCommand
281SQLcommand.CommandText = "SELECT * FROM user"
282Dim SQLreader As SQLiteDataReader = SQLcommand.ExecuteReader()
283ListBox1.Items.Clear()
284While SQLreader.Read()
285ListBox1.Items.Add(String.Format("ID = {0}, name = {1}", SQLreader(0), SQLreader(1)))
286'ListBox1.Items.Add(String.Format("ID = {0}, name = {1}", SQLreader("id"), SQLreader("name")))
287End While
288SQLcommand.Dispose()
289SQLconnect.Close() End Sub
290End Class
291
292    */