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