How to use MySql With C#

1. How to use MySql With C# Ross Sherman

2. Using MySql MySql is free. You could download it from: http://dev.mysql.com/downloads/ When the setup wizard completes check Launch the MySQL Instance Configuration Wizard and click Finish Choose Development Machine and leave the defaults as they are Choose a password Add at least one user account Choose defaults for the rest of the screens Once installed, start the MySQL Workbench; click Open Connection to Start Querying

3. Creating a new database in MySql; creating new tables in the database create database videostore; use videostore; create table moviesrented (personidint(4), movieidint(4) ); create table customer (id int(4), lastnamevarchar(12), firstnamevarchar(12), email varchar(12), pass varchar(12) ); create table movies (id int(4), movienamevarchar(20), numberavailableint (3)); From Professor Gueorguieva’s database lecture:

4. Inserting values into your database’s tables INSERT INTO customer VALUES ('1212','Stan','Gundy','stan@hotmail.com','coach'), ('1132','Phil','Jackson','phil@hotmail.com','eleven'); INSERT INTO movies VALUES ('1333','Mission_Impossible','3'), ('1334','Jack_Reacher','4'), ('1335','Admission', '2'); INSERT INTO moviesrented VALUES ('1212','1334'), ('1212','1335'), ('1132','1334');

5. Querying your database Select * from customer; select max(id) from customer; select id from movies where id = 1212; select * from customer where firstname like ‘P%’; select customer.id as IDNameCustID, customer.firstname, moviesrented.persionid, movies.moviename from moviesrented, customer, movies where moviesrented.persionid = customer.id and moviesrented.movieid = movies.id;

6. Deleting rows, dropping tables, and adding columns To delete a row in a table: delete from moviesrented where movieid = 1212; To delete all rows from a table: deletefrom moviesrented; To drop a table from your database: drop table moviesrented; To add a new column to an existing table: alter tablecustomeradd birthdate date;

7. If you run into a problem deleting rows: 1) You might run into a problem when you try to delete rows. To fix this problem go to the Edit tab in the MySql Workbench and click on preferences. 2) Once you have clicked on preferences, click on SQL Queries and make sure that “Safe Updates” is unchecked. If it is checked, uncheck it, press ok, and restart MySql for the change to take affect.

8. Connecting Visual Studio to MySql: First, Create a new Windows Forms Project.

9. Next, you will need to add MySql.data as a reference. Adding MySQL.data as a reference: In the Solution Explorer, click on the name of your presentation, right click on references, click on add reference.

10. After clicking on add reference you will be taken to the reference manager form. In Reference Manager form, select Extensions, find MySql.Dataand press the check box next to MySq.Data, them click on ok.

11. At this point, you have created a database and you have connected that database to MySql. Now, you need to query and manipulate the data from your database in your C# code. Go to the design section of your newly created windows form and add a button to it. Double click the button to go to the code.

12. At the top, include the line: using MySql.Data.MySqlClient;

13. In the button’s code include the following lines: private void button1_Click(object sender, EventArgs e) { try { string connectionInfo = "datasource = localhost; port = 3306; username = root; password = csi"; MySqlConnectionconnect = newMySqlConnection(connectionInfo); connect.Open(); MessageBox.Show("Connected"); connect.Close(); } catch(Exception ex) { MessageBox.Show(ex.Message); } }

14. To find out what to put into: string myConnection = "datasource = localhost; port = 3306; username = root; password = csi"; go to the MySql workbench and press ctrl+u. The following screen should appear: The password is the password you created. If you are using the school’s computer the password is csi.

15. How to use MySql to create a login. Place two labels, two textboxes, and a button on your form. 2) Double click on the button to edit the code for the button. In my code on the next slide: FirstName’stextbox is textBox1 and Password’s textbox is textBox2 (I renamed the text but not the actual textboxes). The login button is button1.

16. Check the entered firstname and password with the customer table of the videostore database. privatevoid button1_Click(object sender, EventArgs e) { try { stringconnectionInfo= "datasource = localhost; port = 3306; username = root;” + “password = csi"; MySqlConnectionconnect = newMySqlConnection(connectionInfo); MySqlCommand SQLCommand1 = newMySqlCommand("select * from videostore.customer” + “where firstname = '"+ textBox1.Text +"‘” + “and pass = '"+ textBox2.Text +"';", connect); MySqlDataReader reader; connect.Open(); reader = SQLCommand1.ExecuteReader(); intcount = 0; while (reader.Read()) { count = count + 1; }

17. Login Button Code Continued if (count == 1) { MessageBox.Show("Firstname and password is correct"); } else if (count > 1) { MessageBox.Show("Duplicate Firstname and password. You are not allowed access"); } else MessageBox.Show("Firstname and password is not correct"); connect.Close(); } // end of try block catch(Exception ex) { MessageBox.Show(ex.Message); } } // end of login button code

18. Adding additional forms to your Visual Studio project First, right click on presentation. Then, go down to add and click on new item. Select windows form and click add.

19. The code that makes clicking on the login button take you to the second form. Go back to the code for the login button in slide 17. Inside of if (count == 1) add the 3 lines of code that are in purple: if (count == 1) { MessageBox.Show("Firstname and password is correct"); this.Hide(); Form2 f2 = new Form2(); f2.ShowDialog(); }

20. Querying and inserting values into a database First, add 4 labels, 4 textboxes, and a button to your second form. Then double click on enter data and enter the following code to find the largest id that any customer has: private void button1_Click(object sender, EventArgs e) { string connectionInfo = "datasource = localhost; port = 3306;" + "username = root; password = pawsox11"; MySqlConnection connect = new MySqlConnection(connectionInfo); MySqlCommand SQLCommand1 = new MySqlCommand(" select max(id) as 'id‘” + ” from videostore.customer;", connect); MySqlDataReader reader; connect.Open(); reader = SQLCommand1.ExecuteReader();

21. Querying and inserting values into a database continued In the code below, you are adding one to the max(id) – the largest customer id – and you are inserting that value, along with four copies of string k, into the customer table of the videostore database: intid = 1; while (reader.Read()) { id = reader.GetInt32("id") + 1; MessageBox.Show("This is" + id); } connect.Close(); string k = "stop"; MySqlCommand SQLCommand2 = new MySqlCommand("insert into” + ”videostore.customer(id,lastname,firstname,email,pass)" + "values('" + id + "','" + k + "','" + k + "', '" + k + "', '" + k + "');", connect); connect.Open(); reader = SQLCommand2.ExecuteReader(); connect.Close(); } // close the enter data button code

22. Adding an exit button Add a new button to the second form. Double click on the button. Add the following code to the button: Application.Exit();

23. Adding things to a list box from a database Add a listbox and a new button to your second form. Double click on the new button (I named the text of the button “put into list box” but the button’s name is button2 – the exit button is button 3 in my code).

24. Adding things to a list box from a database continued Because the scope of this button doesn’t include objects created in the other button in this form you will have to create a new object of class MySqlConnection. In the code below I gave the new object of classMySqlConnection the same name as it had in the other button: connect. The SQL query in this code is the same SQL query that I introduced in slide 5. private void button2_Click(object sender, EventArgs e) { stringconnectionInfo = "datasource = localhost; port = 3306;" + "username = root; password = pawsox11"; MySqlConnectionconnect = newMySqlConnection(connectionInfo); MySqlCommandSQLCommand1 = newMySqlCommand("select * from ” + “videostore.customeras c," + "videostore.moviesrented as mr," + "videostore.movies as m where c.id =“ + “mr.personidand m.id" + "= mr.movieid and c.firstname = " + "'" + textBox1.Text + "';", connect);

25. Adding things to a list box from a database continued MySqlDataReader reader; try { connect.Open(); reader = SQLCommand1.ExecuteReader(); while (reader.Read()) { string movieName, firstNameinC; int id = 1; movieName = reader.GetString("moviename"); firstNameinC = reader.GetString("firstname"); id = reader.GetInt32("id"); listBox1.Items.Add(firstNameinC + " " + movieName + " " + id); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } // end of button2’s code Previously, I have shown you how an object of the class MySqlDataReader (I named the object reader) reads one thing from the database. But, as you can see in this code, objects of the MySqlDataReaderclass could get many things from a database. Here, I got a moviename from the movie table, the firstname from the customer table, and the id from the customer table.

26. Summary • //include the using MySqlline at the top • using MySql.Data.MySqlClient; • //create an object of MySqlConnectionwith the string containing datasource, etc as a parameter • stringconnectionInfo = "datasource = localhost; port = 3306; username = root; password = pawsox11"; • MySqlConnectionconnect = newMySqlConnection(connectionInfo); • //create an object of MySqlCommand with the SQL Query as the first parameter and the object of MySqlConnection • //as the second parameter • MySqlCommand SQLCommand1 = newMySqlCommand("select * from videostore.customer;”,connect); try { • //open the connection using the MySqlConnection object • connect.Open(); //use a DataReader to process each record • MySqlDataReaderreader; • reader = SQLCommand1.ExecuteReader(); • while(reader.Read()){//do something with each record} } catch (Exception er){//do something with the exception} //always close the connection • connect.Close();

27. References From the Stanford Online Database Course: Introduction to SQL: http://www.youtube.com/watch?feature=player_embedded&v=wxFmiRwXcQY basic-select-statement: http://www.youtube.com/watch?feature=player_embedded&v=4IxirOdp6bw table-variables-set-operators: http://www.youtube.com/watch?v=-BCCy5Z6i-s&feature=player_embedded subqueries-in-where: http://www.youtube.com/watch?v=IJPXosPGLTU&feature=player_embedded subqueries-in-from-select: http://www.youtube.com/watch?v=8OCAxk1Rybg&feature=player_embedded Professor Chi's database class website: http://163.238.35.144/~chi/CSC715/CSC715.html Professor Chi's SQL part 1 tutorial: http://163.238.35.144/~chi/CSC424/SQL/SQL%20Tutorial%20--%20Part%201%20MySQL.htm C# Visual Studio with MySql Tutorials: http://www.youtube.com/playlist?list=PLS1QulWo1RIZrmdggzEKbhnfvCMHtT-sA Professor Gueorguieva's Database with C# lecture: http://www.cs.csi.cuny.edu/~natacha/TeachSpring_2013/CSC330/CSharp/Lec5/DatabasesCSharp.pdf