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.

In Memory Search using Lamda Expression: Realtime Chat application on web in ASP.Net: Step 4

Welcome at  Realtime Chat application on web in ASP.Net using SignalR technology. This is step 4 and now we will learn how we can search in memory array without any loop like for, while etc. We will use Lamda expression to search an element in array list with generics. I shall not describe about these technologies from theoretical perspective, here I shall show some application of these. You can read theory of these things from MSDN site.

You may think why this is require for our chat application, really good question. We shall use this technique to find an online user in server. However we can take database help to find online users but to minimize database operation I have used this technique. In chat application I have used a public static list (array) to hold online users, and from here I am searching users to generate online friend lists. By that way I have minimize a lot database operation and this technique can improve your chat application.

Lets check the code first then shall describe the codes.

HTML/ASP.Net part

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <h1>In memory search using Lamda Expression in C#.Net</h1>
        <h3>Search country calling code</h3>
        <p>
            Country Name:
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
&nbsp;<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Find Calling Code" />
        </p>
        <p>
            <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
        </p>
        <a href="http://en.wikipedia.org/wiki/List_of_country_calling_codes" target="_blank">Full list is available here</a>
    </form>
</body>
</html>

and the C# code as below:

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

public partial class _Default : System.Web.UI.Page
{
   
    protected void Page_Load(object sender, EventArgs e)
    {       
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        FindCallingCode();
    }
    private void FindCallingCode()
    {
        //In real use databind before searching, using by that way as demo
        List<Country> countries = new List<Country>();
        countries.Add(new Country { callingCode = "+91", name = "India" });
        countries.Add(new Country { callingCode = "+44", name = "UK" });
        countries.Add(new Country { callingCode = "+1", name = "USA" });
        countries.Add(new Country { callingCode = "+88", name = "Bangladesh" });
        countries.Add(new Country { callingCode = "+49", name = "Germany" });
        countries.Add(new Country { callingCode = "+33", name = "France" });
        countries.Add(new Country { callingCode = "+55", name = "Brazil" });

        Country country = countries.FirstOrDefault(x => x.name.ToLower() == TextBox1.Text.Trim().ToLower());
        if (country != null)
            Label1.Text = "Calling code of " + country.name + " is " + country.callingCode;
        else
            Label1.Text = TextBox1.Text + " Not Found in our Country List";
    }
}
public class Country {   
    public string callingCode { get; set; }
    public string name { get; set; }
}

Here is main method is  "FindCallingCode()" and class "Country". Here I am doing all operations. Lets look at some important code.

Country class: This class I have defined to create country objects which will store country name  and calling code. If you need extra properties you can add these easily.

List<Country> countries = new List<Country>();

In the above code I have defined a list (array) of county objects with name countries. Naming convention using as this variable holding multiple country hence its plural name of country.

countries.Add(new Country { callingCode = "+91", name = "India" });

In the above line of code I have adding element of array by creating country class object.

Country country = countries.FirstOrDefault(x => x.name.ToLower() == TextBox1.Text.Trim().ToLower());

This is actually Lamda expression "x=>x.name" this is doing searching operation with method "FirstOrDefault". This line searching all element and doing comparison with user entered country name and stored country name. When matched it will return the country object.

Label1.Text = "Calling code of " + country.name + " is " + country.callingCode;

 In previous line of code we have found country object from array of countries and now getting calling code and name from the found object.

In my chat application I have used mainly this (FirstOrDefault) method and for some cases have used "Count", "Find" methods/properties. "Find" and "FirstOrDefault" both can do a bit similar work but "Find" is very fast (in a blog I found 1000 times) than "FirstOrDefault", hence I am using as and when these are suitable.
For your knowledge you can check other methods as well.

Thanks for reading my blog please visit again for next article.

Source code is available here.

Send client message to server using SignalR method - Realtime Chat application on web in ASP.Net: Step 3

This will be my 3rd post on SignalR technology. In previous post I shown how to get server time using SignalR client method, which will be called from server side. Now I shall show how to transfer data between server and client.

This is very simple solution and it can do as same way as normal .Net method calling with parameter.

I am not explaining these code again as I did it last time.

Startup.cs

using Microsoft.Owin;
using Owin;
[assembly: OwinStartup(typeof(SignalRChat.Startup))]
namespace SignalRChat
{
    public class Startup
    {
        public void Configuration(IAppBuilder app)
        {
            // Any connection or hub wire up and configuration should go here
            app.MapSignalR();
        }
    }
}

ChatHub.cs file code:

using System;
using System.Web;
using Microsoft.AspNet.SignalR;
namespace SignalRChat
{
    public class ChatHub : Hub
    {
        /// <summary>
        /// getservertime
        /// </summary>
        public void getservertime()
        {
            Clients.Caller.serverresponse("This is server response. Server is calling client method from server. <br/> Server time is: " + DateTime.Now.ToShortTimeString());
        }

        public void sendmessage(string usermessage)
        {
            Clients.Caller.serverresponse(usermessage + " :This is server message <br/>");
        }
    }
}

In this code I have added new method "sendmessage(parameter1)" and it will call same client method to return data to client. This is very simple code.


Next will come HTML part, which is as below:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
   
    <title>SignalR Simple Chat</title>
    <style type="text/css">
        .container {
            background-color: #99CCFF;
            border: thick solid #808080;
            padding: 20px;
            margin: 20px;
        }
    </style>
    <meta http-equiv="X-UA-Compatible" content="IE=edge" />

    <!--Reference the jQuery library. -->
    <script src="Scripts/json2.js"></script>
    <script src="Scripts/jquery-1.10.2.js"></script>
    <script src="Scripts/jquery-1.10.2.min.js"></script>
   
    <!--Reference the SignalR library. -->
    <script src="Scripts/jquery.signalR-2.0.0.js"></script>
    <script src="Scripts/jquery.signalR-2.0.0.min.js"></script>
   
    <!--Reference the autogenerated SignalR hub script. -->
    <script src="/signalr/hubs"></script>
    <!--Add script to update the page and send messages.-->
   
    <script type="text/javascript">
$(function () {
    // Declare a proxy to reference the hub.
    var chat = $.connection.chatHub;


    //Write your server response related code here
    chat.client.serverresponse = function (message) {
        $('#dvServerResponse').append(message);
    };

    // Start the connection.
    $.connection.hub.start().done(function () {
        //Write your server invoke related code here
        $('#btnHello').click(function () {
            console.log('call server');
            chat.server.sendmessage($('#txt').val());
        });
    });
});
    </script>

</head>
<body>
    <form id="form1" runat="server">
    <div>
<div>
    Enter your message: <input type="text" id="txt" /><br />
    <input type="button" id="btnHello" value="Send message to server" />   

    <div id="dvServerResponse"></div>   
   
</div>
    </div>
    </form>
</body>
</html>

When you will run this in browser it will show as below:

Hope you have enjoyed this post and please visit my blog again.

Next will show something about Lamda Expression which I shall use in my chat application.

Get Server time - Realtime Chat application on web in ASP.Net using SignalR Technology: Step 2

In previous post I have describe how to set up your project for SignalR with require software. Now I shall show to get server time and how can invoke a server method as request and how can get response with server time by calling client javascript method from server end.
Again here will be these main part of coding and here I am not changing anything in Startup.cs file. This code as it is.

using Microsoft.Owin;
using Owin;
[assembly: OwinStartup(typeof(SignalRChat.Startup))]
namespace SignalRChat
{
    public class Startup
    {
        public void Configuration(IAppBuilder app)
        {
            // Any connection or hub wire up and configuration should go here
            app.MapSignalR();
        }
    }
}

Next is ChatHub.cs and here is very small code as below:

using System;
using System.Web;
using Microsoft.AspNet.SignalR;
namespace SignalRChat
{
    public class ChatHub : Hub
    {
        public void getservertime()        {
            Clients.Caller.serverresponse("This is server response. Server is calling client method from server.
Server time is: " + DateTime.Now.ToShortTimeString());
        }
    }
}

Here I have added on public method "getservertime()". Note here I have written all method in small letters and shall call this from client javascript in small letters only. Previously I have tested by writing in capital letters and have seen this is not working. So I would suggest to write these methods always in small letters only, to avoid difficulties.

"getservertime()" is very small method which will invoked by client on button press and this method will invoke a client's javascript method to send response to client. Note here I am writting 'Clients.Caller' to find out the client from where server method has invoked. If I need to call any different client's method (usually for chat application one user will send method to other, so one client will invoke server method and server method will call other client's client method. These things I shall show later) then there will be some different way, which I shall explain later. After 'Client.Caller' method name is coming that is "serverresponse()". This client method is passing argument and this will deliver server message to client.

Next I shall explain about client code and these all are HTML and Javascript only.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
   
    <title>SignalR Simple Chat</title>
    <style type="text/css">
        .container {
            background-color: #99CCFF;
            border: thick solid #808080;
            padding: 20px;
            margin: 20px;
        }
    </style>
    <meta http-equiv="X-UA-Compatible" content="IE=edge" />

    <!--Reference the jQuery library. -->
    <script src="Scripts/json2.js"></script>
    <script src="Scripts/jquery-1.10.2.js"></script>
    <script src="Scripts/jquery-1.10.2.min.js"></script>
   
    <!--Reference the SignalR library. -->
    <script src="Scripts/jquery.signalR-2.0.0.js"></script>
    <script src="Scripts/jquery.signalR-2.0.0.min.js"></script>
   
    <!--Reference the autogenerated SignalR hub script. -->
    <script src="/signalr/hubs"></script>
    <!--Add script to update the page and send messages.-->
   
    <script type="text/javascript">
$(function () {
    // Declare a proxy to reference the hub.
    var chat = $.connection.chatHub;


    //Write your server response related code here
    chat.client.serverresponse = function (message) {
        $('#dvServerResponse').append(message);
    };

    // Start the connection.
    $.connection.hub.start().done(function () {
        //Write your server invoke related code here
        $('#btnHello').click(function () {
            console.log('call server');
            chat.server.getservertime();
        });
    });
});
    </script>

</head>
<body>
    <form id="form1" runat="server">
    <div>
<div>
    <div id="dvServerResponse"></div>   
    <input type="button" id="btnHello" value="Get Server Time" />   
</div>
    </div>
    </form>
</body>
</html>

These HTML and Javascript code I have written in ASP.Net file however this will work in normal HTML file as well.
Initial part is javascript library file referencing section. Here I am calling files in three steps, first JSON and JQeury files, next SignalR library and finally Hub files. Next starting custom java script codes.

In java script code firstly I have created a proxy of chatHub. Next code is

//Write your server response related code here

    chat.client.serverresponse = function (message) {

        $('#dvServerResponse').append(message);

    };

For time being you can assume this is standard syntax of creating client methods which will be called from server and within this cal implement client activities. For my case I am appending server response at div.

Other part of client codes as below

//Write your server invoke related code here

$('#btnHello').click(function () {

    console.log('call server');

    chat.server.getservertime();

});

This method will start executing once btnHello clicked and it will invoke server method to get server response. Here to invoke server method syntax like "chat.server.getservertime()" or "proxy-object.server.server-method()".

Finally result will displayed in client browser.

Here is the full code.

Thanks for reading my blog.

SignalR - Hello World: Real time chat application on Web in ASP.Net using SignalR technology : Step 1

Programming with distributed technology is my passion. Earlier I have worked with Remoting, Socket, Web Service and a bit with WCF. So when I first came to know about SignalR technolog, really I can not wait to start coding on it. Now I can able to write own chat application for AlapMe and really its working fine. Most of all by using SignalR I can use socket of HTML5. I shall write multiple posts on SignalR as tutorial as I did in past with C# Socket programming. I shall publish these posts on this blog and on my socket programming blog as well.
I have started SignalR programming with Visual Studio Express 2013 which is freely downloadable from here. Also you can read full documentation from Microsoft from this link. As my target to share some practical example with ready to use code hence I shall not describe in detail about the technology. You can follow Microsoft documentation website for it.
To start with SignalR first coding you need to follow steps are below.
Lunch your VS 2013 and create a blank project and add new item of SignalR Hub Class (v2) set its name as you wish, for my case its MyHub.cs. When you will add this class Visual Studio automatically add all related references and jquery classes. To add these things it may take some time may be 2-3 minutes. This new item will come with a sample method 'Hello()', for now just follow these later I shall describe what is these things and why these are. Full code will look like below:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using Microsoft.AspNet.SignalR;

namespace SignalR_Learning

{

    public class MyHub : Hub

    {

        public void Hello()

        {

            Clients.All.hello();

        }

    }

}

Now go to next step and add next item in project, that is OWIN Startup class. I am giving its default name 'Startup1.cs' for my sample project. Visual Studio will create this file with some pre-added code. We will extend these code as per your needs. Code as below:

using System;

using System.Threading.Tasks;

using Microsoft.Owin;

using Owin;

[assembly: OwinStartup(typeof(SignalR_Learning.Startup1))]

namespace SignalR_Learning

{

    public class Startup1

    {

        public void Configuration(IAppBuilder app)

        {

            // For more information on how to configure your application, visit http://go.microsoft.com/fwlink/?LinkID=316888

        }

    }

}

In method Configuration I shall add just a line of code 'app.MapSignalR();' so full method will look like:

public void Configuration(IAppBuilder app)

{

    // For more information on how to configure your application, visit      http://go.microsoft.com/fwlink/?LinkID=316888

    app.MapSignalR();

}

 Now we will move to front end coding and this part will develop with simple HTML page, however you can use ASPX page as well. Now I have added a HTML page with name 'index.html'

<! DOC TYPE ht ml >

< ht ml xmlns="http://www.w3.org/1999/xhtml" >

    SignalR Simple Chat

    "X-UA-Compatible" content="IE=edge" / >

    

    

     < sc ript src="Scripts/jquery-1.10.2.js">

    < sc ript src="Scripts/jquery-1.10.2.min.js">

   

    < scri pt src="Scripts/json2.js">

    

    "Scripts/jquery.signalR-2.0.0.js">

    "Scripts/jquery.signalR-2.0.0.min.js">

    

    "/signalr/hubs">

    

    "text/ ">

        var chat;

        $(f unction () {

            // Declare a proxy to reference the hub.

            //chat = $.hubConnection.myhub;

            chat = $.connection.myhub;

            //alert(chat);

            chat.client.hello = function (message) {

                alert(message);

            };

            // Start the connection.

            $.connection.hub.start().done(function () {

                $('#btnhello').click(function () {                   

                    alert(chat);

                });

            });

        });

    

    "button" value="Hello" id="btn hello" />

 

 

Excel-VBA: Find Last Cell of Active WorkSheet

Hello here is the code

Public Function GetLastCellAddress() As String
    ' Variables
    Dim intCol As Integer
    Dim lngRow As Long
    Dim rUsedRange As Range
    Dim rCell As Range

    ' first unlock all cells
    Cells.Locked = False
    If WorksheetFunction.CountA(Cells) > 0 Then
        lngRow = Cells.Find(what:="*", After:=[A1], _
                            SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        intCol = Cells.Find(what:="*", After:=[A1], _
                            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        Set rUsedRange = ActiveSheet.Range("$A$1" & ":" & Cells(lngRow, intCol).Address)
       
        lngRow = Cells.Find(what:="*", After:=[A1], _
                            SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        intCol = Cells.Find(what:="*", After:=[A1], _
                            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        Set rUsedRange = ActiveSheet.Range("$A$1" & ":" & Cells(lngRow, intCol).Address)
        GetLastCellAddress = rUsedRange.Address
    End If
End Function

Real time chat application on Web in ASP.Net using SignalR technology : Introduction

I shall start posting code and description about how to create web chat application in asp.net using .Net's latest technology SignalR step by step. Also I shall share how to create server event based application. Already I have developed this chat server and using in my social website http://alap.me.

However I shall start posting on it step by step so a learner can learn it easily. I shall start from very begging with many small small things and shall cover ASP.Net parts and related stuff like jQuery, CSS, HTML5 or normal java script. Because all of these things will require to build full complete chat application.

The same code I shall share which I have used in my social network site chat part. I shall publish my code as open source so any one can use it in his application and any one can enhance it so other people can use it as like Linux did in early days. To use my chat application code just they need to mention in somewhere in this website (say at footer) a thanks to me with my personal website url that is http://sumanbiswas.com and just drop me a mail that they are using my code for my information. Whole code will be totally free of cost.

So this is my introduction of chat application source code and from next I shall post it by step by step.