· 8 years ago · Jan 25, 2018, 06:18 PM
1using System;
2using System.Collections.Generic;
3using System.Linq;
4using System.Text;
5using System.Threading.Tasks;
6using MySql.Data.MySqlClient;
7
8
9namespace mysql_connect.Models
10{
11 class DBConnect
12 {
13 private MySqlConnection connection;
14 private string server;
15 private string database;
16 private string uid;
17 private string password;
18
19 //Constructor
20 public DBConnect()
21 {
22 Initialize();
23 }
24
25 //Initialize values
26 //private void Initialize()
27 public void Initialize()
28 {
29 server = "localhost";
30 database = "lab8"; // nazwa bazy danych
31 uid = "root";//login usera
32 password = "admin1";// hasło usera
33 string connectionString;
34 connectionString = "SERVER=" + server + ";" +
35 "DATABASE=" + database + ";" +
36 "UID=" + uid + ";" +
37 "PASSWORD=" + password + ";";
38
39 connection = new MySqlConnection(connectionString);
40 }
41
42 //Open connection to database
43 private bool OpenConnection()
44 {
45 try
46 {
47 connection.Open();
48 return true;
49 }
50 catch (MySqlException ex)
51 {
52 //When handling errors, you can your application's response based
53 //on the error number.
54 //The two most common error numbers when connecting are as follows:
55 //0: Cannot connect to server.
56 //1045: Invalid user name and/or password.
57 switch (ex.Number)
58 {
59 case 0:
60 //MessageBox.Show("Cannot connect to server. Contact administrator");
61 Console.WriteLine("Cannot connect to server. Contact administrator");
62 break;
63
64 case 1045:
65 //MessageBox.Show("Invalid username/password, please try again");
66 Console.WriteLine("Invalid username/password, please try again");
67 break;
68 }
69 return false;
70 }
71 }
72
73 //Close connection
74 private bool CloseConnection()
75 {
76 try
77 {
78 connection.Close();
79 return true;
80 }
81 catch (MySqlException ex)
82 {
83 //MessageBox.Show(ex.Message);
84 Console.WriteLine(ex.Message);
85 return false;
86 }
87 }
88
89
90
91 //Insert statement
92 public void Insert(string query)
93 {
94 //string query = "INSERT INTO tableinfo (name, age) VALUES('John Smith', '33')";
95
96 //open connection
97 if (this.OpenConnection() == true)
98 {
99 //create command and assign the query and connection from the constructor
100 MySqlCommand cmd = new MySqlCommand(query, connection);
101
102 //Execute command
103 cmd.ExecuteNonQuery();
104
105 //close connection
106 this.CloseConnection();
107 }
108 }
109
110 //Update statement
111 public void Update(string query)
112 {
113 // string query = "UPDATE tableinfo SET name='Joe', age='22' WHERE name='John Smith'";
114
115 //Open connection
116 if (this.OpenConnection() == true)
117 {
118 //create mysql command
119 MySqlCommand cmd = new MySqlCommand();
120 //Assign the query using CommandText
121 cmd.CommandText = query;
122 //Assign the connection using Connection
123 cmd.Connection = connection;
124
125 //Execute query
126 cmd.ExecuteNonQuery();
127
128 //close connection
129 this.CloseConnection();
130 }
131 }
132
133 //Delete statement
134 public void Delete(string query)
135 {
136 // string query = "DELETE FROM tableinfo WHERE name='John Smith'";
137
138 if (this.OpenConnection() == true)
139 {
140 MySqlCommand cmd = new MySqlCommand(query, connection);
141 cmd.ExecuteNonQuery();
142 this.CloseConnection();
143 }
144 }
145
146 //Select statement
147 public List<string>[] Select()
148 {
149
150 string query = "SELECT * FROM logs";
151
152 //Create a list to store the result
153 List<string>[] list = new List<string>[3];
154 list[0] = new List<string>();
155 list[1] = new List<string>();
156 //list[2] = new List<string>();
157
158 //Open connection
159 if (this.OpenConnection() == true)
160 {
161 //Create Command
162 MySqlCommand cmd = new MySqlCommand(query, connection);
163 //Create a data reader and Execute the command
164 MySqlDataReader dataReader = cmd.ExecuteReader();
165
166 //Read the data and store them in the list
167 while (dataReader.Read())
168 {
169 list[0].Add(dataReader["temp"] + "");
170 list[1].Add(dataReader["czas"] + "");
171 //list[2].Add(dataReader["age"] + "");
172 }
173
174 //close Data Reader
175 dataReader.Close();
176
177 //close Connection
178 this.CloseConnection();
179
180 //return list to be displayed
181 return list;
182 }
183 else
184 {
185 return list;
186 }
187
188 }
189
190 ///Count statement
191 public int Count(string query)
192 {
193 //string query = "SELECT Count(*) FROM tableinfo";
194 //int Count = -1;
195 int Count = 0;
196
197 //Open Connection
198 if (this.OpenConnection() == true)
199 {
200 //Create Mysql Command
201 MySqlCommand cmd = new MySqlCommand(query, connection);
202
203 //ExecuteScalar will return one value
204 Count = int.Parse(cmd.ExecuteScalar() + "");
205
206 //close Connection
207 this.CloseConnection();
208
209 return Count;
210 }
211 else
212 {
213 return Count;
214 }
215 }
216
217 //Backup
218 public void Backup()
219 {
220 }
221
222 //Restore
223 public void Restore()
224 {
225 }
226 }
227}
228
229using System;
230using System.Collections.Generic;
231using System.Linq;
232using System.Text;
233using System.Threading.Tasks;
234using mysql_connect.Models;
235using System.Diagnostics;
236
237namespace mysql_connect
238{
239 class Program
240 {
241 static void Main(string[] args)
242 {
243 DBConnect a = new DBConnect();
244 Console.WriteLine(a.Count("select connection_id();"));
245 Console.WriteLine("ile rekordów ?");
246
247 int recordNum = int.Parse(Console.ReadLine());
248 //Stopwatch timer5 = new Stopwatch();
249 Stopwatch timer1 = new Stopwatch();
250 //Stopwatch timer2 = new Stopwatch();
251 //Stopwatch timer3 = new Stopwatch();
252 //Stopwatch timer4 = new Stopwatch();
253 //Stopwatch timer6 = new Stopwatch();
254 Random rng = new Random();
255
256 {
257 /*
258 //for (int k = 0; k < 5; k++)
259 //{
260 // switch (k)
261 // {
262 // case 0:
263 // for (int j = 1; j <= 6; j++)
264 // {
265 // try
266 // {
267
268
269 // Random rng = new Random();
270 // //a.Insert("Create table logs(temp double, czas datetime(6) not null primary key) engine = myisam; ");
271 // //a.Insert("");
272
273 // switch (j)
274 // {
275 // case 1:
276
277 // a.Insert("drop table if exists logs1");
278 // a.Insert("Create table logs1(temp double,czas datetime(6) not null primary key) engine = myisam; ");
279 // timer1.Start();
280 // for (int i = 0; i < recordNum; i++)
281 // {
282 // a.Insert("insert into lab4.logs" + j + "(temp, czas) Values ("
283 // + rng.Next(1000000, 10000000) + ", now(6)); ");
284 // }
285 // timer1.Stop();
286 // TimeSpan ts1 = timer1.Elapsed;
287 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts1);
288 // timer1.Reset();
289 // break;
290
291 // case 2:
292 // a.Insert("drop table if exists logs2; Create table logs2(temp double,czas int not null primary key auto_increment) engine = myisam; ");
293 // timer2.Start();
294 // for (int i = 0; i < recordNum; i++)
295 // {
296 // a.Insert("insert into lab4.logs" + j + "(temp) Values ("
297 // + rng.Next(1000000, 10000000) + ");");
298 // }
299 // timer2.Stop();
300 // TimeSpan ts2 = timer2.Elapsed;
301 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts2);
302 // timer2.Reset();
303 // break;
304
305 // case 3:
306 // a.Insert("drop table if exists logs3; Create table logs3(temp double,czas int not null auto_increment unique) engine = myisam; ");
307 // timer3.Start();
308 // for (int i = 0; i < recordNum; i++)
309 // {
310 // a.Insert("insert into lab4.logs" + j + "(temp) Values ("
311 // + rng.Next(1000000, 10000000) + ");");
312 // }
313 // timer3.Stop();
314 // TimeSpan ts3 = timer3.Elapsed;
315 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts3);
316 // timer3.Reset();
317 // break;
318
319 // case 4:
320 // a.Insert("drop table if exists logs4; Create table logs4(temp double,czas datetime(6) not null primary key) engine = innodb;");
321 // timer4.Start();
322 // for (int i = 0; i < recordNum; i++)
323 // {
324 // a.Insert("insert into lab4.logs" + j + "(temp, czas) Values ("
325 // + rng.Next(1000000, 10000000) + ", now(6)+0); ");
326 // }
327 // timer4.Stop();
328 // TimeSpan ts4 = timer4.Elapsed;
329 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts4);
330 // timer4.Reset();
331 // break;
332 // case 5:
333 // a.Insert("drop table if exists logs5; Create table logs5(temp double,czas int not null primary key auto_increment) engine = innodb; ");
334 // timer5.Start();
335 // for (int i = 0; i < recordNum; i++)
336 // {
337 // a.Insert("insert into lab4.logs" + j + "(temp) Values ("
338 // + rng.Next(1000000, 10000000) + ");");
339 // }
340 // timer5.Stop();
341 // TimeSpan ts5 = timer5.Elapsed;
342 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts5);
343 // timer5.Reset();
344 // break;
345
346 // case 6:
347 // a.Insert("drop table if exists logs6; Create table logs6(temp double,czas int not null auto_increment unique) engine = innodb; ");
348 // timer6.Start();
349 // for (int i = 0; i < recordNum; i++)
350 // {
351 // a.Insert("insert into lab4.logs" + j + "(temp) Values ("
352 // + rng.Next(1000000, 10000000) + ");");
353 // }
354 // timer6.Stop();
355 // TimeSpan ts6 = timer6.Elapsed;
356 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts6);
357 // timer6.Reset();
358 // break;
359
360
361
362 // }
363
364
365
366
367
368 // }
369
370 // catch (Exception e)
371 // {
372
373 // Console.WriteLine("There was a problem in logs" + j + ":");
374 // Console.WriteLine(e.Message);
375
376 // Console.ReadKey();
377 // }
378 // }
379 // break;
380
381 // case 1:
382 // a.Insert("Set Global Transaction isolation level repeatable read;");
383 // Console.Write("Repetable read\n\n\n");
384 // for (int j = 1; j <= 6; j++)
385 // {
386 // try
387 // {
388
389
390 // Random rng = new Random();
391 // //a.Insert("Create table logs(temp double, czas datetime(6) not null primary key) engine = myisam; ");
392 // //a.Insert("");
393
394 // switch (j)
395 // {
396 // case 1:
397
398 // a.Insert("drop table if exists logs1");
399 // a.Insert("Create table logs1(temp double,czas datetime(6) not null primary key) engine = myisam; ");
400 // timer1.Start();
401 // for (int i = 0; i < recordNum; i++)
402 // {
403 // a.Insert("insert into lab4.logs" + j + "(temp, czas) Values ("
404 // + rng.Next(1000000, 10000000) + ", now(6)); ");
405 // }
406 // timer1.Stop();
407 // TimeSpan ts1 = timer1.Elapsed;
408 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts1);
409
410 // timer1.Reset();
411 // break;
412
413 // case 2:
414 // a.Insert("drop table if exists logs2; Create table logs2(temp double,czas int not null primary key auto_increment) engine = myisam; ");
415 // timer2.Start();
416 // for (int i = 0; i < recordNum; i++)
417 // {
418 // a.Insert("insert into lab4.logs" + j + "(temp) Values ("
419 // + rng.Next(1000000, 10000000) + ");");
420 // }
421 // timer2.Stop();
422 // TimeSpan ts2 = timer2.Elapsed;
423 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts2);
424
425 // timer2.Reset(); break;
426
427 // case 3:
428 // a.Insert("drop table if exists logs3; Create table logs3(temp double,czas int not null auto_increment unique) engine = myisam; ");
429 // timer3.Start();
430 // for (int i = 0; i < recordNum; i++)
431 // {
432 // a.Insert("insert into lab4.logs" + j + "(temp) Values ("
433 // + rng.Next(1000000, 10000000) + ");");
434 // }
435 // timer3.Stop();
436 // TimeSpan ts3 = timer3.Elapsed;
437 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts3);
438 // timer3.Reset(); break;
439
440 // case 4:
441 // a.Insert("drop table if exists logs4; Create table logs4(temp double,czas datetime(6) not null primary key) engine = innodb;");
442 // timer4.Start();
443 // for (int i = 0; i < recordNum; i++)
444 // {
445 // a.Insert("insert into lab4.logs" + j + "(temp, czas) Values ("
446 // + rng.Next(1000000, 10000000) + ", now(6)+0); ");
447 // }
448 // timer4.Stop();
449 // TimeSpan ts4 = timer4.Elapsed;
450 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts4);
451 // timer4.Reset(); break;
452 // case 5:
453 // a.Insert("drop table if exists logs5; Create table logs5(temp double,czas int not null primary key auto_increment) engine = innodb; ");
454 // timer5.Start();
455 // for (int i = 0; i < recordNum; i++)
456 // {
457 // a.Insert("insert into lab4.logs" + j + "(temp) Values ("
458 // + rng.Next(1000000, 10000000) + ");");
459 // }
460 // timer5.Stop();
461 // TimeSpan ts5 = timer5.Elapsed;
462 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts5);
463 // timer5.Reset(); break;
464
465 // case 6:
466 // a.Insert("drop table if exists logs6; Create table logs6(temp double,czas int not null auto_increment unique) engine = innodb; ");
467 // timer6.Start();
468 // for (int i = 0; i < recordNum; i++)
469 // {
470 // a.Insert("insert into lab4.logs" + j + "(temp) Values ("
471 // + rng.Next(1000000, 10000000) + ");");
472 // }
473 // timer6.Stop();
474 // TimeSpan ts6 = timer6.Elapsed;
475 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts6);
476 // timer6.Reset(); break;
477
478
479
480 // }
481
482
483
484
485
486 // }
487
488 // catch (Exception e)
489 // {
490
491 // Console.WriteLine("There was a problem in logs" + j + ":");
492 // Console.WriteLine(e.Message);
493
494 // Console.ReadKey();
495 // }
496 // }
497 // break;
498
499 // case 2:
500 // a.Insert("Set Global Transaction isolation level READ committed;");
501 // Console.Write("Read committed\n\n\n");
502 // for (int j = 1; j <= 6; j++)
503 // {
504 // try
505 // {
506
507
508 // Random rng = new Random();
509 // //a.Insert("Create table logs(temp double, czas datetime(6) not null primary key) engine = myisam; ");
510 // //a.Insert("");
511
512 // switch (j)
513 // {
514 // case 1:
515
516 // a.Insert("drop table if exists logs1");
517 // a.Insert("Create table logs1(temp double,czas datetime(6) not null primary key) engine = myisam; ");
518 // timer1.Start();
519 // for (int i = 0; i < recordNum; i++)
520 // {
521 // a.Insert("insert into lab4.logs" + j + "(temp, czas) Values ("
522 // + rng.Next(1000000, 10000000) + ", now(6)); ");
523 // }
524 // timer1.Stop();
525 // TimeSpan ts1 = timer1.Elapsed;
526 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts1);
527
528 // timer1.Reset(); break;
529
530 // case 2:
531 // a.Insert("drop table if exists logs2; Create table logs2(temp double,czas int not null primary key auto_increment) engine = myisam; ");
532 // timer2.Start();
533 // for (int i = 0; i < recordNum; i++)
534 // {
535 // a.Insert("insert into lab4.logs" + j + "(temp) Values ("
536 // + rng.Next(1000000, 10000000) + ");");
537 // }
538 // timer2.Stop();
539 // TimeSpan ts2 = timer2.Elapsed;
540 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts2);
541 // timer2.Reset(); break;
542
543 // case 3:
544 // a.Insert("drop table if exists logs3; Create table logs3(temp double,czas int not null auto_increment unique) engine = myisam; ");
545 // timer3.Start();
546 // for (int i = 0; i < recordNum; i++)
547 // {
548 // a.Insert("insert into lab4.logs" + j + "(temp) Values ("
549 // + rng.Next(1000000, 10000000) + ");");
550 // }
551 // timer3.Stop();
552 // TimeSpan ts3 = timer3.Elapsed;
553 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts3);
554 // timer3.Reset(); break;
555
556 // case 4:
557 // a.Insert("drop table if exists logs4; Create table logs4(temp double,czas datetime(6) not null primary key) engine = innodb;");
558 // timer4.Start();
559 // for (int i = 0; i < recordNum; i++)
560 // {
561 // a.Insert("insert into lab4.logs" + j + "(temp, czas) Values ("
562 // + rng.Next(1000000, 10000000) + ", now(6)+0); ");
563 // }
564 // timer4.Stop();
565 // TimeSpan ts4 = timer4.Elapsed;
566 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts4);
567 // timer4.Reset(); break;
568 // case 5:
569 // a.Insert("drop table if exists logs5; Create table logs5(temp double,czas int not null primary key auto_increment) engine = innodb; ");
570 // timer5.Start();
571 // for (int i = 0; i < recordNum; i++)
572 // {
573 // a.Insert("insert into lab4.logs" + j + "(temp) Values ("
574 // + rng.Next(1000000, 10000000) + ");");
575 // }
576 // timer5.Stop();
577 // TimeSpan ts5 = timer5.Elapsed;
578 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts5);
579 // timer5.Reset(); break;
580
581 // case 6:
582 // a.Insert("drop table if exists logs6; Create table logs6(temp double,czas int not null auto_increment unique) engine = innodb; ");
583 // timer6.Start();
584 // for (int i = 0; i < recordNum; i++)
585 // {
586 // a.Insert("insert into lab4.logs" + j + "(temp) Values ("
587 // + rng.Next(1000000, 10000000) + ");");
588 // }
589 // timer6.Stop();
590 // TimeSpan ts6 = timer6.Elapsed;
591 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts6);
592 // timer6.Reset(); break;
593
594
595
596 // }
597
598
599
600
601
602 // }
603
604 // catch (Exception e)
605 // {
606
607 // Console.WriteLine("There was a problem in logs" + j + ":");
608 // Console.WriteLine(e.Message);
609
610 // Console.ReadKey();
611 // }
612 // }
613 // break;
614
615 // case 3:
616 // a.Insert("Set Global Transaction isolation level READ uncommitted;");
617 // Console.Write("Read uncommitted\n\n\n");
618 // for (int j = 1; j <= 6; j++)
619 // {
620 // try
621 // {
622
623
624 // Random rng = new Random();
625 // //a.Insert("Create table logs(temp double, czas datetime(6) not null primary key) engine = myisam; ");
626 // //a.Insert("");
627
628 // switch (j)
629 // {
630 // case 1:
631
632 // a.Insert("drop table if exists logs1");
633 // a.Insert("Create table logs1(temp double,czas datetime(6) not null primary key) engine = myisam; ");
634 // timer1.Start();
635 // for (int i = 0; i < recordNum; i++)
636 // {
637 // a.Insert("insert into lab4.logs" + j + "(temp, czas) Values ("
638 // + rng.Next(1000000, 10000000) + ", now(6)); ");
639 // }
640 // timer1.Stop();
641 // TimeSpan ts1 = timer1.Elapsed;
642 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts1);
643
644 // timer1.Reset(); break;
645
646 // case 2:
647 // a.Insert("drop table if exists logs2; Create table logs2(temp double,czas int not null primary key auto_increment) engine = myisam; ");
648 // timer2.Start();
649 // for (int i = 0; i < recordNum; i++)
650 // {
651 // a.Insert("insert into lab4.logs" + j + "(temp) Values ("
652 // + rng.Next(1000000, 10000000) + ");");
653 // }
654 // timer2.Stop();
655 // TimeSpan ts2 = timer2.Elapsed;
656 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts2);
657 // timer2.Reset(); break;
658
659 // case 3:
660 // a.Insert("drop table if exists logs3; Create table logs3(temp double,czas int not null auto_increment unique) engine = myisam; ");
661 // timer3.Start();
662 // for (int i = 0; i < recordNum; i++)
663 // {
664 // a.Insert("insert into lab4.logs" + j + "(temp) Values ("
665 // + rng.Next(1000000, 10000000) + ");");
666 // }
667 // timer3.Stop();
668 // TimeSpan ts3 = timer3.Elapsed;
669 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts3);
670 // timer3.Reset(); break;
671
672 // case 4:
673 // a.Insert("drop table if exists logs4; Create table logs4(temp double,czas datetime(6) not null primary key) engine = innodb;");
674 // timer4.Start();
675 // for (int i = 0; i < recordNum; i++)
676 // {
677 // a.Insert("insert into lab4.logs" + j + "(temp, czas) Values ("
678 // + rng.Next(1000000, 10000000) + ", now(6)+0); ");
679 // }
680 // timer4.Stop();
681 // TimeSpan ts4 = timer4.Elapsed;
682 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts4);
683 // timer4.Reset(); break;
684 // case 5:
685 // a.Insert("drop table if exists logs5; Create table logs5(temp double,czas int not null primary key auto_increment) engine = innodb; ");
686 // timer5.Start();
687 // for (int i = 0; i < recordNum; i++)
688 // {
689 // a.Insert("insert into lab4.logs" + j + "(temp) Values ("
690 // + rng.Next(1000000, 10000000) + ");");
691 // }
692 // timer5.Stop();
693 // TimeSpan ts5 = timer5.Elapsed;
694 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts5);
695 // timer5.Reset(); break;
696
697 // case 6:
698 // a.Insert("drop table if exists logs6; Create table logs6(temp double,czas int not null auto_increment unique) engine = innodb; ");
699 // timer6.Start();
700 // for (int i = 0; i < recordNum; i++)
701 // {
702 // a.Insert("insert into lab4.logs" + j + "(temp) Values ("
703 // + rng.Next(1000000, 10000000) + ");");
704 // }
705 // timer6.Stop();
706 // TimeSpan ts6 = timer6.Elapsed;
707 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts6);
708 // timer6.Reset(); break;
709
710
711
712 // }
713
714
715
716
717
718 // }
719
720 // catch (Exception e)
721 // {
722
723 // Console.WriteLine("There was a problem in logs" + j + ":");
724 // Console.WriteLine(e.Message);
725
726 // Console.ReadKey();
727 // }
728 // }
729 // break;
730
731 // case 4:
732 // Console.Write("SERIALIZABLE\n\n\n");
733 // a.Insert("Set Global Transaction isolation level SERIALIZABLE;");
734 // for (int j = 1; j <= 6; j++)
735 // {
736 // try
737 // {
738
739
740 // Random rng = new Random();
741 // //a.Insert("Create table logs(temp double, czas datetime(6) not null primary key) engine = myisam; ");
742 // //a.Insert("");
743
744 // switch (j)
745 // {
746 // case 1:
747
748 // a.Insert("drop table if exists logs1");
749 // a.Insert("Create table logs1(temp double,czas datetime(6) not null primary key) engine = myisam; ");
750 // timer1.Start();
751 // for (int i = 0; i < recordNum; i++)
752 // {
753 // a.Insert("insert into lab4.logs" + j + "(temp, czas) Values ("
754 // + rng.Next(1000000, 10000000) + ", now(6)); ");
755 // }
756 // timer1.Stop();
757 // TimeSpan ts1 = timer1.Elapsed;
758 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts1);
759
760 // timer1.Reset(); break;
761
762 // case 2:
763 // a.Insert("drop table if exists logs2; Create table logs2(temp double,czas int not null primary key auto_increment) engine = myisam; ");
764 // timer2.Start();
765 // for (int i = 0; i < recordNum; i++)
766 // {
767 // a.Insert("insert into lab4.logs" + j + "(temp) Values ("
768 // + rng.Next(1000000, 10000000) + ");");
769 // }
770 // timer2.Stop();
771 // TimeSpan ts2 = timer2.Elapsed;
772 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts2);
773 // timer2.Reset(); break;
774
775 // case 3:
776 // a.Insert("drop table if exists logs3; Create table logs3(temp double,czas int not null auto_increment unique) engine = myisam; ");
777 // timer3.Start();
778 // for (int i = 0; i < recordNum; i++)
779 // {
780 // a.Insert("insert into lab4.logs" + j + "(temp) Values ("
781 // + rng.Next(1000000, 10000000) + ");");
782 // }
783 // timer3.Stop();
784 // TimeSpan ts3 = timer3.Elapsed;
785 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts3);
786 // timer3.Reset(); break;
787
788 // case 4:
789 // a.Insert("drop table if exists logs4; Create table logs4(temp double,czas datetime(6) not null primary key) engine = innodb;");
790 // timer4.Start();
791 // for (int i = 0; i < recordNum; i++)
792 // {
793 // a.Insert("insert into lab4.logs" + j + "(temp, czas) Values ("
794 // + rng.Next(1000000, 10000000) + ", now(6)+0); ");
795 // }
796 // timer4.Stop();
797 // TimeSpan ts4 = timer4.Elapsed;
798 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts4);
799 // timer4.Reset(); break;
800 // case 5:
801 // a.Insert("drop table if exists logs5; Create table logs5(temp double,czas int not null primary key auto_increment) engine = innodb; ");
802 // timer5.Start();
803 // for (int i = 0; i < recordNum; i++)
804 // {
805 // a.Insert("insert into lab4.logs" + j + "(temp) Values ("
806 // + rng.Next(1000000, 10000000) + ");");
807 // }
808 // timer5.Stop();
809 // TimeSpan ts5 = timer5.Elapsed;
810 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts5);
811 // timer5.Reset(); break;
812
813 // case 6:
814 // a.Insert("drop table if exists logs6; Create table logs6(temp double,czas int not null auto_increment unique) engine = innodb; ");
815 // timer6.Start();
816 // for (int i = 0; i < recordNum; i++)
817 // {
818 // a.Insert("insert into lab4.logs" + j + "(temp) Values ("
819 // + rng.Next(1000000, 10000000) + ");");
820 // }
821 // timer6.Stop();
822 // TimeSpan ts6 = timer6.Elapsed;
823 // Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts6);
824 // timer6.Reset(); break;
825
826
827
828 // }
829
830
831
832
833
834 // }
835
836 // catch (Exception e)
837 // {
838
839 // Console.WriteLine("There was a problem in logs" + j + ":");
840 // Console.WriteLine(e.Message);
841
842 // Console.ReadKey();
843 // }
844 // }
845 // break;
846
847 // }
848 //}
849 */
850 }
851
852 a.Insert("Set Global Transaction isolation level repeatable read;");
853 Console.WriteLine("\n\nRepeatable Read");
854 Run(a,timer1,rng,recordNum);
855
856
857 a.Insert("Set Global Transaction isolation level READ committed;");
858 Console.WriteLine("\n\nRead Commited");
859 Run(a, timer1, rng, recordNum);
860
861 a.Insert("Set Global Transaction isolation level READ uncommitted;");
862 Console.WriteLine("\n\nRead Uncommited");
863 Run(a, timer1, rng, recordNum);
864
865 a.Insert("Set Global Transaction isolation level SERIALIZABLE;");
866 Console.WriteLine("\n\nSerializable");
867 Run(a, timer1, rng, recordNum);
868 }
869
870
871 static void Run(DBConnect a, Stopwatch t,Random rng, int recordNum)
872 {
873 for (int j =1;j<=6;j++) {
874 a.Insert("drop table if exists logs" + j);
875 if (j == 1) a.Insert("Create table logs1(temp double,czas datetime(6) not null ) engine = myisam; ");
876 if (j == 2) a.Insert("Create table logs2(temp double,czas int not null primary key auto_increment) engine = myisam; ");
877 if (j == 3) a.Insert("Create table logs3(temp double,czas int not null auto_increment unique) engine = myisam; ");
878 if (j == 4) a.Insert("Create table logs4(temp double,czas datetime(6) not null ) engine = innodb;");
879 if (j == 5) a.Insert("Create table logs5(temp double,czas int not null primary key auto_increment) engine = innodb; ");
880 if (j == 6) a.Insert("drop table if exists logs6; Create table logs6(temp double,czas int not null auto_increment unique) engine = innodb; ");
881
882
883
884 t.Start();
885 for (int i = 0; i < recordNum; i++)
886 {
887 if (j == 1) a.Insert("insert into logs" + j + "(temp, czas) Values (" + rng.Next(1000000, 10000000) + ", now(6)); ");
888 if (j == 2) a.Insert("insert into logs" + j + "(temp) Values (" + rng.Next(1000000, 10000000) + ");");
889 if (j == 3) a.Insert("insert into logs" + j + "(temp) Values (" + rng.Next(1000000, 10000000) + ");");
890 if (j == 4) a.Insert("insert into logs" + j + "(temp, czas) Values (" + rng.Next(1000000, 10000000) + ", now(6)+0); ");
891 if (j == 5) a.Insert("insert into logs" + j + "(temp) Values (" + rng.Next(1000000, 10000000) + ");");
892 if (j == 6) a.Insert("insert into logs" + j + "(temp) Values (" + rng.Next(1000000, 10000000) + ");");
893 }
894 t.Stop();
895 TimeSpan ts1 = t.Elapsed;
896 Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts1);
897
898 t.Reset();
899 }
900 }
901
902 }
903}