· 8 years ago · Mar 30, 2018, 02:18 PM
1using System;
2using System.IO;
3using System.Collections.Generic;
4using System.Linq;
5using Npgsql;
6using System.Data;
7using Project3Tools;
8
9
10
11namespace Project3
12{
13 class Program
14 {
15 static void Main(string[] args)
16 {
17 //Databases
18 //
19 //Wijk:
20 // naam Varchar (PK)
21 //
22 //Straatnaam:
23 // id SERIAL PK
24 // straatnaam varchar (FK van
25 //
26 //
27 //
28 //
29 //
30 //
31 //
32
33 Project3Tools.GeneralParser P2 = new Project3Tools.GeneralParser();
34 GeneralParser parser = new GeneralParser();
35
36
37 //Scholen table
38 //0. Brin nummer (PK) varchar 2/2
39 //1. Instellingsnaam (varchar) 3/
40 //2. Straatnaam (integer) 4/ (NEEDS CONVERSION)
41 //3. Postcode (varchar) 6/
42
43 List<List<String>> School1 = parser.ParseCSV
44 (
45 @"D:\AlleCsv\scholen\school05_alleBasis.csv",
46 ';',
47 (int line, int column, string value) =>
48 {
49 if (column == 2 || column == 4 || column == 5 || column == 7 || column == 8) { return true; } else { return false; }
50 },
51 (string value) => value + " "
52 );
53 List<List<String>> School2 = parser.ParseCSV
54 (
55 @"D:\AlleCsv\scholen\school02_Middelbaar.csv",
56 ';',
57 (int line, int column, string value) =>
58 {
59 if (column == 2 || column == 4 || column == 5 || column == 7 || column == 8) { return true; } else { return false; }
60 },
61 (string value) => value + " "
62 );
63 List<List<String>> School3 = parser.ParseCSV
64 (
65 @"D:\AlleCsv\scholen\school03_Mbo.csv",
66 ';',
67 (int line, int column, string value) =>
68 {
69 if (column == 6 || column == 7 || column == 9 || column == 10) { return true; } else { return false; }
70 },
71 (string value) => value + " "
72 );
73 List<List<String>> School4 = parser.ParseCSV
74 (
75 @"D:\AlleCsv\scholen\school04_HboWo.csv",
76 ';',
77 (int line, int column, string value) =>
78 {
79 if (column == 5 || column == 6 || column == 8 || column == 9) { return true; } else { return false; }
80 },
81 (string value) => value + " "
82 );
83
84
85 SQLConnectionTool N1 = new SQLConnectionTool("Host = localhost; Username = postgres; Password = test; Database = Test");
86 //string[] typenames = { "varchar(150)", "varchar(100)", "varchar(100)", "varchar(100)" };
87 //N1.createTable("Scholen", School1[0].ToList(), typenames.ToList(), true, 1, "Niveau");
88 //N1.insertIntoTable("Scholen", School1[0].ToList(), School1, "Niveau", "'Basisschool'");
89 //N1.insertIntoTable("Scholen", School1[0].ToList(), School2, "Niveau", "'Middelbare School'");
90 //N1.insertIntoTable("Scholen", School1[0].ToList(), School3, "Niveau", "'MBO'");
91 //N1.insertIntoTable("Scholen", School1[0].ToList(), School4, "Niveau", "'HBO'");
92 //Console.Read();
93 }
94 }
95
96 //Created by Allon
97 public class GeneralParser
98 {
99 public List<List<string>> ParseCSV
100 (
101 string location,
102 char seperator,
103 Func<int, int, string, bool> filter,
104 Func<string, string> action
105 )
106 {
107 List<List<string>> parsed = new List<List<string>>();
108 try
109 {
110 using (StreamReader reader = new StreamReader(location))
111 {
112 string line;
113 /* Lees elke regel van het csv bestand */
114 int lineCounter = 0;
115 while ((line = reader.ReadLine()) != null)
116 {
117 lineCounter += 1;
118 /* Splits de regel op in kolommen en loop door elke kolom heen */
119 string[] columns = line.Split(seperator);
120 int columnCounter = 0;
121 List<string> rowList = new List<string>();
122 foreach (string column in columns)
123 {
124 columnCounter += 1;
125 /* Filter */
126 if (filter(lineCounter, columnCounter, column))
127 {
128 /* Voer een actie uit op de waarde en voeg het toe aan de rijlijst */
129 rowList.Add(action(column));
130 }
131 }
132 parsed.Add(rowList);
133 }
134 }
135
136 }
137 catch (Exception e)
138 {
139 Console.WriteLine("Something went wrong while reading the csv file: " + e);
140 }
141 return parsed;
142 }
143
144 public List<List<string>> parseList
145 (
146 List<List<string>> list,
147 Func<List<List<string>>, List<string>, bool> filter,
148 Func<List<List<string>>, List<string>, List<string>> action
149 )
150 {
151 List<List<string>> parsed = new List<List<string>>();
152 foreach (List<string> sublist in list)
153 {
154 if (filter(list, sublist))
155 parsed.Add(action(list, sublist));
156 }
157 return parsed;
158 }
159
160 public List<string> parseList
161 (
162 List<string> list,
163 Func<List<string>, string, bool> filter,
164 Func<List<string>, string, string> action
165 )
166 {
167 List<string> parsed = new List<string>();
168 foreach (string value in list)
169 {
170 if (filter(list, value))
171 parsed.Add(action(list, value));
172 }
173 return parsed;
174 }
175
176 public List<string> compareLists(List<string> list1, List<string> list2)
177 {
178 List<string> compared = new List<string>();
179 IEnumerable<string> intersection = list1.Intersect(list2);
180 foreach (string value in intersection)
181 {
182 compared.Add(value);
183 }
184 return compared;
185 }
186
187 public List<List<string>> compareLists(List<List<string>> list1, List<List<string>> list2)
188 {
189 List<List<string>> compared = new List<List<string>>();
190 parseList(list1, (listOne, value) => true, (listOne, value) =>
191 {
192 parseList(list2, (listTwo, value2) => true, (listTwo, value2) =>
193 {
194 return compareLists(value, value2);
195 });
196 return value;
197 });
198 return compared;
199 }
200
201 public List<string> ListTo1d(List<List<string>> list, Func<int, string, bool> filter)
202 {
203 List<string> result = new List<string>();
204 /* Loop door de eerste laag */
205 foreach (List<string> sublist in list)
206 {
207 int counter = 0;
208 /* Loop door de tweede laag */
209 foreach (string value in sublist)
210 {
211 counter += 1;
212 /* Filter */
213 if (filter(counter, value))
214 {
215 result.Add(value);
216 }
217 }
218 }
219 return result;
220 }
221 }
222
223 //Created by Tim
224 public class SQLConnectionTool
225 {
226 public SQLConnectionTool(string connectionstring)
227 {
228 CON = new NpgsqlConnection(connectionstring);
229 }
230 NpgsqlConnection CON;
231
232 //Voorbeeld input:
233 //tablename: "Mijntable"
234 //names = {"naam", "leeftijd"}
235 //typenames = {"Varchar(30)", "integer"}
236 //droptable = true
237 //PrimaryKey = null
238
239 //Voorbeeld output:
240 //DROP TABLE Mijntable;
241 //CREATE TABLE if not exists Mijntable
242 //(
243 // id SERIAL PRIMARY KEY,
244 // naam varchar(30),
245 // leeftijd integer
246 //);
247 public void createTable(string tablename, List<string> names, List<string> typenames, bool dropTable, int PrimaryKey, string extra)
248 {
249 if (names.Count != typenames.Count)
250 {
251 //MessageBox.Show("Please make sure that the amount of names equals the amount of type names");
252 throw new Exception("YA DUN! Names: " + names.Count + " typenames: " + typenames.Count);
253 }
254 if (PrimaryKey >= names.Count && PrimaryKey != 0)
255 {
256 //MessageBox.Show("Please make sure that the primary key is smaller than the amount of data types");
257 throw new Exception("YA DUN!");
258 }
259
260
261 NpgsqlCommand COM = new NpgsqlCommand();
262 COM.Connection = CON;
263 CON.Open();
264 if (dropTable)
265 {
266 COM.CommandText = "DROP TABLE if exists " + tablename + ";";
267 Console.Write(COM.CommandText + "\n");
268 COM.ExecuteNonQuery();
269 }
270 COM.CommandText = "CREATE TABLE if not exists " + tablename + "\n ( \n ";
271 if (PrimaryKey == 0)
272 {
273 COM.CommandText = COM.CommandText + "id SERIAL PRIMARY KEY, \n ";
274 }
275 for (int i = 0; i < typenames.Count; i++)
276 {
277 COM.CommandText = COM.CommandText + names[i].Replace(" ", "").Replace("-", "_") + " " + typenames[i];
278 if (i + 1 == PrimaryKey)
279 {
280 COM.CommandText = COM.CommandText + " PRIMARY KEY";
281 }
282 if ((i != typenames.Count && extra != "") || (i != typenames.Count - 1 && extra == ""))
283 {
284 COM.CommandText = COM.CommandText + ", ";
285 }
286 COM.CommandText = COM.CommandText + "\n ";
287 }
288 if (extra != "")
289 {
290 COM.CommandText = COM.CommandText + extra + " varchar (100)";
291 }
292 COM.CommandText = COM.CommandText + "\n );\n";
293 Console.Write(COM.CommandText);
294 Console.Read();
295 COM.ExecuteNonQuery();
296 CON.Close();
297 }
298
299 //Voorbeeld input:
300 //tablename = "Mijntable"
301 //names = {"naam", "leeftijd"}
302 //typenames = {"Varchar(30)", "integer"}
303 //Values = {"Eddy", "42"},{"Fred", "28"}, {"Jos", "6"}, {"Loubna", "6"}, {"Rini", "6"}}
304 //
305 //Voorbeeld output:
306 //INSERT INTO Mijntable (naam, leeftijd) VALUES ('Eddy', 42);
307 //INSERT INTO Mijntable (naam, leeftijd) VALUES ('Fred', 28);
308 //INSERT INTO Mijntable (naam, leeftijd) VALUES ('Jos', 6);
309 //INSERT INTO Mijntable (naam, leeftijd) VALUES ('Loubna', 6);
310 //INSERT INTO Mijntable (naam, leeftijd) VALUES ('Rini', 6);
311
312 public void insertIntoTable(string tablename, List<string> names, List<List<string>> Values, string extraName, string extraVal)
313 {
314 if (names.Count != Values[0].Count)
315 {
316 //MessageBox.Show("Please make sure that the amount of typenames, names and values are equal");
317 throw new Exception("YA DUN! Names: " + names.Count + " values[0]: " + Values[0].Count);
318 }
319
320 NpgsqlCommand COM = new NpgsqlCommand();
321 COM.Connection = CON;
322 CON.Open();
323
324 for (int i = 1; i < Values.Count; i++)
325 {
326 COM.CommandText = "INSERT INTO " + tablename + "(";
327 for (int j = 0; j < Values[i].Count; j++)
328 {
329 COM.CommandText = COM.CommandText + names[j].Replace(" ", "");
330 if ((j != Values[i].Count && extraName != "") || (j != Values[i].Count - 1 && extraName == ""))
331 {
332 COM.CommandText = COM.CommandText + ", ";
333 }
334 }
335 if (extraName != "")
336 {
337 COM.CommandText = COM.CommandText + extraName;
338 }
339 COM.CommandText = COM.CommandText + ") VALUES (";
340 for (int j = 0; j < Values[i].Count; j++)
341 {
342 COM.CommandText = COM.CommandText + "'" + Values[i][j] + "'";
343 if ((j != Values[i].Count && extraName != "") || (j != Values[i].Count - 1 && extraName == ""))
344 {
345 COM.CommandText = COM.CommandText + ", ";
346 }
347 }
348 if (extraName != "")
349 {
350 COM.CommandText = COM.CommandText + extraVal;
351 }
352 COM.CommandText = COM.CommandText + "); \n";
353 Console.Write(COM.CommandText);
354 try
355 {
356 COM.ExecuteNonQuery();
357 }
358 catch
359 {
360 COM.CommandText = "";
361 }
362 }
363 CON.Close();
364 }
365
366
367 public void joinTables(List<List<string>> table1, List<List<string>> table2, string tablename1, string tablename2, string newtable, int joinRowTable1, int joinRowTable2, List<int> selectRowsTable1, List<int> selectRowsTable2)
368 {
369 NpgsqlCommand COM = new NpgsqlCommand();
370 CON.Open();
371 COM.CommandText = "SELECT ";
372 foreach (int i in selectRowsTable1)
373 {
374 COM.CommandText = COM.CommandText + tablename1 + "." + table1[0][selectRowsTable1[i]] + ", ";
375 }
376 int j = 0;
377 foreach (int i in selectRowsTable2)
378 {
379 COM.CommandText = COM.CommandText + tablename2 + "." + table2[0][selectRowsTable2[i]];
380 if (j != selectRowsTable2.Count - 1)
381 {
382 COM.CommandText = COM.CommandText + ", ";
383 }
384 j++;
385 }
386 COM.CommandText = COM.CommandText + "\n";
387 COM.CommandText = COM.CommandText + "INTO " + newtable + "\n";
388 COM.CommandText = COM.CommandText + "FROM " + tablename1 + "\n";
389 COM.CommandText = COM.CommandText + "INNER JOIN " + tablename2 + " ON " + tablename1 + "." + table1[0][joinRowTable1] + " = " + tablename2 + "." + table2[0][joinRowTable2] + ";";
390 Console.WriteLine(COM.CommandText);
391 Console.Read();
392 COM.ExecuteNonQuery();
393 CON.Close();
394 }
395
396 public void readTable(string tablename, List<int> rows)
397 {
398 DataSet DS = new DataSet();
399 DataTable DT = new DataTable();
400
401 CON.Open();
402 NpgsqlDataAdapter DA = new NpgsqlDataAdapter(("SELECT * FROM " + tablename), CON);
403 DA.Fill(DS);
404 DT = DS.Tables[0];
405 foreach (DataRow ROW in DT.Rows)
406 {
407 for (int i = 0; i < rows.Count; i++)
408 {
409 Console.Write(ROW[rows[i]].ToString() + " ");
410 }
411 Console.Write("\n");
412 }
413 CON.Close();
414 }
415
416 public int straatnaamtoint(string straatnaam)
417 {
418
419 return 0;
420 }
421 }
422}