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