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