Click here to Skip to main content
14,577,604 members

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

Rate this:
4.50 (7 votes)
Please Sign up or sign in to vote.
4.50 (7 votes)
23 Sep 2014CPOL
This article shows how to connect to MySql without an ORM when using Visual Studio Express 2013 for ASP.NET MVC development.

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.

The Source Code included

I have included the ASP.NET MVC project where I made my working connection. It will not work 'out of the box' because you will not have the same database on your side. Use any table on an existing database in the cPerson class and you should be good to go.

Getting Started

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

Image 1

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

Image 2

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:

Image 3

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:

Image 4

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'

Image 5

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:

Image 6

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

Image 7

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:

Image 8

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ger Hayden
Software Developer
Ireland Ireland
My first program was written in Basic on a Sinclair Spectrum ZX 16K in the summer of '85. Having studied Computer Systems I attempted to break into the world of C but took a wrong turn and got immersed in COBOL!

I looked a C again in 1994 but didnt follow up on it. In 2001 I introduced myself to Visual C++ 6.0 courtesy of Ivor Hortons book, but found the going difficult. I tipped my toe in the .NET water in '05 but the first example I tried in VC++ 2005 express didnt work and allied with the absence of MFC in the express package, I parked that up.

Along the way my career got shunted into software testing

A personal machine change force me to migrate to VS2008 in 2008. The new edition of Ivor Hortons book for VC++ in VS2008 reintroduced me to .NET and I got curious whereupon I went out and acquired Stephen Fraser's "Pro Visual C++/CLI and
the .NET 3.5 Platform". I was hooked!

After 20 years I think I finally found my destination.

But it would take a further 8 years of exile before I was reappointed to a developer role. In that time I migrated to C# and used selenium wedriver (courtesy of Arun Motoori's Selenium By Arun) as the catalyst to finally grab the opportunity.

Comments and Discussions

 
QuestionBase de datos IMAGEN Pin
Stiven Zamora3-Nov-18 9:02
MemberStiven Zamora3-Nov-18 9:02 
AnswerRe: Base de datos IMAGEN Pin
Nelek3-Nov-18 9:05
protectorNelek3-Nov-18 9:05 
QuestionAbout DataBase Pin
Member 1316896127-Jul-17 18:35
MemberMember 1316896127-Jul-17 18:35 
Questionentity framework Pin
Anil Sharma198329-Nov-15 22:35
professionalAnil Sharma198329-Nov-15 22:35 
AnswerRe: entity framework Pin
Ger Hayden30-Nov-15 2:53
MemberGer Hayden30-Nov-15 2:53 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun23-Sep-14 20:13
MemberHumayun Kabir Mamun23-Sep-14 20:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Article
Posted 23 Sep 2014

Stats

127.1K views
4.4K downloads
17 bookmarked