· 8 years ago · Mar 28, 2018, 11:50 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
13namespace Project3
14{
15 class Program
16 {
17 static void Main(string[] args)
18 {
19 GeneralParser parser = new GeneralParser();
20 List<List<String>> School1 = parser.ParseCSV
21 (
22 @"D:\AlleCsv\scholen\school01_Basis.csv",
23 ';',
24 (int line, int column, string value) => { if (column < 5) { return true; } else { return false; }
25 },
26 (string value) => value + " "
27 );
28
29
30 for (int i = 0; i < School1.Count(); i++)
31 {
32 for (int j = 0; j < School1[0].Count(); j++)
33 {
34 Console.Write(School1[i][j]);
35 }
36 Console.WriteLine();
37 }
38 SQLConnectionThing.editData();
39 List<int> intlist = new List<int>();
40 intlist.Add(1);
41 intlist.Add(0);
42 SQLConnectionThing.readData("lolz", intlist);
43 Console.Read();
44 }
45 }
46
47 //Created by Allon
48 public class GeneralParser
49 {
50 public List<List<string>> ParseCSV
51 (
52 string location,
53 char seperator,
54 Func<int, int, string, bool> filter,
55 Func<string, string> action
56 )
57 {
58 List<List<string>> parsed = new List<List<string>>();
59 try
60 {
61 using (StreamReader reader = new StreamReader(location))
62 {
63 string line;
64 /* Lees elke regel van het csv bestand */
65 int lineCounter = 0;
66 while ((line = reader.ReadLine()) != null)
67 {
68 lineCounter += 1;
69 /* Splits de regel op in kolommen en loop door elke kolom heen */
70 string[] columns = line.Split(seperator);
71 int columnCounter = 0;
72 List<string> rowList = new List<string>();
73 foreach (string column in columns)
74 {
75 columnCounter += 1;
76 /* Filter */
77 if (filter(lineCounter, columnCounter, column))
78 {
79 /* Voer een actie uit op de waarde en voeg het toe aan de rijlijst */
80 rowList.Add(action(column));
81 }
82 }
83 parsed.Add(rowList);
84 }
85 }
86
87 }
88 catch (Exception e)
89 {
90 Console.WriteLine("Something went wrong while reading the csv file: " + e);
91 }
92 return parsed;
93 }
94
95 public List<List<string>> parseList
96 (
97 List<List<string>> list,
98 Func<List<List<string>>, List<string>, bool> filter,
99 Func<List<List<string>>, List<string>, List<string>> action
100 )
101 {
102 List<List<string>> parsed = new List<List<string>>();
103 foreach (List<string> sublist in list)
104 {
105 if (filter(list, sublist))
106 parsed.Add(action(list, sublist));
107 }
108 return parsed;
109 }
110
111 public List<string> parseList
112 (
113 List<string> list,
114 Func<List<string>, string, bool> filter,
115 Func<List<string>, string, string> action
116 )
117 {
118 List<string> parsed = new List<string>();
119 foreach (string value in list)
120 {
121 if (filter(list, value))
122 parsed.Add(action(list, value));
123 }
124 return parsed;
125 }
126
127 public List<string> compareLists(List<string> list1, List<string> list2)
128 {
129 List<string> compared = new List<string>();
130 IEnumerable<string> intersection = list1.Intersect(list2);
131 foreach (string value in intersection)
132 {
133 compared.Add(value);
134 }
135 return compared;
136 }
137
138 public List<List<string>> compareLists(List<List<string>> list1, List<List<string>> list2)
139 {
140 List<List<string>> compared = new List<List<string>>();
141 parseList(list1, (listOne, value) => true, (listOne, value) => {
142 parseList(list2, (listTwo, value2) => true, (listTwo, value2) => {
143 return compareLists(value, value2);
144 });
145 return value;
146 });
147 return compared;
148 }
149
150 public List<string> ListTo1d(List<List<string>> list, Func<int, string, bool> filter)
151 {
152 List<string> result = new List<string>();
153 /* Loop door de eerste laag */
154 foreach (List<string> sublist in list)
155 {
156 int counter = 0;
157 /* Loop door de tweede laag */
158 foreach (string value in sublist)
159 {
160 counter += 1;
161 /* Filter */
162 if (filter(counter, value))
163 {
164 result.Add(value);
165 }
166 }
167 }
168 return result;
169 }
170 }
171
172 //Created by Tim
173 public class SQLConnectionThing
174 {
175 public static NpgsqlConnection CON = new NpgsqlConnection("Host=localhost;Username=postgres;Password=test;Database=Test");
176
177 //Need to see what this does...
178 public static DataTable GrabData(string statement)
179 {
180 DataTable DT = new DataTable();
181
182 NpgsqlConnection Con = new NpgsqlConnection("Host=localhost;Username=postgres;Password=test;Database=Test");
183 NpgsqlCommand Comm = new NpgsqlCommand(statement, CON);
184 NpgsqlDataAdapter ada = new NpgsqlDataAdapter(Comm);
185 CON.Open();
186 var cmd = new NpgsqlCommand(statement, CON);
187 var reader = cmd.ExecuteReader();
188 while (reader.Read())
189 {
190 DT.Rows.Add(reader.GetString(0));
191 }
192 CON.Close();
193 return DT;
194 }
195
196 //public static void createTable(string tablename, list<string> typenames)
197 //
198 //public static void insertIntoTable()
199 //
200 //public static void readFromTable()
201 //
202 //
203 //
204 //
205 //
206 //
207 //
208 //
209 //
210 //
211 //
212 //
213 //
214 //
215 //
216 //
217 //
218 //
219 //
220 //
221 //
222 //
223 //
224 //
225 //
226 //
227
228 public static void readData(string tablename, List<int> rows)
229 {
230 DataSet DS = new DataSet();
231 DataTable DT = new DataTable();
232 NpgsqlConnection Con = new NpgsqlConnection("Host=localhost;Username=postgres;Password=test;Database=Test");
233
234 Con.Open();
235 NpgsqlDataAdapter DA = new NpgsqlDataAdapter("SELECT * FROM " + tablename, Con);
236 DA.Fill(DS);
237 DT = DS.Tables[0];
238 foreach (DataRow ROW in DT.Rows)
239 {
240 for (int i = 0; i < rows.Count; i++)
241 {
242 Console.Write(ROW[rows[i]] + " ");
243 }
244 Console.Write("\n");
245 }
246
247 Con.Close();
248
249 }
250
251 public static void editData()
252 {
253 NpgsqlConnection Con = new NpgsqlConnection("Host=localhost;Username=postgres;Password=test;Database=Test");
254 NpgsqlCommand Com = new NpgsqlCommand();
255 Com.Connection = Con;
256 Con.Open();
257 Com.CommandText = "DROP TABLE lolz";
258 Com.ExecuteNonQuery();
259 Com.CommandText = @"CREATE TABLE if not exists lolz
260(
261 id SERIAL,
262 name varchar(30)
263); ";
264 Com.ExecuteNonQuery();
265 Random rnd = new Random();
266 for (int i = 0; i < 100; i++)
267 {
268 int a = rnd.Next(1, 6);
269 switch (a)
270 {
271 case 1:
272 Com.CommandText = "INSERT INTO lolz(name) VALUES ('FRED');";
273 break;
274 case 2:
275 Com.CommandText = "INSERT INTO lolz(name) VALUES ('EDDY');";
276 break;
277 case 3:
278 Com.CommandText = "INSERT INTO lolz(name) VALUES ('HERBERT');";
279 break;
280 case 4:
281 Com.CommandText = "INSERT INTO lolz(name) VALUES ('HENDRIK');";
282 break;
283 case 5:
284 Com.CommandText = "INSERT INTO lolz(name) VALUES ('BOB');";
285 break;
286
287 }
288 Com.ExecuteNonQuery();
289 }
290
291 Con.Close();
292 }
293 }
294}