Working With Micro-ORM Dapper In MVC

29. May 2012 12:50

ASP.NET MVC 

I love working with MVC but I do have a hard time working with complex data with EntityFramework (EF). I heard of using inline SQL queries in MVC but don't know how to use it and will it have any performance issues? Few month's back I downloaded the StackOverflow (SO) Data Explorer which is an open-source project built on MVC to explore data dump provided by SO. The thing that took my attention in that code was that they have been SQL queries to get the data from the database. This looks promising to me as the guys at SO will never compromise with performance. SO Data Explorer uses the micro-ORM called Dapper which is authored by Sam Saffron of Stack Overlfow.

Getting Dapper

Dapper is also available on NuGet or you can also drop this single file in your project which is hosted at Google Code. You can use the below NuGet command to install Dapper.

Usage

Performing CRUD operations in EF is simple for simple scenarios, but when the required data is complex I prefer using SQL queries with joins and other related stuff which make my work far much easier. To demonstrate how to use Dapper to perform CRUD operations, I am building a simple application to manage contacts. I started up with creating a model named ContactModel. You can copy the below code or you can have one for yourself.

public class Contacts
{
    [Key]
    public int ContactID { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string Email { get; set; }
    public string Skype { get; set; }
}

The model is ready but we are not using EF Code First which created the database for us on the fly and therefore I have a bit more work to do. I'll now create a database with a table which is same like my model. After the table has been created now I will add a new class to my project where I will write functions which will help me in performing CRUD operations. I will talk about view and controllers later when I am done with my DB access class. For this sample application I have used SQL CE 4.0 DB, but if you wish you can use SQL Server DB.

At the first, I just want to list down the contacts I have in the database. Practically this would have been the last thing to show in the example but I am showing this at first so you get an idea how Dapper works. As we have fresh database with no records, write down and insert query or just simply punch in the data in. Now in my data access class I will create a simple function which returns me the collection of contacts.

try
{
    using (SqlCeConnection con = new SqlCeConnection(ConStr))
    {
        con.Open();
        var contacts = con.Query<Contacts>("Select Name, Address, Email, Skype from Contacts");
        return contacts;
     }
}
catch (Exception x)
{
    throw x;
}

The above functions returns the collection of the contacts which I will use in my Index view where I am going to list down all the contacts I have with me (See the SQL query :) ). Add a new controller or use the existing Home controller and add the below ActionResult method ListContacts.

public ActionResult ListContacts()
{
    var db = new DB();
    return View(db.GetContacts());
}

I now must have a view which will display the list of all the contacts. Right-click on ListContacts() method and choose Add View. A pretty familiar dialog pops up like the one below.

Nothing fancy here, just select the model class as the Contacts and Scaffold template as List. Hit F5 and change the URL to {View}/ListContacts and you will be able to see the list of contacts.

Performing INSERT/UPDATE and DELETE operations

To insert the data in the database create the below method in the DB access class:

public bool CreateContact(Contacts contact)
{
    try
    {
        using (SqlCeConnection con = new SqlCeConnection(ConStr))
        {
            con.Open();

            string sqlQuery = "INSERT INTO Contacts([Name],[Address],[Email],[Skype]) VALUES (@Name,@Address,@Email,@Skype)";
            con.Execute(sqlQuery,
                                  new
                                  {
                                      contact.Name,
                                      contact.Address,
                                      contact.Email,
                                      contact.Skype
                                  });
        }
        return true;
    }
    catch (Exception x)
    {
        return false;
    }
}

I have a normal INSERT statement which will insert the records for me. The point that you need to note is that it is executed by the Execute extension method of the SqlCeConnection class. If you use SQL Server then the class will be SqlConnection class. Returning to controller and just to remind you that if we were working with EF which would have done the CRUD part for us then you must be aware that there are 2 ActionResult methods which were required for this purpose. One will just return the view and the other one with [HttpPost] attribute will create the record. I require a create view so I can add contacts. Follow the same steps you have followed above while creating the ListContacts view. For your further help here are the 2 ActionResult methods to create a new record (I am sure you can create a view!! Just right-click Create() method and click AddView and the scaffold is Create).

public ActionResult Create()
{
    return View();
}

[HttpPost]
public ActionResult Create(Contacts contact)
{
    var db = new DB();
    bool isCreated = db.CreateContact(contact);
    if (isCreated)
    {
        return RedirectToAction("ListContacts");
    }
    else
    {
        return View();
    }
}

The process of updating a contact will be similar like we have done for the insert. Before updating a contact I need to get the details of the contact when the user clicks on the Edit link on the page. When the user clicks the Edit hyperlink on the list page, the contact ID will be paased as a parameter.

public Contacts GetContactByID(int ContactId)
{
    using (SqlCeConnection con = new SqlCeConnection(ConStr))
    {
        con.Open();
        string strQuery = string.Format("Select ContactID, Name, Address, Email, Skype from Contacts where " +
        "ContactID={0}", ContactId);
        var customer = con.Query<Contacts>(strQuery).Single<Contacts>();
        return customer;
    }
}

public bool UpdateContact(Contacts contact)
{
    try
    {
        using (SqlCeConnection con = new SqlCeConnection(ConStr))
        {
            con.Open();
            string sqlQuery = "UPDATE Contacts SET Name =@Name, Address =@Address, Address = @Address, " +
            "Email = Email, Skype = @Skype WHERE ContactID=@ContactID";
            con.Execute(sqlQuery, new
            {
                contact.Name,
                contact.Address,
                contact.Email,
                contact.Skype,
                contact.ContactID
            });
        }
        return true;
    }
    catch (Exception)
    {
        return false;
    }
}

The GetContactByID method will accept the contactId which is passed as a parameter to the Edit ActionResult method. I am calling this method from the Edit ActionResult. The main purpose of this method is to fetch the contact details on the basis of the ID passed to it. The returned Contacts serves as a model to the Edit view which then populates the details of the contact I want to edit. I assume here you know how to create a View. Here is my Edit and Edit with HttpPost which does the actual update work. The UpdateContact method will get called from the Edit ActionResult method which is attributed with HttpPost.

public ActionResult Edit(int ContactID)
{
    var db = new DB();
    return View(db.GetContactByID(ContactID));
}

[HttpPost]
public ActionResult Edit(Contacts contact)
{
    var db = new DB();
    db.UpdateContact(contact);
    return RedirectToAction("ListContacts");
}

Following the same procedure that I followed for insert and update I am going to implement the delete functionality. The below code will be used to delete the contact details.

public bool DeleteContact(int ContactID)
 {
     try
     {
         using (SqlCeConnection con = new SqlCeConnection(ConStr))
         {
             con.Open();
             string sqlQuery = "DELETE FROM Contacts WHERE ContactID=@ContactID";
             con.Execute(sqlQuery, new { ContactID });
         }
         return true;
     }
     catch (Exception)
     {
         return false;
     }
 }

And for your ease, here are the 2 ActionResult methods.

public ActionResult Delete(int ContactID)
{
    var db = new DB();
    return View(db.GetContactByID(ContactID));
}

[HttpPost]
public ActionResult Delete(int ContactID, FormCollection frm)
{
    var db = new DB();
    bool IsDeleted = db.DeleteContact(ContactID);
    if (IsDeleted)
    {
        return RedirectToAction("ListContacts");
    }
    else
    {
        return View();
    }
}

This is it. A simple application where you can perform CRUD operations using SQL queries with micro-ORM Dapper.

Performance

Another reason for using a micro-ORM, Dapper in my case, but if you check the documentation you can see the table which clearly state the difference between the different ORMs.

There are other ORMs that you can try like the Massive ORM and PetaPOCO.

 No Rating

Get Song Lyrics From LYRDB With ASP.NET MVC And JQuery

26. November 2011 14:59

API ASP.NET MVC 

Most of the times I have seen people looking for song lyrics on online MP3 or lyrics database sites. Few of them provide API to fetch lyrics which is good for developers. Last.fm has a powerful API but they don't provide any API call to fetch lyrics. The two most popular APIs on the web that I am aware of is WikiLyrics, LyricsDB (popularly known as LyrDB), there is another API from ChartLyrics but I don't find it as useful as the other two APIs. The WikiLyrics API works beautifully, but it has one drawback the lyrics fetched from the service is incomplete. I wrote a complete desktop application to fetch lyrics, album art, artist image, album name and album release date but never made it available to download for public. I am planning to release it to general users. But till then you can take a look at this simple little app which I created in my spare time to fetch lyrics from LyrDB.

This little simple application is in ASP.NET MVC. I am not a designer so I will take advantage of the default design. In the home controller I have a simple method named GetLyrics which accepts two parameters Artist Name and Song Name. The method returns results in JSON as it will be good for me to parse the results in JQuery. Take a look at the code:

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult GetLyrics(string ArtistName, string SongName)
{
            string response = string.Empty;
            string LyrURL = string.Empty;
            string url = "http://webservices.lyrdb.com/lookup.php?q=" + ArtistName + "|" + SongName + "&for=match";
            WebResponse wResp = (WebRequest.Create(url) as HttpWebRequest).GetResponse();
            using (StreamReader sReader = new StreamReader(wResp.GetResponseStream()))
            {
                response = sReader.ReadToEnd();
            }

            if (response == "")
            {
                response = "No Lyrics found on LyrDB for " + SongName + " by " + ArtistName;
                goto Output;
            }

            string[] lyrID = response.Split('\\');

            response = "";

            LyrURL = "http://webservices.lyrdb.com/getlyr.php?q=" + lyrID[0]+"&callback=?";

            Output:

            return Json(new
            {
                success = true,
                Lyrics = LyrURL
            });
}
The above code does not return the lyrics but the URL to get the lyrics. I am a big Jquery fan and as usual I will be using it here to make an AJAX call and load an IFRAME with the lyrics URL.
$(document).ready(function () {
        $("#btnSearch").click(function () {
            $("#btnSearch").attr('disabled', true);
            $("#lyrics").addClass("loading");
            $("#LyrFrame").attr('src', '');
            var Artist = $("#txtArtistName").val();
            var Song = $("#txtSongName").val();
            var url = "/Home/GetLyrics?ArtistName=" + Artist + "&SongName=" + Song;
            $.ajax({
                url: url,
                type: "POST",
                dataType: "json",
                data: "{'ArtistName': " + Artist + ",'SongName': " + Song + "}",
                success: function (data) {
                    $("#lyrics").html("<iframe id=\"LyrFrame\" src=\"" + data.Lyrics + "\" marginwidth=\"0\" marginheight=\"0\" frameborder=\"0\" vspace=\"0\" hspace=\"0\" width=\"980\" height=\"750\"></iframe>");
                    $("#btnSearch").attr('disabled', false);
                    $("#lyrics").removeClass("loading");
                }
            });

        });
});

The URL in the above script calls GetLyrics method which is inside Home Controller takes artist and song name as query string. On the success of the call the IFRAME gets loaded with the lyrics URL. The output will look something like this:

 

You can download the application source code from the below link, but I want to tell you that it will not fetch each and every song lyrics for you. It's a database and it can provide us only information it has. But the desktop application I am will release in future will check around 2 lyrics API at least and I believe that my application guarantees 100% result. So follow my upcoming posts.

Download: LyrDBApp.zip (886.46 kb)

Related Links:

 No Rating

Upload and Show Image Without Post Back With Jquery

11. October 2011 11:20

ASP.NET ASP.NET MVC Jquery Web 

Previously in a lot of hurry I wrote a post on how to upload files in MVC3 with the help of Uploadify, a famous jQuery plugin to upload files. If you have not heard about it yet, then it is time for you to check and find out what this plugin can do for you. Appending my last post about this plugin (which was not very well written) I tried one more thing with it today. This time I am trying to upload an image and then show it inside a DIV without a post back. If you have the source code from the previous post, then it will be good as you need not to start from the scratch.

I assume that you are a good designer as I am not. I am saying this because I really got a blank head in designing. Therefore it is up to you how you are going to show off the images on the page, I am just telling you a way to do that. I am using MVC3 for this example but it will work for both web forms and MVC. The only difference in MVC and web forms is in the server side code which is responsible to upload the file. If you are using MVC3 then your method to upload the file in the controller will be something like this:

public string Upload(HttpPostedFileBase fileData)
{
        var fileName = this.Server.MapPath("~/uploads/" + System.IO.Path.GetFileName(fileData.FileName));
        fileData.SaveAs(fileName);
        return "OK";
}

As we are using Uploadify, we now have to make changes in some of the parameters of the plugin. The Upload method is inside home controller, so the script parameter of the plugin will be /Home/Upload. For my convenience I have changed the multi and auto parameter to false and true respectively. By now this is the jQuery code I have:

<script type="text/javascript">
$(window).load(
function () {
    $("#fileuploader").fileUpload({
        'uploader': '/Scripts/uploader.swf',
        'cancelImg': '/Images/cancel.png',
        'buttonText': 'Select Image',
        'script': 'Home/Upload',
        'folder': '/uploads',
        'fileDesc': 'Image Files',
        'fileExt': '*.jpg;*.jpeg;*.gif;*.png',
        'multi': false,
        'auto': true
    });
}
);
</script>

In the end I append one more parameter onComplete which gets fired when the plugin finish uploading the file. To get the uploaded path of the image I am using fileObj.filePath and response to check if the file has been uploaded successfully or not. If the response I received after the uploading of the file is completed is ok then I will set the html to show the image in another DIV. Below is the complete configuration of the plugin.

<script type="text/javascript">
$(window).load(
function () {
    $("#fileuploader").fileUpload({
        'uploader': '/Scripts/uploader.swf',
        'cancelImg': '/Images/cancel.png',
        'buttonText': 'Select Image',
        'script': 'Home/Upload',
        'folder': '/uploads',
        'fileDesc': 'Image Files',
        'fileExt': '*.jpg;*.jpeg;*.gif;*.png',
        'multi': false,
        'auto': true,
        'onComplete': function (event, ID, fileObj, response) {
            if (response == "OK") {
                $("#uImg").addClass("loading");
                var htmlString = "<img src=\"" + fileObj.filePath + "\" alt=\"" + fileObj.name + "\" />";
                $("#uImg").html(htmlString);
                $("#uImg").removeClass("loading");
            }
        }
    });
}
);
</script>

Just to demonstrate I have placed an alert message box with the response which got fired on the onComplete event after the image has been uploaded successfully. After this I have created a html string which is actually a html img tag with src and alt tags. I have then set the same html string to another DIV which acts as a holder of the image being uploaded. Here is an output after the image is uploaded. Just to remind you, it is all done without post back.

This is it, try it yourself and I recommend you to check out the documentation of the plugin I used. Check out the other links below and one more thing that I would like you to do is to set the multi property of the plugin to true and the try uploading multiple images. If you end up making something awesome then do share with me/us.

Related Links:

Currently rated 4.6 by 8 people

Integrate DISQUS For Comment Management In MVC

3. September 2011 18:02

API ASP.NET MVC 

I have been working on a MVC project which I will be using to save and manage all my code snippets. I get this plan in my head when my 160GB of HDD got crashed which holds most of my work and code. I started writing the web application in web forms and almost got it completed, but eventually changed my mind and started writing the application from scratch in MVC 3 with Razor view engine.

As I will be making this application online, I am sure to receive some comments when I share my code with others. To know what people thing about the code I published, I must have a commenting system for my application to know what people think. Instead of writing my own comment mechanism I would rather want to go with DISQUS commenting system. For my blog I am not using DISQUS, but for this application I will use DISQUS as it will save my time and is also a reliable way to manage comments.

Working with MVC is fun and even more when you are working with helper classes. Read my blog post on Working with Microsoft Web Helpers in ASP.NET to know more about helper classes and their features and how they can save your time in developing applications. I am going to show you how you can integrate DISQUS commenting system in your MVC application with ease. To get started, register a DISQUS account

Create a new MVC project in Visual Studio and without any delay go to NuGet console and type in the below command to add the DISQUS helper class.

 
The DISQUS helper class has 2 methods which we will be going to use.
  • Initialize: This method will help us to initialize the commenting system. It accepts your short forum name as a parameter which you have created at the time of creating your account at DISQUS.
  • ShowComments: This method accepts the name of the page.
To initialize DISQUS, we need to set the Initialize method in _ViewStart.cshtml file. You can find this file under Views folder. Make sure that this is a first line in the file as we have to initialize before the layout of the page gets rendered.
@{
    Disqus.Initialize("shortforumname");
 }
Now as we have initialized, we can show the comments on the page. To show the comments on the page we will use the ShowComments method and pass the page name in the parameter. Just to be more precise about this parameter, I would like to point out that on a forum, blog or on a website every page has a unique name or forum post id or blog post and the parameter that we are going to pass to this method should also be the same unique post id or page name.
@Disqus.ShowComments(pageIdentifier: "post123")
This will render the DISQUS comments as shown below:
Someone will now make a comment here and move to another page and then make another comment. The only way DISQUS has to identify the comments on the basis of the PageIdentifier parameter in the ShowComments method. When some other user requests the same page again from a different geographical location, the DISQUS will check the PageIdentifier and then load the related comments.
I hope this post gives you an overview on how you can integrate DISQUS commenting system on you site in MVC.
Currently rated 4.5 by 2 people

Using ReCaptcha In ASP.NET WebForms And MVC

31. July 2011 14:58

API ASP.NET ASP.NET MVC 

ReCaptcha is now being used widely by many websites especially where there is a possibility of spamming the website, blog or a forum. Here I will show you on how you can use ReCaptcha in ASP.NET and MVC. Get a ReCaptcha Gobal Key and ASP.NET ReCaptcha DLL. Once you get the global key we are ready to go.
 
ReCaptcha in WebForms
 
Fire Visual Studio and create a new ASP.NET web forms project. First add a ReCaptcha DLL in the project and as we are talking about a user control we will have to register the control. On the top of the page use the register directive to register the ReCaptcha control.
<%@ register tagprefix="recaptcha" namespace="Recaptcha" assembly="Recaptcha" %>
Now on the page, we will use the control.
<recaptcha:recaptchacontrol id="recaptcha" runat="server" publickey="public key"  privatekey="private key" />
You will get the public key and the private key when you register for the ReCaptcha global key. This will render the ReCaptcha control on the page.
Notice that I have also placed a button and the label (with no text) on the page along with the ReCaptcha control. On the submit button we will check the Page.IsValid method. If the ReCaptcha text entered by the user is correct then the Page.IsValid method will return true else it will return false. Therefore the code on the submit button will be as follows:
if (Page.IsValid)
{
      lblStatus.Text = "Captcha Validated!";
}
else
{
      lblStatus.Text = "Invalid Captcha!";
}
This is it, hit F5 and try it out. We are now done implementing ReCaptcha in web forms and now we will see it in action in MVC.
 
ReCaptcha in MVC
 
Create a new MVC project in Visual Studio and I am using MVC 3 with RAZOR View Engine. To implement ReCaptcha in MVC, we will be using Microsoft Web Helpers. I assume you are aware of NuGet. Just in case if you are not aware of the Microsoft Web Helpers read my post on Working With Microsoft Web Helpers In MVC 3 Razor View. Microsoft web helpers allow us to use ReCaptcha in a very easy way. In WebForms we have used a DLL to render the ReCaptcha control on the page, but with Microsoft web helpers we can render the control with a single line of code. As you have used NuGet to install Microsoft Web Helpers library, make sure that you also use the correct namespace on the top of the page.
@using Microsoft.Web.Helpers;

@using (Html.BeginForm("Submit", "Home", FormMethod.Post))
{
    @ReCaptcha.GetHtml(publicKey: "<public key>", theme: "red")
}
I have rendered the control inside the form because when the data gets submitted after user enters the text the Submit method of the Home controller gets fired and check the captcha text entered by the user. As you know we have two keys with us, one is a public key and the other is a private key. We have used the public key to render the control on the page and we will now use the private key to validate the captcha. Also make sure that in order to use the ReCaptcha class on server side code you need to add the Microsoft.Web.Helpers namespace.
[HttpPost]
public ActionResult Submit()
{
        if (ReCaptcha.Validate(privateKey: "<private key>"))
        {
             return Content("Valid Captcha");
        }
        else
       {
            return Content("InValid Captcha");
       }
}
The ReCaptcha class of the web helpers provides a method to validate the entered text by the user. The private key of the ReCaptcha is passed as a parameter in order to validate the captcha text.
Using ReCaptcha with ASP.NET WebForms or MVC is pretty simple and easy. Here are some of the links which I feel you might find useful.
Currently rated 5.0 by 1 person