Share via


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:

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.

image

Now modify the Web Config:

image

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:

image

 

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:

image

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:

image

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”.

image

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

image

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:

image

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.

image

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.

Source Code can be found at: