Connecting to MySQL from ASP.NET MVC using Visual Studio Express for Web

• Download the source projects - 5.9 MB

Objective

The purpose of this article is demonstrate how to use a MySQL database with ASP.NET MVC using Microsoft Visual Studio Express for Web without an ORM such as Nhibernate or EntityFramework. The example will include reading in an image.

Example Origin

The example is driven by a desktop ticketing system I am working on where there will be limited online access to a subset of its features. This system has 30 dedicated table handling assemblies. The online interface will be limited to passenger registration, ticket purchasing, payments andp passenger enquiries.

Because I am using Express products, initial research indicated that a connection to MySQL was not possible. Nelson LeQuet's course on this subject available through Udemy or 3DBuzz. Nelson uses a combination of Nhibernate / FlusentMigrator for his site, and I highly recommend this technology set for new developments. Rather than handing me the fish Nelson taught me how to cast that line, so I started a new ASP.NET MVC project, added the same MySQL.Data library that I use in desktop development, pulled in the same namespaces, made the calls as if in desktop mode and there it was - a working connection to MySQL from and ASP.NET MVC site written using Microsoft Visual Studio Express.

In addition to not showing how to set up a test project using the Test Explorer, they were all confined to tesing single form examples where the form presented was tied to the process invoked. In my example the form associated with the process I will invoke appears in third place!

My offering here has its origins largely in these excellent sources of material, fundamental to getting me started.

• [LEQUET] - "Comprehensive ASP.NET MVC by Nelson LeQuet from Udemy
• [FREEMAN] - "Pro ASP.NET by Adam Freeman from Apress

[FREEMAN] got me off the ground, but his example uses Entity Framework and SqlSever. I failed to see how I could leave out the EF aspect, and was unable to get a working MySQL connection fromm what I learned here. Nonetheless my favourite book on the subject.

[LEQUET] provides an alternative to EntityFramework in Nhibernate, and has his example laid out in such a way as to see how it is possible to proceed without any ORM. In this course there is also exposure to other excellent resources such as FluentMigrator, Bcrypt, Bootstrap, Elmah, Git, and WebDeploy.

The text that follows demonstrates how to get that connection working on a basic ASP.NET MVC page.

Introducing the sample application.

The sample application is a trivial web page that reads and ID, Name, Address and Image from a table called PersonMaster. You may substitute these for any column / table combination on one of your databases when running it.

Getting Started

Open Microsoft Visual Studio Express for Web and create a new project, choosing ASP.NET MVC Web Application as illustrated

Next, select the empty template, leave the view engine as Razor, and do not opt to create a test project.

Choose your preferred source code control (or none)

Coding the Solution

Open Web.Config for the project and include the ConnectionStrings after configuration/appsettings just before system.web

  ?<connectionStrings?>
  ?<add name="MySQLConnection"
  connectionString="Server=localhost;
                    user id=root;
                    password=myrootpass;
                    persist security info=True;
                    database=mytestdb"
  providerName="System.Data.SqlClient" /?>
?</connectionStrings?>

Because we choose the empty template, there are no pages to render. In the Solution Explorer add a folder called Home to the Views folder:

Right click on the new Home folder, and choose Add, then View from the pop up menu. Name your new view 'Index', leave the engine as 'Razor' and have all three tick boxes clear as shown here:

This will create a new module called Index.cshtml. Replace the auto-generated code in it with the following:

@model Razor.Models.cPerson
@{
    Layout = null;
}
?<!DOCTYPE html?>
?<html?>
?<head?>
    ?<meta name="viewport" content="width=device-width" /?>
    ?<title?>Index?</title?>
?</head?>
?<body?>
    ?<div?>
        ?<p?>Name: @Model.Name?</p?>
        ?<p?>Address 1: @Model.Address1?</p?>
        ?<p?>Address 2: @Model.Address2?</p?>
        @if (Model.Photo != null)
        {
            ?<div style="float:left;margin-right:20px"?>
               ?<img width="75" height="75" src="@Url.Action("GetImage", "Home")" /?>
            ?</div?>
        }
    ?</div?>
?</body?>
?</html?>

Next, right click on the Controller folder and choose Add followed by Controller from the popup. Name your new Controller 'HomeController'

This is how 'HomeController' should look:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace Razor.Controllers
{
    public class HomeController : Controller
    {

        cPerson myPerson = new cPerson(12);

        public ActionResult Index()
        {
            return View(myPerson);
        }

        public FileContentResult GetImage()
        {
            if (myPerson.Photo != null)
            {
                return File(myPerson.Photo, "jpg");
            }
            else
            {
                return null;
            }
        }
    }
}

You will notice some redlining under the cPerson class references. So we need to create that in the model - but first we need to add a reference to MySQL.Data. Use the same one that you always use - for me this is what it is:

Now, right click on Models and add a class, cPerson

This is what cPerson should look like:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using MySql.Data.MySqlClient;
using System.Configuration;

namespace Razor.Models
{
    public class cPerson
    {
        public int PersonID { get; set; }
        public string Name { get; set; }
        public string Address1 { get; set; }
        public string Address2 { get; set; }
        public byte[] Photo { get; set; }

        private bool connection_open;
        private MySqlConnection connection;

        public cPerson()
        {

        }

        public cPerson(int arg_id)
        {
            Get_Connection();
            PersonID = arg_id;
            //    m_Person = new CPersonMaster();
            //  List<CPersonMaster> PersonList = new List<CPersonMaster>();
            //PersonList = CComs_PM.Fetch_PersonMaster(connection, 4, arg_id);

            //if (PersonList.Count==0)
            //  return "";

            //m_Person = PersonList[0];

            //DB_Connect.CloseTheConnection(connection);
	try
	{
        

		MySqlCommand cmd = new MySqlCommand();
		cmd.Connection = connection;
		cmd.CommandText =
	string.Format("select concat (person_id, ') ', surname, ', ', forename) Person, Address1, Address2, photo, length(photo) from PersonMaster where Person_ID = '{0}'",
									  PersonID);

		MySqlDataReader reader = cmd.ExecuteReader();

		try
		{
			reader.Read();

			if (reader.IsDBNull(0) == false)
				Name = reader.GetString(0); 
			else
				Name = null;

            if (reader.IsDBNull(1) == false)
				Address1 = reader.GetString(1); 
			else
				Address1 = null;

            if (reader.IsDBNull(2) == false)
				Address2 = reader.GetString(2); 
			else
				Address2 = null;

			if (reader.IsDBNull(3) == false)
					{
						Photo = new byte[reader.GetInt32(4)];
                        reader.GetBytes(3, 0, Photo, 0, reader.GetInt32(4));
					}
					else
					{	
						Photo = null;
					}
            reader.Close();

		}
		catch (MySqlException e)
		{
			string  MessageString = "Read error occurred  / entry not found loading the Column details: "
				+ e.ErrorCode + " - " + e.Message + "; \n\nPlease Continue";
			//MessageBox.Show(MessageString, "SQL Read Error");
			reader.Close();
			Name= MessageString;
            Address1 = Address2 = null;
		}
	}
	catch (MySqlException e)
	{
			string  MessageString = "The following error occurred loading the Column details: "
				+ e.ErrorCode + " - " + e.Message;
			Name= MessageString;
            Address1 = Address2 = null;
		}
             
             
             
             
             connection.Close();


        }

        private void Get_Connection()
        {
            connection_open = false;

            connection = new MySqlConnection();
            //connection = DB_Connect.Make_Connnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString);
            connection.ConnectionString = ConfigurationManager.ConnectionStrings["MySQLConnection"].ConnectionString;

            //            if (db_manage_connnection.DB_Connect.OpenTheConnection(connection))
            if (Open_Local_Connection())
            {
                connection_open = true;
            }
            else
            {
                //					MessageBox::Show("No database connection connection made...\n Exiting now", "Database Connection Error");
                //					 Application::Exit();
            }

        }

        private bool Open_Local_Connection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }
    }
}

If its all hooked up properly, when you compile the project and launch the site, your output will resemble this:

How it all Works

All the key action occurs in the model,the key steps are:

• Include a using statement for the MySql.Data.MySqlClient namespace
• Declare and instantiate an attribute of type MySqlConnection to hold the connection handle
• Read the connection string from Web.Config into the ConnectionString attribute of the connection handle
• Open the connection by invoking the Open method on the connection handle
• Declare and instantiate an attribute of type MySqlCommand
• Pass the connection handle to the connection attribute of your MySqlCommand attribute.
• Put your SQL statement into the CommandText attribute of your MySqlCommand attribute.
• Create a MySqlDataReader attribute and instantiate it by invoking the ExecuteReader method of your MySqlCommand attribute.
• Use the Read method of the MySqlDataReader attribute to recover your data.
• Close the connection using the Close method on the connection handle.

I have left some commented out code in the final example. These are from where I made a successful connection to some of my C++/CLI assemblies. I have retained them for reference purposes.

Conclusion

In putting together this article I have stayed closer to topic than I regularly do. The only 'extra' to be found here is the retrieval of data from an image column. I would only recommend following this example if you have very specific requirements that cannot be met by the likes of Nhibernate or Entity Framework.

Finally always close your connection after each query or transaction. Originally I had been looking for a means to make an upfront connection and propagate that handle throughout my site to eliminate the overhead of repeated connections. While reducing the connection overhead may be good practice in a desktop application it is not a good idea for website. This is because leaving connections open to the end paves the way for a DOS or denial of service attack through using up all available database connections. Even if your site is never attacked, too many concurrent connections will pose problems for your legitimate users operating the site under normal parameters if there are too many of them accessing it at once.

History

2014-09-22 - V1.0 - Initial submission