API tools faq
paste
Guest User

Untitled

a guest
Apr 2nd, 2018
64
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 35.22 KB | None | 0 0
raw download clone embed print report
  1. using System;
  2. using System.IO;
  3. using System.Collections.Generic;
  4. using System.Linq;
  5. using Npgsql;
  6. using System.Data;
  7. using System.Text.RegularExpressions;
  8.  
  9.  
  10.  
  11. namespace Project3
  12. {
  13. class Program
  14. {
  15. static void Main(string[] args)
  16. {
  17. GeneralParser parser = new GeneralParser();
  18.  
  19.  
  20.  
  21. string[] ScholenNaamTypes = { "id", "instellingsnaam", "straatnaam", "wijk"};
  22. SQLConnectionTool N1 = new SQLConnectionTool("Host = localhost; Username = postgres; Password = Test; Database = Project3Test");
  23.  
  24.  
  25.  
  26.  
  27.  
  28. List<int> straatnamen = new List<int>();
  29. #region scholen
  30. //Scholen table
  31. //0. Brin nummer (PK) (varchar) 2/2/4/3
  32. //1. Instellingsnaam (varchar) 3/3/5/4
  33. //2. Straatnaam (integer) 4/4/6/5 (NEEDS CONVERSION)
  34. //3. Wijk (varchar) 6/6/8/7 (NEEDS CONVERSION)
  35. //4. Niveau (varchar) x/x/x/x
  36. //5. Plaatsnaam 7/7/9/8
  37.  
  38. List<List<String>> School1 = parser.ParseCSV
  39. (
  40. @"D:\AlleCsv\scholen\school01_Basis.csv",
  41. ';',
  42. (int line, int column, string value, string[] columns) =>
  43. {
  44. if (column == 2 || column == 3 || column == 4 || column == 6 || column == 7) { return true; } else { return false; }
  45. },
  46. (string value) => value + " "
  47. );
  48. List<List<String>> School2 = parser.ParseCSV
  49. (
  50. @"D:\AlleCsv\scholen\school02_Middelbaar.csv",
  51. ';',
  52. (int line, int column, string value, string[] columns) =>
  53. {
  54. if (column == 2 || column == 3 || column == 4 || column == 6 || column == 7) { return true; } else { return false; }
  55. },
  56. (string value) => value + " "
  57. );
  58. List<List<String>> School3 = parser.ParseCSV
  59. (
  60. @"D:\AlleCsv\scholen\school03_Mbo.csv",
  61. ';',
  62. (int line, int column, string value, string[] columns) =>
  63. {
  64. if (column == 4 || column == 5 || column == 6 || column == 8 || column == 9) { return true; } else { return false; }
  65. },
  66. (string value) => value + " "
  67. );
  68. List<List<String>> School4 = parser.ParseCSV
  69. (
  70. @"D:\AlleCsv\scholen\school04_HboWo.csv",
  71. ';',
  72. (int line, int column, string value, string[] columns) =>
  73. {
  74. if (column == 3 || column == 4 || column == 5 || column == 7 || column == 8) { return true; } else { return false; }
  75. },
  76. (string value) => value + " "
  77. );
  78.  
  79. List<List<String>> postcodesenwijken = parser.ParseCSV
  80. (
  81. @"D:\AlleCsv\wijken\Postcodesenwijken.csv",
  82. ';',
  83. (int line, int column, string value, string[] columns) =>
  84. {
  85. if (column == 0 || column == 3) { return true; } else { return false; }
  86. },
  87. (string value) => value
  88. );
  89. N1.Postcodesenwijken = postcodesenwijken;
  90.  
  91.  
  92. string[] typenamespostcodes = { "Varchar(10)", "Varchar(40)" };
  93.  
  94. string[] typenames = { "varchar(10)", "varchar(150)", "varchar(100)", "varchar(100)" };
  95.  
  96. List<List<string>> nschool1 = new List<List<string>>();
  97. for (int i = 0; i < School1.Count; i++)
  98. {
  99. if (School1[i][4].ToString().Contains("ROTTERDAM"))
  100. {
  101. List<string> l = new List<string>();
  102. School1[i][2] = N1.straatnaamtoint(School1[i][2], School1[i][3]).ToString();
  103. School1[i][3] = N1.PostcodeNaarWijk(School1[i][3].Replace(" ", ""));
  104. for (int j = 0; j < School1[i].Count - 1; j++)
  105. {
  106. Console.ForegroundColor = ConsoleColor.White;
  107. Console.WriteLine("SCHOOL: " + School1[0][j] + " " + School1[i][j].ToString() + " " + j.ToString());
  108. l.Add(School1[i][j]);
  109. }
  110. nschool1.Add(l);
  111. }
  112. }
  113. N1.insertIntoTable("scholen", ScholenNaamTypes.ToList(), nschool1, "niveau", "'lager'");
  114.  
  115.  
  116.  
  117. List<List<string>> nschool2 = new List<List<string>>();
  118. for (int i = 0; i < School2.Count; i++)
  119. {
  120. if (School2[i][4].ToString().Contains("ROTTERDAM"))
  121. {
  122. List<string> l = new List<string>();
  123. School2[i][2] = N1.straatnaamtoint(School2[i][2], School2[i][3]).ToString();
  124. School2[i][3] = N1.PostcodeNaarWijk(School2[i][3].Replace(" ", ""));
  125. for (int j = 0; j < School2[i].Count - 1; j++)
  126. {
  127. Console.ForegroundColor = ConsoleColor.White;
  128. Console.WriteLine("SCHOOL: " + School2[0][j] + " " + School2[i][j].ToString() + " " + j.ToString());
  129. l.Add(School2[i][j]);
  130. }
  131. nschool2.Add(l);
  132. }
  133. }
  134. N1.insertIntoTable("scholen", ScholenNaamTypes.ToList(), nschool2, "niveau", "'middelbaar'");
  135.  
  136.  
  137.  
  138. List<List<string>> nschool3 = new List<List<string>>();
  139. for (int i = 0; i < School3.Count; i++)
  140. {
  141. if (School3[i][4].ToString().Contains("ROTTERDAM"))
  142. {
  143. List<string> l = new List<string>();
  144. School3[i][2] = N1.straatnaamtoint(School3[i][2], School3[i][3]).ToString();
  145. School3[i][3] = N1.PostcodeNaarWijk(School3[i][3].Replace(" ", ""));
  146. for (int j = 0; j < School3[i].Count - 1; j++)
  147. {
  148. Console.ForegroundColor = ConsoleColor.White;
  149. Console.WriteLine("SCHOOL: " + School3[0][j] + " " + School3[i][j].ToString() + " " + j.ToString());
  150. l.Add(School3[i][j]);
  151. }
  152. nschool3.Add(l);
  153. }
  154. }
  155. N1.insertIntoTable("scholen", ScholenNaamTypes.ToList(), nschool3, "niveau", "'hoger'");
  156.  
  157.  
  158.  
  159. List<List<string>> nschool4 = new List<List<string>>();
  160. for (int i = 0; i < School4.Count; i++)
  161. {
  162. if (School4[i][4].ToString().Contains("ROTTERDAM"))
  163. {
  164. List<string> l = new List<string>();
  165. School4[i][2] = N1.straatnaamtoint(School4[i][2], School4[i][3]).ToString();
  166. School4[i][3] = N1.PostcodeNaarWijk(School4[i][3].Replace(" ", ""));
  167. for (int j = 0; j < School4[i].Count - 1; j++)
  168. {
  169. Console.ForegroundColor = ConsoleColor.White;
  170. Console.WriteLine("SCHOOL: " + School4[0][j] + " " + School4[i][j].ToString() + " " + j.ToString());
  171. l.Add(School4[i][j]);
  172. }
  173. nschool4.Add(l);
  174. }
  175. }
  176. N1.insertIntoTable("scholen", ScholenNaamTypes.ToList(), nschool4, "niveau", "'hoger'");
  177. #endregion
  178.  
  179. #region misdaden
  180. /*create table if not exists misdaad(
  181. casenummer char(10) primary key,
  182. einddatumtijd date,
  183. begindatumtijd date,
  184. type varchar(20),
  185. straatnaam int,
  186. wijk varchar(30),
  187. jaartal date,
  188. foreign key (jaartal) references jaartal(jaartal),
  189. constraint fk_misdaad_straatnaam foreign key (straatnaam, wijk) references straatnaam(id, wijk),
  190. constraint typecontrole check (type='straatroof' or type='fietsdiefstal')
  191. */
  192.  
  193. //Misdaden table
  194. //0. Casenummer (PK) (varchar) 00
  195. //1. Begindatumtijd (datetime) 08,09 (NEEDS CONVERSION)
  196. //2. Einddatumtijd (datetime) 10,11 (NEEDS CONVERSION)
  197. //3. Type (varchar) 13
  198. //4. Straatnaam (int) 19 (NEEDS CONVERSION)
  199. //5. Wijk (varchar) 17
  200. //6. Jaartal (varchar) x
  201.  
  202. string[] misdaadtypesarray = {"casenummer", "begindatumtijd", "einddatumtijd", "type", "straatnaam", "wijk", "jaartal"};
  203. List<string> misdaadtypes = misdaadtypesarray.ToList();
  204.  
  205.  
  206. List<List<String>> misdaden = parser.ParseCSV
  207. (
  208. @"D:\AlleCsv\misdaden\Straatroof-2011.csv",
  209. ';',
  210. (int line, int column, string value, string[] columns) =>
  211. {
  212. if (column == 0 || column == 8 || column == 9 || column == 10 || column == 11 || column == 13 || column == 19 || column == 22) { return true; } else { return false; }
  213. },
  214. (string value) => value
  215. );
  216.  
  217. List<List<String>> misdaden2 = new List<List<string>>();
  218.  
  219. for (int i = 0; i < misdaden.Count; i++)
  220. {
  221. List<String> l = new List<string>();
  222. for (int j = 0; j < misdaden[0].Count; j++)
  223. {
  224. try
  225. {
  226. Console.ForegroundColor = ConsoleColor.White;
  227. l.Add(misdaden[i][j]);
  228. }
  229. catch
  230. {
  231. Console.ForegroundColor = ConsoleColor.Red;
  232. Console.WriteLine("NOT ADDED");
  233. break;
  234. }
  235. if (l.Count == misdaden[0].Count)
  236. {
  237. misdaden2.Add(l);
  238. Console.ForegroundColor = ConsoleColor.Green;
  239. Console.WriteLine("ADDED");
  240.  
  241. }
  242. }
  243. Console.WriteLine();
  244. }
  245.  
  246.  
  247. Console.ForegroundColor = ConsoleColor.White;
  248. for (int i = 0; i < misdaden2.Count; i++)
  249. {
  250. for (int j = 0; j < misdaden2[0].Count; j++)
  251. {
  252. Console.Write(misdaden2[i][j] + ", ");
  253. }
  254. Console.WriteLine();
  255. }
  256.  
  257. List<List<string>> misdaden3 = new List<List<string>>();
  258.  
  259. for (int i = 0; i < misdaden2.Count; i++)
  260. {
  261. //0. Casenummer (PK) (varchar) 00
  262. //1. Begindatumtijd (datetime) 08,09 (NEEDS CONVERSION)
  263. //2. Einddatumtijd (datetime) 10,11 (NEEDS CONVERSION)
  264. //3. Type (varchar) 13
  265. //4. Straatnaam (int) 19 (NEEDS CONVERSION)
  266. //5. Wijk (varchar) 17
  267. //6. Jaartal (varchar) x
  268. if (misdaden2[i][7].Replace(" ", "") != "")
  269. {
  270. List<string> l = new List<string>();
  271. Console.ForegroundColor = ConsoleColor.White;
  272. l.Add(misdaden2[i][0]);
  273. l.Add(dateANDtimeTOdatetime(misdaden2[i][1], misdaden2[i][2]));
  274. l.Add(dateANDtimeTOdatetime(misdaden2[i][3], misdaden2[i][4]));
  275. l.Add(misdaden2[i][5]);
  276. l.Add(N1.straatnaamtoint(misdaden2[i][6], misdaden2[i][7]).ToString());
  277. l.Add(N1.PostcodeNaarWijk(misdaden2[i][7]));
  278. l.Add("2011");
  279. misdaden3.Add(l);
  280. }
  281. }
  282. for (int i = 0; i < misdaden3.Count; i++)
  283. {
  284. for (int j = 0; j < misdaden3[i].Count; j++)
  285. {
  286. Console.WriteLine(misdaadtypes[j] + ": " + misdaden3[i][j]);
  287. }
  288. }
  289.  
  290. N1.insertIntoTable("misdaad", misdaadtypes, misdaden3, "", "");
  291.  
  292.  
  293.  
  294. //Misdaden table
  295. //0. Casenummer (PK) (varchar) 00
  296. //1. Begindatumtijd (datetime) 11,12 (NEEDS CONVERSION)
  297. //2. Einddatumtijd (datetime) 14,15 (NEEDS CONVERSION)
  298. //3. Type (varchar) 3
  299. //4. Straatnaam (int) 9 (NEEDS CONVERSION)
  300. //5. Wijk (varchar) 8 (NEEDS CONVERSION)
  301. //6. Jaartal (varchar) 16
  302. List<List<String>> fietsendiefstallen1 = parser.ParseCSV
  303. (
  304. @"D:\AlleCsv\misdaden\fietsdiefstal-2011-2013.csv",
  305. ';',
  306. (int line, int column, string value, string[] columns) =>
  307. {
  308. if (column == 0 || column == 11 || column == 12 || column == 14 || column == 15 || column == 3 || column == 9 || column == 8 || column == 16 || column == 7) { return true; } else { return false; }
  309. },
  310. (string value) => value
  311. );
  312.  
  313. for (int i = 0; i < fietsendiefstallen1.Count; i++)
  314. {
  315. for (int j = 0; j < fietsendiefstallen1[i].Count; j++)
  316. {
  317. Console.Write(fietsendiefstallen1[i][j] + ", ");
  318. }
  319. Console.WriteLine();
  320. }
  321. List<List<string>> fietsendiefstallen2 = new List<List<string>>();
  322. for (int i = 0; i < fietsendiefstallen1.Count; i++)
  323. {
  324. try
  325. {
  326. if (fietsendiefstallen1[i][2].Contains("ROTTER"))
  327. {
  328. Console.ForegroundColor = ConsoleColor.Blue;
  329. Console.WriteLine("Fietsendiefstal");
  330. List<string> l = new List<string>();
  331. l.Add(fietsendiefstallen1[i][0]);
  332. l.Add(dateANDtimeTOdatetime(fietsendiefstallen1[i][5], fietsendiefstallen1[i][6]));
  333. l.Add(dateANDtimeTOdatetime(fietsendiefstallen1[i][7], fietsendiefstallen1[i][8]));
  334. l.Add("fietsdiefstal");
  335. Console.WriteLine((Regex.Replace(fietsendiefstallen1[i][4], @"\d", "").Replace(" ", "")).ToString());
  336. Console.WriteLine(N1.straatnaamtoint2(Regex.Replace(fietsendiefstallen1[i][4], @"\d", "").Replace(" ", "")).ToString());
  337. if (N1.straatnaamtoint2(Regex.Replace(fietsendiefstallen1[i][4], @"\d", "").Replace(" ", "")) != 0)
  338. {
  339. l.Add(N1.straatnaamtoint2(Regex.Replace(fietsendiefstallen1[i][4], @"\d", "").Replace(" ", "")).ToString());
  340. Console.WriteLine(N1.findWijk(N1.straatnaamtoint2(Regex.Replace(fietsendiefstallen1[i][4], @"\d", "").Replace(" ", ""))));
  341. if (N1.findWijk(N1.straatnaamtoint2(Regex.Replace(fietsendiefstallen1[i][4], @"\d", "").Replace(" ", ""))) != "")
  342. {
  343. Console.WriteLine("KJ");
  344. }
  345. }
  346. if (N1.findWijk(N1.straatnaamtoint2(Regex.Replace(fietsendiefstallen1[i][4], @"\d", "").Replace(" ", ""))) != "")
  347. {
  348. l.Add(N1.findWijk(N1.straatnaamtoint2(Regex.Replace(fietsendiefstallen1[i][4], @"\d", "").Replace(" ", ""))));
  349. }
  350. l.Add(fietsendiefstallen1[i][9]);
  351. for (int j = 0; j < l.Count; j++)
  352. {
  353. Console.Write(l[j].ToString() + ", ");
  354. }
  355. Console.WriteLine(l.Count);
  356. if (l.Count == 7)
  357. {
  358. fietsendiefstallen2.Add(l);
  359. }
  360.  
  361. }
  362. }
  363. catch
  364. {
  365.  
  366. }
  367. }
  368. Console.Write("KJKJ");
  369.  
  370.  
  371. for (int i = 0; i < fietsendiefstallen2.Count; i++)
  372. {
  373. for (int j = 0; j < fietsendiefstallen2[i].Count; j++)
  374. {
  375. Console.WriteLine(fietsendiefstallen2[i][j] + ": " + misdaadtypes[j]);
  376. }
  377. Console.WriteLine();
  378. }
  379. N1.insertIntoTable("misdaad", misdaadtypes, fietsendiefstallen2, "", "");
  380.  
  381. Console.Read();
  382.  
  383.  
  384. #endregion
  385. }
  386.  
  387. public static string dateANDtimeTOdatetime(string date, string time)
  388. {
  389. return date + " " + time;
  390. }
  391. }
  392.  
  393. //Created by Allon
  394. public class GeneralParser
  395. {
  396. public List<List<string>> ParseCSV(string location, char seperator, Func<int, int, string, string[], bool> filter, Func<string, string> action)
  397. {
  398. List<List<string>> parsed = new List<List<string>>();
  399. try
  400. {
  401. using (StreamReader reader = new StreamReader(location))
  402. {
  403. string line;
  404. /* Lees elke regel van het csv bestand */
  405. int lineCounter = 0;
  406. while ((line = reader.ReadLine()) != null)
  407. {
  408. /* Splits de regel op in kolommen en loop door elke kolom heen */
  409. string[] columns = line.Split(seperator);
  410. int columnCounter = 0;
  411. List<string> rowList = new List<string>();
  412. foreach (string column in columns)
  413. {
  414. /* Filter */
  415. if (filter(lineCounter, columnCounter, column, columns))
  416. {
  417. /* Voer een actie uit op de waarde en voeg het toe aan de rijlijst */
  418. rowList.Add(action(column));
  419. }
  420. columnCounter += 1;
  421. }
  422. if (rowList.Count > 0)
  423. parsed.Add(rowList);
  424. lineCounter += 1;
  425. }
  426. }
  427. }
  428. catch (Exception e)
  429. {
  430. Console.WriteLine("Something went wrong while reading the csv file: " + e);
  431. }
  432. return parsed;
  433. }
  434.  
  435. public List<List<string>> parseList(List<List<string>> list, Func<List<List<string>>, List<string>, bool> filter, Func<List<List<string>>, List<string>, List<string>> action)
  436. {
  437. List<List<string>> parsed = new List<List<string>>();
  438. foreach (List<string> sublist in list)
  439. {
  440. if (filter(list, sublist))
  441. if (sublist.Count > 0)
  442. parsed.Add(action(list, sublist));
  443. }
  444. return parsed;
  445. }
  446.  
  447. public List<string> parseList(List<string> list, Func<List<string>, string, bool> filter, Func<List<string>, string, string> action)
  448. {
  449. List<string> parsed = new List<string>();
  450. foreach (string value in list)
  451. {
  452. if (filter(list, value))
  453. if (value != "")
  454. parsed.Add(action(list, value));
  455. }
  456. return parsed;
  457. }
  458.  
  459. public List<List<string>> compareLists(List<string> list1, List<string> list2, Func<List<string>, string, Func<List<string>, string, bool>> filter, Func<List<string>, string, Func<List<string>, string, string>> action)
  460. {
  461. List<List<string>> compared = new List<List<string>>();
  462. compared.Add(parseList(list1, (list, value) => true, (list, value) => {
  463. compared.Add(parseList(list2, filter(list, value), action(list, value)));
  464. return value;
  465. }));
  466. return compared;
  467. }
  468.  
  469. public List<List<string>> compareLists(List<List<string>> list1, List<List<string>> list2, Func<List<string>, string, Func<List<string>, string, bool>> filter, Func<List<string>, string, Func<List<string>, string, string>> action)
  470. {
  471. List<List<string>> compared = new List<List<string>>();
  472. parseList(list1, (list, sublist) => true, (list, sublist) => {
  473. return parseList(sublist, (list1Sublist, list1Value) => true, (list1Sublist, list1Value) => {
  474. parseList(list2, (list2List, list2Sublist) => true, (list2List, list2Sublist) => {
  475. compared.Add(parseList(list2Sublist, filter(list1Sublist, list1Value), action(list1Sublist, list1Value)));
  476. return list2Sublist;
  477. });
  478. return list1Value;
  479. });
  480. });
  481. return compared;
  482. }
  483.  
  484. public List<string> ListTo1d(List<List<string>> list, Func<int, string, bool> filter)
  485. {
  486. List<string> result = new List<string>();
  487. /* Loop door de eerste laag */
  488. foreach (List<string> sublist in list)
  489. {
  490. int counter = 0;
  491. /* Loop door de tweede laag */
  492. foreach (string value in sublist)
  493. {
  494. /* Filter */
  495. if (filter(counter, value))
  496. {
  497. result.Add(value);
  498. }
  499. counter += 1;
  500. }
  501. }
  502. return result;
  503. }
  504.  
  505. public List<List<string>> removeDuplicates(List<List<string>> list)
  506. {
  507. List<List<string>> filtered = new List<List<string>>();
  508.  
  509. return filtered;
  510. }
  511.  
  512. public string BuurtNaarWijk(string buurt)
  513. {
  514. var buurtenlijst = ParseCSV(@"D:\AlleCsv\wijken", ';', (line, row, column, columns) => columns[1] == buurt && row == 0, (column) => column);
  515. return buurtenlijst[0][0].Substring(0, 5);
  516. }
  517.  
  518. public string PostcodeNaarWijk(string postcode)
  519. {
  520. var postcodeWijkLijst = ParseCSV(@"D:\AlleCsv\wijken\Postcodeseenwijken.csv", ';', (line, row, column, columns) => line > 0 && columns[0].Substring(0, 4) == postcode && row == 3, (column) => column);
  521. return postcodeWijkLijst[0][0];
  522. }
  523. }
  524.  
  525. //Created by Tim
  526. public class SQLConnectionTool
  527. {
  528. public List<List<string>> Postcodesenwijken;
  529.  
  530. public SQLConnectionTool(string connectionstring)
  531. {
  532. CON = new NpgsqlConnection(connectionstring);
  533. }
  534. NpgsqlConnection CON = new NpgsqlConnection("Host = localhost; Username = postgres; Password = Test; Database = Project3Test");
  535. public static List<straatnamen> allestraten = new List<straatnamen>();
  536.  
  537.  
  538. //Voorbeeld input:
  539. //tablename: "Mijntable"
  540. //names = {"naam", "leeftijd"}
  541. //typenames = {"Varchar(30)", "integer"}
  542. //droptable = true
  543. //PrimaryKey = null
  544.  
  545. //Voorbeeld output:
  546. //DROP TABLE Mijntable;
  547. //CREATE TABLE if not exists Mijntable
  548. //(
  549. // id SERIAL PRIMARY KEY,
  550. // naam varchar(30),
  551. // leeftijd integer
  552. //);
  553. public void createTable(string tablename, List<string> names, List<string> typenames, bool dropTable, int PrimaryKey, string extra)
  554. {
  555. if (names.Count != typenames.Count)
  556. {
  557. //MessageBox.Show("Please make sure that the amount of names equals the amount of type names");
  558. throw new Exception("YA DUN! Names: " + names.Count + " typenames: " + typenames.Count);
  559. }
  560. if (PrimaryKey >= names.Count && PrimaryKey != 0)
  561. {
  562. //MessageBox.Show("Please make sure that the primary key is smaller than the amount of data types");
  563. throw new Exception("YA DUN!");
  564. }
  565.  
  566.  
  567. NpgsqlCommand COM = new NpgsqlCommand();
  568. COM.Connection = CON;
  569. CON.Open();
  570. if (dropTable)
  571. {
  572. COM.CommandText = "DROP TABLE if exists " + tablename + ";";
  573. Console.Write(COM.CommandText + "\n");
  574. COM.ExecuteNonQuery();
  575. }
  576. COM.CommandText = "CREATE TABLE if not exists " + tablename + "\n ( \n ";
  577. if (PrimaryKey == 0)
  578. {
  579. COM.CommandText = COM.CommandText + "id SERIAL PRIMARY KEY, \n ";
  580. }
  581. for (int i = 0; i < typenames.Count; i++)
  582. {
  583. COM.CommandText = COM.CommandText + names[i].Replace(" ", "").Replace("-", "_") + " " + typenames[i];
  584. if (i + 1 == PrimaryKey)
  585. {
  586. COM.CommandText = COM.CommandText + " PRIMARY KEY";
  587. }
  588. if ((i != typenames.Count && extra != "") || (i != typenames.Count - 1 && extra == ""))
  589. {
  590. COM.CommandText = COM.CommandText + ", ";
  591. }
  592. COM.CommandText = COM.CommandText + "\n ";
  593. }
  594. if (extra != "")
  595. {
  596. COM.CommandText = COM.CommandText + extra + " varchar (100)";
  597. }
  598. COM.CommandText = COM.CommandText + "\n );\n";
  599. Console.Write(COM.CommandText);
  600. COM.ExecuteNonQuery();
  601. CON.Close();
  602. }
  603.  
  604. //Voorbeeld input:
  605. //tablename = "Mijntable"
  606. //names = {"naam", "leeftijd"}
  607. //typenames = {"Varchar(30)", "integer"}
  608. //Values = {"Eddy", "42"},{"Fred", "28"}, {"Jos", "6"}, {"Loubna", "6"}, {"Rini", "6"}}
  609. //
  610. //Voorbeeld output:
  611. //INSERT INTO Mijntable (naam, leeftijd) VALUES ('Eddy', 42);
  612. //INSERT INTO Mijntable (naam, leeftijd) VALUES ('Fred', 28);
  613. //INSERT INTO Mijntable (naam, leeftijd) VALUES ('Jos', 6);
  614. //INSERT INTO Mijntable (naam, leeftijd) VALUES ('Loubna', 6);
  615. //INSERT INTO Mijntable (naam, leeftijd) VALUES ('Rini', 6);
  616.  
  617. public void insertIntoTable(string tablename, List<string> names, List<List<string>> Values, string extraName, string extraVal)
  618. {
  619. if (names.Count != Values[0].Count)
  620. {
  621. //MessageBox.Show("Please make sure that the amount of typenames, names and values are equal");
  622. throw new Exception("YA DUN! Names: " + names.Count + " values[0]: " + Values[0].Count);
  623. }
  624.  
  625. NpgsqlCommand COM = new NpgsqlCommand();
  626. COM.Connection = CON;
  627. CON.Open();
  628.  
  629. for (int i = 1; i < Values.Count; i++)
  630. {
  631. COM.CommandText = "INSERT INTO " + tablename + "(";
  632. for (int j = 0; j < Values[i].Count; j++)
  633. {
  634. COM.CommandText = COM.CommandText + names[j].Replace(" ", "");
  635. if ((j != Values[i].Count && extraName != "") || (j != Values[i].Count - 1 && extraName == ""))
  636. {
  637. COM.CommandText = COM.CommandText + ", ";
  638. }
  639. }
  640. if (extraName != "")
  641. {
  642. COM.CommandText = COM.CommandText + extraName;
  643. }
  644. COM.CommandText = COM.CommandText + ") VALUES (";
  645. for (int j = 0; j < Values[i].Count; j++)
  646. {
  647. COM.CommandText = COM.CommandText + "'" + Values[i][j] + "'";
  648. if ((j != Values[i].Count && extraName != "") || (j != Values[i].Count - 1 && extraName == ""))
  649. {
  650. COM.CommandText = COM.CommandText + ", ";
  651. }
  652. }
  653. if (extraName != "")
  654. {
  655. COM.CommandText = COM.CommandText + extraVal;
  656. }
  657. COM.CommandText = COM.CommandText + "); \n";
  658. Console.Write(COM.CommandText);
  659. try
  660. {
  661. COM.ExecuteNonQuery();
  662. }
  663. catch
  664. {
  665. COM.CommandText = "";
  666. }
  667. }
  668. CON.Close();
  669. }
  670.  
  671.  
  672. public void joinTables(List<List<string>> table1, List<List<string>> table2, string tablename1, string tablename2, string newtable, int joinRowTable1, int joinRowTable2, List<int> selectRowsTable1, List<int> selectRowsTable2)
  673. {
  674. NpgsqlCommand COM = new NpgsqlCommand();
  675. CON.Open();
  676. COM.CommandText = "SELECT ";
  677. foreach (int i in selectRowsTable1)
  678. {
  679. COM.CommandText = COM.CommandText + tablename1 + "." + table1[0][selectRowsTable1[i]] + ", ";
  680. }
  681. int j = 0;
  682. foreach (int i in selectRowsTable2)
  683. {
  684. COM.CommandText = COM.CommandText + tablename2 + "." + table2[0][selectRowsTable2[i]];
  685. if (j != selectRowsTable2.Count - 1)
  686. {
  687. COM.CommandText = COM.CommandText + ", ";
  688. }
  689. j++;
  690. }
  691. COM.CommandText = COM.CommandText + "\n";
  692. COM.CommandText = COM.CommandText + "INTO " + newtable + "\n";
  693. COM.CommandText = COM.CommandText + "FROM " + tablename1 + "\n";
  694. COM.CommandText = COM.CommandText + "INNER JOIN " + tablename2 + " ON " + tablename1 + "." + table1[0][joinRowTable1] + " = " + tablename2 + "." + table2[0][joinRowTable2] + ";";
  695. Console.WriteLine(COM.CommandText);
  696. Console.Read();
  697. COM.ExecuteNonQuery();
  698. CON.Close();
  699. }
  700.  
  701. public void readTable(string tablename, List<int> rows)
  702. {
  703. DataSet DS = new DataSet();
  704. DataTable DT = new DataTable();
  705.  
  706. CON.Open();
  707. NpgsqlDataAdapter DA = new NpgsqlDataAdapter(("SELECT * FROM " + tablename), CON);
  708. DA.Fill(DS);
  709. DT = DS.Tables[0];
  710. foreach (DataRow ROW in DT.Rows)
  711. {
  712. for (int i = 0; i < rows.Count; i++)
  713. {
  714. Console.Write(ROW[rows[i]].ToString() + " ");
  715. }
  716. Console.Write("\n");
  717. }
  718. CON.Close();
  719. }
  720.  
  721.  
  722. public int straatnaamtoint(string straatnaam, string postcode)
  723. {
  724. NpgsqlCommand COM = new NpgsqlCommand();
  725. COM.Connection = CON;
  726. GeneralParser GP = new GeneralParser();
  727. postcode = postcode.Replace(" ", "");
  728.  
  729. Console.WriteLine("Trying to convert " + postcode + " to wijk");
  730. string wijk = PostcodeNaarWijk(postcode);
  731. if (wijk != "COULDNOTCONVERT")
  732. {
  733. Console.ForegroundColor = ConsoleColor.Green;
  734. Console.WriteLine("Managed to convert " + postcode + " to wijk " + wijk);
  735. }
  736. else
  737. {
  738. Console.ForegroundColor = ConsoleColor.Red;
  739. Console.WriteLine("Did not manage to convert " + postcode);
  740. }
  741.  
  742. Console.ForegroundColor = ConsoleColor.White;
  743. if (allestraten.Count != 0)
  744. {
  745. for (int i = 0; i < allestraten.Count - 1; i++)
  746. {
  747. if (allestraten[i].straatnaam == straatnaam)
  748. {
  749. Console.ForegroundColor = ConsoleColor.Green;
  750. Console.WriteLine("MATCH!");
  751. Console.WriteLine("FOUND " + straatnaam + " ON ID " + allestraten[i].id.ToString());
  752. System.Threading.Thread.Sleep(10);
  753. return allestraten[i].id;
  754. }
  755. }
  756. }
  757.  
  758.  
  759. System.Threading.Thread.Sleep(30);
  760. Console.ForegroundColor = ConsoleColor.Red;
  761. Console.WriteLine("NO MATCH!");
  762. allestraten.Add(new straatnamen(straatnaam, wijk, allestraten.Count));
  763. Console.WriteLine("ADDING " + straatnaam + "TO DATABASE ON ID " + allestraten[allestraten.Count - 1].id.ToString());
  764. Console.ForegroundColor = ConsoleColor.DarkCyan;
  765. CON.Open();
  766. COM.Connection = CON;
  767. COM.CommandText = "INSERT INTO straatnaam(id, straatnaam, wijk) VALUES ( " + allestraten[allestraten.Count - 1].id.ToString() + ", '" + allestraten[allestraten.Count - 1].straatnaam.Replace("'", "") + "', '" + allestraten[allestraten.Count - 1].wijk + "');\n";
  768. try
  769. {
  770. COM.ExecuteNonQuery();
  771. Console.WriteLine(COM.CommandText);
  772. }
  773. catch (Exception e)
  774. {
  775. }
  776. CON.Close();
  777. return allestraten[allestraten.Count - 1].id;
  778. }
  779.  
  780. public int straatnaamtoint2(string straatnaam)
  781. {
  782. NpgsqlCommand COM = new NpgsqlCommand();
  783. COM.Connection = CON;
  784. GeneralParser GP = new GeneralParser();
  785. for (int i = 0; i < allestraten.Count; i++)
  786. {
  787. if (allestraten[i].straatnaam.ToUpper().Contains("ZUIDWIJ"))
  788. {
  789. Console.WriteLine(allestraten[i].straatnaam);
  790. }
  791. if (allestraten[i].straatnaam.ToUpper().Replace(" ", "") == straatnaam.ToUpper().Replace(" ", ""))
  792. {
  793. Console.ForegroundColor = ConsoleColor.Green;
  794. Console.WriteLine("MATCH!");
  795. Console.WriteLine("FOUND " + straatnaam + " ON ID " + allestraten[i].id.ToString());
  796. System.Threading.Thread.Sleep(10);
  797. return allestraten[i].id;
  798. }
  799. }
  800. return 0;
  801. }
  802.  
  803. public string PostcodeNaarWijk(string postcode)
  804. {
  805. for (int i = 0; i < Postcodesenwijken.Count; i++)
  806. {
  807. if (Postcodesenwijken[i][0].Contains(postcode))
  808. {
  809. return Postcodesenwijken[i][1].ToString();
  810. }
  811. }
  812.  
  813. return "COULDNOTFINDWIJK";
  814. }
  815.  
  816. public string findWijk(int straatint)
  817. {
  818. for (int i = 0; i < allestraten.Count; i++)
  819. {
  820. if (allestraten[i].id == straatint)
  821. {
  822. return allestraten[i].wijk;
  823. }
  824. }
  825. return "";
  826. }
  827. }
  828.  
  829. public class straatnamen
  830. {
  831. public string straatnaam;
  832. public string wijk;
  833. public int id;
  834. public straatnamen(string straatnaam, string wijk, int id)
  835. {
  836. this.straatnaam = straatnaam;
  837. this.wijk = wijk;
  838. this.id = id;
  839. }
  840. }
  841. }
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!