.NET Development

Using HTML 5 and the Web API for AJAX file uploads with image preview and a progress bar

Gal Ratner — Fri, 22 Mar 2013 21:52:00 GMT

AJAX file uploads used to be a client / server process in HTML 4. They required a round trip to a server side module, usually using an IFRAME and constant querying of the number of bytes uploaded, then reloading a progress bar inside the IFRAME with the current progress value.
Luckily HTML 5 has a level 2 XMLHttpRequest object that natively support file uploads. This makes it a breeze to give your users a great experience when uploading files to your application.

In this article we are going to use it to show an end to end process of previewing an image and uploading it to the server while showing a progress bar.
Technologies we will be using include:

HTML 5 File API
HTML 5 XMLHttpRequest Level 2
jQuery
jQuery UI Progressbar
MVC ApiController
Web API’s MultipartFormDataStreamProvider

Let’s start by building our view. At the very least we will need an input file, a preview div and a submit button. In this example I also use a few more parameters that serve as hints for image processing: a caption text, font size and type and color.

@{
    ViewBag.Title = "Home Page";
}
@section featured {
    <form name="form1" method="post" enctype="multipart/form-data" action="api/uploads">
    <section class="featured">
        <div class="content-wrapper">
            <hgroup class="title">
                <h1>@ViewBag.Title.</h1>
                <h2>@ViewBag.Message</h2>
            </hgroup>
            <p>
                Upload<input id="imageFile" type="file" />
            </p>
        </div>
    </section>
}
<h3>Write something:</h3>
<ol class="round">
     <div id="imageParams" style="width:100%;height:50%;">
        <div id="imageText" style="width:50%;float:left">
            <textarea name="styled" id="styled" onfocus="this.value=''; setbg('#e5fff3');" onblur="setbg('white')">Enter Text here...</textarea>
        </div>
        <div id="textparams" style="width:50%;float:left">
            Pick a text color: <input id="textcolor" name="textcolor" class="color" value="ffffff" /><br />
            <div id="fontName">Pick a Font:
                            <input type="radio" id="Tahoma" name="fontNameRadio" value="Tahoma" onchange="changeFont(this);" checked /><label for="Tahoma" style="font-family:Tahoma">Tahoma</label>
                            <input type="radio" id="Arial" name="fontNameRadio" value="Arial" onchange="changeFont(this);" /><label for="Arial" style="font-family:Arial">Arial</label>
                            <input type="radio" id="Verdana" name="fontNameRadio" value="Verdana" onchange="changeFont(this);" /><label for="Verdana" style="font-family:Verdana">Verdana</label>
                            <input type="radio" id="Helvetica" name="fontNameRadio" value="Helvetica" onchange="changeFont(this);" /><label for="Helvetica" style="font-family: Helvetica">Helvetica</label>
                        </div><br />
            Pick a Font Size: <input id="fontSize" name="fontSize" value="12" />px<br /><br />
             </div>
        </div>
    <div id ="theOuptout" style="height:50%; width:100%; float:left">
        <div id="theBackground" style="background-repeat:no-repeat;background-position:left top; width:700px;height:700px;border:2px solid; position: relative; float:left; background-size: contain">
            <div id="caption">Enter Text here...</div>
        </div>
    </div>
</ol>
        <input id="actionButton" type="submit" onclick="return sendForm(this.form);" value="Upload">
        </form>
<div id="progressbar"></div>
@section scripts {
    @Scripts.Render("~/bundles/jqueryui")
    @Scripts.Render("~/bundles/jscolor")
    @Styles.Render("~/Content/themes/base/css")
    @Scripts.Render("~/bundles/index")   
}

Once we have our UI in place we need to add the view script logic and validation. In order to use minification, I have bundled the script:

bundles.Add(new ScriptBundle("~/bundles/index").Include(
                      "~/Scripts/Views/Index.js"));

And added it to the view:

@Scripts.Render("~/bundles/index")

Let’s take a look at Index.js

$(function () {
    var container = $('#theBackground')
    $("#caption").draggable({ containment: container }).resizable({ containment: container });
 
    $("#fontName").buttonset();
 
    $("#styled").keypress(function () {
        $("#caption").html($("#styled").val());
    });
 
    $("#textcolor").change(function () {
        $("#caption").css("color", "#" + $("#textcolor").val());
    });
 
    $("#fontSize").spinner({
        spin: function (event, ui) {
            $("#caption").css("font-size", ui.value);
        },
        max: 50,
        min: 0,
    });
    // Show an image preview
    document.getElementById('imageFile').addEventListener('change', handleFileSelect, false);
    
});
 
function changeFont(fontName) {
    $("#caption").css("font-family", fontName.value);
}
 
function handleFileSelect(evt) {
    var files = evt.target.files; // FileList object
 
    // Loop through the FileList and render image files as thumbnails.
    for (var i = 0, f; f = files[i]; i++) {
 
        // Only process image files.
        if (!f.type.match('image.*')) {
            continue;
        }
 
        var reader = new FileReader();
 
        // Closure to capture the file information.
        reader.onload = (function (theFile) {
            return function (e) {
                // Render thumbnail.
                $("#theBackground").css("background-image", "url('" + e.target.result + "')");
            };
        })(f);
 
        // Read in the image file as a data URL.
        reader.readAsDataURL(f);
    }
}
 
function sendForm(form) {
    if ($("#imageFile").val() == '') {
        alert('Please select an image');
        return false;
    }
    $("#progressbar").progressbar();
    var formData = new FormData(form);
    formData.append("offsetTop", $("#caption").position().top);
    formData.append("offsetLeft", $("#caption").position().left);
    formData.append("imageFile", document.getElementById("imageFile").files[0]);
    var xhr = new XMLHttpRequest();
    xhr.open('POST', form.action, true);
    xhr.upload.onprogress = function (e) {
        if (e.lengthComputable) {
            var percentComplete = (e.loaded / e.total) * 100;
            $("#progressbar").progressbar({ value: percentComplete });
        }
    };
    xhr.onload = function () {
        if (this.status == 200) {
            $("#progressbar").progressbar("destroy");
            location.href = '/Home/ViewImage/' + encodeURIComponent(this.response).replace(/[!'()]/g, escape).replace(/\*/g, "%2A");
        };
    };
    xhr.send(formData);
 
    return false; // Prevent page from submitting.
}

You can already see that when the DOM is loaded, we use jQuery and jQuery UI to make our image caption div resizable and resizable and, to make a buttonset out of our radio buttons and to initialize the progress bar.

Next we set up the preview. When a user selects an image, we use the File API to show the image to the user before we upload it.
Adding an event listener: document.getElementById('imageFile').addEventListener('change', handleFileSelect, false);

We create a FileReader:

var reader = new FileReader();

Use its onload event to set the result as the background of out preview div and then call its readAsDataURL to read the image.

When a user decides to submit the form and upload their image, we override the onSubmit event and use AJAX instead of the default submit action:

function sendForm(form) {
    if ($("#imageFile").val() == '') {
        alert('Please select an image');
        return false;
    }
    $("#progressbar").progressbar();
    var formData = new FormData(form);
    formData.append("offsetTop", $("#caption").position().top);
    formData.append("offsetLeft", $("#caption").position().left);
    formData.append("imageFile", document.getElementById("imageFile").files[0]);
    var xhr = new XMLHttpRequest();
    xhr.open('POST', form.action, true);
    xhr.upload.onprogress = function (e) {
        if (e.lengthComputable) {
            var percentComplete = (e.loaded / e.total) * 100;
            $("#progressbar").progressbar({ value: percentComplete });
        }
    };
    xhr.onload = function () {
        if (this.status == 200) {
            $("#progressbar").progressbar("destroy");
            location.href = '/Home/ViewImage/' + encodeURIComponent(this.response).replace(/[!'()]/g, escape).replace(/\*/g, "%2A");
        };
    };
    xhr.send(formData);
 
    return false; // Prevent page from submitting.
}

Values in the submitted forms include all regular form fields with the addition of our own custom values and the file selected by the user. The onprogress event is used to track and display the overall upload progress.

Now that we have all of the client code in place, we can move over to the server and use some pretty cool Web API features we are going to use an ApiController:

public class UploadsController : ApiController
    {
        public async Task<HttpResponseMessage> PostFormData()
        {
            // Check if the request contains multipart/form-data.
            if (!Request.Content.IsMimeMultipartContent())
            {
                throw new HttpResponseException(HttpStatusCode.UnsupportedMediaType);
            }
            string root = HttpContext.Current.Server.MapPath("~/Images/Uploads/");
            var provider = new MultipartFormDataStreamProvider(root);
 
            try
            {
                await Request.Content.ReadAsMultipartAsync(provider);
                foreach (MultipartFileData file in provider.FileData)
                {
                    //Add the original extention to the file
                    string newFileName = String.Format("{0}{1}", file.LocalFileName, Path.GetExtension(file.Headers.ContentDisposition.FileName.Replace("\"", "")));
                    File.Move(file.LocalFileName, newFileName);
                    // Do the image processing here and return a URL to view the image
                    ProcessImage(newFileName,
                        provider.FormData["styled"],
                        int.Parse(provider.FormData["offsetTop"]),
                        int.Parse(provider.FormData["offsetLeft"]),
                        provider.FormData["textcolor"],
                        provider.FormData["fontNameRadio"],
                        int.Parse(provider.FormData["fontSize"]));
                    return new HttpResponseMessage()
                    {
                        Content = new StringContent(Path.GetFileName(newFileName).Replace(".", "dot")),
                        StatusCode = HttpStatusCode.OK
                    };
                }
                return Request.CreateErrorResponse(HttpStatusCode.NotFound, "Error processing image");
            }
            catch (System.Exception e)
            {
                return Request.CreateErrorResponse(HttpStatusCode.InternalServerError, e);
            }
        }
 
        private void ProcessImage(string fileName, string caption, int captionTop, int captionLeft, string fontColor, string fontFamily, int fontSize)
        {
            if (string.IsNullOrWhiteSpace(caption))
                caption = "Something clever should have been here.";
            if (string.IsNullOrWhiteSpace(fontColor))
                fontColor = "#ffffff";
            Color color = System.Drawing.ColorTranslator.FromHtml(fontColor.StartsWith("#") ? fontColor : "#" + fontColor);
            PointF textPosition = new PointF((float)captionTop, (float)captionLeft);
            using (Bitmap bitmap = (Bitmap)Image.FromFile(fileName))
            {
                SolidBrush drawBrush = new SolidBrush(color);
                using (Graphics graphics = Graphics.FromImage(bitmap))
                {
                    using (Font drawFont = new Font(fontFamily, (float)fontSize))
                    {
                        graphics.DrawString(caption, drawFont, drawBrush, textPosition);
                    }
                }
 
                bitmap.Save(fileName + "tmp");//save the image file
            }
            File.Replace(fileName + "tmp", fileName, null);
        }
    }

System.Net.Http includes a MultipartFormDataStreamProvider. This provider is used with HTML file uploads for writing file content to a FileStream. The stream provider looks at the <b>Content-Disposition</b> header field and determines an output Stream based on the presence of a <b>filename</b> parameter.

string root = HttpContext.Current.Server.MapPath("~/Images/Uploads/");
var provider = new MultipartFormDataStreamProvider(root);

At this point our provider is set to save the files in our Uploads directory

await Request.Content.ReadAsMultipartAsync(provider);

Reads the content of the submitted form and saves the file uploaded to disk asynchronously. As a default, the files are renamed to a random name and contain no extension.

We can now use the rest of the form’s parameters to help us process the image as we like. In our case, we simply write a caption into it. When we are done, we compose an HttpResponseMessage containing he location of the new image to be returned to the client.
The client can then use the message and redirect the user to view the uploaded image. This time, served from the server.

 xhr.onload = function () {
        if (this.status == 200) {
            $("#progressbar").progressbar("destroy");
            location.href = '/Home/ViewImage/' + encodeURIComponent(this.response).replace(/[!'()]/g, escape).replace(/\*/g, "%2A");
        };
    };

Conclusion
As you can see, HTML 5 has made some significant improvements that are much needed in the web 2.0 world. The ability to access a local file and AJAX submit multipart forms are a much needed addition and are sure to make your code simpler and your users happier.

Use MVC 4 and OAuthWebSecurity to post into your users Facebook timeline

Gal Ratner — Thu, 27 Sep 2012 00:52:00 GMT

ASP.NET 4.5 and ASP.NET MVC 4 took an important step forward with the release of Visual Studio 2012.

In addition to the new features and improved performance, the main direction seems to be bundling less namespaces in the core framework and more official libraries on NuGet.

The main benefit of the new approach is the ability to update more code in an out-of-band release without being tied into the bi yearly .NET core release cycle.

In other words: Microsoft and third party vendors can update releases on weekly basis, developers can then update the dedicated NuGet package and use the most current version of the library.

Started writing an application with jQuery 1.6 and want to update? Just use the NuGet package manager to update to the latest jQuery version without manually replacing references.
This new direction opened the door to try and integrate with some of the more popular platforms today. Facebook, Twitter, Yahoo, Google and Bing.

All now have direct OAuth/OpenID integration from within .NET. If an API changes, an updated NuGet package can be released immediately.
One of the components to add the new OAuth/OpenID integration is the membership provider, adding the most requested feature: “Log in With”. Developers can now add a “Login with Facebook” button to a site and use the native .NET OAuthWebSecurity.RegisterFacebookClient method to specify a Facebook application to use.
Both MVC and Web Forms support the new functionality, however, they both go about it in different ways. Web Forms use the familiar membership provider classes and table scheme with the addition of the open source library DotNetOpenAuth and MVC is using a simpler and more flexible provider called SimpleMembershipProvider. For OAuth integration MVC 4 adds a class called OAuthWebSecurity and this class invokes DotNetOpenAuth. In fact if you create a new internet application and navigate into the AccountController, you can see the new classes in action.

There are many blog posts on how to use external clients in the login view so today I am going to take the integration one step forward.

I am going to post actions of users that are logged into my site using Facebook into their wall. The site I am going to use is a live site and is located here: http://yardsale-finder.com the idea being users list a yard sale, it shows up on the site and a post is showed on their Facebook timeline indicating they have just listed a new yard sale.

Let begin by adding our new application in Facebook

Now that we registered our application and received an appId and an appSecret we can register the new login with our RegisterAuth

public static void RegisterAuth()
        {
            Dictionary<string, object> FacebooksocialData = new Dictionary<string, object>();
 
            FacebooksocialData.Add("Icon", "/Images/fb.png");
 
            OAuthWebSecurity.RegisterFacebookClient(
                appId: FacebookHelper.appId,
                appSecret: FacebookHelper.appSecret,
                displayName: "Facebook", 
                extraData: FacebooksocialData);
        }

Notice we also added an image in order to replace the default login button with a nice Facebook icon.

Next let’s navigate to ExternalLoginCallback in AccountController. This method uses the OAuthWebSecurity and will generate all of the underlying pluming of OAuth. In this method I added two lines of code:

 AuthenticationResult result = OAuthWebSecurity.VerifyAuthentication(Url.Action("ExternalLoginCallback", new { ReturnUrl = returnUrl }));
            if (!result.IsSuccessful)
            {
                return RedirectToAction("ExternalLoginFailure");
            }
 
            // Save the accesstoken into session
            Session["accesstoken"] = result.ExtraData["accesstoken"];
            Session["id"] = result.ExtraData["id"];

Since the default OAuth process returns information in the provider redirect, we simply collect it into a session. We will need the id and accesstoken in order to further communicate with Facebook’s API.
As a user creates a new yard sale listing we can give then a choice: if they have logged in using their Facebook account, let’s ask them if they wish to publish the new listing into Facebook:

@if (Session["accesstoken"] != null){
        <div class="editor-label">
            <label for="addToFacebook">Submit to Facebook?</label>
        </div>
        <div class="editor-field">
            @Html.CheckBox("addToFacebook", new { data_val = "true" })
        </div>
        }

If they have checked the “Submit to Facebook” button we can call the Facebook API using the accesstoken we collected into the session

[HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Create(UserYardsale useryardsale, double longitude, double latitude, bool addToFacebook = false)
        {
            useryardsale.UserId = WebSecurity.CurrentUserId;
            var newYardsaleLocation = DbGeography.PointFromText(String.Format("POINT({0} {1})", longitude, latitude), DbGeography.DefaultCoordinateSystemId);
 
            if (!newYardsaleLocation.IsEmpty && ModelState.IsValid)
            {
                useryardsale.Location = newYardsaleLocation;
                db.UserYardsales.Add(useryardsale);
                db.SaveChanges();
                if (addToFacebook)
                {
                    FacebookHelper facebook = new FacebookHelper();
                    facebook.PostToFacebook();
                }
                return RedirectToAction("Index");
            }
 
            return View(useryardsale);
        }

As we make a call to graph.facebook.com, the new status will show up on the user’s wall.

public async void PostToFacebook()
        {
            try
            {
                Dictionary<string, string> properties = new Dictionary<string, string>
            {
                { "access_token", (string)HttpContext.Current.Session["accesstoken"] },
                { "message",  WebSecurity.CurrentUserName + " Have listed a new Yardsale on http://yardsale-finder.com" }
            };
 
                FormUrlEncodedContent authentication = new FormUrlEncodedContent(properties);
                HttpClient client = new HttpClient();
                HttpResponseMessage response = await client.PostAsync(String.Format("https://graph.facebook.com/{0}/feed", HttpContext.Current.Session["id"]), authentication);
                response.EnsureSuccessStatusCode();
                dynamic result = await response.Content.ReadAsAsync<dynamic>();
            }
            catch (Exception)
            {
 
            }
        }

Conclusion

.NET 4.5 is indeed a very large step forward in incorporating more parts of the eco system we currently use online. The extensive use of NuGet allows developers to update applications in a rate that was until now reserved for open source libraries and in house development.

In a few simple steps, we managed to set up deep integration with Facebook that is surly to benefit our users and add to the viral value of our application.

Introducing the Inverted Software Data Block. Simple ADO.NET to objects with no boilerplate code

Gal Ratner — Fri, 08 Jun 2012 17:24:00 GMT

I get asked many times by readers that have read To O/R Map or not to O/R Map - The Data Layer debate what framework I use and recommend when coding high volume systems. I always send them to Move a DataReader to an Object with reflection Revisited, but the truth is that article contains only a partial answer.

Sure you can use dynamic records, static reflection and many more techniques, but to truly make a system fast, you need to cache anything you can in memory, avoid complexity, keep Big O notation to a minimum and in general just go with a “less is better” attitude.

I therefore decided to skip to the bottom line of this article and show you what I use and how I use it.

After you got a sense of how easy it is to work with the Inverted Software DataBlock, I shell break down the framework into the underlying components.

Using the DataBlock

Getting an object from the output of a stored procedure:

Customer customer = CRUDHelper.GetObject<Customer>(parameters, generator, sprocName, stringConnection);

Getting a list of objects:

List<Customer> customers = CRUDHelper.GetObjectList<Customer>(generator, sprocName, stringConnection);

Getting a paged list of objects:

List<Customer> customers = CRUDHelper.GetObjectList<Customer>(generator, pageIndex, rowsPerPage, sprocName, stringConnection, out virtualTotal);

Updating a stored procedure from an object:

CRUDHelper.UpdateObject<Customer>(objectToUpdate, sprocName, stringConnection);

A generator will be a function that creates a customer object:

Func<Customer> generator = () => new Customer();

An older version of the Block was using Activator.CreateInstance<T>() which has proven to be far too slow.

This is my Customer object:

public class Customer
    {
        [CrudField(UsedFor = CrudFieldType.Delete | CrudFieldType.Read | CrudFieldType.Update)]
        public int CustomerID { get; set; }
        [CrudField(UsedFor = CrudFieldType.Read | CrudFieldType.Update | CrudFieldType.Create)]
        public string Name { get; set; }
        [CrudField(UsedFor = CrudFieldType.Read | CrudFieldType.Update | CrudFieldType.Create)]
        public string Email { get; set; }
        [CrudField(UsedFor = CrudFieldType.Read | CrudFieldType.Update | CrudFieldType.Create)]
        public bool Active { get; set; }
    }

It is simply a POCO object with an attribute, signaling the DataBlock how to use its properties.

The block also includes two convenience methods:

CRUDHelper.UpdateRecord(parameters, sprocName, stringConnection);
CRUDHelper.DeleteRecord(rowID, parameterName, sprocName, stringConnection);

When parameters is a Dictionary<string, object>.

Now that you know how to use the Inverted Software DataBlock let’s see how it’s built:
There are only six files making up the Block:

CrudField .cs Contains a custom attribute, signaling the Block what property is used in what operation type.
CRUDHelper.cs This is the main class and the entry point to the Block.
DataBlockException.cs Defines a custom exception the Block will throw on errors.
ObjectHelper.cs Contains cached information about objects properties, attributes and stored procedures output.
ObjectPool.cs Was taken from the ParallelExtensionsExtras to help with SQLCommands creation.
SQLHelper.cs A takeoff from the original PetShop example SQLHelper. Although this class does not resemble the original Microsoft example, it still contains basic ADO.NET code.

There is no magic here, just highly optimized code. For example: let’s look at a function in ObjectHelper:

public static List<string> GetColumnNames(SqlDataReader reader, string sprocName)
        {
            return QueryColumnNamesCache.GetOrAdd(sprocName, (key) =>
            {
                List<string> columnNames = new List<string>();
                System.Data.DataTable readerSchema = reader.GetSchemaTable();
                for (int i = 0; i < readerSchema.Rows.Count; i++)
                    columnNames.Add(readerSchema.Rows[i]["ColumnName"].ToString());
                return columnNames;
            });
        }

This function keeps a query’s list of output fields in memory and is used to iterate thought an object’s properties in order to load field to property by matching their names.

I have attached the complete source code to this article and you can feel free to download and use it in your systems.

If you simply want to use the final product, please install the Nuget Package.

Note: I would like to thank Alexandru Botez for his help with the Emit magic in ObjectHelper.cs

Using ASP.NET MVC 4, ApiControllers and SQL Server 2012 to find the best cup of coffee in Seattle

Gal Ratner — Tue, 22 May 2012 19:27:00 GMT

ASP.NET MVC continues to innovate and in its current version (4) it offers support for Web APIs. Today we are going to examine a real life scenario of using Web APIs along with MVC 4 as we are utilizing the Google Earth plug-in to mark a defined area and SQL Server 2012 geography type to help us persist and calculate GIS data.
In our example we are going to mark an area on the globe using a polygon, calculate the center of the area, find all of the coffee shops closest to the center of the area and arrange them by distance. We are going to mark them on the map as well as show a full list including ranking.
Before we begin, let’s briefly go over the technologies we are going to use today:
ASP.NET Web API
Formally known as the WCF Web API this was a community driven project hosted on Codeplex (http://wcf.codeplex.com/wikipage?title=WCF%20HTTP). It was created to allow direct control over HTTP based communication between client and server applications. Its simplicity and compatibility for working within applications built with a RESTful architecture helped it gain popularity and find its way into the .NET 4.5 Framework under the namespace System.Net.Http.
There are lots of tutorials online about using the Web API and you can start learning it by going here: http://www.asp.net/web-api
ApiController
Residing in System.Web.Http, APIControllers utilize System.Net.Http to handle HTTP verbs and map them into methods. A GET verb will be mapped to a Get() method, POST to Post() and so on. Using client JavaScript libraries we are able to build a RESTful application fairly quickly with the default controller mapping. More on ApiController here: http://msdn.microsoft.com/en-us/library/system.web.http.apicontroller(v=vs.108).aspx
Knockout.js
A part of MVC 4, Knockout is a handy MVVM style JavaScript library and is designed for mobile and responsive applications. The library helps with binding of underlying data models to UI elements and templates and reflects changes to the data by updating the UI. There are many more features to Knockout, however, we are going to use it today to show a simple coffee shop list. More on Knockout can be found here: http://knockoutjs.com/
SQL Server 2012 geography
I have blogged about SQL geography in the past with SQL Server 2008 R2 (http://galratner.com/blogs/net/archive/2011/05/14/spatial-search-made-easy-with-google-maps-api-and-sql-server-2008.aspx). SQL Server 2012 adds more features to its geography and geometry spatial types such as circular strings, curve polygons, and compound curves. In addition it has spatial index improvements, new and updated methods and aggregates for all types and much more.
Google Earth API
Almost everybody is familiar with Google Earth. The Earth plug-in has an excellent API here: https://developers.google.com/earth/ and is JavaScript based. In today’s example I am going to make extensive use of its features to draw a polygon, fly to location and add placemarks.
Google Places API
Google Places is the equivalent of Yelp.com. It is a global business database and has an API that supports HTTPS REST queries. The API’s documentation can be found here: https://developers.google.com/maps/documentation/places/. An API key is needed and is provided for free by Google. We are going to use the Places API to query for all the coffee shops in a defined location.

Starting our example
Visual Studio 2011 comes with MVC 4 already baked in. As I am writing this post it is still in beta (5/2012) so we are going to use Visual Studio 2010 along with its version of MVC 4. It includes almost everything we need and works with .NET 4.0. You can download the installer here: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=28942
After installing MVC 4 we are going to create a new Web API project

Under the Controllers folder you will notice a new ApiController named ValuesController. This controller will be our base when communicating with the Earth plug-in.
First let’s navigate to the Index.cshtml and lay out our earth on the screen: We will add an earth div to the html and instantiation JavaScript functions.
HTML:

<div id="map3d" style="height: 700px; width: 100%;"></div>

JavaScript:

<script src="@Url.Content("~/Scripts/knockout-2.0.0.js")" type="text/javascript"></script>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <meta name="viewport" content="width=device-width" />
    <script type="text/javascript">
      var ge; 
      var viewModel; // Our knockout.js model
      google.load("earth", "1");
      google.load("maps", "2");
 
      function init() {
          google.earth.createInstance('map3d', initCB, failureCB);
      }
 
      function initCB(instance) {
          ge = instance;
          ge.getWindow().setVisibility(true);
          ge.getNavigationControl().setVisibility(ge.VISIBILITY_SHOW);
          // Create an initial Look of the earth
          var la = ge.createLookAt('');
          la.set(@Model.Latitude, @Model.Longitude, 0, ge.ALTITUDE_RELATIVE_TO_GROUND, -8.541, 66.213, 2000);
          ge.getView().setAbstractView(la);
          viewModel = {
              CoffeeLocations: ko.observableArray([])
          };
          ko.applyBindings(viewModel);
          // Add the polygon stored in the database
          AddPolygon();
          // Search and show all coffee shops as placemarks
          GetCoffeeLocations();
          // Listen for mousemove on the globe.
          google.earth.addEventListener(ge.getGlobe(), 'mouseup', function (event) {
              event.preventDefault();
              getPolygonBorder().getCoordinates().pushLatLngAlt(event.getLatitude(), event.getLongitude(), 100);
          });
      }
 
      function failureCB(errorCode) {
      }
 
       function flyToLocation() {
        var geocodeLocation = document.getElementById('Location').value;
        var geocoder = new google.maps.ClientGeocoder();
        geocoder.getLatLng(geocodeLocation, function(point) {
        if (point) {
          var lookAt = ge.createLookAt('');
          lookAt.set(point.y, point.x, 10, ge.ALTITUDE_RELATIVE_TO_GROUND, -8.541, 66.213, 2000);
          ge.getView().setAbstractView(lookAt);
        }
      });
    }
 
      google.setOnLoadCallback(init);
  </script>
  <script src="@Url.Content("~/Scripts/EarthFunctions.js")" type="text/javascript"></script>
  <script src="@Url.Content("~/Scripts/PlacesFunctions.js")" type="text/javascript"></script>

After we instantiated the earth and directed it to start at a location, we can add our database and data layer.
Our database will be simple. We will use table Location to hold the information

Now let’s make the stored procedures we need in order to get and update the data:

CREATE PROCEDURE [dbo].[GetLocationText]
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP 1
        LocationText
    FROM
        Location
END

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UpdateLocation]
    @LocationText VARCHAR(MAX)
AS
BEGIN

    SET NOCOUNT ON;

    IF NOT EXISTS(SELECT 1 FROM Location)
        BEGIN
            INSERT INTO
                Location (LocationValue)
            VALUES
                (geography::STGeomFromText(@LocationText, 4326))
        END
    ELSE
        BEGIN
            UPDATE
                Location
            SET
                LocationValue = (geography::STGeomFromText(@LocationText, 4326))
        END
END

UpdateLocation will update the location using a text representation of the polygon we want to save.
Finally we need to be able to find the center of our polygon.

CREATE PROCEDURE [dbo].[GetLocationCenter]

AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @g geometry;
    DECLARE @p VARCHAR(MAX);
    SET @p = (SELECT TOP 1 LocationText FROM Location)
    SET @g = geometry::STGeomFromText(@p, 0)
    SELECT @g.STCentroid().ToString();
END

Notice we converted our geography type to simple geometry and used STCentroid() to calculate its center.

After we built our database we need to build the data layer. There is nothing smart here, just the same boilerplate code we always use for CRUD operations so I am going to skip this step. If you are interested in seeing the DAL code I have attached the complete example to the bottom of this tutorial and you can download and run it on your own.

Building the controllers

We will be using the ValuesController and adding a PlacesController to show all of the coffee shops.
Our ValuesController will have two methods: one to get our polygon from the database and one to save it. Here is the code for the ValuesController:

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using System.Text.RegularExpressions;
 
using WebAPIExample.Models;
 
 
namespace WebAPIExample.Controllers
{
    public class ValuesController : ApiController
    {
 
        // GET /api/values
        public IEnumerable<GlobalPoint> Get()
        {
            List<GlobalPoint> myPolygon = new List<GlobalPoint>();
            try
            {
                string AreaWKT = DAL.Database.GetLocationText();
                string pattern = @"[-+]?[0-9]*\.?[0-9]+";
                MatchCollection matches = Regex.Matches(AreaWKT, pattern);
                for (int i = 0; i < matches.Count; i++)
                {
                    myPolygon.Add(new GlobalPoint()
                    {
                        Longitude = Convert.ToDouble(matches[i].Value),
                        Latitude = Convert.ToDouble(matches[++i].Value)
                    });
                }
            }
            catch (Exception ex)
            {
                throw new HttpResponseException("Error Getting polygon", ex); 
            }
            return myPolygon;
        }
 
        // POST /api/values
        public HttpResponseMessage Post(string ShapePoints)
        {
            // Validate input and concatenate a Polygon
            try
            {
                StringBuilder polygon = new StringBuilder();
                polygon.Append("POLYGON((");
                string[] points = ShapePoints.Split('|');
                for (int i = 0; i < points.Length; i++)
                {
                    double latitude = 0;
                    double longitude = 0;
                    if (double.TryParse(points[i], out latitude) && double.TryParse(points[++i], out longitude))
                        polygon.Append(longitude + " " + latitude + ",");
 
                }
                // Make sure the last latitude and longitude close the polygon
                if (points[0] != points[points.Length - 2] && points[1] != points[points.Length - 1])
                    polygon.Append(double.Parse(points[1]) + " " + double.Parse(points[0]) + ",");
                polygon.Replace(",", "))", polygon.Length - 1, 1);
                // Save to database
                DAL.Database.UpdateLocation(polygon.ToString());
            }
            catch (Exception ex)
            {
                throw new HttpResponseException("Error Saving Map", ex);
            }
            return new HttpResponseMessage(HttpStatusCode.OK);
        }
    }
}

Notice that in order to save the polygon we need a collection of points containing the latitude and longitude of its corners. We are going to use them to concatenate a Well-known text (WKT) formatted string used to describe vector geometry objects. We can then convert our WKT polygon to a geography type by using STGeomFromText in our stored procedure.

After we built the controller we can now use it to draw and save our polygon. The code to do that is written entirely in JavaScript and looks like:

function AddPolygon() {
    // Create the placemark.
    var polygonPlacemark = ge.createPlacemark('');
 
    // Create the polygon.
    var polygon = ge.createPolygon('');
    polygon.setAltitudeMode(ge.ALTITUDE_RELATIVE_TO_GROUND);
    polygonPlacemark.setGeometry(polygon);
 
    // Add points for the outer shape.
    var border = ge.createLinearRing('');
    border.setAltitudeMode(ge.ALTITUDE_RELATIVE_TO_GROUND);
    AddPolygonCoordinates(border);
    polygon.setOuterBoundary(border);
 
    //Create a style and set width and color of line
    polygonPlacemark.setStyleSelector(ge.createStyle(''));
    var lineStyle = polygonPlacemark.getStyleSelector().getLineStyle();
    polygonPlacemark.getStyleSelector().getPolyStyle().setFill(0);
    lineStyle.setWidth(5);
    lineStyle.getColor().set('9900ffff');
    
 
    // Add the placemark to Earth.
    ge.getFeatures().appendChild(polygonPlacemark);
}
 
function AddPolygonCoordinates(border) {
    $.getJSON("/api/values", function (data) {
        $.each(data, function (i, ltem) {
            border.getCoordinates().pushLatLngAlt(ltem.lat, ltem.lng, 100);
        });
    });
}
 
function clearMap() {
    viewModel.CoffeeLocations([]);
    var features = ge.getFeatures();
    features.getFirstChild().getGeometry().getOuterBoundary().getCoordinates().clear();
}
 
function saveMap() {
    var features = ge.getFeatures();
    var points = features.getFirstChild().getGeometry().getOuterBoundary().getCoordinates();
    var polygonPoints = '';
    for (i = 0; i < points.getLength(); i++)
        polygonPoints += points.get(i).getLatitude() + "|" + points.get(i).getLongitude() + "|";
 
    $.post('/api/values', {ShapePoints : polygonPoints}, function (data) {
        $('#message').html("Map Saved");
    });
}
 
function getPolygonBorder() {
    var features = ge.getFeatures();
    return features.getFirstChild().getGeometry().getOuterBoundary();
}

$.getJSON("/api/values") is a jQuery function that simply makes a GET request to the controller and gets our list of points as JSON. Adding them to the map is simple: border.getCoordinates().pushLatLngAlt(ltem.lat, ltem.lng, 100);

We have also added a function to clear the map in case we need to create a new polygon.
Now that we have the code to get, update and show our polygon on Google earth lets search for close coffee shops!
We will use a PlacesController and make a simple call to Google Places with the center of our area as a starting point:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Http;
using System.Web.Http;
using System.Json;
using System.Net;
 
using WebAPIExample.Models;
using System.Text.RegularExpressions;
 
namespace WebAPIExample.Controllers
{
    public class PlacesController : ApiController
    {
        string APIKey = "your API Key";
        // GET /api/places
 
        public IEnumerable<Place> Get()
        {
            List<Place> places = new List<Place>();
            GlobalPoint centerPoint = Utils.GetCenter();
            using (WebClient client = new WebClient())
            {
                string requestURL = string.Format("https://maps.googleapis.com/maps/api/place/search/json?key={0}&location={1}&rankby=distance&types=cafe|bakery&sensor=false", APIKey, centerPoint.Latitude + "," + centerPoint.Longitude);
                string results = client.DownloadString(requestURL);
                var placesResponse = System.Web.Helpers.Json.Decode(results);
                foreach (var result in placesResponse.results)
                {
                    places.Add(new Place()
                    {
                        Name = result.name,
                        Rating = Convert.ToString(result.rating),
                        Latitude = Convert.ToDouble(result.geometry.location.lat),
                        Longitude = Convert.ToDouble(result.geometry.location.lng)
                    });
                }
                return places;
            }
        }
    }
}

Now that we have our controller already returning a list of coffee shops we need to show them on the map and bind them to a list along with their ratings:

function GetCoffeeLocations() {
    $.getJSON("/api/places", function (data) {
        viewModel.CoffeeLocations(data); // Bind the model
 
        $.each(data, function (i, ltem) {
            // Create the placemark.
            var placemark = ge.createPlacemark('');
            placemark.setName(ltem.name);
            // Set the placemark's location.  
            var point = ge.createPoint('');
            point.setLatitude(ltem.lat);
            point.setLongitude(ltem.lng);
            placemark.setGeometry(point);
 
            // Add the placemark to Earth.
            ge.getFeatures().appendChild(placemark);
        });
    });
}

Let’s add a knockout.js HTML template at the bottom of the map in order to show the list:

 <div data-bind="template: { name: 'point-template', foreach: viewModel.CoffeeLocations}"></div>
            <script type="text/html" id="point-template">
                <span data-bind="text: name"></span>
                (Rated: <span data-bind="text: rating"></span>)<br>
            </script>

Putting is together

Great! We are almost done. Just some minor enhancements to the UI to allow some “fly to” functionality and we are ready to aim the map to our favorite city!

<input id="FlyButton" type="button" value="Fly Here" onclick="flyToLocation();"/>

function flyToLocation() {
        var geocodeLocation = document.getElementById('Location').value;
        var geocoder = new google.maps.ClientGeocoder();
        geocoder.getLatLng(geocodeLocation, function(point) {
        if (point) {
          var lookAt = ge.createLookAt('');
          lookAt.set(point.y, point.x, 10, ge.ALTITUDE_RELATIVE_TO_GROUND, -8.541, 66.213, 2000);
          ge.getView().setAbstractView(lookAt);
        }
      });
    }

That’s it! If everything went to plan we should be looking at a complete map and list that looks like this:

It will show our polygon, coffee shops as place marks and a bottom list which includes names and ratings.

Conclusion

In this example we have put together a real life application that can be used as a part of any GIS aware product. We have seen how simple the Web API can be and how powerful the features of SQL Server geography are. The edition of some new slick client side frameworks such as knockout and the Google earth plug-in API helped us create an appealing and responsive user interface with minimal effort.
Don’t forget to download the attached code and experiment with it yourself.

Happy Coding!

Reloading a Razor WebGrid after Ajax calls using a partial view

Gal Ratner — Fri, 02 Dec 2011 00:19:00 GMT

If you are using Razor and MVC you probably make some use of the built in controls in System.Web.Helpers. WebGrid, located in the Helpers assembly, was created for WebMatrix’s Razor Web Pages and landed itself nicely to Razor views in MVC.
WebGrid, much like ASP.NET’s ListView control, is designed to display a data driven HTML table on the screen. It has support for paging, sorting and column customization.
In this article, we will be taking a quick look at loading a WebGrid with data, both as a part of a page and as a standalone AJAX call. AJAX calls can occur after a page load or sort change and as a part of the interaction of a user with the data on the grid.
Let’s begin by making a view. The view will contain the body of the page rendered in the @RenderBody() section of the main layout. In our case the grid will reside in the index page and the index view will simply look like this:

@model IEnumerable<Deployments.Models.Deployment>
@{
    ViewBag.Title = "Deployments";
}
 
<h2>@ViewBag.Message</h2>
<p>
    @Html.ActionLink("Request Deployment", "CreateDeployment")
</p>
@Html.Partial("DeploymentList", Model)

The Model is the data we wish to display and Html.Partial adds a partial view along with our grid.
We will take a look at the data later. First let’s display our grid. The grid resided in a partial view named DeploymentList:

@model IEnumerable<Deployments.Models.Deployment>
 
@{
    var grid = new WebGrid(null, rowsPerPage: ViewBag.PageSize, ajaxUpdateContainerId: "deploymentsGrid", canSort: false);
    grid.Bind(Model, rowCount: ViewBag.TotalRecords, autoSortAndPage: false);
}

A key part of the grid is ajaxUpdateContainerId. It signals the grid what container to update after an AJAX call. “deploymentsGrid” is a div surrounding the partial view and containing the Grid and any helper functions that will get updated in AJAX calls.
Now after we declared the grid we can look at the controller. The grid sends some query string parameters for paging and sorting by default. Since our grid has pagination enabled, our controller should be able to deal with an optional page parameter.

private DeploymentsEntities context = new DeploymentsEntities();
        private int pageSize = 10;
 
        public ActionResult Index(int? page)
        {
            ViewBag.Message = "Deployments";
            ViewBag.PageSize = pageSize;
            ViewBag.TotalRecords = context.Deployments.Count(d => d.Active == true);
            var model = context.Deployments
                .Include("Website")
                .Include("Environment")
                .OrderByDescending(d => d.DeploymentID)
                .Where(d => d.Active == true)
                .Skip(((page.HasValue ? page.Value : 1) - 1) * pageSize)
                .Take(pageSize);
            if (Request.IsAjaxRequest())
                return PartialView("DeploymentList", model);
            else
                return View(model);
        }

As you can see, when our controller is invoked by an AJAX request, we do not need to return the entire index view. We can simply return the partial view containing the grid. Retuning a partial view will make sure only the HTML we need to update is actually being generated.
This is the rest of the grid view:

<div id="deploymentsGrid">
 @grid.GetHtml(
        columns: grid.Columns(
                    grid.Column("Website.WebsiteName", header: "Website"),
                    grid.Column("Environment.EnvironmentName", header: "From"),
                    grid.Column("Environment1.EnvironmentName", header: "To"),
                    grid.Column("RequestedBy", header: "Requested By"),
                    grid.Column("RequestedDate", header: "Requested Time"),
                    grid.Column("ExecutedBy", header: "Executed By"),
                    grid.Column("ExecutedDate", header: "Executed Time"),
                    grid.Column("WebsiteSync", header: "Website", format: (item) => (item.WebsiteSync) ? Html.Raw("<img src='/images/active.png' />") :
                        Html.Raw("<img src='/images/inactive.png' />")),
                    grid.Column("DatabaseSync", header: "Database", format: (item) => (item.DatabaseSync) ? Html.Raw("<img src='/images/active.png' />") :
                        Html.Raw("<img src='/images/inactive.png' />")),
                    grid.Column("Comments", header: "Comments"),
                    grid.Column("", header: "Done", format: (item) => (string.IsNullOrWhiteSpace(item.ExecutedBy)) ? 
                        @Ajax.ActionLink("Done", "Done", new { id = item.DeploymentID }, 
                        new AjaxOptions() {
                            Confirm = "Did you check your work?",
                            HttpMethod = "Get",
                            OnSuccess = "updateGrid()"
                        }) :
                        Html.Raw("<a href='" + item.Environment1.WebsiteURL + "' target='_new'>View</a>"))
                        )
              )
 
<script type="text/javascript">
    function updateGrid() {
        @Html.Raw(HttpUtility.HtmlDecode(grid.GetContainerUpdateScript("/?page=" + (grid.PageIndex + 1)).ToString()))
    }
</script>
</div>

If you read through the code, you probably noticed the @Ajax.ActionLink “Done”. This is a simple GET call along with a parameter containing the ID of the record we wish to update. The interesting part about it is the OnSuccess call. This is a JavaScript call that is made when the ActionLink returns successfully.
Since our update operation succeeded, we will go ahead and update the grid:

@Html.Raw(HttpUtility.HtmlDecode(grid.GetContainerUpdateScript("/?page=" + (grid.PageIndex + 1)).ToString()))

GetContainerUpdateScript is built into the grid. If you view the source of the page, you will probably find it in the JavaScript onClick events of your pager. We are simply calling it again, along with the current grid page in order to update the container div.
As you can tell, WebGrid has some great web 2.0 functionality out of the box and can help you speed up Razor development.

I would like to thank Brad Laney for the @Html.Raw tip.

Give your old ASP.NET validators a makeover with jQuery TOOLS

Gal Ratner — Tue, 08 Nov 2011 21:33:00 GMT

If you have been working with the default validators in ASP.NET for a while you are probably used to the plain old look they create on your page and while you can rework the way they look, ASP.NET validators are essentially a Label. They are unable to change the appearance of other elements or run scripts without a complete page postback.

jQuery TOOLS is a jQuery based UI library you can find here. jQuery TOOLS contains a validator as a part of its form tools. The jQuery TOOLS validator is based on HTML 5 and CSS standards and has the ability to animate, change the appearances of the element it is validating, support multiple languages and more.

In this article I am doing to show how to quickly plug jQuery TOOLS into ASP.NET’s validation so that you can continue to use default validation along with the new look.

First let’s build our form. We are going to use ASP TextBoxs along with CustomValidators.

Let’s import jQuery tools along with some CSS directly taken from their example:

<script src="http://cdn.jquerytools.org/1.2.6/full/jquery.tools.min.js"></script>
<link rel="stylesheet" type="text/css" href="http://static.flowplayer.org/tools/demos/validator/css/form.css"/>

Next we are going to add the form elements along with some HTML 5 attributes. The attributes will help jQuery tools to validate the fields:

<form id="form1" runat="server">
	<fieldset>
		<p><asp:TextBox ID="NameTextBox" required="required" runat="server"></asp:TextBox>
		    <asp:CustomValidator ID="NameCustomValidator" ControlToValidate="NameTextBox" 
                ClientValidationFunction="jQueryValidation" ValidateEmptyText="true" 
                runat="server" onservervalidate="NameCustomValidator_ServerValidate"></asp:CustomValidator></p>
		<p><asp:TextBox ID="EmailTextBox" type="email" required="required" runat="server"></asp:TextBox>
            <asp:CustomValidator ID="EmailCustomValidator" ControlToValidate="EmailTextBox" 
                ClientValidationFunction="jQueryValidation" ValidateEmptyText="true" 
                runat="server" onservervalidate="EmailCustomValidator_ServerValidate"></asp:CustomValidator></p>
	</fieldset>
	<asp:Button ID="SubmitButton" runat="server" Text="Submit" onclick="SubmitButton_Click" />
	</form>

Notice we used ClientValidationFunction to signal our validators they need to run a Javascript function for client validation. Now we can wire the validation in using this snippet:

<script language="javascript">
		function jQueryValidation(source, arguments) {
		    var name = $("#" + source.controltovalidate).validator();
			arguments.IsValid = name.data("validator").checkValidity();
		}
    </script>

Server side validation:

For security purposes and to accommodate JavaScript disabled browsers we also need server side validation. Since we are checking for a required name and email, the server side validation will look like this:

protected void NameCustomValidator_ServerValidate(object source, ServerValidateEventArgs args)
    {
        args.IsValid = !string.IsNullOrWhiteSpace(args.Value);
    }
    protected void EmailCustomValidator_ServerValidate(object source, ServerValidateEventArgs args)
    {
        args.IsValid = Regex.IsMatch(args.Value,
              @"^(?("")(""[^""]+?""@)|(([0-9a-zA-Z]((\.(?!\.))|[-!#\$%&'\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-zA-Z])@))" +
              @"(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,6}))$");
    }

Now that everything is wired and working, validation will look much better

As you can see we have preserved all of the default behavior of the original validation.
For more customization options, please check the jQuery TOOLS documentation here

Deep dive into the LinkedIn API: Use C# to search for your dream Job (writing C# :))

Gal Ratner — Sat, 15 Oct 2011 02:02:00 GMT

LinkedIn has an extensive API designed to access almost every aspect of the site. Developers can access People and Connections, Groups, Companies, Jobs, Social Stream, Communications and more. There are two types of LinkedIn APIs: JavaScript based and REST based with the ability to connect the two. For example: you can log in with a JavaScript button and monitor your social updates using server based code. You can then send yourself summary emails with the updates.
This is a deep dive into both APIs. We are going to be using the JavaScript login button, showing the user’s name using HTML. LinkedIn’s REST API uses OAuth authentication which we will utilize in order to get a new REST token. We will then re log in on the server and invoke the REST API, perform a jobs keyword search, return the results as JASON and show them to the user.

Before we start we will need a developer account. We can then log into the development portal and create a new application. We will name the application and get our API Key and Secrete Key. We will use them in order to authenticate.

Let’s begin by using the JavaScript API in order to create a login with LinkedIn button. The client code looks like this:

<script type="text/javascript" src="http://platform.linkedin.com/in.js">
        api_key: <%: Utils.APIKey %>
        authorize: true
        credentials_cookie: true
    </script>

<script type="in/Login">

api_key will be the application key and credentials_cookie will be set to indicate the API we wish to receive an authentication cookie. The cookie will contain the data we need in order to communicate with the REST API

We need to remember when running this code locally that a client to server request will only be accepted over https so we need to run IIS Express and use the local SSL certificate

Once the user have logged on we can display their details:

Hello, <?js= firstName ?> <?js= lastName ?>

We can now utilize the entire JavaScript API to make any call we wish. The full API is described here: https://developer.linkedin.com/javascript

Lets exchange the client calls for server calls

The first step will be to read the authentication cookie. The cookie contains all the values we need in order to exchange out JS token with a REST token.
{ "signature_method":"HMAC-SHA1", "signature_order": ["access_token", "member_id"], "access_token":"AD2dpVe1tOclAsNYsCri4nOatfstw7ZnMzWP", "signature":"73f948524c6d1c07b5c554f6fc62d824eac68fee", "member_id":"vvUNSej47H" "signature_version": 1}

Let’s load it into a dynamic object

/// <summary>
        /// Get the cookie from the JSAPI
        /// </summary>
        /// <returns></returns>
        public static dynamic GetOauthCookie()
        {
            var oaut = HttpContext.Current.Request.Cookies[string.Format(oautCookie, APIKey)];
            dynamic cookie = null;
            if (oaut != null)
            {
                JavaScriptSerializer jss = new JavaScriptSerializer();
                cookie = jss.Deserialize<dynamic>(HttpUtility.UrlDecode(oaut.Value));
            }
            if (IsCookieValid(cookie))
                return cookie;
            return null;
        }

We must now make sure the cookie is valid and has not been tampered with in any way. We can do that by concatenating a string from the cookie values in the field names in the signature_order. Once we have them we can verify the hash generated from the values using our keys matches the hash in the cookie’s signature field.

/// <summary>
        /// Validate the cookie from the JSAPI
        /// </summary>
        /// <param name="cookie"></param>
        /// <returns></returns>
        public static bool IsCookieValid(dynamic cookie)
        {
            // The signature base is calculated by concatenating the values of the fields listed in the signature_order field in the order they appear.
            string signature = string.Empty;
            foreach (var fieldName in cookie["signature_order"])
                signature += cookie[fieldName];
            // After you construct the signature base, use the encryption algorithm specified in the signature_method to calculate the signature using your API secret.
            if (cookie["signature_method"] == "HMAC-SHA1")
            {
                return VerifyHash(signature, cookie["signature"]);
            }
            return true;
        }
 
        public static bool VerifyHash(string signature, string cookieSignature)
        {
            if (GetHMACSHA1Hash(SecretKey, signature) == cookieSignature)
                return true;
            return false;
        }
 
        public static string GetHMACSHA1Hash(string key, string value)
        {
            string hash = string.Empty;
            using (HMACSHA1 hmac = new HMACSHA1(System.Text.ASCIIEncoding.ASCII.GetBytes(key)))
            {
                hmac.ComputeHash(System.Text.ASCIIEncoding.ASCII.GetBytes(value));
                hash = System.Convert.ToBase64String(hmac.Hash);
            }
            return hash;
        }

If the values match this cookie is valid and we can continue with the REST token request.

The next part gets a little complicated so, let’s break it down to steps:
The cookie contains an access token. The access token needs to be posted into the LinkedIn server; however, since LinkedIn use OAuth for any REST request we need to make sure to follow the process to sign a request according to the OAuth 1.0a specs. You can read them here: http://oauth.net/core/1.0a/.

Luckily we can find many libraries that can help is with OAuth. One of them is the open source class OAuthBase.cs that was written by Eran Sandler and can be found here: http://oauth.googlecode.com/svn/code/csharp/OAuthBase.cs .The example solution at the bottom of this page contains this class and you can feel free to investigate it further.

We need to post the access token as an OAuth parameter so we are going to append xoauth_oauth2_access_token to the request.
The last step before we do the post is to append the generated OAuth signature.

Now we can make the request:

/// <summary>
        /// Exchange JSAPI Token for a REST API OAuth Token
        /// </summary>
        private void LoadRESTToken()
        {
            dynamic cookie = Utils.GetOauthCookie();
            // xoauth_oauth2_access_token parameter, set to the value of the access_token field in the cookie.
            string accessToken = cookie["access_token"];
            string url = String.Format("{0}{1}?xoauth_oauth2_access_token={2}", Utils.LinkedinAPI, Utils.AccessTokenPath, accessToken);
            OAuthBase oAuth = new OAuthBase();
            string nonce = oAuth.GenerateNonce();
            string timeStamp = oAuth.GenerateTimeStamp();
            string normalizedUrl = string.Empty;
            string normalizedRequestParameters = string.Empty;
            string signature = oAuth.GenerateSignature(new Uri(url), Utils.APIKey, Utils.SecretKey, accessToken, null, "POST", timeStamp, nonce, out normalizedUrl, out normalizedRequestParameters);
            normalizedRequestParameters = normalizedRequestParameters += String.Format("&oauth_signature={0}", HttpUtility.UrlEncode(signature));
            string oAuthToken = Utils.GetOAuthToken(normalizedUrl, normalizedRequestParameters);
            //Load both token and secret into the current session
            context.Session["oauth_token"] = HttpUtility.ParseQueryString(oAuthToken)["oauth_token"];
            context.Session["oauth_token_secret"] = HttpUtility.ParseQueryString(oAuthToken)["oauth_token_secret"];
        }

/// <summary>
        /// Gets the REST API OAuth Token
        /// </summary>
        /// <param name="url"></param>
        /// <param name="postData"></param>
        /// <returns></returns>
        public static string GetOAuthToken(string url, string postData)
        {
            string token = string.Empty;
            using (WebClient client = new WebClient())
            {
                client.Headers.Add("content-type", "application/x-www-form-urlencoded");
                try
                {
                    byte[] byteArray = Encoding.ASCII.GetBytes(postData);
                    byte[] responseArray = client.UploadData(url, "POST", byteArray);
                    token = Encoding.ASCII.GetString(responseArray);
                }
                catch (Exception ex)
                {
                    token = ex.Message;
                }
            }
 
            return token;
        }

The result is a string containing both the token and token secret. We now have access to the REST API!

With access to the rest API lets start building the UI and wiring the application. Lets build a text box for keyword searches and a JSON call in order to populate a list with the results:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="LinkedInJobFinder.Default" %>
<%@ Import Namespace="LinkedInJobFinder" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript" src="http://platform.linkedin.com/in.js">
        api_key: <%: Utils.APIKey %>
        authorize: true
        credentials_cookie: true
    </script>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.3/jquery.min.js"></script>
    <script type="text/javascript">
    function searchListings(){
        jQuery.getJSON("JobData.ashx?jsoncallback=?",
        {
            Query: $("#searchText").val()
        },
        function (data) {
            $("#listingResults").empty();
            if (data.numResults == 0) {
                $("#listingResults").html("Sorry there are results. Please try again.");
                return;
            }
            $.each(data.jobs.values, function (i, item) {
                $("<b>Position</b><br>").appendTo("#listingResults");
                $("<p>Company: " + item.company.name + " location: " + item.locationDescription + "</p>").appendTo("#listingResults");
                $("<p>" + item.descriptionSnippet + "</p>").appendTo("#listingResults");
            });
        });
    }
</script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <script type="in/Login">
Hello, <?js= firstName ?> <?js= lastName ?>.<br />
Search for your dream job! <input type="text" name="searchText" id="searchText" /><br />
    <a href="javascript:searchListings();">Search</a>
</script><br /><br />
<div id="listingResults">
    </div>
 
    </form>
   
</body>
</html>

For improved performance, The JSON calls will be redirected to an HttpAsyncHandler and we are going to use an object pool from the Parallel Extensions Extras to allow for fast network calls.

Here is the handler’s code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Threading;
using System.Threading.Tasks;
using System.Text;
using System.Web.SessionState;
 
using OAuth;
 
namespace LinkedInJobFinder
{
    /// <summary>
    /// Summary description for JobData
    /// </summary>
    public class JobData : IHttpAsyncHandler, IRequiresSessionState
    {
        public IAsyncResult BeginProcessRequest(HttpContext context, AsyncCallback cb, Object extraData)
        {
            AsynchOperation asynch = new AsynchOperation(cb, context, extraData);
            asynch.StartAsyncWork();
            return asynch;
        }
 
        public void EndProcessRequest(IAsyncResult result)
        {
 
        }
 
        public void ProcessRequest(HttpContext context)
        {
            throw new InvalidOperationException();
        }
 
        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
 
    class AsynchOperation : IAsyncResult
    {
        private bool completed;
        private Object state;
        private AsyncCallback callback;
        private HttpContext context;
 
        bool IAsyncResult.IsCompleted
        {
            get
            {
                return completed;
            }
        }
 
        WaitHandle IAsyncResult.AsyncWaitHandle
        {
            get
            {
                return null;
            }
        }
 
        Object IAsyncResult.AsyncState
        {
            get
            {
                return state;
            }
        }
 
        bool IAsyncResult.CompletedSynchronously
        {
            get
            {
                return false;
            }
        }
 
        public AsynchOperation(AsyncCallback callback, HttpContext context, Object state)
        {
            this.callback = callback;
            this.context = context;
            this.state = state;
            this.completed = false;
        }
 
        public void StartAsyncWork()
        {
            dynamic cookie = Utils.GetOauthCookie();
            // If this user has no session open it by getting a new OAuth 1.0a token for the REST API
            if (context.Session["oauth_token"] == null)
               LoadRESTToken();
 
            Task.Factory.StartNew(() =>
            {
                var jobRequest = Utils.JobRequestPool.GetObject();
 
                try
                {
                    string jobListings;
                    string keywordSearch = String.Format("{0}?keywords={1}", Utils.LinkedinJobAPIURL, HttpUtility.UrlEncode(context.Request.QueryString["Query"]));
                    jobListings = jobRequest.GetJobs(keywordSearch, Convert.ToString(context.Session["oauth_token"]), Convert.ToString(context.Session["oauth_token_secret"]));
                    context.Response.Write(context.Request.QueryString["jsoncallback"] + "(" + jobListings + ")");
                }
                catch (Exception)
                {
 
                }
                Utils.JobRequestPool.PutObject(jobRequest);
                completed = true;
                callback(this);
            });
        }
 
        /// <summary>
        /// Exchange JSAPI Token for a REST API OAuth Token
        /// </summary>
        private void LoadRESTToken()
        {
            dynamic cookie = Utils.GetOauthCookie();
            // xoauth_oauth2_access_token parameter, set to the value of the access_token field in the cookie.
            string accessToken = cookie["access_token"];
            string url = String.Format("{0}{1}?xoauth_oauth2_access_token={2}", Utils.LinkedinAPI, Utils.AccessTokenPath, accessToken);
            OAuthBase oAuth = new OAuthBase();
            string nonce = oAuth.GenerateNonce();
            string timeStamp = oAuth.GenerateTimeStamp();
            string normalizedUrl = string.Empty;
            string normalizedRequestParameters = string.Empty;
            string signature = oAuth.GenerateSignature(new Uri(url), Utils.APIKey, Utils.SecretKey, accessToken, null, "POST", timeStamp, nonce, out normalizedUrl, out normalizedRequestParameters);
            normalizedRequestParameters = normalizedRequestParameters += String.Format("&oauth_signature={0}", HttpUtility.UrlEncode(signature));
            string oAuthToken = Utils.GetOAuthToken(normalizedUrl, normalizedRequestParameters);
            //Load both token and secret into the current session
            context.Session["oauth_token"] = HttpUtility.ParseQueryString(oAuthToken)["oauth_token"];
            context.Session["oauth_token_secret"] = HttpUtility.ParseQueryString(oAuthToken)["oauth_token_secret"];
        }
    }
}

The last step would be to actually make a keyword reach call to the LinkedIn REST endpoint and return the JSON result to the client. We need to remember to sign every request:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Net;
using System.IO;
using System.Xml.Linq;
using System.Text;
 
using OAuth;
 
namespace LinkedInJobFinder
{
    public class JobRequest : IDisposable
    {
        WebClient client;
        OAuthBase oAuth;
        private bool disposed = false;
 
        public JobRequest()
        {
            client = new WebClient();
            client.Headers.Add("x-li-format", "json");
            client.UseDefaultCredentials = true;
            oAuth = new OAuthBase();
        }
 
        /// <summary>
        /// Download a json string of jobs using a keyword search
        /// </summary>
        /// <param name="searchFilter"></param>
        /// <param name="oauthToken"></param>
        /// <param name="oauthTokenSecret"></param>
        /// <returns></returns>
        public string GetJobs(string searchFilter, string oauthToken, string oauthTokenSecret)
        {
            string nonce = oAuth.GenerateNonce();
            string timeStamp = oAuth.GenerateTimeStamp();
            string normalizedUrl = string.Empty;
            string normalizedRequestParameters = string.Empty;
            string signature = oAuth.GenerateSignature(new Uri(searchFilter), Utils.APIKey, Utils.SecretKey, oauthToken, oauthTokenSecret, "GET", timeStamp, nonce, out normalizedUrl, out normalizedRequestParameters);
            normalizedRequestParameters = normalizedRequestParameters += String.Format("&oauth_signature={0}", HttpUtility.UrlEncode(signature));
 
            try
            {
                return client.DownloadString(String.Format("{0}?{1}", normalizedUrl, normalizedRequestParameters));
            }
            catch (Exception ex)
            {  
                return ex.Message;
            }
        }
 
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
 
        protected virtual void Dispose(bool disposing)
        {
            if (!disposed)
            {
                if (disposing)
                    client.Dispose();
                disposed = true;
            }
        }
    }
}

The results are a list of jobs available to the user:

Conclusion

This was a complete C# usage example of the LinkedIn API. You can find the API reference in the developer portal and download the sample code below.

How to record Skype voice conversations

Gal Ratner — Sat, 27 Aug 2011 16:32:00 GMT

First let’s start at the end. If you are looking for free software to record Skype voice conversations, please skip to the bottom of this page and download the attached file. It contains a Windows Installer msi file and should install a recorder on your machine.

The rest of you, please keep reading :)

Skype uses a public API to listen and transmit messages to all programs on your computer. Messages are being transmitted via the native windows API and will require us to use some external method calls.
In this article we are going to build a WPF client that will communicate with Skype, detect voice calls, redirect the incoming and outgoing streams into files and finally, create a complete conversation file.

Let’s start by including the necessary external methods:

[DllImport("user32.dll")]
static extern uint RegisterWindowMessage(string lpString);
 
[DllImport("user32.dll")]
public static extern IntPtr SendMessageTimeout(IntPtr windowHandle, uint Msg, IntPtr wParam, IntPtr lParam, SendMessageTimeoutFlags flags, uint timeout, out IntPtr result);
 
[DllImport("user32.dll")]
public static extern IntPtr SendMessageTimeout(IntPtr windowHandle, uint Msg, IntPtr wParam, ref COPYDATASTRUCT lParam, SendMessageTimeoutFlags flags, uint timeout, out IntPtr result);

Communication with Skype

Sending message

SendMessageTimeout sends the specified message to one or more windows. Notice we have two overloads of SendMessageTimeout. One accepts a value to be sent and the other accepts a stuct. We are going to use the first overload to connect to Skype and the second to send API commands.

Receiving messages

In order to receive messages from Skype we need to register our window in the system. RegisterWindowMessage defines a new window message that is guaranteed to be unique throughout the system. The lpString can be used when sending or posting messages.

First let’s get a handle to our window and then register it:

IntPtr windowHandle = new WindowInteropHelper(this).Handle;
NativeCalls.DetectSkype(windowHandle);

/// <summary>
        /// Run at startup to register a system window message
        /// </summary>
        /// <param name="windowHandle">Handle to the current program window</param>
        /// <returns></returns>
        public static bool DetectSkype(IntPtr windowHandle)
        {
            hWnd = windowHandle;
            APIDiscover = RegisterWindowMessage(Utils.SkypeDiscover);
            if (APIDiscover == 0)
                return false;
            APIAttach = RegisterWindowMessage(Utils.SkypeAttach);
            if (APIAttach == 0)
                return false;
 
            return true;
        }

Next we need to intercept incoming messages. We can do that by attaching a native hook to the method WndProc. This method is available to us in windows forms; however, since we are using WPF we will need to explicitly attach it to our window:

HwndSource source = PresentationSource.FromVisual(this) as HwndSource;
source.AddHook(WndProc);

private IntPtr WndProc(IntPtr hwnd, int msg, IntPtr wParam, IntPtr lParam, ref bool handled)
        {
            if (msg == NativeCalls.APIAttach && (uint)lParam == NativeCalls.SKYPECONTROLAPI_ATTACH_SUCCESS)
            {
                // Get the current handle to the Skype window
                NativeCalls.HWND_BROADCAST = wParam;
                handled = true;
                return new IntPtr(1);
            }
            // Skype sends our program messages using WM_COPYDATA. the data is in lParam
            if (msg == NativeCalls.WM_COPYDATA && wParam == NativeCalls.HWND_BROADCAST)
            {
                COPYDATASTRUCT data = (COPYDATASTRUCT)Marshal.PtrToStructure(lParam, typeof(COPYDATASTRUCT));
                StatusTextBox.AppendText(data.lpData + Environment.NewLine);
                // Check for connection
                if (data.lpData.IndexOf("CONNSTATUS ONLINE") > -1)
                    ConnectButton.IsEnabled = false;
                // Check for calls
                IsCallInProgress(data.lpData);
                handled = true;
                return new IntPtr(1);
            }
 
            return IntPtr.Zero;
        }

Every time we receive a notification from Skype we need to check for beginning and end of voice calls and if a call that we are recording has ended we need to process the results.

Connecting to a working instance of Skype

Once have launched and logged into our account in Skype we need to try and connect to the working instance. We can do that by sending a message with the DiscoverAPI handle. Skype will pop up a dialog asking us if we allow a program to connect to Skype. Once we clicked allow we will get back a message indicating we are online:

public static void ConnectToSkype()
        {
            // To initiate communication, a client application broadcasts the SkypeControlAPIDiscover message, 
            // including its window handle as a wParam parameter. 
            // Skype responds with a SkypeControlAPIAttach message to the specified window and indicates the connection status.
            IntPtr result;
            IntPtr aResult = SendMessageTimeout(HWND_BROADCAST, APIDiscover, hWnd, IntPtr.Zero, SendMessageTimeoutFlags.SMTO_NORMAL, 100, out result);
        }

Starting and stopping voice recording

Each conversation in Skype has an ID. We need to capture the ID in order to create the conversation file name:

/// <summary>
        /// Check is a call is in progress and activate the record button
        /// </summary>
        /// <param name="status"></param>
        private void IsCallInProgress(string status)
        {
            //Listen to: CALL {} STATUS INPROGRESS
            if (status.IndexOf("CALL") > -1 && status.IndexOf("STATUS INPROGRESS") > -1)
            {
                string callRegex = @"CALL\s+(\d+)";
                Regex r = new Regex(callRegex, RegexOptions.IgnoreCase);
                Match m = r.Match(status);
                Utils.CurrentCallNumber = Convert.ToInt32(m.Groups[1].Value);
                RecordButton.IsEnabled = true;
            }
            else if (status.IndexOf("CALL") > -1 && status.IndexOf("STATUS FINISHED") > -1)
            {
                // Call ended.
                Utils.CurrentCallNumber = 0;
                RecordButton.IsEnabled = false;
                // If we are still recording, stop and prcess the conversation
                if (RecordButton.Content.ToString() == "Stop Recording")
                {
                    RecordButton.Content = "Start Recording";
                    MakeConversationFile();
                }
            }
        }

Once we have discovered that a conversation has started we need to send two commands to Skype. The first command will redirect the incoming audio stream to a wave file and the second will do the same to the local microphone audio stream:

public static string RecordOutputCommand = @"ALTER CALL {0} SET_OUTPUT FILE=""{1}.output""";
public static string RecordInputCommand = @"ALTER CALL {0} SET_CAPTURE_MIC FILE=""{1}.input""";

private void RecordButton_Click(object sender, RoutedEventArgs e)
        {
            // Fill in the new conversation file name
             string conversationFile = String.Format(Utils.SkypeConversationsFile, Utils.SkypeConversationsFolder, Utils.CurrentCallNumber);
             FileNameTextBox.Text = conversationFile;
             try
             {
                 if (!Directory.Exists(Utils.SkypeConversationsFolder))
                     Directory.CreateDirectory(Utils.SkypeConversationsFolder);
             }
             catch (Exception ex)
             {
                 MessageBox.Show("Cannot create directory " + Utils.SkypeConversationsFolder + ". " + ex.Message);
             }
            // Start recording
            if (RecordButton.Content.ToString() == "Start Recording")
            {
                string recordOutputCommand = String.Format(Utils.RecordOutputCommand, Utils.CurrentCallNumber, conversationFile);
                string recordInputCommand = String.Format(Utils.RecordInputCommand, Utils.CurrentCallNumber, conversationFile);
                NativeCalls.SendSkypeMessage(recordOutputCommand);
                NativeCalls.SendSkypeMessage(recordInputCommand);
                RecordButton.Content = "Stop Recording";
            }
            else
            {
                // Stop recording
                string recordEndOutputCommand = String.Format(Utils.RecordEndOutputCommand, Utils.CurrentCallNumber);
                string recordEndInputCommand = String.Format(Utils.RecordEndInputCommand, Utils.CurrentCallNumber);
                NativeCalls.SendSkypeMessage(recordEndOutputCommand);
                NativeCalls.SendSkypeMessage(recordEndInputCommand);
                RecordButton.Content = "Start Recording";
                MakeConversationFile();
            }
        }

To stop recording we simply redirect both stream back into their default location:

public static string RecordEndOutputCommand = @"ALTER CALL {0} SET_OUTPUT SOUNDCARD=""default""";
public static string RecordEndInputCommand = @"ALTER CALL {0} SET_CAPTURE_MIC PORT=""356""";

Now that we have finished recording the conversation we have two files, each fine contains different output and we need to merge them into a single conversation file.

Merging wav files with SoX

SoX is a small media processing utility program. You can download it from sourceforge here.
We are going to invoke SoX from an external process and use its command line parameters to merge the files.

/// <summary>
        /// Process both input and microphone files into a single conversation file
        /// </summary>
        private void MakeConversationFile()
        {
            string conversationFile = FileNameTextBox.Text;
            Task.Factory.StartNew(() =>
            {
                Thread.Sleep(5000); // Time for skype to release the files
                // Run Sox to merge both files into a single file
                Utils.RunExternalProcess(Environment.CurrentDirectory + @"\Sox\Sox", @"-m """ + conversationFile + @".output""" + @" """ + conversationFile + @".input""" + @" """ + conversationFile + @"""", true, 3000, string.Empty, string.Empty, false, true, false, true);
                // If sox created the new file, delete both original files
                try
                {
                    if (File.Exists(conversationFile))
                    {
                        File.Delete(conversationFile + ".input");
                        File.Delete(conversationFile + ".output");
                    }
                }
                catch { }
                // Open the folder
                Process.Start("explorer.exe", "/select," + conversationFile);
            });
        }

You can tell that the last step was to open the location of the conversation file for the user to inspect.

Conclusion

The Skype open API contains many more useful commands and you can see the complete documentation here. For your connivance I have attached the complete code to this example and a full msi installer for a working demo program.

Secure user authentication with one way password hash

Gal Ratner — Tue, 05 Jul 2011 20:34:00 GMT

Keeping users passwords in your database is a part of almost every application, yet securing passwords is rarely being done correctly.

I recently read an article by Coda Hale about the ineffectiveness of password salts. Coda Suggested using bcrypt to store passwords. He reasoned his argument by explaining bcrypt is extremely slow to compute, therefore making it slow to hack.

I completely agree, however, I wanted to add another way of safely storing passwords in a more conventional way by hiding the salt in the hash. The idea wasn’t mine. It belongs to a DBA named Scott Hulberg. It’s pretty simple and for the sake of this blog post I am not going to implement it completely. I am going to prepend the salt to the password hash, making it invisible to a hacker. You can go further by writing an algorithm to plant the salt in the hash array as you see fit.

Since the only way to match a one way hashed password is to use the salt we used to generate this hash, if a hacker cannot get to the salt, they cannot retrieve the original password.
Let’s begin by composing the method to create our hash and prefix it with the salt:

public static readonly int PASSWORD_SALT_LENGTH = 16;
 
    /// <summary>
    /// Generate a one way hash from a password.
    /// </summary>
    /// <param name="password"></param>
    /// <returns></returns>
    public static byte[] GetHashedPasswordBytes(string password)
    {
        // Generate random bytes as salt.
        // Prepend the salt and compute the hash along with the password
        // Then prepend the salt to the hashed result
 
        byte[] salt = PasswordUtils.GetRandomBytes(PasswordUtils.PASSWORD_SALT_LENGTH); // Get a random array of bytes
        byte[] enteredPassword = Encoding.UTF8.GetBytes(password); // Get the password bytes
        List<byte> passwordByteList = new List<byte>(); // Prepend the salt to the password
        passwordByteList.AddRange(salt);
        passwordByteList.AddRange(enteredPassword);
        byte[] passwordHashResult;
 
        using (SHA256 shaM = new SHA256Managed())
        {
            passwordHashResult = shaM.ComputeHash(passwordByteList.ToArray()); // Get the hash
        }
        passwordByteList.Clear();
        passwordByteList.AddRange(salt); // Prepend the salt Hash result
        passwordByteList.AddRange(passwordHashResult);
        return passwordByteList.ToArray();
    }
 
    /// <summary>
    /// Get an array of random bytes
    /// </summary>
    /// <param name="size">The size of the array</param>
    /// <returns></returns>
    public static byte[] GetRandomBytes(int size)
    {
        byte[] random = new Byte[size];
        RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
        rng.GetNonZeroBytes(random);
        return random;
    }

This is the method to re-generate a password hash at login time:

 /// <summary>
    /// Compares a generated password hash to the original password.
    /// </summary>
    /// <param name="password">The original password</param>
    /// <param name="passwordHash">The hash</param>
    /// <returns>Thre if the password matches</returns>
    public static bool ComparePasswordHash(string password, byte[] passwordHash)
    {
        bool isValidated = false;
        byte[] salt = new byte[PasswordUtils.PASSWORD_SALT_LENGTH];
        byte[] hash = new byte[passwordHash.Length - PasswordUtils.PASSWORD_SALT_LENGTH];
        // userPassword is composed from the hash value and the salt.
        // seperate the salt from the hash value and use the salt to compute and compare the passwords
        Array.Copy(passwordHash, salt, salt.Length);
        Array.Copy(passwordHash, PasswordUtils.PASSWORD_SALT_LENGTH, hash, 0, hash.Length);
        byte[] enteredPassword = Encoding.UTF8.GetBytes(password);
        List<byte> passwordByteList = new List<byte>();
        passwordByteList.AddRange(salt);
        passwordByteList.AddRange(enteredPassword);
        byte[] passwordHashResult;
        using (SHA256 shaM = new SHA256Managed())
        {
            passwordHashResult = shaM.ComputeHash(passwordByteList.ToArray());
        }
        if (PasswordUtils.CompareBytes(passwordHashResult, hash))
            isValidated = true;
        return isValidated;
    }
 
    /// <summary>
    /// Compare two byte arrarys
    /// </summary>
    /// <param name="array1"></param>
    /// <param name="array2"></param>
    /// <returns></returns>
    public static bool CompareBytes(byte[] array1, byte[] array2)
    {
        if (array1.Length != array2.Length)
            return false;
 
        for (int i = 0; i < array1.Length; i++)
            if (array1[i] != array2[i])
                return false;
 
        return true;
    }

Now to log in a user we are going to compare the original hash to the new hash. If the hash matches, then the user has entered the correct password:

var passwordHash = PasswordUtils.GetHashedPasswordBytes("test");
        if (PasswordUtils.ComparePasswordHash("test", passwordHash))
            Console.WriteLine("Password mached");

As you can tell, unless a hacker knows where they salt is stored, they cannot retrieve it and cannot re-compute the original password.
Thank you Brad Laney for sending me the original Coda article.

Display your top selling products in ASP.NET using a Bubble Chart

Gal Ratner — Tue, 28 Jun 2011 04:01:00 GMT

System.Web.DataVisualization contains 34 types of charts. The most common of them is the Column chart on which I already blogged about in Display a sales chart with ASP.NET Chart control and Linq to SQL.

Today I am going to take our sales chart one step further and display the top selling products for the year. Since we have multiple products for each time span, I chose a Bubble chart.
First let’s look at the data model:

We are going to group our year’s sales into months and extract the top 5 selling products for that month.

This is our Linq statement:

var productSales = from o in context.Orders
                               where o.DatePlaced.Value.Year == year
                               group o by o.DatePlaced.Value.Month into g
                               orderby g.Key
                               select new
                               {
                                   Month = g,
                                   TopProducts = (from op in context.OrderProducts
                                                  where op.OrderDate.Value.Year == year && op.OrderDate.Value.Month == g.Key
                                                  group op by op.ProductID into opg
                                                  orderby opg.Count() descending
                                                  select new { ProductName = context.Products.Where(p => p.ProductID == opg.Key).Single().ProductName, ProductCount = opg.Count() }).Take(5)
                               };

This is our chart:

 <asp:Chart ID="ProductSalesChart" Width="400px" runat="server">
         <Legends>
            <asp:Legend Name="Default" Title="Top Selling Items"></asp:Legend>
        </Legends>
            <Series>
               
            </Series>
            <ChartAreas>
                <asp:ChartArea Name="ChartArea1">
                </asp:ChartArea>
            </ChartAreas>
        </asp:Chart>

Now let’s add a new Series for each month. We are going to display the current month as text and add new data points indicating the top selling products.

 foreach (var sale in productSales)
            {
                Series series = new Series(Enum.Parse(typeof(Month), sale.Month.FirstOrDefault().DatePlaced.Value.Month.ToString()).ToString()) { ChartType = SeriesChartType.Bubble};
                foreach (var topProduct in sale.TopProducts){
                    DataPoint point = new DataPoint() { XValue = sale.Month.Key, YValues = new double[] { (double)topProduct.ProductCount }, Label = topProduct.ProductName };
                    series.Points.Add(point);
                    
                }
                ProductSalesChart.Series.Add(series);
            }

The end result is the following chart:

As you can see it is fairly simple to create a visually appealing chart that can help sales professional better understand sales data.

Sharing memory session between servers

Gal Ratner — Wed, 01 Jun 2011 20:25:00 GMT

Session variables hold per user information. Unlike cookies, sessions store information on the server rather than on the client. The client holds a session cookie with the client’s session ID and at the time of an HTTP request, the server accesses the client’s session ID and retrieves the session data.

The default implementation of session has the web server holding the information in memory. This implementation as fast as it is has some drawbacks: Recycling your app pool or restarting IIS will abandon all of the sessions currently in memory. Sharing sessions between web servers is also not possible and when load balancing traffic between servers we need to resort to “sticky sessions”, a method in which all client requests will be redirected to the same physical server as the first request. This would enable us to still use default sessions, however, it will not solve the first issue I mentioned and we are still running the risk of overloading a server’s memory under heavy traffic load when some of our servers are slow to respond.

ASP.NET supports four session storage options:

Off: Session state is disabled.
InProc: Session state is in process with an ASP.NET worker process.
StateServer: Session state is using the out-of-process ASP.NET State Service to store state information.
SQLServer: Session state is using an out-of-process SQL Server database to store state information.
Custom: Session state is using a custom data store to store session-state information.
In this article I am going to discuss the StateServer option in which the server holds session data in memory and outside the web server. This allows the presents of a dedicated session server and sharing of session data between all servers.
The internal .NET implementation of StateServer resides in System.Web.SessionState.OutOfProcSessionStateStore and internally relies on socket connections methods in webengine.dll. Since all session data passes through sockets, all data must be serializable.

In order to activate the session store we need to run the aspnet_state.exe service.

In our web.config we need to set the session state mode:
<configuration>
<system.web>
    <sessionState mode="StateServer"
      stateConnectionString="tcpip=SampleStateServer:42424"
      cookieless="false"
      timeout="20"/>
</system.web>
</configuration>

The last step would be to sync up all of our servers machine keys in our web.config.

machineKey configures algorithms and keys to use for encryption, decryption, and validation of forms-authentication data and view-state data, and for out-of-process session state identification.
We need to synch two keys:
validationKey: used to generate out-of-process, application-specific session IDs to ensure that session-state variables are isolated between sessions.
decryptionKey: Specifies the key that is used to encrypt and decrypt data or the process by which the key is generated.
This is how our machineKey will look like:

machineKey is located in our <system.web> tag.
Syncing our machineKey is done since session information is encrypted and we need all the web servers to be able to encrypt and decrypt the data.
You can generate new keys using the following code:

class Program
	{
		static void Main(string[] args)
		{
			CreateMachineKey(24, 64);
		}
 
		public static void CreateMachineKey(int decryption, int validation)
		{
			string decryptionKey = CreateKey(decryption);
			string validationKey = CreateKey(validation);
			Console.WriteLine(@"<machineKey validationKey=""{0}"" decryptionKey=""{1}"" validation=""SHA1""/>", validationKey, decryptionKey);
			Console.ReadLine();
		}
 
		public static string CreateKey(int numBytes)
		{
			RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
			byte[] buff = new byte[numBytes - 1];
			rng.GetBytes(buff);
			return BytesToHexString(buff);
		}
 
		public static string BytesToHexString(byte[] bytes)
		{
			StringBuilder hexString = new StringBuilder(64);
			for (int i = 0; i < bytes.Length; i++)
				hexString.Append(String.Format("{0:X2}", bytes[i]));
			int counter;
			return hexString.ToString();
		}
	}

Once we have it all configured we can give our website a try and make sure all of our session variables are working.
If we encounter any empty session values we will most likely need to examine serialization issues in our session objects.

Conclusion
Keeping sessions in a dedicated session state server can help alleviate memory pressure on your web server as well as help with memory data duplication. It is also a good practice with any new website development to use serializable session objects as pre planning for StateServer mode.

Spatial Search made easy with Google Maps API and SQL Server 2008

Gal Ratner — Sat, 14 May 2011 15:20:00 GMT

Spatial search is the process of searching locations nearby to a position in space. A good example of a spatial search would be finding all of the restaurants near your present location. With Google’s Maps API and SQL Server’s Geography data types we can build a quick spatial search in no time.

In this little exercise I will be detecting the user’s location, displaying it on a map and suggesting nearby restaurants. Since not all browsers support the W3C standards I will also allow manual input of the user’s address.
Spatial search relies on latitude and longitude which are the global position coordinates. The process of turning an address to a set of latitude and longitude coordinates is referred to as Geocoding.

Let’s start by adding the Google Maps API along with the auto user location detection. We are also going to add a service all to our database. The database will contain the recommended restaurants along with their latitude and longitude. The service call will be invoked by clicking the button when viewing the page with a non Geolocation supported browsers.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.2/jquery.min.js"></script>
    <script type="text/javascript" src="http://maps.google.com/maps/api/js?sensor=true"></script>
    <script type="text/javascript" src="http://code.google.com/apis/gears/gears_init.js"></script>
<script type="text/javascript">
 
    var initialLocation;
    var map;
    var infowindow = new google.maps.InfoWindow();
    var latitude;
    var longitude;
 
    function initialize() {
        var myOptions = {
            zoom: 10,
            mapTypeId: google.maps.MapTypeId.ROADMAP
        };
        map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
 
        // Try W3C Geolocation method (Preferred)
        if (navigator.geolocation) {
            navigator.geolocation.getCurrentPosition(function (position) {
                latitude = position.coords.latitude;
                longitude = position.coords.longitude;
                initialLocation = new google.maps.LatLng(position.coords.latitude, position.coords.longitude);
                contentString = "Location found using W3C standard";
                map.setCenter(initialLocation);
                infowindow.setContent(contentString);
                infowindow.setPosition(initialLocation);
                infowindow.open(map);
            }, function () {
                handleNoGeolocation();
            });
        } else if (google.gears) {
            // Try Google Gears Geolocation
            var geo = google.gears.factory.create('beta.geolocation');
            geo.getCurrentPosition(function (position) {
                latitude = position.coords.latitude;
                longitude = position.coords.longitude;
                initialLocation = new google.maps.LatLng(position.latitude, position.longitude);
                contentString = "Location found using Google Gears";
                map.setCenter(initialLocation);
                infowindow.setContent(contentString);
                infowindow.setPosition(initialLocation);
                infowindow.open(map);
            }, function () {
                handleNoGeolocation();
            });
        } else {
            // Browser doesn't support Geolocation
            handleNoGeolocation();
        }
    }
 
    function handleNoGeolocation(errorFlag) {
        $('#noLocation').show();
    }
 
    function codeAddress() {
        var geocoder = new google.maps.Geocoder();
        geocoder.geocode({ 'address': $("#LocationText").val() }, function (results, status) {
            if (status == google.maps.GeocoderStatus.OK) {
                map.setCenter(results[0].geometry.location);
                var marker = new google.maps.Marker({
                    map: map,
                    position: results[0].geometry.location
                });
                latitude = results[0].geometry.location.lat();
                longitude = results[0].geometry.location.lng();
                getNearby();
            } else {
                alert("Geocode was not successful for the following reason: " + status);
            }
        });
    }
 
    $(function () {
        initialize(); ;
    });
 
    function getNearby() {
        jQuery.ajax({
            url: 'NearbyLocations.svc/GetNearbyLocations',
            data: "latitude=" + latitude + "&longitude=" + longitude,
            dataType: 'json',
            success: function (data) {
                $.each(data.d, function (i, item) {
                    var myLatlng = new google.maps.LatLng(item.Latitude, item.Longitude);
                    var marker = new google.maps.Marker({ position: myLatlng, title: item.Description });  
                    marker.setMap(map);  
                })
            }
        });
    }
</script>
 
</head>
<body>
    <form id="form1" runat="server">
    <div id="noLocation" style="display:none">Enter Address 
    <input id="LocationText" type="text" value="Beverly Hills, California" /><input id="AddressButton1" type="button" value="Set Address" onclick="codeAddress();" /></div>
 
    <div id="map_canvas" style="height:500px; width:800px"></div>
    </form>
</body>
</html>

This is our database table:

I have added some of my favorite restaurants:

In order to search the database for locations close to ours we will convert our latitude and longitude coordinates to the SQL data type GEOGRAPHY and search for places nearby using STDistance. STDistance will return the distance in meters between two GEOGRAPHY points so we need to convert it to miles.

USE [GeolocationExample]
GO
 
/****** Object:  StoredProcedure [dbo].[GetNearbyLocations]    Script Date: 05/14/2011 08:25:25 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
CREATE PROCEDURE [dbo].[GetNearbyLocations] 
	@Distance int,
	@Latitude float,
	@Longitude float
AS
BEGIN
	
	SET NOCOUNT ON;
 
    DECLARE @StartPoint geography;
	SET @StartPoint = GEOGRAPHY::STGeomFromText('Point(' + CAST(@Longitude AS VARCHAR(32)) + ' ' + CAST(@Latitude AS VARCHAR(32)) + ')',4326)
	
	SELECT * FROM Location L
	WHERE
    GEOGRAPHY::STGeomFromText('Point(' + CAST(L.Longitude AS VARCHAR(32)) + ' ' + CAST(L.Latitude AS VARCHAR(32)) + ')',4326).STDistance(@StartPoint) <= (@Distance / 0.000621371192)
 
END
 
GO

The final step will be using a WCF service to invoke our stored procedure along with our desired search radius:

using System.Collections.Generic;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.ServiceModel.Web;
 
using GeolocationExampleModel;
 
[ServiceContract(Namespace = "")]
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
public class NearbyLocations
{
    [OperationContract]
    [WebGet]
    public List<GeoLocation> GetNearbyLocations(double latitude, double longitude)
	{
        WebOperationContext.Current.OutgoingResponse.Headers.Add("Cache-Control", "no-cache"); WebOperationContext.Current.OutgoingResponse.Headers.Add("Pragma", "no-cache");
        List<GeoLocation> locations = new List<GeoLocation>();
        int distance = 20; // Miles
        using (GeolocationExampleEntities context = new GeolocationExampleEntities())
        {
            var nearby = context.GetNearbyLocations(distance, latitude, longitude);
            foreach (var near in nearby)
                locations.Add(new GeoLocation() { Description = near.Description, Latitude = near.Latitude, Longitude = near.Longitude });
        }
        return locations;
	}
 
    [DataContract]
    public class GeoLocation
    {
        [DataMember]
        public string Description { get; set; }
        [DataMember]
        public double Latitude { get; set; }
        [DataMember]
        public double Longitude { get; set; }
    }
}

Here I used Entity Framework in order to return the results from the database, however, I needed to convert them to a valid data contract in order to pass them to the client.

This is the final Map:

The map contains all three records as markers.

Conclusion:
As you can tell spatial search can be composed together fairly quickly, however, as always when we are using 3rd party components we are subject to Google’s terms of service. I hope you find this useful.

Consume an OData WCF Data Service as JSON with JQuery

Gal Ratner — Thu, 14 Apr 2011 20:34:00 GMT

OData(Open Data Protocol) is an HTTP based protocol for querying and updating data using full REST syntax. The full specifications of OData can be found on http://odata.org

WCF Data Services (formerly ADO.NET Data Services, formerly Project Astoria) is the .NET based implementation of OData enabled services. WCF Data Services can use an Entity Framework Context or any data model exposing at least one property that returns an entity set that is an IQueryable(Of T). The model must also implement IUpdatable.

A sample http request to an OData service will look like:
http://services.odata.org/OData/OData.svc/Categories(1)/Products?$skip=2

This example will get the products associated with the category identified by key value 1, starting with the third product.

WCF Data Services is capable of returning data in multiple formats. The default format will be set based on the requesting client’s accept request header with an option to override the format using the $format query option. For example, this will return the results from the previous example in JSON format:
http://services.odata.org/OData/OData.svc/Categories(1)/Products?$skip=2&$format=json

Currently supported formats and their matching accept request headers
•    Atom: application/atom+xml
•    Xml: application/xml
•    Json: application/json

In this article we are going to create a new WCF Data Service, use a code first Entity Framework model and consume a JSON feed using JQuery.

The ET Model we are using is the same model used in my article http://galratner.com/blogs/net/archive/2011/03/24/four-ways-to-utilize-entity-framework-4.aspx with a slight change; since the service invokes the model’s default constructor we are going to make sure our string connection is set in the process. The updated model looks like this:

public class EFExampleDbContext : DbContext
    {
        public EFExampleDbContext()
            : base("EFExampleEntitiesStringConnection")
        {
 
        }
 
        public EFExampleDbContext(string nameOrConnectionString)
            : base(nameOrConnectionString)
        {
 
        }
        
        public DbSet<Customer> Customers { get; set; }
        public DbSet<Order> Orders { get; set; }
    }

Now let’s create a new WCF Data Service and set the appropriate permissions on our entity sets:

public class CustomersDataService : DataService<EFExampleDbContext>
    {
        public static void InitializeService(DataServiceConfiguration config)
        {
            config.SetEntitySetAccessRule("Customers", EntitySetRights.AllRead);
            config.SetEntitySetAccessRule("Orders", EntitySetRights.AllRead);
            config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
        }
    }

The last step will be to query our service from the client. Let’s use JQuery’s getJSON method since it internally sets the accept request header to application/json.

<script>
    $.getJSON("CustomersDataService.svc/Customers",
  function (data) {
      $.each(data.d, function (i, item) {
          $("<p/>").html(item.FirstName + " " + item.LastName).appendTo("#myData");
      });
  });
 </script>

<div id="myData"></div>

This will query our CustomersDataService for all of our customers, get the response in JSON format and display the data in the #myData div.

Conclusion
OData can be a very flexible and powerful protocol when used to retrieve and update data from multiple clients using a single data model on the server. There are many open source client projects and Javascript helper libraries with a full list on http://odata.org. OData is widely adopted including support in RIA services, Share point and more. If you are planning to syndicate content over HTTP WCF Data Services might be your preferred option.

Four ways to utilize Entity Framework 4

Gal Ratner — Thu, 24 Mar 2011 05:28:00 GMT

There is some confusion out there about a simple question: How can I use Entity Framework? Some developers have heard about reverse engineering models so I decided to write a quick preview to the main features of EF4. This isn’t an in-depth article, but, rather a visual summary of the main four scenarios EF can accommodate. If you are looking to utilize any of the scenarios described here you might need to seek further documentation and realize that development with Entity Framework is a vast subject and is beyond the scope of a single article.

Let’s explore the scenarios available to us:

Database First
The most common scenario is starting with an existing database and generating a model from it. I have created a small database with two tables, one for customers and the other for orders. There is a one-to-many relationship between a customer and the orders that can belong to a customer

We can begin by adding a new ADO.NET Entity Data Model

The Entity Data Model Wizard will ask us to choose a Model Contents. Lets select Generate From Database

We can select an existing database connection or define a new connection.

Finally the Wizard will ask us to select the objects we want the model to contain: Tables, Views and Stored Procedures.

When we click finish the wizard will create the new model. We can see here that both of our tables are now in the model.

Model First
In a Model First approach we will create a model first and then generate a database from it. Let’s begin by selecting an empty model from the Entity Data Model Wizard.

An empty design surface will show up, allowing us to select and add Associations, Entities and Inheritance from the toolbox on the left. When we are done designing our model we can click anywhere in the design pan and select Generate Database from model.

The Generate Database Wizard will open and show the SQL that is about to run in order to create our new database. Here we can fine tune the create statements in order to optimize columns, indexes etc.

Here is the final SQL about to run on our new database

Model with POCO
POCO (Plain Old CLR Object) can replace your entities and are useful when you need more control over your data objects. For example if you are returning a data object from a WCF and need to serialize some of the fields you can add a POCO object with data member attributes on the properties you need to return. Although you can code your POCOs yourself it will be significantly easier to use a POCO Generator. You can download the ADO.NET C# POCO Entity Generator from your Visual Studio’s Extension Manager. The generator is T4 (Text Template Transformation Toolkit) based and will create the .tt files in your solution. As a first step, search and install the generator using the Extension Manger.

Then right click on your model and select Add Code Generation Item

Select ADO.NET POCO Entity Generator.

You will get some security dialogs while the templates are generating your POCO classes. The end result will be a class for each model entity and a class for the Context. There are some gotchas with POCOs. The syntax might differ and you can’t mix POCO and regular models. Performance will too suffer as Entity Framework creates some background proxy objects.

This is an example of a generated POCO:

Code First
The last use case is hard coding both the Context and the entities to create or use a database structure. In a Code First case we can generate a database whenever the model changes, make changes to an existing database or simply use an existing database. This option differs from model with POCO in a few areas; since the model is able to make changes to dataset, the entities are more tightly coupled with database tables. Primary and foreign keys can be defined as attributes in the entity classes or using the Fluent API.
To use code first we need to download the RC release of EF 4.1 using NuGet’s supported package sources. We are going to select and install the library EntityFramework which contains the RC release.

After we have done that and NuGet has added the necessary references to our project we need to code the context to match our existing database. We are going to inherit from DBContext and since we are using an existing string connection we added a constructor to accept the connection name if the string connection matches the model name, ET will automatically use it. In our case we set it ourselves.

Now we can code the individual entity classes

Putting everything together looks like this:

We can tell that the model followed our foreign key constraint as we defined in the attributes.

Conclusion
This has been a very short summary of all of the current options available to you when working with Entity Framework 4. I did not go into details for each method as there is plenty of documentation out there. I hope you find this article useful when deciding on development strategy with Entity Framework.

Reading an Excel spreadsheet into dynamic objects

Gal Ratner — Fri, 18 Mar 2011 02:36:00 GMT

Excel represents documents using Office Open XML (OOXML), a zipped XML based format developed for spreadsheet and chart representation. In this article I am going to demonstrate parsing an Excel spreadsheet and loading the data into a generic List. OOXML was incorporated into Office in 2007 therefore the following code will not work with older versions of Excel.
This is our spreadsheet:

In order to read and parse the Spreadsheet we are going to use the Open XML SDK 2.0 for Microsoft Office available for download from Microsoft. The complete SDK includes the DocumentFormat.OpenXml library and some productivity tools that will help you examine the structure of Office documents. Open XML SDK is built on top of System.IO.Packaging so in order to use the DocumentFormat.OpenXml library you also need to import WindowsBase.dll.

Let’s begin by loading the correct worksheet into a SpreadsheetDocument

private static List<ExpandoObject> GetSpreadsheetData(string workSheet, string filePath)
        {
            List<ExpandoObject> data = new List<ExpandoObject>();
 
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filePath, false))
            {
                // Get the worksheet we are working with
                IEnumerable<Sheet> sheets = spreadsheetDocument.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == workSheet);
                WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheets.First().Id);
                Worksheet worksheet = worksheetPart.Worksheet;
                SharedStringTablePart sstPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                SharedStringTable ssTable = sstPart.SharedStringTable;
                // Get the CellFormats for cells without defined data types
                WorkbookStylesPart workbookStylesPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<WorkbookStylesPart>().First();
                CellFormats cellFormats = (CellFormats)workbookStylesPart.Stylesheet.CellFormats;
 
                ExtractRowsData(data, worksheet, ssTable, cellFormats);
            }
 
            return data;
        }

As you can tell we loaded the worksheet and a styles used for cell formatting. This will come in handy later on.
Now that we have the worksheet we use the first row to set the field names of our objects. We use ExpandoObject since it allows dynamically adding of members.

/// <summary>
        /// Get the data using the first row as columns and the rest of the rows as data
        /// </summary>
        /// <param name="data"></param>
        /// <param name="worksheet"></param>
        /// <param name="ssTable"></param>
        /// <param name="cellFormats"></param>
        private static void ExtractRowsData(List<ExpandoObject> data, Worksheet worksheet, SharedStringTable ssTable, CellFormats cellFormats)
        {
            var columnHeaders = worksheet.Descendants<Row>().First().Descendants<Cell>().Select(c => Convert.ToString(ProcessCellValue(c, ssTable, cellFormats))).ToArray();
            var columnHeadersCellReference = worksheet.Descendants<Row>().First().Descendants<Cell>().Select(c => c.CellReference.InnerText.Replace("1", string.Empty)).ToArray();
            var spreadsheetData = from row in worksheet.Descendants<Row>()
                                  where row.RowIndex > 1
                                  select row;
 
            foreach (var dataRow in spreadsheetData)
            {
                dynamic row = new ExpandoObject();
                Cell[] rowCells = dataRow.Descendants<Cell>().ToArray();
                for (int i = 0; i < columnHeaders.Length; i++)
                {
                    // Find and add the correct cell to the row object
                    Cell cell = dataRow.Descendants<Cell>().Where(c => c.CellReference == columnHeadersCellReference[i] + dataRow.RowIndex).FirstOrDefault();
                    if (cell != null)
                        ((IDictionary<String, Object>)row).Add(new KeyValuePair<String, Object>(columnHeaders[i], ProcessCellValue(cell, ssTable, cellFormats)));
                }
                data.Add(row);
            }
        }

Its important to mention that fields that are empty cells are not a part of the row collection and do not show up in the underlying XML structure, therefore we need to explicitly search for the correct cell in the current row by matching its CellReference to the CellReference of the header. For example: Cell A7 will be matched against header A1. If a Cell exists in the XML we will add it as an object member.
The last step is to process the cell’s value. Values in Excel can be a little misleading. Some values will be held in the actual cell, some will be held in the SharedStringTable. Some cells data types will be set and some wont forcing us to rely on cell formatting. I have not found a defined set of guidelines to extracting cell values and all of the code examples I found online did not fully work so I had to simply rely on reading the underlying XML source and coming up with my own set of rules.

/// <summary>
        /// Process the valus of a cell and return a .NET value
        /// </summary>
        static Func<Cell, SharedStringTable, CellFormats, Object> ProcessCellValue = (c, ssTable, cellFormats) =>
        {
            // If there is no data type, this must be a string that has been formatted as a number
            if (c.DataType == null)
            {
                CellFormat cf = cellFormats.Descendants<CellFormat>().ElementAt<CellFormat>(Convert.ToInt32(c.StyleIndex.Value));
                if (cf.NumberFormatId >= 0 && cf.NumberFormatId <= 13) // This is a number
                    return Convert.ToDecimal(c.CellValue.Text);
                else if (cf.NumberFormatId >= 14 && cf.NumberFormatId <= 22) // This is a date
                    return DateTime.FromOADate(Convert.ToDouble(c.CellValue.Text));
                else
                    return c.CellValue.Text;
            }
 
            switch (c.DataType.Value)
            {
                case CellValues.SharedString:
                    return ssTable.ChildElements[Convert.ToInt32(c.CellValue.Text)].InnerText;
                case CellValues.Boolean:
                    return c.CellValue.Text == "1" ? true : false;
                case CellValues.Date:
                    return DateTime.FromOADate(Convert.ToDouble(c.CellValue.Text));
                case CellValues.Number:
                    return Convert.ToDecimal(c.CellValue.Text);
                default:
                    if (c.CellValue != null)
                        return c.CellValue.Text;
                    return string.Empty;
            }
        };

NumberFormatId is defined in a table and took a while to find online.
After running the code the result is a generic list holding dynamic objects containing strongly typed members matching the types in your spreadsheet..