MySql Database

Setup

You need to download the USBWebServer, or another portable local webserver application. You can download a working portable webserver from this link:

http://www.usbwebserver.net/en/download.php

This is quite a simple program to use, and i would recommend it for most students. It works well on your own devices but less well on the college system.

In Visual Studio, and with Windows Form Application or even a Monogame project started, click the `Project` tab and click `Manage Nuget Packages`.

Next, search for `MySql.Data` and install it:

Database

You should be able to run the USBWebserver and will need to accept a couple of firewall permission dialogs.

You should be able to create a database from the `PHPMyAdmin` tab.

Connection

You need to add the following into the using section of your code:

using MySql.Data;
using MySql.Data.MySqlClient;

Then you can add the code to connect to your database:

string Constr = "server=localhost;user=root;database=mydb;port=3306;password=usbw;";

            //Connects to database when form loads
            MySqlConnection conn = new MySqlConnection(Constr);


            try
            {

                Console.WriteLine("Connecting");
                conn.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Connection Failed");
                Console.WriteLine(ex.ToString());

            }
            conn.Close();
            Console.WriteLine("Connection Complete");

`Constr` is the connection string to use and you should notice the name of the database in this example is `mydb`.

Select Example

MySqlConnection con = new MySqlConnection("server=localhost;User Id=root;database=result;password=1234");
    con.Open();

    MySqlCommand cmd = new MySqlCommand("Select * from users where username=?username and password=?password", con);
    cmd.Parameters.Add(new MySqlParameter("username", this.Login1.UserName));
    cmd.Parameters.Add(new MySqlParameter("password", this.Login1.Password)); 

    MySqlDataReader dr = cmd.ExecuteReader();
    if (dr.HasRows ==true)
    {
        e.Authenticated = true;
    }

Insert Example

MySqlConnection con = new MySqlConnection("server=localhost;User Id=root;database=result;password=1234");
con.Open();
cmd.Connection = con;

cmd.CommandText = "INSERT INTO myTable VALUES(NULL, @number, @text)";
cmd.Prepare();

cmd.Parameters.AddWithValue("@number", 1);
cmd.Parameters.AddWithValue("@text", "One");

cmd.ExecuteNonQuery();