Connecting to a MySQL Azure Database from ASP.NET

Introduction

In an ASP.NET MVC application, developers using Entity Framework and ASP.NET Scaffolding can connect a remote Azure MySQL database. We can create a Web application to save and retrieve application information on a MySQL Azure database. In this article, I will demonstrate you how to create a Web application with MVC and Entity Framework that communicates with a remote MySQL Azure database.

Log In to Azure or Create a New Azure Account

Before you start working on a .NET MVC application, you need to get a Windows Azure account. Anyone can open a Windows Azure account for free. For those who already have an existing Azure account, you can go ahead and log in.

Creating a New MySQL Database on Windows Azure

After getting the required access to an Azure Account, you need to create a MySQL Database to store application data.

To create a new MySQL database, you need to select the ‘New’ option on the bottom left of your Web page and then select the option Data + Storage -> MySQL Database ->. Set the name and provide the configurations you need. This is shown in Figure 1.

Azure MySQL Database Creation
Figure 1: Azure MySQL Database Creation

Configurations entered above will be used in a Web Application to access the Azure Database. During database creation, you also have to select a pricing tier, as mentioned in Figure 2.

Azure MySQL Database Pricing
Figure 2: Azure MySQL Database Pricing

After successful deployment of MySQL DB, you will get the confirmation depicted in Figure 3.

Azure MySQL Successful Deployment
Figure 3: Azure MySQL Successful Deployment

To get the connection string, select the database created and, on the right side of the main window you will get an option called “Show Connection String”.

When you select that option, a new tab will appear, as in Figure 4, with the connection string formatted to different providers.

Azure MySQL Connectionstring
Figure 4: Azure MySQL Connectionstring

ASP.NET MVC Application to Connect MySQL DB

To create a new MVC application, Open Visual Studio >> File >> New Project menu, expand the Web category, and select ASP.NET Web Application. This is shown in Figure 5.

New ASP.NET Web Application
Figure 5: New ASP.NET Web Application

Press OK. A new screen will appear; it has several options for a template to use in your project.

Select MVC template (see Figure 6).

MVC Template
Figure 6: MVC Template

After you’ve selected your template, click OK. Your first MVC application will be created using ASP.NET, as shown in Figure 7.

New Student Model
Figure 7: New Student Model

After you’ve created the MVC Web application, you need to create data model.

For that, select the option “Add New Item” in the solution and choose the “Class” option. Create the class as depicted in the following code snippet.

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

namespace ProjectMySQL.Models
{
   public class Student
   {
      public int StudentId { get; set; }

      public string Class { get; set; }

      public string SchoolName { get; set; }
   }
}

Next, install the EntityFramework and MySQL.Data.Entity by using NuGet package manager, as shown in Figure 8.

NuGet packages installed
Figure 8: NuGet packages installed

Next, we need to add Scaffold. To add Scaffold, on the top of Controller folder, select the option “Add New Scaffold Item”. On the new screen, select the option MVC6 Controller with views using Entity Framework (see Figure 9).

MVC Scaffold
Figure 9: MVC Scaffold

Next, Select the name of the Controller, Model class, and the Data Context class, as depicted in Figure 10.

MVC Model Class
Figure 10: MVC Model Class

The new Controllers and Views associated will be created successfully. Refer to the following code snippet.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
using ProjectMySQL.Models;

namespace MySQL.Controllers
{
   public class StudentssController : Controller
   {
      private ApplicationDbContext db = new ApplicationDbContext();

      // GET: Students
      public ActionResult Index()
      {
         return View(db.Student.ToList());
      }

      // GET: Students/Details/5
      public ActionResult Details(string id)
      {
         if (id == null)
         {
            return new HttpStatusCodeResult(HttpStatusCode
               .BadRequest);
         }
         Student student = db.Student.Find(id);
         if (student == null)
         {
            return HttpNotFound();
         }
         return View(student);
      }

      // GET: Students/Create
      public ActionResult Create()
      {
         return View();
      }

      // POST: Students/Create
      // To protect from overposting attacks, please enable the
      // specific properties you want to bind to. For more details,
      // see https://go.microsoft.com/fwlink/?LinkId=317598.
      [HttpPost]
      [ValidateAntiForgeryToken]
      public ActionResult Create([Bind(Include =
         "StudentId,Class,SchoolName")] Student student)
      {
         if (ModelState.IsValid)
         {
            db.Student.Add(student);
            db.SaveChanges();
            return RedirectToAction("Index");
         }

         return View(student);
      }

      // GET: Students/Edit/5
      public ActionResult Edit(string id)
      {
         if (id == null)
         {
            return new HttpStatusCodeResult(HttpStatusCode
               .BadRequest);
         }
         Student student = db.Student.Find(id);
         if (student == null)
         {
            return HttpNotFound();
         }
         return View(student);
      }

      // POST: Students/Edit/5
      // To protect from overposting attacks, please enable the
      // specific properties you want to bind to. For more details,
      // see https://go.microsoft.com/fwlink/?LinkId=317598.
      [HttpPost]
      [ValidateAntiForgeryToken]
      public ActionResult Edit([Bind(Include = "Id,Model,Year")]
         Student student)
      {
         if (ModelState.IsValid)
         {
            db.Entry(student).State = EntityState.Modified;
            db.SaveChanges();
            return RedirectToAction("Index");
         }
         return View(student);
      }

      // GET: Students/Delete/5
      public ActionResult Delete(string id)
      {
         if (id == null)
         {
            return new HttpStatusCodeResult(HttpStatusCode
               .BadRequest);
         }
         Student student = db.Student.Find(id);
         if (student == null)
         {
            return HttpNotFound();
         }
         return View(student);
      }

      // POST: Students/Delete/5
      [HttpPost, ActionName("Delete")]
      [ValidateAntiForgeryToken]
      public ActionResult DeleteConfirmed(string id)
      {
         Student student = db.Student.Find(id);
         db.Student.Remove(student);
         db.SaveChanges();
         return RedirectToAction("Index");
      }

      protected override void Dispose(bool disposing)
      {
         if (disposing)
         {
            db.Dispose();
         }
         base.Dispose(disposing);
      }
   }
}

Next, the following database connection string of the Web.config file needs to be updated.

Finally, update the menu section of Layout file and add a new Submenu item.

<connectionStrings>
   <add name="DefaultConnection" connectionString="Data Source=
      (LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|
      \aspnet-ProjectMySQL-20190416110941.mdf;Initial Catalog=
      aspnet-ProjectMySQL-20190416110941;Integrated Security=True"
      providerName="System.Data.SqlClient" />
</connectionStrings>

Press the F5 button to run the Web application. The new entity appears on the menu.

Conclusion

That was all about Connecting MySQL Azure Database from ASP.NET. I hope this article was helpful!! That’s all for today; happy reading!

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read