MVC with ADO.Net on MySQL (using Stored Procedure)

I am really excited to share this piece of unique code with you. Really this is different MVC without entity framework and based on traditional ADO.Net and database not even MS SQL its MySQL! Yes I am going to share these different type code with all of you.

Before sharing code I would like to tell a brief background about why I have tried it. I have websites all were built on traditional ASP.Net with ADO.Net and now I am trying to migrate these in MVC. But to move everything in MVC at a time will be a big problem, hence I am trying to do it part by part and I believe with ADO.Net MVC will work great. So I do not need to modify database or database access layer. This will reduce my effort and cost.

Next is why MySQL not MS SQL? Reason is, I have small budget for these websites and MS SQL express great for this for now. But in future if I need database more than 10GB or 1GB RAM support for better database operation then MS SQL Express will be a bar for me and paid version is seems very costly according to INR currency. But MySQL enterprise also is free to use and this I am using since last more than 1 year in production on Windows 2008 server without any single problem. About to decide MySQL I get confidence from Google, Facebook, Yahoo etc. companies because so far I know they are all doing their work on MySQL only.

I believe it was a nice explanation about background, now lets come on coding part.

Coding with MySQL is almost similar to MS SQL on .Net part. Just you need to add some reference of MySQL's ADO.Net DLLs and next you need to use MySQL name space in your code. For connection, command, DataAdapter you need to refer MySQL connection, MySQL Command and MySQLDataAdapter. Your calling process and other part will be exactly same. DataTable, DataSet will be same as MS SQL.

In the picture you can see I have added 4 dll files to enable MySQL database access. In MySQL website they share DLLs for specific .Net versions. I am using .Net 4.5 hence I took this files. Files are in source code so for .Net 4.5 you can use these file in your project.

To access MySQL database I have setup my connection string like below:

<add name="connStr" connectionString="server=localhost;user=root;database=world;port=3306;password=****" providerName="MySql.Data.MySqlClient"/>

My project structure as below:

In this screenshot AllCountries.xshtml is my view in Razor format, CountryController.cs is my controller and Contry.cs is my model where I have written my data access code. Its exactly similar like other ADO.Net data access code. 

I am using MySQL's provided database (schema) 'world' for my example. 'world' schema is containing one table 'country' and it is containing data about countries, I am going to use these to show my example. Table structure is very simple and it is as below

To get data from 'country' table I have developed 'GetCountryList' Stored Procedure which I shall call from code.

USE `world`;

DELIMITER $$ 

USE `world`$$ 

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetCountryList`( ) 

  BEGIN  

       select * from world.country; 

  END$$ 

DELIMITER ;

My model class - 'Country.cs' as below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Web.Security;

namespace World
{
    public class Country
    {
        string connStr;
        MySqlConnection cnn;
        MySqlCommand cmd;
        public Country()
        {
            connStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();
            cnn = new MySqlConnection(connStr);
        }

        public DataTable GetCountryList()
        {
            DataTable dt = new DataTable();
            cmd = new MySqlCommand("GetCountryList");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = cnn;

            MySqlDataAdapter adap = new MySqlDataAdapter(cmd);
            if (cnn.State != ConnectionState.Open ||
                cnn.State == ConnectionState.Broken ||
                cnn.State != ConnectionState.Connecting ||
                cnn.State != ConnectionState.Executing ||
                cnn.State != ConnectionState.Fetching)
                try
                {
                    adap.Fill(dt);
                    return dt;
                }
                catch (Exception ex)
                {
                    if (cnn.State != ConnectionState.Closed)
                    {
                        cnn.Close();
                    }
                }
            return dt;
        }
    }   
}

This model class is invoking stored procedure 'GetCountryList' and fetching data from database. Class is completely similar with my earlier project's data access layer class and there has no change, I am using it directly. To show as an example I made is simple with single stored procedure. There has some difference to access MySQL with MS SQL. Look for below codes

using MySql.Data.MySqlClient;

Here I am using MySQLClient which we generally use SqlClient for MS SQL database and declaring MySQL Command, Connection and DataAdapter as below:

MySqlConnection cnn;MySqlCommand cmd;MySqlDataAdapter adap = new MySqlDataAdapter(cmd);

CountryController.cs

Controller class for my project is very simple. This is just creating an object of my country class and invoking method to get data. This method will return a datatable and data table is sending to View.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using World;
namespace MVC4.Controllers
{
    public class CountryController : Controller
    {
        public ActionResult AllCountries()
        {
            Country countries = new Country();
            return View(countries.GetCountryList());
        }
    }
}

View - AllCountries.cshtml, I am using Razor syntax to populate data in view and this is standalone/complete view without any layout of any other partial views. Code as below:

@{
    Layout = null;
}
<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>AllCountries</title>
</head>
<body>
    <div>
        <table border="1" cellpadding="5">
            <thead>
                <tr>
                    @foreach (System.Data.DataColumn col in Model.Columns)
                    {
                        <th>@col.Caption</th>
                    }
                </tr>
            </thead>
            <tbody>
                @foreach (System.Data.DataRow row in Model.Rows)
                {
                    <tr>
                        @foreach (var cell in row.ItemArray)
                        {
                            <td>@cell.ToString()</td>
                        }
                    </tr>
                }
            </tbody>
        </table>
    </div>
</body>
</html>

This is very simple code, Razor code is reading DataTable columns and priting these on html by below code. Razor is nothing new, its just earlier traditional things with new name with an @ sign. 

@foreach (System.Data.DataColumn col in Model.Columns)
{
         <th>@col.Caption</th>
}

This is normal foreach loop to print column caption.

Below code is another foreach loop which is going through each cell and printing these.

@foreach (System.Data.DataRow row in Model.Rows)
{
    <tr>
        @foreach (var cell in row.ItemArray)
        {
            <td>@cell.ToString()</td>
        }
    </tr>
}

After executing these codes, output will show like below.

Thank you for reading my blog. You can download full source code from here.