Difference between revisions of "MySql Database"

From TRCCompSci - AQA Computer Science
Jump to: navigation, search
(Created page with "=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.usbwe...")
 
(Select Example)
 
(4 intermediate revisions by the same user not shown)
Line 11: Line 11:
  
 
[[File:Mysqldata.png]]
 
[[File:Mysqldata.png]]
 +
 +
=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=
 
=Connection=
Line 43: Line 48:
 
             conn.Close();
 
             conn.Close();
 
             Console.WriteLine("Connection Complete");
 
             Console.WriteLine("Connection Complete");
 +
</syntaxhighlight>
 +
 +
`Constr` is the connection string to use and you should notice the name of the database in this example is `mydb`.
 +
 +
=Select Example=
 +
<syntaxhighlight lang=c#>
 +
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)
 +
    {
 +
        // combination is in Database
 +
    }
 +
</syntaxhighlight>
 +
 +
=Insert Example=
 +
<syntaxhighlight lang=c#>
 +
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();
 
</syntaxhighlight>
 
</syntaxhighlight>

Latest revision as of 14:28, 3 December 2023

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:

Mysqldata.png

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)
    {
        // combination is in Database
    }

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();