· 8 years ago · Mar 25, 2018, 11:04 AM
1using System;
2using System.Collections.Generic;
3using System.Data.SQLite;
4using System.Linq;
5using System.Text;
6using System.Threading.Tasks;
7using System.Windows;
8
9namespace ArmyArranger.Global
10{
11 static class Database
12 {
13 static SQLiteConnection sqlconnect = new SQLiteConnection(string.Format("Data Source={0}", System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "testbase.db")));
14 static SQLiteCommand command;
15 static SQLiteDataReader result;
16
17 static public void Connect()
18 {
19 try
20 {
21 sqlconnect.Open();
22 }
23 catch(Exception ex)
24 {
25 MessageBox.Show(ex.Message);
26 return;
27 }
28
29
30 try
31 {
32 command = new SQLiteCommand("" +
33 "CREATE TABLE IF NOT EXISTS Weapon(" +
34 "ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE," +
35 "Name VARCHAR(50) NOT NULL," +
36 "Range VARCHAR(50) NOT NULL," +
37 "Shots VARCHAR(50) NOT NULL," +
38 "Penetration INTEGER NULL," +
39 "RequiresLoader BIT NOT NULL" +
40 ")", sqlconnect);
41 command.ExecuteNonQuery();
42
43 command = new SQLiteCommand("" +
44 "CREATE TABLE IF NOT EXISTS Unit(" +
45 "ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE," +
46 "Name VARCHAR(50) NOT NULL," +
47 "NationID INTEGER NOT NULL," +
48 "Type VARCHAR(50) NOT NULL," +
49 "WeaponDescription VARCHAR(255) NULL," +
50 "Composition VARCHAR(50) NOT NULL," +
51 "Inexperienced INTEGER," +
52 "Regular INTEGER," +
53 "Veteran INTEGER," +
54 "PointsPerInexp INTEGER," +
55 "PointsPerRegular INTEGER," +
56 "PointsPerVeteran INTEGER," +
57 "BaseSize INTEGER NOT NULL," +
58 "MaxSize INTEGER NOT NULL," +
59 "ArmourClass INTEGER NULL," +
60 "FOREIGN KEY(NationID) REFERENCES Nation(ID)" +
61 ")", sqlconnect);
62 command.ExecuteNonQuery();
63
64 command = new SQLiteCommand("" +
65 "CREATE TABLE IF NOT EXISTS Selector(" +
66 "ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE," +
67 "Name VARCHAR(50) NOT NULL," +
68 "Date VARCHAR(50) NOT NULL," +
69 "Mandatory VARCHAR(500) NULL," +
70 "Headquarters VARCHAR(500) NULL," +
71 "Infantry VARCHAR(500) NULL," +
72 "ArmouredCars VARCHAR(500) NULL," +
73 "Artillery VARCHAR(500) NULL," +
74 "Tanks VARCHAR(500) NULL," +
75 "Transport VARCHAR(500) NULL," +
76 "NationID INTEGER NOT NULL," +
77 "FOREIGN KEY(NationID) REFERENCES Nation(ID)" +
78 ")", sqlconnect);
79 command.ExecuteNonQuery();
80
81 command = new SQLiteCommand("" +
82 "CREATE TABLE IF NOT EXISTS Rule(" +
83 "ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE," +
84 "Name VARCHAR(50) NOT NULL," +
85 "Description VARCHAR(500) NOT NULL," +
86 "Type VARCHAR(50) NOT NULL," +
87 "Source VARCHAR(50) NULL" +
88 ")", sqlconnect);
89 command.ExecuteNonQuery();
90
91 command = new SQLiteCommand("" +
92 "CREATE TABLE IF NOT EXISTS Option(" +
93 "ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE," +
94 "Description VARCHAR(255) NOT NULL," +
95 "Cost INTEGER NULL," +
96 "Count INTEGER NULL," +
97 "WeaponID INTEGER NULL," +
98 "RuleID INTEGER NULL," +
99 "UnitID INTEGER NULL," +
100 "FOREIGN KEY(WeaponID) REFERENCES Weapon(ID)," +
101 "FOREIGN KEY(RuleID) REFERENCES Rule(ID)," +
102 "FOREIGN KEY(UnitID) REFERENCES Unit(ID)" +
103 ")", sqlconnect);
104 command.ExecuteNonQuery();
105
106 command = new SQLiteCommand("" +
107 "CREATE TABLE IF NOT EXISTS Nation(" +
108 "ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE," +
109 "Name VARCHAR(50) NOT NULL" +
110 ")", sqlconnect);
111 command.ExecuteNonQuery();
112
113 command = new SQLiteCommand("" +
114 "CREATE TABLE IF NOT EXISTS Unit_Weapon(" +
115 "UnitID INTEGER NOT NULL," +
116 "WeaponID INTEGER NOT NULL," +
117 "FOREIGN KEY(UnitID) REFERENCES Unit(ID)" +
118 "FOREIGN KEY(WeaponID) REFERENCES Weapon(ID)" +
119 ")", sqlconnect);
120 command.ExecuteNonQuery();
121
122 command = new SQLiteCommand("" +
123 "CREATE TABLE IF NOT EXISTS Rule_Unit(" +
124 "RuleID INTEGER NOT NULL," +
125 "UnitID INTEGER NOT NULL," +
126 "FOREIGN KEY(RuleID) REFERENCES Rule(ID)" +
127 "FOREIGN KEY(UnitID) REFERENCES Unit(ID)" +
128 ")", sqlconnect);
129 command.ExecuteNonQuery();
130
131 command = new SQLiteCommand("" +
132 "CREATE TABLE IF NOT EXISTS Rule_Nation(" +
133 "RuleID INTEGER NOT NULL," +
134 "NationID INTEGER NOT NULL," +
135 "FOREIGN KEY(RuleID) REFERENCES Rule(ID)" +
136 "FOREIGN KEY(NationID) REFERENCES Nation(ID)" +
137 ")", sqlconnect);
138 command.ExecuteNonQuery();
139
140 command = new SQLiteCommand("" +
141 "CREATE TABLE IF NOT EXISTS Rule_Selector(" +
142 "RuleID INTEGER NOT NULL," +
143 "SelectorID INTEGER NOT NULL," +
144 "FOREIGN KEY(RuleID) REFERENCES Rule(ID)" +
145 "FOREIGN KEY(SelectorID) REFERENCES Selector(ID)" +
146 ")", sqlconnect);
147 command.ExecuteNonQuery();
148
149 command = new SQLiteCommand("" +
150 "CREATE TABLE IF NOT EXISTS Rule_Option(" +
151 "RuleID INTEGER NOT NULL," +
152 "OptionID INTEGER NOT NULL," +
153 "FOREIGN KEY(RuleID) REFERENCES Rule(ID)" +
154 "FOREIGN KEY(OptionID) REFERENCES Option(ID)" +
155 ")", sqlconnect);
156 command.ExecuteNonQuery();
157
158 command = new SQLiteCommand("" +
159 "CREATE TABLE IF NOT EXISTS Rule_Weapon(" +
160 "RuleID INTEGER NOT NULL," +
161 "WeaponID INTEGER NOT NULL," +
162 "FOREIGN KEY(RuleID) REFERENCES Rule(ID)" +
163 "FOREIGN KEY(WeaponID) REFERENCES Weapon(ID)" +
164 ")", sqlconnect);
165 command.ExecuteNonQuery();
166 }
167 catch (Exception ex)
168 {
169 MessageBox.Show(ex.Message);
170 return;
171 }
172 }
173
174
175 static public void Close()
176 {
177 sqlconnect.Close();
178 }
179
180
181 static public SQLiteDataReader ExecuteCommand(string query)
182 {
183 try
184 {
185 command = new SQLiteCommand(query, sqlconnect);
186 result = command.ExecuteReader();
187 }
188 catch (Exception ex)
189 {
190 throw ex;
191 }
192 return result;
193 }
194
195 static public int GetLastInsertedID()
196 {
197 try
198 {
199 using (SQLiteCommand cmd = sqlconnect.CreateCommand())
200 {
201 cmd.CommandText = @"SELECT last_insert_rowid()";
202 cmd.ExecuteNonQuery();
203 int lastID = Convert.ToInt32(cmd.ExecuteScalar());
204
205 return lastID;
206 }
207 }
208 catch (Exception ex)
209 {
210 throw ex;
211 }
212 }
213 }
214}