Difference between revisions of "Connect MySQL to ASP.Net Web App"

From TRCCompSci - AQA Computer Science
Jump to: navigation, search
(→‎Getting the data)
(→‎Using the Connection)
 
(5 intermediate revisions by the same user not shown)
Line 13: Line 13:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
−
Just for this example we will be retrieving the details from the user table, so I created this class to store each record, it should be inside the subclass of `PageModel` :
 
−
 
−
<syntaxhighlight lang=c#>
 
−
public class User
 
−
{
 
−
    public string username { get; set; }
 
−
    public string password { get; set; }
 
−
    public int status { get; set; }
 
−
}
 
−
</syntaxhighlight>
 
−
 
−
Next I created a list of User to store the retrieved data, it should be inside the subclass of `PageModel`:
 
−
 
−
<syntaxhighlight lang=c#>
 
−
public static List<User> users = new List<User>();
 
−
</syntaxhighlight>
 
  
 
You need to have a local webserver running with an installation of MySQL & PHP. I have used the USBWebServer from your SQL lessons. I have also created this connection string. The string below includes the name of the database, mine is just called `test`. The string should be inside the subclass of `PageModel`:
 
You need to have a local webserver running with an installation of MySQL & PHP. I have used the USBWebServer from your SQL lessons. I have also created this connection string. The string below includes the name of the database, mine is just called `test`. The string should be inside the subclass of `PageModel`:
Line 47: Line 31:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
−
== Getting the data==
+
==Using the Connection==
−
Now, in the constructor method I have added the following code:
+
Now, in the OnGet method I have added the following code, which will create, open, and close the database connection:
 
<syntaxhighlight lang=c#>
 
<syntaxhighlight lang=c#>
−
public IndexModel(ILogger<IndexModel> logger)
+
public void OnGet()
 
{
 
{
−
    _logger = logger;
 
−
 
−
    users.Clear(); // clear users before a load or reload
 
−
 
 
     using var connection = GetConnection; // this gets the connection
 
     using var connection = GetConnection; // this gets the connection
  
 
     connection.Open(); // you must open the connection
 
     connection.Open(); // you must open the connection
 
              
 
              
−
     using var Command = new MySqlCommand("SELECT * FROM test;", connection ); // you can add your SQL into a command
+
     connection.Close();
−
    using var reader = Command.ExecuteReader(); // you execute the query
 
−
    while (reader.Read())
 
−
    {
 
−
        User temp = new User(); // create user
 
−
        temp.username = reader.GetString(0);
 
−
        temp.password = reader.GetString(1);
 
−
        temp.status = reader.GetInt32(2);
 
−
        users.Add(temp); // add user to list
 
−
    }
 
 
}
 
}
−
</syntaxhighlight>
 
−
 
−
Remember, here `test` in the SQL refers to the table name, so if you have a different table name you must change the SQL. To show the data in your page go back to the HTML code for the page and add:
 
−
<syntaxhighlight lang=c#>
 
−
@foreach (var u in IndexModel.users)
 
−
{
 
−
<table class="table">
 
−
<thead>
 
−
<tr class="bg-primary">
 
−
<th>User</th>
 
−
<th>Password</th>
 
−
<th>Status</th>
 
−
 
−
</tr>
 
−
</thead>
 
−
<tbody>
 
−
 
−
<tr>
 
−
<td class="bg-info">@u.username</td>
 
−
<td class="bg-info">@u.password</td>
 
−
<td class="bg-info">@u.status</td>
 
−
 
−
</tr>
 
−
 
−
</tbody>
 
−
</table>
 
−
}
 
−
</syntaxhighlight>
 
−
 
−
==Insert data==
 
−
Inside your subclass of `PageModel` add the following method, this will run a submit:
 
−
<syntaxhighlight lang=c#>
 
−
public IActionResult OnPost()
 
−
{
 
−
    User temp = new User();
 
−
    temp.username = Request.Form["user"];
 
−
    temp.password = Request.Form["password"];
 
−
    temp.status = Convert.ToInt32(Request.Form["status"]);
 
−
 
−
    using var connection = GetConnection;
 
−
 
−
    connection.Open();
 
−
    string sql = "insert into test values(@p1, @p2, @p3);";
 
−
    using var Command = new MySqlCommand(sql, connection);
 
−
    Command.Parameters.AddWithValue("@p1", temp.username);
 
−
    Command.Parameters.AddWithValue("@p2", temp.password);
 
−
    Command.Parameters.AddWithValue("@p3", temp.status);
 
−
 
−
    Command.ExecuteNonQuery();
 
−
    connection.Close();
 
−
 
−
    users.Add(temp);
 
−
 
−
    return Page();
 
−
}
 
−
</syntaxhighlight>
 
−
 
−
In your html you need to create a form like this:
 
−
 
−
<syntaxhighlight lang=html>
 
−
<form method="post">
 
−
<input type="Text" name="user">
 
−
<input type="Text" name="password">
 
−
<input type="number" name="status">
 
−
<input type="submit">
 
−
</form>
 
 
</syntaxhighlight>
 
</syntaxhighlight>

Latest revision as of 07:24, 23 October 2024

Install MySQL.Data

Using the Nuget Package Manager install the following:

  • MySql.Data;
  • MySql.Data.MySqlClient;

Creating the connection

Now using the page you want to connect, open the Model `cs` code.

At the top add the following 

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


You need to have a local webserver running with an installation of MySQL & PHP. I have used the USBWebServer from your SQL lessons. I have also created this connection string. The string below includes the name of the database, mine is just called `test`. The string should be inside the subclass of `PageModel`: 

string connection = "server=localhost;user=root;database=test;port=3306;password=usbw;";

Finally I created this method to get the connection, it should be inside the subclass of `PageModel`: 

        public MySqlConnection GetConnection 
        {
            get
            {
                return new MySqlConnection(connection);
            }
        }

Using the Connection

Now, in the OnGet method I have added the following code, which will create, open, and close the database connection: 

public void OnGet()
{
    using var connection = GetConnection; // this gets the connection

    connection.Open(); // you must open the connection
            
    connection.Close();
}
Retrieved from ‘http://www.trccompsci.online/mediawiki/index.php?title=Connect_MySQL_to_ASP.Net_Web_App&oldid=9013’