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