Difference between revisions of "MySql Database"
(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:
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();