· 8 years ago · Jan 25, 2018, 06:20 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
229namespace mysql_connect
230{
231 class Program
232 {
233 static void Main(string[] args)
234 {
235 DBConnect a = new DBConnect();
236 Console.WriteLine(a.Count("select connection_id();"));
237 Console.WriteLine("ile rekordów ?");
238
239 int recordNum = int.Parse(Console.ReadLine());
240 //Stopwatch timer5 = new Stopwatch();
241 Stopwatch timer1 = new Stopwatch();
242 //Stopwatch timer2 = new Stopwatch();
243 //Stopwatch timer3 = new Stopwatch();
244 //Stopwatch timer4 = new Stopwatch();
245 //Stopwatch timer6 = new Stopwatch();
246 Random rng = new Random();
247 a.Insert("Set Global Transaction isolation level repeatable read;");
248 Console.WriteLine("\n\nRepeatable Read");
249 Run(a,timer1,rng,recordNum);
250
251
252 a.Insert("Set Global Transaction isolation level READ committed;");
253 Console.WriteLine("\n\nRead Commited");
254 Run(a, timer1, rng, recordNum);
255
256 a.Insert("Set Global Transaction isolation level READ uncommitted;");
257 Console.WriteLine("\n\nRead Uncommited");
258 Run(a, timer1, rng, recordNum);
259
260 a.Insert("Set Global Transaction isolation level SERIALIZABLE;");
261 Console.WriteLine("\n\nSerializable");
262 Run(a, timer1, rng, recordNum);
263 }
264
265
266 static void Run(DBConnect a, Stopwatch t,Random rng, int recordNum)
267 {
268 for (int j =1;j<=6;j++) {
269 a.Insert("drop table if exists logs" + j);
270 if (j == 1) a.Insert("Create table logs1(temp double,czas datetime(6) not null ) engine = myisam; ");
271 if (j == 2) a.Insert("Create table logs2(temp double,czas int not null primary key auto_increment) engine = myisam; ");
272 if (j == 3) a.Insert("Create table logs3(temp double,czas int not null auto_increment unique) engine = myisam; ");
273 if (j == 4) a.Insert("Create table logs4(temp double,czas datetime(6) not null ) engine = innodb;");
274 if (j == 5) a.Insert("Create table logs5(temp double,czas int not null primary key auto_increment) engine = innodb; ");
275 if (j == 6) a.Insert("drop table if exists logs6; Create table logs6(temp double,czas int not null auto_increment unique) engine = innodb; ");
276
277
278
279 t.Start();
280 for (int i = 0; i < recordNum; i++)
281 {
282 if (j == 1) a.Insert("insert into logs" + j + "(temp, czas) Values (" + rng.Next(1000000, 10000000) + ", now(6)); ");
283 if (j == 2) a.Insert("insert into logs" + j + "(temp) Values (" + rng.Next(1000000, 10000000) + ");");
284 if (j == 3) a.Insert("insert into logs" + j + "(temp) Values (" + rng.Next(1000000, 10000000) + ");");
285 if (j == 4) a.Insert("insert into logs" + j + "(temp, czas) Values (" + rng.Next(1000000, 10000000) + ", now(6)+0); ");
286 if (j == 5) a.Insert("insert into logs" + j + "(temp) Values (" + rng.Next(1000000, 10000000) + ");");
287 if (j == 6) a.Insert("insert into logs" + j + "(temp) Values (" + rng.Next(1000000, 10000000) + ");");
288 }
289 t.Stop();
290 TimeSpan ts1 = t.Elapsed;
291 Console.WriteLine("logs{1} query passed {0} rows time{2}", a.Count("select count(*) from logs" + j), j, ts1);
292
293 t.Reset();
294 }
295 }
296
297 }
298}