Azure: Visual Studio 2013 connection to MySQL
Previous Related Blog Next Related Blog
In my previous post, I discussed the online article that connects to MySQL, it was fairly simple, and so will this blog. We will simply make sure that you are able to connect and use CRUD to implement a simple UI. For some reason Google doesn’t get you to the right MySQL pages for Visual Studio.
Sources:
- https://dev.mysql.com/doc/connector-net/en/connector-net-visual-studio.html
- Various reading that I can’t find my notes
Sample Code
Discussion:
Model-View-Controller is considered the way to go for ASP.NET and XAML/C# design. Not sure why, initially code behind was the bomb, but now it’s MVC. Ok. Let’s use MVC, in a REALLY SIMPLE way to implement a connection to MySQL.
First we need to modify the Web Config to generate our connection string. We will use the included sakila MySQL database, in the previous blog we connected to it using a console app. If you do not have the sakila MySQL Database, please make sure it is installed and you are connected to it. Your Server Explorer should look like the following, scroll down for more.
Now modify the Web Config:
Add the following you can copy and paste this, but make sure to change the password! Scroll down to see how it should look if you are successful:
<connectionStrings>
<add name="MySQLConnection"
connectionString="server=localhost;
user=root;
database=sakila;
port=3306;
password=********************;
persist security info=True;"
providerName="System.Data.Sq1Client"/>
</connectionStrings>
So that your Web.config looks like this:
Now Add a View:
Since this is a “Razor” type of HTML page, your initial page might be titled: Index.CSHTML. If you like VB, you could also use the VBHTML type of Razor page.
Right Click on the Views Folder and add a Folder named Home
Right Click on the Home Folder and then Add in the fly out, then make sure that your Add View form looks like this:
Add the HTML to your View:
Use this code (you can select all and then do a copy and paste, if you called your project MVASPFirstOne.Models.cActorName, otherwise replace MVASPFirstOne.Models.cActorName with a more correct name):
<!--Copy Code Starting Here-->
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
<div>
<p>Name: @Model.first_name</p>
<p>Last Name: @Model.last_name</p>
</div>
</body>
</html>
<!--Stop Copying Code Here-->
Your CSHTML page looks like the following:
Now create a Controller, note that the “View” is pretty simple, if they are not, then review your architecture and design.
Create A Controller
On the Controller folder, right click and select “Add” then Controller. You this dialog box will appear, change Default1 to the word: “Home” so that you will see “HomeController”.
In your code view, select all and paste in the following code. We haven’t created the Model yet, but make sure to add the using MVCASPFirstOne.Models, substitute your project name for the “MVCASPFirstOne”
//******* Start copying code here **********
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MVCASPFirstOne.Models;
namespace MVCASPFirstOne.controllers
{
public class HomeController : Controller
{
cActorName thePerson = new cActorName(12);
public ActionResult Index()
{
return View(thePerson);
}
}
}
//****** Stop copying code here ***********
Your HomeController.cs should look like the following
Now let’s add a Model
In this case, the model is the complex component, but in many cases the controllers are more complicated, BUT the VIEW must never be complex. You may want to become familiar with the concept of skinnability, not covering that in this document.
Right click on Models, add a Class.cs, change it’s name to cActorName.cs
Add the following code (there is a lot):
//********Copy Code Starting Here ********
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using System.Configuration;
namespace MVCASPFirstOne.Models
{
public class cActorName
{
public int actor_id { get; set; }
public string first_name { get; set; }
public string last_name { get; set; }
public string last_update { get; set; }
//private bool connection_open;
private MySqlConnection connection;
public cActorName()
{
}
public cActorName(int arg_id)
{
Get_Connection();
actor_id = arg_id;
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connection;
cmd.CommandText = string.Format("SELECT first_name, last_name FROM actor WHERE (actor_id = 1)");
MySqlDataReader reader = cmd.ExecuteReader();
try
{
reader.Read();
if (reader.IsDBNull(0) == false)
first_name = reader.GetString(0);
else
first_name = null;
if (reader.IsDBNull(1) == false)
last_name = reader.GetString(1);
else
last_name = null;
reader.Close();
}
catch (MySqlException e)
{
string MessageString = "Read error occurred " + e.ErrorCode + " - " + e.Message + ";";
reader.Close();
}
}
catch (MySqlException e)
{
string MessageString = "The following error occurred "
+ e.ErrorCode + " - " + e.Message;
first_name = "Marvelous Gentleman";//MessageString;
last_name = last_update = null;
}
}
private void Get_Connection()
{
connection = new MySqlConnection();
connection.ConnectionString = ConfigurationManager.ConnectionStrings["MySQLConn_sakila"].ConnectionString;
Open_Local_Connection();
}
private bool Open_Local_Connection()
{
try
{
connection.Open();
return true;
}
catch (Exception e)
{
return false;
}
}
}
}
//********Stop Copying Code Here ********
Your design environment should look like the image below, but not all code is shown, just the top 33 or so lines:
If all has gone well you should be able to connect to your MySQL database
You should see the following, if you used actor_id=1 in the SQL statement, then you will see PENELOPE as she is the first actor in the database.
Conclusion:
You now have connected to your MySQL database and retrieved data from it using a very simple MVC system. In this system we used a View (Index.cshtml), a Model (cActorName), and a Controller (HomeController).
This is a relatively useless database tool, but it is does make sure that you have everything working on your system to implement MySQL.
Over the next few sessions, I will cover how to use the Entity Framework and hopefully we will be able to load this database into the Dreamspark Azure instance! Once we do that, then the Raspberry Pi Sensor data will be able to be uploaded directly from the Raspberry Pi using Linux or Windows 10 to Azure.