Schotime.net

FluentValidation Xval Integration

Schotime — Fri, 12 Jun 2009 14:00:00 +0000

After a few months of using FluentValidation I asked its author Jeremy Skinner if it were possible to integrate this with xVal. At that time it was not possible because there were no easy way to access the properties needed by xVal. After submitting a few patches, we now have a solution which enables xVal integration with most of the FV validators.

It currently supports the following FV validatiors:

NullValidator
NotEmptyValidator
LengthValidator
RegularExpressionValidator
ComparisonValidator including:
- Equal
- Not Equal
- Greater Than or Equal
- Less Than or Equal

To configure the integration we need to tell xVal to use the FV rules provider rather than the default one. This is done in the global.asax.cs in Application_Start().

xVal.ActiveRuleProviders.Providers.Clear();
xVal.ActiveRuleProviders.Providers.Add(
    new FluentValidation.xValIntegration.
            FluentValidationRulesProvider(new AttributedValidatorFactory()));

The rules provider here is instructed to use the AttributedValidatorFactory to instruct the provider to use the attribute attached to the model class to find the validation class for that model.

Note: This is still new and xVal is still in beta so there may be some issues. If you find any please let me know so we can fix them as soon as possible.

Hopefully once its ready it can be checked in with the other providers at the xVal codeplex site.

This is currently in the development source code which can be downloaded and tried now.

Cheers,

Adam

Fluent Validation Model Binder – Asp.net MVC

Schotime — Mon, 11 May 2009 13:59:00 +0000

A few weeks ago I found the Fluent Validation framework by Jeremy Skinner. I needed to conditionally validate a model depending on an application setting. eg. Description field mandatory / not mandatory depending on the clients business requirements. I loved the simplicity of the framework and the separation from the model it provided.

Since then I have submitted a few patches for the framework, one of which is the Fluent Validation Model binder. Inspired by the Data Annotations Model binder, it works in much the same way. Once you have it set to your default model binder, it will validate any model which contains the specific attribute. This will become clear in the examples below.

Firstly lets take our model.

    [Validator(typeof(LineItemValidator))]
    public class LineItem
    {
        public int LineNumber { get; set; }
        public DateTime Date { get; set; }
        public string Description { get; set; }
        public decimal Net { get; set; }
        public decimal Tax { get; set; }
        public decimal Gross { get; set; }
    }

Attached to this simple LineItem class is a Validator attribute. This attribute is used by the Model Binder to locate the Class used for validation. Below I will define my LineItemValidator class which will hold the rules for the validation.

    public class LineItemValidator : AbstractValidator<LineItem>
    {
        public LineItemValidator()
        {
            RuleFor(x => x.Description)
                .NotEmpty().When(x => Settings.DescriptionRequired)
                .And
                .Length(0, 30);

            RuleFor(x => x.Date)
                .GreaterThanOrEqualTo(DateTime.Today);

            RuleFor(x => x.Net)
                .GreaterThan(0);

            RuleFor(x => x.Gross)
                .GreaterThan(0)
                .And
                .Equal(y => (y.Net + y.Tax))
                .WithName("Total Amount");
        }
    }

As you can see the class must inherit from AbstractValidator where T is the model you want to define the rules for. The first rule uses the conditional When clause where it will only validate that the field is NotEmpty when the Settings.DescriptionRequired boolean is true. Also, another thing is the complex validation taking place on the Gross field. Not only does it validate that it is greater than 0, but that the value is equal to the net + tax amount. This is very elegant indeed. I have also specified the ‘WithName’ clause which has also been integrated into the model binder so that if an error occurs with the gross field, the WithName value will be displayed when an error happens. This is extremely handy for language localization or when the name of the field on the Model is insufficient.

Wiring this Model binder up in the Application_start event in the global.asax.cs is as easy as this.

     ModelBinders.Binders.DefaultBinder =
                new FluentValidationModelBinder(new AttributedValidatorFactory());

Note that we have to pass an instance of the AttributedValidatoryFactory into the Model Binder. This means if you have an alternate way of locating the Validator class other than via the attribute you can inherit from IValidatorFactory and create your own.

Now when a parameter of LineItem gets passed into a controller it will be validated against the model and all errors placed into the ModelState. This can then check the isValid property to determine if there are any errors and proceed accordingly.

        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult Edit(List<LineItem> lineItems)
        {
            if (ModelState.IsValid)
                return RedirectToAction("Edit");

            return View();
        }

And that’s it. I hope you enjoy using the Fluent Validation framework as much as I have and happy coding. For all you guys waiting for xVal integration, I will try and post a solution in the coming week.

Adam

Integrating xVal Validation with Linq-to-Sql

Schotime — Tue, 31 Mar 2009 13:58:00 +0000

In a previous post I showed you how you can use xVal and the IDataErrorInfo class to add validation to your Asp.net MVC website. In this post I will extend that to Linq-to-Sql and the classes it generates.

The northwind database has a suppliers table. The info contained below is using that table with linq-to-sql.

After adding the table to the designer, a Supplier class gets constructed in the background. This class is a partial class which means we can add to it without changing the code auto-generated in the designer.cs file.

We can then create a partial class called Supplier inheriting from Custom Validation and add a MetadataType attribute to it. This attribute specifies the class for which use for validating the Supplier class.

    [MetadataType(typeof(SupplierValidation))]
    public partial class Supplier : CustomValidation
    {
    }

We can then create the SupplierValidation class specifying the properties of the Supplier class we would like to be validated. For instance here I only want to validate the ContactName and the ContactTitle of the Supplier.

        public class SupplierValidation
    {
        [Required]
        public string ContactName { get; set; }

        [Required, Range(0, 10)]
        public string ContactTitle { get; set; }
    }

This specifies that both fields are required and that the ContactTitle cannot be more than 10 characters in length.

The other benefit of using the buddy class here is that if you need to regenerate a table in the linq-to-sql designer, you won’t lose your changes because they’re contained in a separate file.

From here when a Supplier gets passed to in as a parameter on a Controller action it will be validated using the rules in the Supplier Validation class.

Cheers,

Adam

Custom Validation Attributes

Schotime — Wed, 11 Mar 2009 13:57:00 +0000

In my last post I showed you how to use Validation attributes on your model for validation. Today I will show how you can create your own attributes that can provide custom validation for you.

To accomplish this we need to inherit from ValidationAttribute. We’ll create an attribute today that will only allow a "Yes" or "No" value to be entered. A pretty bad example however it will still suffice.

public class TwoValueAttribute : ValidationAttribute
{
    private string str1 { get; set; }
    private string str2 { get; set; }

    public TwoValueAttribute(string str1, string str2)
    {
        this.str1 = str1;
        this.str2 = str2;
    }

    public override bool IsValid(object value)
    {
        if (value.ToString() == str1 || value.ToString() == str2)
            return true;
        else
            return false;
    }

    public override string FormatErrorMessage(string name)
    {
        return string.Format(CultureInfo.CurrentCulture,
                             base.ErrorMessageString,
                             name, this.str1, this.str2);
    }
}

All the validation logic takes place in the IsValid function. You can override the FormatErrorMessage function so that you can supply a custom error message for each instance that you want to validate if you so wish.

I’ll show you what I mean.

public class MyOrder : CustomValidation
{
    [TwoValue("Yes","No",
        ErrorMessage="Only the values '{1}' and '{1}' are possible"
                + " for the {0} field")]
    public string Value1 { get; set; }

    [TwoValue("Yes", "No")]
    public string Value2 { get; set; }
}

Value2 in the above case will give you the default error message if the value is not "Yes" or "No". However if Value1 is not "Yes" or "No" then the error message supplied will be used and the tokens replaced by the value entered, and the two values used for validation.

And that’s it. Pretty simple really.

Cheers,

Adam

Validation with Asp.net MVC, xVal & IDataerrorInfo

Schotime — Thu, 05 Mar 2009 11:46:07 +0000

I’ve been playing around with lots of different Validation concepts recently and I think I have come up with something that will be very useful. Hopefully you will also find it useful.

Firstly I have been looking at Steve Sanderson’s new open source project xVal and decided that it was a good place to start. I was mainly looking at server side validation but xVal can also generate script to enable client side validation.

Firstly I’ll show you how I integrated the DataAnnotations validation pack that Dynamic data uses and is available to us in 3.5sp1 as far as I know.

public class CustomValidation : IDataErrorInfo
{
    string IDataErrorInfo.Error
    {
        get
        {
            return string.Empty;
        }
    }

    string IDataErrorInfo.this[string columnName]
    {
        get
        {
            List<ErrorInfo> errors =
                DataAnnotations.GetErrors(this, columnName).ToList();
            return errors.Count > 0 ? errors[0].ErrorMessage : null;
        }
    }
}

So I first created a class called CustomValidation that implements the IDataErrorInfo interface. From here only two Properties need to be set, the second one being the most important. Here I use the get property accessor to check the errors for each column and if there are errors I return the error message for the first error.

From here we need our Model that we want validated to inherit from this class as follows.

    public class MyProduct : CustomValidation
    {
        [Range(0, 100, ErrorMessage="{0} must be set between {1} and {2}")]
        public double? UnitPrice { get; set; }

        [StringLength(30)]
        public string ProductName { get; set; }

        public List<MyOrder> Orders { get; set; }
    }

    public class MyOrder : CustomValidation
    {
        [Required]
        public string Name { get; set; }
    }

We can then attach Validation attributes. note: you will need to add a reference to the System.ComponentModel.DataAnnotations assembly for these attributes to show up.

Once we have these two things setup we can start using it. Actually before I start I will show you the DataAnnotations.GetErrors methods.

public static class DataAnnotations
{
    public static IEnumerable<ErrorInfo> GetErrors(object instance)
    {
        return GetErrors(instance, null);
    }

    public static IEnumerable<ErrorInfo> GetErrors(object instance, string name)
    {
        var metadataAttrib = instance.GetType()
                .GetCustomAttributes(typeof(MetadataTypeAttribute), true)
                .OfType<MetadataTypeAttribute>().FirstOrDefault();
        var buddyClassOrModelClass = metadataAttrib != null
                ? metadataAttrib.MetadataClassType
                : instance.GetType();
        var buddyClassProperties = TypeDescriptor.GetProperties(buddyClassOrModelClass)
            .Cast<PropertyDescriptor>();
        var modelClassProperties = TypeDescriptor.GetProperties(instance.GetType())
            .Cast<PropertyDescriptor>();

        var list = from buddyProp in buddyClassProperties
                   join modelProp in modelClassProperties on
                            buddyProp.Name equals modelProp.Name
                   from attribute in buddyProp.Attributes.OfType<ValidationAttribute>()
                   where !attribute.IsValid(modelProp.GetValue(instance))
                   select new ErrorInfo(
                       buddyProp.Name,
                       attribute.FormatErrorMessage(modelProp.Name),
                       instance);

        if (name != null)
            list = list.Where(x => x.PropertyName == name);

        return list;
    }
}

note: you will need the using statement and a reference to the xVal.dll
using xVal.ServerSide;

Now we can start.
Here is are sample controller actions. Creating a MyProduct and sending it to the view. Then accepting a MyProduct when the form gets posted back.

public ActionResult ValidationTest()
{
    MyProduct p = new MyProduct()
    {
        ProductName = "Magazine",
        UnitPrice = 23
    };

    p.Orders = new List<MyOrder>();
    p.Orders.Add(new MyOrder { Name = "Robert King" });

    return View(p);
}

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult ValidationTest(MyProduct product)
{
    if (ModelState.IsValid)
    {
        return RedirectToAction("ValidationTest");
    }

    return View(product);
}

In the latter action method the product argument has already been validated by the time it arrives at the if statement. All that needs to be done is to check if the Modelstate is valid and if it is redirect back to the page else display the posted data back to the user with the errors in Modelstate.

Here is what the view looks like.

<h2>Custom Validationh2>

<%= Html.ValidationSummary() %>

<% using (Html.BeginForm()) { %>

    <div style="padding-left: 15px">
        <div>
            Unit Price: <%= Html.TextBox("product.UnitPrice", Model.UnitPrice)%>
            <%= Html.ValidationMessage("product.UnitPrice")%>
        div>
        <div>
            Product Name: <%= Html.TextBox("product.ProductName", Model.ProductName)%>
             <%= Html.ValidationMessage("product.ProductName")%>
        div>
        <div>
            Orders:
            <% int j = 0;
               foreach (MyOrder o in Model.Orders) { %>
               <div  style="padding-left: 15px">
                    <%= j + 1 %>. <%= Html.TextBox("product.Orders[" + j + "].Name", o.Name)%>
                    <%= Html.ValidationMessage("product.Orders[" + j + "].Name")%><br />
                    <% j++; %>
               div>
            <% } %>
        div>
    div>

    <br />
    <input type="submit" />

<% } %>

Not that this even works when complex binding lists to that have been posted. I’ll try and get a full working example for download here as well if I get enough interest.

And that’s it. In upcoming posts I’ll show you how:

To do the validation without using the IDataErrorInfo interface and its automatic binding.
Use the methods that come with xVal to implement some client side validation.
How to create your own validation attributes.
Plugging in Castle Validator or other supported validators into xVal.

Hope this helps you get started with Validation in Asp.net MVC.

Cheers,
Adam

Custom Authorization With Asp.net MVC

Schotime — Tue, 17 Feb 2009 11:17:29 +0000

The whole advantage with MVC over webforms is extensibility at every point. Extensibility, Extensibility, Extensibility.

Authorization is a very important and every web project has there own needs and requirements. Full customisation is paramount.

Here I will show you a simple way to customise your authorization.

In MVC attributes are used to protect a controller method, so we to get started all we need to do is inherit from the AuthorizeAttribute class.

    public class CustomAuthorizeAttribute : AuthorizeAttribute
    {
        protected override bool AuthorizeCore(HttpContextBase httpContext)
        {
            string[] users = Users.Split(',');

            if (!httpContext.User.Identity.IsAuthenticated)
                return false;

            if (users.Length > 0 &&
                !users.Contains(httpContext.User.Identity.Name,
                    StringComparer.OrdinalIgnoreCase))
                return false;

            return true;
        }
    }

This is the basics. We can put any logic we like in here and all we have to do is return false if for whatever reason the user should not be authorized. Then all you need to do is decorate the controller method with the new attribute as below.

    [CustomAuthorize]
    public ActionResult Index()
    {
        return View();
    }

From this simple example we can expand it with custom Roles.

    public class CustomAuthorizeAttribute : AuthorizeAttribute
    {
        // the "new" must be used here because we are overriding
        // the Roles property on the underlying class
        public new SiteRoles Roles;

        protected override bool AuthorizeCore(HttpContextBase httpContext)
        {
            if (httpContext == null)
                throw new ArgumentNullException("httpContext");

            string[] users = Users.Split(',');

            if (!httpContext.User.Identity.IsAuthenticated)
                return false;

            SiteRoles role = (SiteRoles)httpContext.Session["role"];

            if (Roles != 0 && ((Roles & role) != role))
                return false;

            return true;
        }
    }

Where the SiteRoles class is defined as below.

    [Serializable]
    [Flags]
    public enum SiteRoles
    {
        User = 1 << 0,
        Admin = 1 << 1,
        Helpdesk = 1 << 2
    }

This can then be used be used as follows.

    [CustomAuthorize(Roles=SiteRoles.Admin|SiteRoles.HelpDesk)]
    public ActionResult Index()
    {
        return View();
    }

This will only allow the Admin and the Helpdesk Role access to the Index controller. If you don’t belong to one of these roles then you will be sent to the Login page.

The possibilities are really endless.
Happy coding.

Adam

Retrieving Data Without the Dataset With Custom SQL

Schotime — Thu, 12 Feb 2009 10:38:16 +0000

In my previous post I used custom sql query and transformed the results into a nested class. However you may have overlooked way I got the data into a class. It quite easy and will work with all database connectors that implement IDbConnection.

Here is how its done. We use the DataContext that came with and used by Linq2Sql.

        public class RawData
        {
            public int MyId { get; set; }
            public string Name { get; set; }
        }

        DataContext dc = new DataContext(new MySqlConnection(connString));
        IEnumerable<RawData> rd =
            dc.ExecuteQuery<RawData>("select myid, name from mytable where myid > ?",5);

And thats it. All the data from the mytable database table will get pushed into the IEnumerable variable. As long as the names of the columns are the same as the class then this will work. This ExecuteQuery method also takes a parameters object argument which will replace the question marks with the variables specified.

It makes it very easy, especially since there isn’t a very good linq 2 mysql option out there yet and Entity Framework still doesn’t support it.

Cheers,
Adam

Transforming one-to-many Sql into Nested Class

Schotime — Thu, 22 Jan 2009 11:13:13 +0000

Back in the days of Classic ASP, when you had a one too many relationship displaying the data was pretty painful. You would do things like

        var reference_number_old = "$##%@";
        while (recordsExist)
        {
            reference_number = String(rs(0));

            if (reference_number_old != reference_number)
            {
                Response.Write(reference_number);
                reference_number_old = reference_number;
            }

            //Do more stuff
        }

Sooo very very painful, and that’s only grouping by one column (reference number).

Now however with OO programming and the advance of Linq there is a much nicer and easier way to deal with this Master-Detail type one-to-many relationship.

Lets see how we do it now, still using our own custom sql.

Firstly here is our class to represent our flat data straight out of the database.

        public class FlatCustomerAndOrders
        {
            public int ReferenceNumber { get; set; }
            public string Name { get; set; }
            public string Address { get; set; }
            public int OrderId { get; set; }
            public string ProductName { get; set; }
            public string Description { get; set; }
            public decimal Amount { get; set; }
        }

Even filling this class used to be a bit painful before Linq. You would have to create a connection, create a reader, then instantiate the FlatCustomerAndOrders class. Set the properties then add the object to a list while records could still be read. Here’s how I do it now.

        DataContext dc = new DataContext(new SqlConnection(connString));
        IEnumerable<FlatCustomerAndOrders> flat =
            dc.ExecuteQuery<FlatCustomerAndOrders>("select c.referencenumber, c.name," +
            "c.address, o.orderid, o.productname, o.description, o.amount from customers " +
            "c inner join orders o on c.referencenumber = o.referencenumber");

Thats its. We have our List of FlatCustomerAndOrders.

The next step is to organise the data so that the data is nested so its easy to display with just two foreach loops. The two classes:

        public class Customer
        {
            public int ReferenceNumber { get; set; }
            public string Name { get; set; }
            public string Address { get; set; }
            public List<Order> Orders { get; set; }
        }

        public class Order
        {
            public int OrderId { get; set; }
            public string ProductName { get; set; }
            public string Description { get; set; }
            public decimal Amount { get; set; }
        }

To fill these two class we do some clever Linq using the GroupBy method. Here’s the code.

        IList<Customer> Customers =
            flat.GroupBy(cust => new { cust.ReferenceNumber, cust.Name, cust.Address })
                .Select(c => new Customer()
                {
                    ReferenceNumber = c.Key.ReferenceNumber,
                    Name = c.Key.Name,
                    Address = c.Key.Address,
                    Orders = c.Select(o => new Order()
                    {
                        OrderId = o.OrderId,
                        ProductName = o.ProductName,
                        Description = o.Description,
                        Amount = o.Amount
                    }).ToList()
                }).ToList();

And that’s it. It may look like a bit of a mouthful but its quite simple really.

It just takes the flat list and applies the groupby method to it grouping by the three customer columns. It then builds a new list to fill the Orders property with.

Its then super easy to display the data like so.

        foreach (Customer c in Customers)
        {
            //Display the customer details

            foreach (Order o in c.Orders)
            {
                //Display the orders for each customer
            }
        }

Well I hope this helps you as much as it has helped me.

Cheers

Schotime

Running ASP.NET MVC Applications Under IIS6

Schotime — Sat, 18 Oct 2008 09:41:29 +0000

Yes…Finally…I have full control of my HTML again, but am still able to work with a great language like C#. MVC is here and its great. I’m absolutely loving it. But how the heck do you get it to work under IIS6. What if I want extensionless URL’s? Here is a few options for you.

Option 1:
Running IIS with Wildcard Application mapping.

This option allows extensionless URL’s but a what performance price. Every single request including images, files and css etc. will get passed through the aspnet_isapi.dll. For small sites this may not be a huge issue, but small sites soon because large sites and this then becomes an issue.

I’m not going to run through how to do this, because there are already some nice blogs about this and I don’t really recommend this option.

Option 2:
Running IIS with .mvc (or whatever you like) extensions.

I started out running my site (schotime.net) like this, however in the end I decided on Option 3.
URLs look like this. /Home.mvc/Index rather than /Home/Index which is not that bad but again not as nice as the latter.

Anyways here’s how you set this up.
Firstly your global.asax.cs should look like this.

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

namespace MVCApplication1
{
    public class MvcApplication : System.Web.HttpApplication
    {
        public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

            routes.MapRoute(
                "Default",                                              
                "{controller}.mvc/{action}/{id}",                       
                new { controller = "Home", action = "Index", id = "" }  
            );

            routes.MapRoute(
                "Defaultest",                                           
                "Default.aspx",                                         
                new { controller = "Home", action = "Index", id = "" }  
            );

        }

        protected void Application_Start()
        {
            RegisterRoutes(RouteTable.Routes);
            RouteTable.Routes.RouteExistingFiles = true;
        }
    }
}

This will get you started. All you need to do then is map the extension .mvc to the aspnet_isapi.dll in IIS under Home Directory -> Configuration -> Mappings or use an already mapped extension like .aspx and your mvc application should work.

Option 3:

This option requires the use of a clever isapi filter to rewrite the URLs.

I originally read an article on Bia Securities which uses the Isapi_rewrite 3rd party plugin however, Isapi_rewrite is not free so I thought I would find a suitable open source solution. Here it is: Ionic Rewriter -> http://www.codeplex.com/IIRF

It works in much the same way as the isapi_rewrite but will a few little differences. Once you download the IsapiRewrite4.dll, place the dll in a directory eg. C:\WINDOWS\system32\inetsrv\IIRF and run through the installation instructions included in the download (readme.txt) under Installation. Next comes the configuration. Firstly here is the IsapiRewrite4.ini configuration file ported from the Bia Securities article.

#RewriteLog c:\temp\iirfLog.out

#RewriteLogLevel 3

RewriteRule ^/Default\.aspx /Home.mvc [I,L]

RewriteRule ^/$ /Home.mvc [I,L]

RewriteRule ^/([\w]+)$ /$1.mvc [I,L]

RewriteRule ^/(?!Content)([\w]*)/(.*) /$1.mvc/$2 [I,L]

The [I,L] stands for a case-insensitive match and to stop processing if the current rule is a match.

Important Note:

Any physical files that you directly linked to should not be routed. In my MVC folder system I store all of these files under the /Content directory. Wherever you store your files replace the text Content with the folder that your files reside. otherwise images, css etc. will fail to display.

Now all that’s left to do is to modify the global.asax.cs to support this.

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

namespace MVCApplication1
{
    public class MvcApplication : System.Web.HttpApplication
    {
        public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

            routes.MapRoute(
                "Default",                                              // Route name
                "{controller}/{action}/{id}",                           // URL with parameters
                new { controller = "Home", action = "Index", id = "" }, // Parameter defaults
                new { controller = @"[^\.]*" }
            );

            routes.MapRoute(
                "Defaultmvc",                                               // Route name
                "{controller}.mvc/{action}/{id}",                           // URL with parameters
                new { controller = "Home", action = "Index", id = "" },     // Parameter defaults
                new { controller = @"[^\.]*" }
            );
        }

        protected void Application_Start()
        {
            RegisterRoutes(RouteTable.Routes);
        }
    }
}

Note: The line with this on it -> new { controller = @"[^\.]*" } is very important. The routes won’t work without it.

And that’s it! You’re done.

Personally I like the last option but if you have no access to your hosting web server then Option 2 is probably the best.

Schotime

Dataset, Datatable to Json

Schotime — Sun, 27 Jul 2008 05:51:36 +0000

After my previous posts about returning data to the client as a JSON object, I decided to have a go at returning a generic Datatable/Dataset. This however is not as easy as simple returning a Datatable in your code behind method or web service. There is a solution though and here it is.

If you break a Datatable down it is really only a List of Dictionary objects so that’s how we’ll approach this problem. This is compatible with .NET 2.0 and above, with the Ajax installed.

I’d like to acknowledge RichardD for the idea.

Below is the solution.

using System.Collections.Generic;
using System.Data;

public static class JsonMethods
{
    private static List<Dictionary<string, object>>
        RowsToDictionary(DataTable table)
    {
        List<Dictionary<string, object>> objs =
            new List<Dictionary<string, object>>();
        foreach (DataRow dr in table.Rows)
        {
            Dictionary<string, object> drow = new Dictionary<string, object>();
            for (int i = 0; i < table.Columns.Count; i++)
            {
                drow.Add(table.Columns[i].ColumnName, dr[i]);
            }
            objs.Add(drow);
        }

        return objs;
    }

    public static Dictionary<string, object> ToJson(DataTable table)
    {
        Dictionary<string, object> d = new Dictionary<string, object>();
        d.Add(table.TableName, RowsToDictionary(table));
        return d;
    }

    public static Dictionary<string, object> ToJson(DataSet data)
    {
        Dictionary<string, object> d = new Dictionary<string, object>();
        foreach (DataTable table in data.Tables)
        {
            d.Add(table.TableName, RowsToDictionary(table));
        }
        return d;
    }
}

The static class JsonMethods exposes two public static methods and a private method. The public method ToJson() takes either a Dataset or a Datatable, and returns a Dictionary object. The key to this class is the RowsToDictionary() method.

This method iterates through all the rows creating a dictionary entry for each column in the row using the column name as the key and storing the data value into the object. It then adds the Dictionary object to a List of Dictionary Objects and returns this to the ToJson() method. This Dictionary list is then added to another Dictionary object using the table name as the key. We’ll see how this all works together soon.

Lets have a look at the code behind now.

[System.Web.Script.Services.ScriptMethod(ResponseFormat = ResponseFormat.Json)]
[System.Web.Services.WebMethod]
public static Dictionary<string, object> getTable()
{
    string sql = "select user_name, active_indicator, create_date from users";
    string connString = "database=db; server=localhost; user id=sa;";

    return JsonMethods.ToJson(GetDataTable(sql, connString));
}

private static DataTable GetDataTable(string sql, string connString)
{
    using (SqlConnection myConnection = new SqlConnection(connString))
    {
        using (SqlCommand myCommand = new SqlCommand(sql, myConnection))
        {
            myConnection.Open();
            using (SqlDataReader myReader = myCommand.ExecuteReader())
            {
                DataTable myTable = new DataTable();
                myTable.TableName = "mydt";
                myTable.Load(myReader);
                myConnection.Close();
                return myTable;
            }
        }
    }
}

So what I have above is two static methods. One is GetTable which is the one we will access from the client. The other is a generic method for loading a results set into a Datatable. Note how I have set the TableName property. You will see why soon.

So using the jMsAjax plugin as below will return the following JSON object.

$.jmsajax({
    type: "POST",
    url: "Default.aspx",
    method: "getTable",
    data: {},
    dataType: "msjson",
    success: function(data) {
        $(outputDT(data.mydt)).appendTo("body");
    }
});

Results (data):

{"mydt":{"user_name":"000001","active_indicator":"Y","create_date":"\/Date(1170892765197)\/"}, {"user_name":"000002","active_indicator":"Y","create_date":"\/Date(1170892765197)\/"}]}

In the resulting data, the table name is the key to referencing the array of values. In this case we use ‘mydt’ as the key. In the success function on the client request you may also notice an outputDT function. This is a little helper function which takes a JSON Datatable and returns a the results in a table. This is very useful for debugging. Here is the client side code.

"); for (var name in dataTable[0]) headers.push(""); headers.push(""); for (var row in dataTable) { rows.push(""); for (var name in dataTable[row]) { rows.push(""); } rows.push(""); } var top = "

function outputDT(dataTable)
{
    var headers = [];
    var rows = [];

    headers.push("

"+name+"

"); rows.push(dataTable[row][name]); rows.push("

"; var bottom = "

"; return top + headers.join("") + rows.join("") + bottom; }

So as you can see, its now very easy to return a Datatable or Dataset as a JSON object ready for use on the client.

Hope this is as useful for you as it is for me.

Cheers,

Schotime