Tuesday, May 31, 2011

Learn to put up with Visual Studio, it's not perfect. Nothing is

An anecdote:

In VS, click Project (next to File,Edit,View), select Properties

then in Application tab (you'll notice you're already in 3.5), select the Target Framework to 2.0, then compile (it will error). then put it back again to 3.5, then compile again, the error will disappear

i think it is just a small glitch in Visual Studio, just fool the IDE :-)

Stupid if you may ask.

source: http://stackoverflow.com/questions/205644/error-when-using-extension-methods-in-c/383517#383517

And when clicking Update Service reference is a no joy undertaking; sometimes you have to remove the service reference, and then re-add it again. Sometimes you have to restart Visual Studio to make things take in effect, ah.. the dev's life! :-)

Another anecdote(from me, a colleague experienced it too), the first time one installed a nuget on his/her Visual Studio, his/her Visual Studio will become very slow. So when the colleague installed nuget on his Visual Studio for the first time, he noticed Visual Studio became very slow; and even he restarted Visual Studio, it's still very slow, I told him to restart his computer. Voila! his Visual Studio became snappy again.


First refuge of those who easily give up: http://stackoverflow.com/questions/205644/error-when-using-extension-methods-in-c/338452#338452

When it comes to choosing a jQuery component, don't be a sheeple

Sharing my same sentiment. Should selectively download which jQuery UI is needed on application, so far we only use datepicker, tab, accordion, dialog, draggable(for dialog need), resizable(for dialog need too). --> http://www.redfrogconsulting.com/love-jquery-hate-the-jquery-ui.php


Too many bloat on jQuery UI, there's an exploding, bouncing, clip, shake, pulsate animation in jQuery UI to name a few, you don't need them, there's also: droppable, position, progress bar, etc, you also don't need them.


Selectively download which ones you need, jQuery UI customizable download is user-friendly anyway, just uncheck then check which ones you need



If few years down the road and the whole jQuery UI become too bloated (say almost 300+ KB) to the point that it is heavier than your own content, I would call it far from being a standard. We're not sheeple folks, we don't need to put up with their boatload of bloat they're injecting in their jQuery components, especially if we don't use them. Let's be happy that we can selectively download (jquery-ui-1.8.12.custom.tab+accordion+datepicker+dialog+draggable+resizable.min.js) which ones we need on jQuery UI website; if there's no customized download facility on jQuery UI website, jQuery UI could receive flaks more than it deserves, and everybody will quickly find other jQuery components that is more lightweight than jQuery UI. Selectively choosing which jQuery UI you only need is the standard, downloading the whole package is silly.


jQuery Tools vs jQuery UI 14 KB(jQuery Tools) vs 209 KB(jQuery UI). jQuery Tools has the only most commonly used features a web program need, thus cutting down the bloat.

And the father(John Resig, see comment #5 of last link) of jQuery weigh in:
jQuery UI Tabs is only 6KB minified and gzipped, jQuery Tools Tabs is 1.5KB minified and gzipped – and that’s saying nothing of the features supported by both plugins.



See jQuery Tools in action


Download jQuery Tools. For future projects :-)

Sunday, May 29, 2011

When it comes to ORM, be mapping-agnostic

Why we need to be mapping-agnostic, especially when we are using a strongly-typed language? One way or another, the classes themselves are a form of mapping.


If you are using a non-strongly-typed language, mapping things explicitly is the way things are ought to be done. This hypothetical non-strongly-typed language warrants explicit mapping:

class Country
{
 CountryId;

 CountryName;
 Population;
 People;
}


class Band
{
 BandId;
  
 BandName;
 Fans;
}


class Person
{
 PersonId;

 PersonName;
 Country;
 FavoriteBand;
}

But the following classes and properties already has all the necessary information that your ORM need to automatically jumpstart mapping your classes to tables, your properties to fields.

public class Country
{
 public virtual int CountryId { get; set; }

 public virtual string CountryName { get; set; }
 public virtual int Population { get; set; }
 public virtual IList<Person> People { get; set; }
}


public class Band
{
 public virtual int BandId { get; set; }
  
 public virtual string BandName { get; set; }
 public virtual IList<Person> Fans { get; set; }
}



public class Person
{
 public virtual int PersonId { get; set; }

 public virtual string PersonName { get; set; }
 public virtual Country Country { get; set; }
 public virtual Band FavoriteBand { get; set; }
}

Aside from your properties has a type already, class reference on another class is already a clue for your ORM to map your class as many-to-one to another class, the IList is already a clue to your ORM that your class is one-to-many to the other class.


So don't let yourselves get caught up with the brewing tension between James Gregory and Fabio Maulo. Your strongly-typed code is already a form of mapping. NHibernate 3.2's built-in Fluent^H^H^H^H^H^HLoquacious-mapper is indeed loquacious. Maybe it's just me, but I've got a feeling that Loquacious mapper won't take off.


That being said, I still highly recommend James Gregory's Fluent mapper instead and its Auto Mapping; by far, Fluent NHibernate is still the simplest way to override some minor things that are not ought to be automapped. Still though, I want James Gregory to make the next Fluent NHibernate not internally process mappings as XMLs. Fluent NHibernate 2.0 FTW! go go go! 加油!

Saturday, May 28, 2011

Sql Server concurrency handling using its rowversion field type and Fluent NHibernate Auto Mapping

The model:
public class Product
{        
    public virtual int ProductId { get; set; }

    public virtual string ProductName { get; set; }
    public virtual string WarehouseLocation { get; set; }
    public virtual string LastAuditedBy { get; set; }
    public virtual int Quantity { get; set; }        
    public virtual byte[] TheRowversion { get; set; }
}


The concurrent update scenario:
class Program
{
    static void Main(string[] args)
    {
        var userA = Mapper.GetSessionFactory().OpenSession();
        var userB = Mapper.GetSessionFactory().OpenSession();
        
        // User A and User B happen to open the same record at the same time
        var a = userA.Get<Product>(1);
        var b = userB.Get<Product>(1);

        a.ProductName = "New & Improved" + a.ProductName + "!";

        b.WarehouseLocation = "Behind appliances section";
        b.LastAuditedBy = "Linus";
        b.Quantity = 7;

        userA.Save(a);
        userA.Flush();


        // This will fail. As the rowversion of the same record since it was first loaded, had 
        // already changed (the record was changed by User A first) when it's User B's time to save the same record.
        userB.Save(b);
        userB.Flush();

       
    }
}


DDL:
create table Product
(
ProductId int identity(1,1) not null primary key,
ProductName nvarchar(100) not null unique,
WarehouseLocation nvarchar(100) not null,
LastAuditedBy nvarchar(100) not null,
Quantity int not null,
TheRowversion rowversion not null
) 


Boilerplate code for mapping. If you decide to make all your tables' version column use Sql Server's built-in rowversion field type and use your own name for version column, you must do both line 59 and 65 respectively. Also, add the version convention to your ISessionFactory builder, line 36. The default(convention-over-configuration) rowversion column name when you don't implement line 59,65,36 is Version.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;

using NHibernate;
using NHibernate.Dialect;

using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using FluentNHibernate.Automapping;
using FluentNHibernate.Conventions;
using FluentNHibernate.Conventions.Instances;

using FluentNHibernate.Conventions.Helpers;

using TestFluentAutomappingWithRowversion.Model;

namespace TestFluentAutomappingWithRowversion
{
    public static class Mapper
    {
        static ISessionFactory _sf = null;
        public static ISessionFactory GetSessionFactory()
        {
            if (_sf != null) return _sf;

            var fc = Fluently.Configure()
                    .Database(MsSqlConfiguration.MsSql2008.ConnectionString(@"Data Source=localhost; Initial Catalog=TestDb; User Id=sa; Password=P@$$w0rd; MultipleActiveResultSets=True"))
                    .Mappings
                    (m =>
                            m.AutoMappings.Add
                            (
                                AutoMap.AssemblyOf<Program>(new CustomConfiguration())
                                    .Conventions.Add<RowversionConvention>()                                
                            )
                    // .ExportTo(@"C:\_Misc\NH")
                    );


            // Console.WriteLine( "{0}", string.Join( ";\n", fc.BuildConfiguration().GenerateSchemaCreationScript(new MsSql2008Dialect() ) ) );
            // Console.ReadLine();

            _sf = fc.BuildSessionFactory();
            return _sf;
        }


        class CustomConfiguration : DefaultAutomappingConfiguration
        {
            IList<Type> _objectsToMap = new List<Type>()
            {
                typeof(Product)
            };
            public override bool ShouldMap(Type type) { return _objectsToMap.Any(x => x == type); }
            public override bool IsId(FluentNHibernate.Member member) { return member.Name == member.DeclaringType.Name + "Id"; }

            public override bool IsVersion(FluentNHibernate.Member member) { return member.Name == "TheRowversion"; }
        }


        class RowversionConvention : IVersionConvention
        {
            public void Apply(IVersionInstance instance) { instance.Generated.Always(); }
        }

    }
}

Entity Framework's Concurrency Handling using Sql Server's rowversion field. And why modal dialog is bad for editing records

The Model:

public class Product
{
 [Key]
 public int ProductId { get; set; }
 public string ProductName { get; set; }
 public string WarehouseLocation { get; set; }
 public string LastAuditedBy { get; set; }
 public int Quantity { get; set; }

 [ConcurrencyCheck]
 public byte[] TheRowversion { get; set; }
}

The concurrent update scenario:

class Program
{
 static void Main(string[] args)
 {
  var userA = new CatalogContext();
  var userB = new CatalogContext();

  // User A and User B happen to open the same record at the same time
  var a = userA.Products.Find(1);
  var b = userB.Products.Find(1);

  
  
  a.ProductName = "New & Improved" + a.ProductName + "!";
     
  b.WarehouseLocation = "Behind appliances section";
  b.LastAuditedBy = "Linus";
  b.Quantity = 7;

  

  userA.SaveChanges();

  // This will fail. As the rowversion of the same record since it was first loaded, had 
  // already changed (the record was changed by User A first) when it's User B's time to save the same record.
  userB.SaveChanges();

}



Why a modal dialog is bad for editing a record? It's bad when you employ concurrency handling (a must) on records. Imagine your program is in kiosk mode, and let's say due to policy or technology constraints(say IE6), launching multiple instances of your app or using multiple tabs(e.g. IE6) isn't allowed/possible. How can we prevent User B from wasting his edits/time(imagine if there are 20 fields on a given record)? from losing his work? Let him open the latest changes of the record he happened to open at the same time with other users in a new tab(e.g. jQuery tab). With non-modal editing, User B can compare his changes against the latest changes of User A, and then he can copy-paste his changes to the latest changes, not much work will be wasted. You cannot facilitate this sort of thing with modal dialog editor, User B will be compelled to lose all his edits, and will just re-open the latest version from User A and re-input again all his concerning fields.


DDL:

create table Product
(
ProductId int identity(1,1) not null primary key,
ProductName nvarchar(100) not null unique,
WarehouseLocation nvarchar(100) not null,
LastAuditedBy nvarchar(100) not null,
Quantity int not null,
TheRowversion rowversion not null
) 

Fluent NHibernate's Auto Mapping impresses me a lot

After writing a fair amount of code employing Entity Framework 4.1's code-first, I'm starting to like Entity Framework's almost zero configuration approach on mapping properties to fields and object relationships. Well I guess, it's the automatic mapping of fields and the manual selection(via DbSet<>) of objects to map to tables that impress me somehow on Entity Framework's approach.


The reason why I avoided automapping in Fluent NHibernate before is I got the wrong impression that one need to put the models in a separate assembly for him/her to have a hassle-free Fluent NHibernate awesome automapping; though putting the models in separate assembly is a good discipline, there are times you just don't want to create a separate project for models, so this decision naturally led me to always map objects and properties manually (via ClassMap<>, Map(),Id(),etc). Oh well, Entity Framework's minimalist and whitelist approach(via DbSet<>, no need to put the models in a separate project) pumped me up to find the same thing in Fluent NHibernate.


That's a big wrong impression there on part of me. Fluent NHibernate also has a whitelist capability for automapping objects to tables; it also has a mechanism to *blacklist* the properties you specified. There's no whitelist mechanism for properties, if there is, things will not be so auto anymore ;-)


Boilerplate code for automapping in Fluent NHibernate:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;

using NHibernate;
using NHibernate.Dialect;

using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using FluentNHibernate.Automapping;
using FluentNHibernate.Conventions;
using FluentNHibernate.Conventions.Instances;
using FluentNHibernate.Conventions.Helpers;

using TestFluentAutomapping.Model;

namespace TestFluentAutomapping
{
    public static class Mapper
    {
        static ISessionFactory _sf = null;
        public static ISessionFactory GetSessionFactory()
        {
            if (_sf != null) return _sf;

            var fc = Fluently.Configure()
                    .Database(MsSqlConfiguration.MsSql2008.ConnectionString(@"Data Source=localhost;Initial Catalog=TestDbx;User id=sa;Password=P@$$w0rd"))
                    .Mappings
                    (   m =>
                            m.AutoMappings.Add
                            (
                                AutoMap.AssemblyOf<Program>(new CustomConfiguration())
                                   .Conventions.Add(ForeignKey.EndsWith("Id"))                                                                                                
                                .Override<Band>(n => { n.HasMany(x => x.Fans).Inverse().KeyColumn("FavoriteBandId"); } )                                
                            )
                            // .ExportTo(@"C:\_Misc\NH")
                    );

           
            // Console.WriteLine( "{0}", string.Join( ";\n", fc.BuildConfiguration().GenerateSchemaCreationScript(new MsSql2008Dialect() ) ) );
            // Console.ReadLine();

            _sf = fc.BuildSessionFactory();
            return _sf;
        }


        class CustomConfiguration : DefaultAutomappingConfiguration
        {
            IList<Type> _objectsToMap = new List<Type>()
            {
                // whitelisted objects to map
                typeof(Person), typeof(Country), typeof(Band)
            };
            public override bool ShouldMap(Type type) { return _objectsToMap.Any(x => x == type); }
            public override bool IsId(FluentNHibernate.Member member) { return member.Name == member.DeclaringType.Name + "Id"; }
        }


    }
}


Objects:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace TestFluentAutomapping.Model
{


    public class Country
    {
        public virtual int CountryId { get; set; }

        public virtual string CountryName { get; set; }
        public virtual int Population { get; set; }
        public virtual IList<Person> People { get; set; }
    }


    public class Band
    {
        public virtual int BandId { get; set; }
        
        public virtual string BandName { get; set; }
        public virtual IList<Person> Fans { get; set; }
    }



    public class Person
    {
        public virtual int PersonId { get; set; }

        public virtual string PersonName { get; set; }
        public virtual Country Country { get; set; }
        public virtual Band FavoriteBand { get; set; }
    }


}


Using the objects that was mapped by means of automapping:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using NHibernate.Linq;

using TestFluentAutomapping.Model;

namespace TestFluentAutomapping
{
    class Program
    {
        static void Main(string[] args)
        {
            var s = Mapper.GetSessionFactory().OpenSession();

            foreach (var c in s.Query<Country>())
            {
                Console.WriteLine("\n{0}'s people", c.CountryName);

                foreach(var p in c.People)
                    Console.WriteLine("* {0}", p.PersonName);
            }

            foreach (var b in s.Query<Band>())
            {
                Console.WriteLine("\n{0}'s fans", b.BandName);
                foreach (var p in b.Fans)
                    Console.WriteLine("* {0}", p.PersonName);
            }


            Console.WriteLine("\nAll people:");
            foreach (var p in s.Query<Person>())
            {                                
                Console.WriteLine("* {0}", p.PersonName);
            }
 
            Console.ReadLine();
        }
    }
}

Output:
Philippines's people
* Michael
* Yeyet

China's people
* Jolin
* Atong

Backstreet Boys's fans
* Jolin
* Yeyet

Beatles's fans
* Michael
* Atong

All people:
* Michael
* Jolin
* Atong
* Yeyet


DDL:
create table Country
(
CountryId int identity(1,1) not null primary key,
CountryName nvarchar(100) not null unique,
Population int not null
);


create table Band
(
BandId int identity(1,1) not null primary key,
BandName nvarchar(100) not null unique,
YearStarted int not null
);


create table Person
(
PersonId int identity(1,1) not null primary key,
PersonName nvarchar(100) not null unique,
CountryId int not null references Country(CountryId),
FavoriteBandId int null references Band(BandId)
);



insert into Band(BandName,YearStarted) values('Beatles', 1957);
insert into Band(BandName,YearStarted) values('Backstreet Boys', 1900);
insert into Country(CountryName, Population) values('Philippines',9);
insert into Country(CountryName, Population) values('China',2);


insert into Person(PersonName,CountryId,FavoriteBandId) values('Michael',1,1)
insert into Person(PersonName,CountryId,FavoriteBandId) values('Yeyet',1,2)
insert into Person(PersonName,CountryId,FavoriteBandId) values('Jolin',2,2)
insert into Person(PersonName,CountryId,FavoriteBandId) values('Atong',2,1)

Friday, May 27, 2011

WCF: "serviceActivations could not be found"

If you received this kind of error:

The type 'WcfService1.Service1', provided as the Service attribute value in the ServiceHost directive, or provided in the configuration element system.serviceModel/serviceHostingEnvironment/serviceActivations could not be found.

It's trying to find Wcf1Service1.Service1. To change to your current service name, example:

public class TestService : IService1
    {
        public string GetData(int value)
        {
            return string.Format("You entered: {0}", value);
        }
    }

...from Solution Explorer, right-click Service1.svc, choose Open With..., select XML (Text) Editor, click OK, change this...

<%@ ServiceHost Language="C#" Debug="true" Service="WcfService1.Service1" CodeBehind="Service1.svc.cs" %>


...To:
<%@ ServiceHost Language="C#" Debug="true" Service="WcfService1.TestService" CodeBehind="Service1.svc.cs" %>

Thursday, May 26, 2011

Cascading DropDownList using ASP.NET MVC JsonResult and jQuery

Model:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;

namespace TestDropdownList.Models
{

    public class Person
    {
        
        public int PersonId { get; set; }
        
        public string PersonName { get; set; }
        
        [   Display(Name="Country"),
            Required
        ]   public string CountryCode { get; set; }
        
        [   Display(Name="City"),
            Required
        ]   public string CityCode { get; set; }

    }

    public class Country
    {
        public string CountryCode { get; set; }
        public string CountryName { get; set; }
    }

    public class City
    {
        public string CountryCode { get; set; }
        public string CityCode { get; set; }
        public string CityName { get; set; }
    }


}

View:
@model TestDropdownList.Models.Person
           

<script src="/Scripts/jquery-1.5.1.min.js" type="text/javascript"></script>
@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>



@using (Html.BeginForm())
{    
    
    if (Model.PersonId != 0)
    {
        <text>Editing: </text> @Model.PersonName
    }
    
    <table>

    <tr>
    <td>@Html.LabelFor(x => x.CountryCode)</td>
    <td style="width: 100px">@Html.DropDownListFor(x => x.CountryCode, new List<SelectListItem>(), new { TheOriginalValue = Model.CountryCode })</td>
    </tr>
    
    
    <tr>
    <td>@Html.LabelFor(x => x.CityCode)</td>
    <td style="width: 100px">@Html.DropDownListFor(x => x.CityCode, new List<SelectListItem>(), new { TheOriginalValue = Model.CityCode })</td>
    </tr>      
    
    </table>
}
    


<script type="text/javascript">
    $(function () {


        var countryCodeInitialValue = $('#CountryCode').attr('TheOriginalValue');
        PopulateCountry(countryCodeInitialValue, function () {
            var cityCodeInitialValue = $('#CityCode').attr('TheOriginalValue');
            $('#CityCode').val(cityCodeInitialValue);            
        });





        $('#CountryCode').change(function () {
            // alert($(this).val() + ": " + $('option:selected', $(this)).text());


            // Why use this?
            // alert($('option:selected', '#CountryCode').val());

            // Or this?
            // alert($('option:selected', $(this)).val());


            // When this will suffice?
            PopulateFromCountry($(this).val());
        });


        /////////////////


        function PopulateCountry(countryCode, doneCallback) {
            $.ajax({ url: '/Home/CountryList/',
                type: 'POST',

                dataType: 'json',
                success: function (data) {

                    var options = $('#CountryCode');
                    $.each(data, function () {
                        options.append($('<option />').val(this.CountryCode).text(this.CountryName));
                    });



                    if (countryCode != "") {
                        $(options).val(countryCode);
                    }
                    else {
                        countryCode = $(options).val();
                    }

                    PopulateFromCountry(countryCode, doneCallback);

                } // ajax callback

            }); // ajax call

        } // PopulateCountry()


        function PopulateFromCountry(countryCode, doneCallback) {

            $.ajax({ url: '/Home/CityList/',
                type: 'POST',
                data: { CountryCode: countryCode }, // parameter on CityList method

                dataType: 'json',
                success: function (data) {

                    var options = $('#CityCode');
                    $('option', options).remove(); // will remove all cities

                    // repopulate all cities
                    $.each(data, function () {
                        options.append($('<option />').val(this.CityCode).text(this.CityName));
                    });


                    if (doneCallback != undefined)
                        doneCallback();


                } // ajax callback

            }); // ajax call

        } // PopulateFromCountry()

    }); //jquery ready

</script>

Controller:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using TestDropdownList.Models;

namespace TestDropdownList.Controllers
{
    public class HomeController : Controller
    {
        public static IList<Person> _persons = new List<Person>()
        {
            new Person{ PersonId = 1, PersonName = "Michael", CityCode = "MNL" },
            new Person{ PersonId = 2, PersonName = "Linus", CityCode = "ALB" },            
            new Person{ PersonId = 3, PersonName = "John", CityCode = "SHA" }
        };

        public ViewResult Index()
        {                        
            return View(new Person());
        }

        public ViewResult Edit(int id)
        {

            Person personToEdit = (from p in _persons where p.PersonId == id select p).Single();
            personToEdit.CountryCode = (from c in Cities where c.CityCode == personToEdit.CityCode select c.CountryCode).Single();
            return View("Index", personToEdit);
        }

        [HttpPost]
        public JsonResult CountryList()
        {            
            // normally, you pass a list obtained from ORM or ADO.NET DataTable or DataReader
            return Json(Countries);
        }

        [HttpPost]
        public JsonResult CityList(string CountryCode)
        {
            // normally, you pass a list obtained from ORM or ADO.NET DataTable or DataReader
            return Json(from c in Cities 
                        where c.CountryCode == CountryCode 
                        select new { c.CityCode, c.CityName });
            
        }

        // MOCK DATA //


        public List<Country> Countries
        {
            get
            {
                return new List<Country>()
                {
                    new Country { CountryCode = "PH", CountryName = "Philippines" },
                    new Country { CountryCode = "CN", CountryName = "China" },
                    new Country { CountryCode = "CA", CountryName = "Canada" },
                    new Country { CountryCode = "JP", CountryName = "Japan" }
                };
            }
        }//Countries



        public List<City> Cities 
        {
            get 
            {
                return new List<City>()
                {
                    new City { CountryCode = "PH", CityCode = "MNL", CityName = "Manila" },
                    new City { CountryCode = "PH", CityCode = "MKT", CityName = "Makati" },
                    new City { CountryCode = "PH", CityCode = "CBU", CityName = "Cebu" },

                    new City { CountryCode = "CN", CityCode = "BEI", CityName = "Beijing" },
                    new City { CountryCode = "CN", CityCode = "SHA", CityName = "Shanghai" },

                    new City { CountryCode = "CA", CityCode = "TOR", CityName = "Toronto" },
                    new City { CountryCode = "CA", CityCode = "MAN", CityName = "Manitoba" },
                    new City { CountryCode = "CA", CityCode = "ALB", CityName = "Alberta" },
                    new City { CountryCode = "CA", CityCode = "VAN", CityName = "Vancouver" },

                    new City { CountryCode = "JP", CityCode = "TOK", CityName = "Tokyo" }
                    
                };
            }
        }//Cities      

    }//HomeController
}


On the controller code, you can see that we retrieve the CountryCode based on Person's CityCode, you need to do this if your database design is heavily normalized, e.g. you don't store the CountryCode on Person table, as you can query it in City table anyhow.

To edit Linus for example, you type this in URL: http://localhost:1232/Home/Edit/2, Alberta will be retrieved for City and Canada for its Country:








Likewise if you type this in URL: http://localhost:1232/Home/Edit/3, the app will retrieve John and obtain Shanghai for City and China for Country






At first, I did this on View:

var countryCodeInitialValue = $('#CountryCode').attr('TheOriginalValue');
PopulateCountry(countryCodeInitialValue);

var cityCodeInitialValue = $('#CityCode').attr('TheOriginalValue');
$('#CityCode').val(cityCodeInitialValue);            


But it may not work, there's no guarantee that the population of City(done asynchronously too) is already done when you call the PopulateCountry. I forgot that the fetching of list are done asynchronously, so in order to wait for the completion of list population, we must provide a callback so we can do the necessary code when the fetching is completed.

This is the corrected code:

var countryCodeInitialValue = $('#CountryCode').attr('TheOriginalValue');
PopulateCountry(countryCodeInitialValue, function () {
    var cityCodeInitialValue = $('#CityCode').attr('TheOriginalValue');
    $('#CityCode').val(cityCodeInitialValue);            
});


You can get working demo from the code's SVN here: http://code.google.com/p/jquery-dynamic-dropdown-list-demo/source/browse/

Download code here: http://code.google.com/p/jquery-dynamic-dropdown-list-demo/downloads/list

How to dynamically populate DropDownList from ASP.NET MVC JsonResult using jQuery

The View:

@model TestDropdownList.Models.Person
           

<script src="/Scripts/jquery-1.5.1.min.js" type="text/javascript"></script>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>


@using (Html.BeginForm())
{
    @Html.LabelFor(x => x.CountryCode, "Country")

    @Html.DropDownListFor(x => x.CountryCode, new List<SelectListItem>() )
}


<script type="text/javascript">
    $(function () {

        $.ajax({ url: '/Home/CountryList/',
            type: 'POST',
            dataType: 'json',
            success: function (data) {

                var options = $('#CountryCode');

                $.each(data, function () {
                    // alert(this.Key + " " + this.Value); // came from .NET Dictionary's Key Value pair
                    options.append($('<option />').val(this.Key).text(this.Value));
                });
            }
        });

        


        $('#CountryCode').change(function () {
            // alert('hello');
            alert($(this).val() + ": " + $('option:selected', $(this)).text());

        });
    });
</script>




The Controller:

[HttpPost]
public JsonResult CountryList()
{            
    // normally, you pass a list obtained from ORM or ADO.NET DataTable or DataReader
    return Json(new Dictionary<string, string>() { { "PH", "Philippines" }, { "CN", "China" }, { "CA", "Canada" }, { "JP", "Japan" } }.ToList());
}


Related to: http://www.ienablemuch.com/2011/05/cascading-dropdownlist-using-aspnet-mvc.html

Search Keywords: json result asp.net mvc html.dropdownlist callback

Dropdownlist callback using jQuery

@model TestDropdownList.Models.Person

<script src="/Scripts/jquery-1.5.1.min.js" type="text/javascript"></script>
@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>


@using (Html.BeginForm())
{
    @Html.LabelFor(x => x.CountryCode, "Country")

    @Html.DropDownListFor(x => x.CountryCode,
            new Dictionary<string, string>() { { "PH", "Philippines" }, { "CN", "China" }, { "CA", "Canada" }, { "JP", "Japan" } }
            .Select(x => new SelectListItem { Value = x.Key , Text = x.Value} ) )
}


<script type="text/javascript">
    $(function () {
        $('#CountryCode').change(function () {
            // alert('hello');
            alert($(this).val() + ": " + $('option:selected', $(this)).text());

        });
    });
</script>

Sunday, May 22, 2011

NHibernate Concurrency Handling using Sql Server Rowversion

The following code implements concurrency handling on NHibernate; if you are using Sql Server 2005 onwards, it's better to use its built-in rowversion field type. Rowversion maps to byte array on .NET. NHibernate can use this field on WHERE clause whenever it do an update. If there's no record updated, it means that other user updated/deleted the current record first before the current user save the same record. To handle this scenario of non-successfully-saved record, the current user shall receive a message that the current record he/she is attempting to save is already stale. Then inform the user to re-open the updated version of the record he/she is editing


The Object(aka Model):

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

namespace NhConcurrentUpdateHandling.Models
{
    public class Product
    {
        public virtual int ProductID { get; set; }
        public virtual string ProductName { get; set; }
        public virtual int Quantity { get; set; }
        public virtual byte[] TheRowversion { get; set; }
    }
}

Relational:
create table Product
(
ProductID int identity(1,1) not null,
ProductName nvarchar(100) not null,
Quantity int not null,
TheRowversion rowversion not null
);

insert into Product(ProductName,Quantity) values
('Keyboard',9)

Mapping:

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

using FluentNHibernate.Mapping;

using NhConcurrentUpdateHandling.Models;


namespace NhConcurrentUpdateHandling.ModelsMappings
{
    public class ProductMapping : ClassMap<Product>
    {
        public ProductMapping()
        {
            Id(x => x.ProductID);
            Map(x => x.ProductName);
            Map(x => x.Quantity);
            Version(x => x.TheRowversion).Generated.Always();
        }
    }
}


Controller:

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

using NHibernate;

using NhConcurrentUpdateHandling.Models;
using NhConcurrentUpdateHandling.ModelsMapper;



namespace NhConcurrentUpdateHandling.Controllers
{
    public class HomeController : Controller
    {
        //
        // GET: /Home/

        public ActionResult Index()
        {
            using (var db = SessionFactoryBuilder.GetSessionFactory().OpenSession())
            {
                var p = db.Get<Product>(1);
                return View(p);
            }
        }

        [HttpPost]
        public ActionResult Index(Product p)
        {

            try
            {
                
                using (var db = SessionFactoryBuilder.GetSessionFactory().OpenSession())
                {
                    var px = db.Merge(p);
                    db.Flush();

                    UpdateModel(px);

                    ViewBag.SuccessfulMessage = "Saved.";

                    return View(px);


                }
                
            }
            catch (StaleObjectStateException ex)
            {
                ViewBag.ErrorMessage = "Your changes is not saved. Please re-open this record to get its latest values. Other user already do something to this record";
                return View(p);
            }
            
        }

    }
}

View:

@model NhConcurrentUpdateHandling.Models.Product

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<script src="@Url.Content("~/Scripts/jquery.validate.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.validate.unobtrusive.min.js")" type="text/javascript"></script>

@ViewBag.ErrorMessage

@using (Html.BeginForm()) {
    @Html.ValidationSummary(true)
    <fieldset>
        <legend>Product</legend>

        @Html.HiddenFor(model => model.ProductID)

        <div class="editor-label">
            @Html.LabelFor(model => model.ProductName)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.ProductName)
            @Html.ValidationMessageFor(model => model.ProductName)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.Quantity)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Quantity)
            @Html.ValidationMessageFor(model => model.Quantity)
        </div>

        @Html.HiddenFor(x => x.TheRowversion)

        <p>
            <input type="submit" value="Save" />
        </p>
    </fieldset>
}

@if(!string.IsNullOrEmpty(ViewBag.SuccessfulMessage)) {
    <b>@ViewBag.SuccessfulMessage</b>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>


Sample generated UPDATE statement when using SQL Server's rowversion as the means of concurrent update handling:

exec sp_executesql 
N'UPDATE [Product] SET ProductName = @p0, Quantity = @p1 
WHERE ProductID = @p2 AND TheRowversion = @p3',
N'@p0 nvarchar(4000),@p1 int,@p2 int,@p3 varbinary(8000)',
@p0=N'Keyboard',@p1=21,@p2=1,@p3=0x00000000000007E3

Notice that the ORM-generated UPDATE statement not only uses primary key on the WHERE clause, it also include the rowversion as well; so the ORM can determine if the record that was being saved is not modified by other users first. If there's no updated record, it means the rowversion had already changed, this happens when other users saved/deleted the record first. Exception will be thrown, and the simplest course of action is for the late-saver user to honor the record that was saved by the other user, the late-saver user shall re-open the saved record by the other user.



Boilerplate code for database connection: http://www.ienablemuch.com/2011/04/boilerplate-code-for-fluent-nh.html

Friday, May 20, 2011

Your ORM supports lazy loading, but don't be so lazy yourself

If you will only use one field from the navigated object, don't rely on lazy loading, don't do code like this:

foreach(var p in db.Persons)
{
    Console.WriteLine("{0} {1}", p.PersonName, p.Country.CountryName);
}

That will produce these queries:

SELECT 
[Extent1].[PersonID] AS [PersonID], 
[Extent1].[PersonName] AS [PersonName], 
[Extent1].[TheCountryID] AS [TheCountryID]
FROM [dbo].[Person] AS [Extent1]


exec sp_executesql N'SELECT 
[Extent1].[CountryID] AS [CountryID], 
[Extent1].[CountryName] AS [CountryName], 
[Extent1].[Population] AS [Population]
FROM [dbo].[Country] AS [Extent1]
WHERE [Extent1].[CountryID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1


exec sp_executesql N'SELECT 
[Extent1].[CountryID] AS [CountryID], 
[Extent1].[CountryName] AS [CountryName], 
[Extent1].[Population] AS [Population]
FROM [dbo].[Country] AS [Extent1]
WHERE [Extent1].[CountryID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=2

That will fetch the entire properties of Country. In this case, even if you didn't use the Population property, it also get populated by the database. This is a trivial example, though in a fairly-complex application, fetching fields that are not needed could put an unnecessary load to your application.


Use projections instead:

foreach (var p in 
    (from x in db.Persons select new { x.PersonName, x.Country.CountryName } ) )
{
    Console.WriteLine("{0} {1}", p.PersonName, p.CountryName);
}


This is the query produced by the ORM with the above Linq. Very efficient.

SELECT 
[Extent1].[TheCountryID] AS [TheCountryID], 
[Extent1].[PersonName] AS [PersonName], 
[Extent2].[CountryName] AS [CountryName]
FROM  [dbo].[Person] AS [Extent1]
INNER JOIN [dbo].[Country] AS [Extent2] ON [Extent1].[TheCountryID] = [Extent2].[CountryID]


So what's the use of lazy loading? It's useful if you need to display multiple headers with multiple details. Think DataRepeater. With lazy loading, you can already display some of the page content without waiting for the whole batch of data to arrive. You can already display the header without waiting for the header's details to arrive.


Though in our trivial code example, it doesn't warrant lazy loading, the Country is not many-to-one to Person, there's only one country per person. The person and its country are immediately displayed together. Aside from country, think of other foreign keys of Person, say there are 10 foreign keys in your Person object, it's a lousy practice to lazy load those 10 related tables, when most of the time you are just getting one field (mostly Name or Description) from those 10 tables.


And if you really need to use all the properties of a navigated property, e.g. CountryName, Population, CountryCode, etc; just eager load the related table by using Include on your ORM's query. This way, you can also avoid writing tedious projection ( select new { field, goes, here, etc })

foreach(var p in db.Persons.Include("Country"))
{
    Console.WriteLine("{0} {1} {2}", p.PersonName, p.Country.CountryName, p.Country.Population);
}            

The generated SQL:
SELECT 
[Extent1].[PersonID] AS [PersonID], 
[Extent1].[PersonName] AS [PersonName], 
[Extent1].[TheCountryID] AS [TheCountryID], 
[Extent2].[CountryID] AS [CountryID], 
[Extent2].[CountryName] AS [CountryName], 
[Extent2].[Population] AS [Population]
FROM  [dbo].[Person] AS [Extent1]
INNER JOIN [dbo].[Country] AS [Extent2] ON [Extent1].[TheCountryID] = [Extent2].[CountryID]


Even you don't use the Population property...
foreach(var p in db.Persons.Include("Country"))
{
    Console.WriteLine("{0} {1}", p.PersonName, p.Country.CountryName);
}
...the above code still produces the same SQL as above. So that's the other side of the coin of eager loading, it is overkill if you will just read only one property from the navigated property; might as well you use projections on your ORM's Linq/HQL, so you can streamline the network load your query utilizes. That's the behavior of eager loading :-)

Thursday, May 19, 2011

Entity Framework Code First Navigation

Entity Framework folks can't claim their ORM can implement things the POCO way, classess are tainted with attributes, it's hard to call it Plain Old CLR Object anymore. I think that's why they just settle with Code-First, whatever that mean :-)


The Objects
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel.DataAnnotations;

namespace EfCodeFirstNavigation.Model
{


    public class Person
    {
        [Key]
        public int PersonID { get; set; }
        public string PersonName { get; set; }
        public int TheCountryID { get; set; }
        

        [ForeignKey("TheCountryID")]
        public virtual Country Country { get; set; }
    }

    public class Country
    {
        [Key]
        public int CountryID { get; set; }
        public string CountryName { get; set; }
        public int Population { get; set; }

        public virtual ICollection<Person> Persons { get; set; }
    }


}

The Relational Mapping and sample data(in comments)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;



namespace EfCodeFirstNavigation.Model
{
    public class CatalogContext : DbContext
    {

        public DbSet<Person> Persons { get; set; }
        public DbSet<Country> Countries { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();


            
        }
    }
}

/* DDL
 

create table Country
(
CountryID int identity(1,1) primary key,
CountryName varchar(100) not null,
Population int not null
);


create table Person
(
PersonID int identity(1,1) primary key,
PersonName varchar(100) not null,
TheCountryID int not null references Country(CountryID)
);


insert into Country(CountryName, Population) values('Philippines', 9)
insert into Country(CountryName, Population) values('China', 2)

insert into Person(PersonName, TheCountryID) values('Michael',1)
insert into Person(PersonName, TheCountryID) values('Jolin',2)
 
*/




Read database:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EfCodeFirstNavigation.Model;

using System.Data.SQLite;

namespace EfCodeFirstNavigation
{
    class Program
    {
        static void Main(string[] args)
        {
            var db = new CatalogContext();
            
            // I don't know why the need to use Include(this is for eager loading, analogous to Fetch of NHibernate) 
            // I thought Entity Framework supported lazy loading already.
            // I'll just research next time why lazy loading doesn't work.
            foreach(var p in db.Persons.Include("Country"))
            {
                Console.WriteLine("{0} {1}", p.PersonName, p.Country.CountryName);
            }
        }
    }//Program
}



Here's the generated query of Entity Framework:
SELECT 
[Extent1].[PersonID] AS [PersonID], 
[Extent1].[PersonName] AS [PersonName], 
[Extent1].[TheCountryID] AS [TheCountryID], 
[Extent2].[CountryID] AS [CountryID], 
[Extent2].[CountryName] AS [CountryName], 
[Extent2].[Population] AS [Population]
FROM  [dbo].[Person] AS [Extent1]
INNER JOIN [dbo].[Country] AS [Extent2] ON [Extent1].[TheCountryID] = [Extent2].[CountryID]


UPDATE(an hour later)

When not including Include, this error happen...
There is already an open DataReader associated with this Command which must be closed first.

..., the solution is to add MultipleActiveResultSets=True to your connection string, rationale can be found from MSDN. You can now remove Include from your Linq. Entity Framework supports lazy loading.

jQuery's LINQ's Select

<script src="/Scripts/jquery-1.5.1.min.js" type="text/javascript"></script>

<body>

@{
    var a = new[] { "The", "quick", "brown", "fox" };
    
    

    var b = a.Select(v => "www." + v + ".com");
    
    foreach(var t in b) {
        Response.Write(t + "<br/>");
    }

    int i = 0;
    var c = b.Select(v => new { v, i = ++i, m = i * 2 });
    
    foreach(var t in c) {
        Response.Write(t.v + " xxx " + t.i + " yyy " + t.m + "<br/>");
    }
    
}



<hr />


<script>

    // This evil code was sourced from http://stackoverflow.com/questions/761148/jquery-document-ready-and-document-write/761190#761190
    $(function () {
        document.write = function (evil) {            
            $('body').append(evil);            
        }
    });
    // ...evil :p mwahahah


    $(function () {
       
        a = ["jumps", "over", "lazy", "dog"];

        b = $.map(a, function (v) {
            return "www." + v + ".com";
        });

        $.each(b, function () {
            document.write(this + "<br/>");
        });


        i = 0;
        c = $.map(b, function (v) {
            return { v: v, i: ++i, m: i * 2 };
        });

        $.each(c, function () {
            document.write(this.v + " xxx " + this.i + ' yyy ' + this.m + "<br/>");
        });

    });
</script>


</body>


Output:

www.The.com
www.quick.com
www.brown.com
www.fox.com
www.The.com xxx 1 yyy 2
www.quick.com xxx 2 yyy 4
www.brown.com xxx 3 yyy 6
www.fox.com xxx 4 yyy 8

www.jumps.com
www.over.com
www.lazy.com
www.dog.com
www.jumps.com xxx 1 yyy 2
www.over.com xxx 2 yyy 4
www.lazy.com xxx 3 yyy 6
www.dog.com xxx 4 yyy 8

Tuesday, May 17, 2011

Pass array from jQuery to ASP.NET MVC controller

The View ( must pass traditional(set to true) property to ajax parameter ):

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

    $.ajax({
        url: '/Home/Simple/',
        type: 'POST',
        traditional: true,
        data: { Title: 'Greatest', Categories: ['show', 'on', 'earth'] },
        dataType: 'json',
        success: function (data) {
            alert(data.Title);
            for (i = 0; i < data.Categories.length; ++i)
                alert(data.Categories[i]);
        }
    });

});


The Controller:
[HttpPost]
public JsonResult Simple(Article a)
{
    return Json(a);
}

The Model:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace JquerySendBatch.Models
{
    public class Article
    {
        public string Title { get; set; }
        public string[] Categories { get; set; }
    }
}


If need to return multi-dimensional array, pass it as JSON object:


<input type='button' id='Simple' value='hello'/>


<script>
    $('#Simple').click(function () {

        var theData = [['a', 'b', 'c', 'z'], ['d', 'e', 'f', 'y']];
        var json = JSON.stringify(theData);

        $.ajax({
            url: '/Home/Test',
            type: 'POST',

            contentType: 'application/json; charset=utf-8',
            data: json,

            dataType: 'json',
            success: function (data) {
                for (i = 0; i < data.length; ++i) {
                    alert('wow');
                    for (j = 0; j < data[i].length; ++j)
                        alert(data[i][j]);
                }


            },
            error: function (a, b, c) {
                alert(a + ' ' + b + ' ' + c);
            }

        });

    });

</script>



Then on your controller:

[HttpPost]
public JsonResult Test(string[][] Categories)
{
    return Json(Categories);
}

Pass complex objects from jQuery to ASP.NET MVC controller method

For some reason this doesn't work even when including traditional

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

    var y = [];

    for (i = 0; i < 3; ++i) {
        var x = new Object();
        x.MemberName = "George" + i;
        x.BestSong = "Something" + i;
        x.BirthYear = 1943 + i;
        y.push(x);
    }


    var band = { BandName: 'Beatles', Members: y };


    $.ajax({
        url: '/Home/Complex/',
        type: 'POST',
        traditional: true,
        data: band,                
        dataType: 'json',
        success: function (data) {
            alert(data.BandName);
            alert(data.Members.length);
            for (i = 0; i < data.Members.length; ++i) {
                var m = data.Members[i];
                alert(m.MemberName + ' / ' + m.BestSong + ' / ' + m.BirthYear);
            }
        }
    });

});
But this one works:
$('#Complex').click(function () {

    var y = [];

    for (i = 0; i < 3; ++i) {
        var x = new Object();
        x.MemberName = "George" + i;
        x.BestSong = "Something" + i;
        x.BirthYear = 1943 + i;
        y.push(x);
    }


    var band = { BandName: 'Beatles', Members: y };


    var json = JSON.stringify(band);



    $.ajax({
        url: '/Home/Complex/',
        type: 'POST',                

        contentType: 'application/json; charset=utf-8',
        data: json,

        dataType: 'json',
        success: function (data) {
            alert(data.BandName);
            alert(data.Members.length);
            for (i = 0; i < data.Members.length; ++i) {
                var m = data.Members[i];
                alert(m.MemberName + ' / ' + m.BestSong + ' / ' + m.BirthYear);
            }
        }
    });

});
The Controller:
[HttpPost]
public JsonResult Complex(Rockband b)
{
    return Json(b);
}
The Model:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace JquerySendBatch.Models
{
    public class Rockband
    {
        public string BandName { get; set; }
        public IList<Member> Members { get; set; }
    }

    public class Member
    {
        public string MemberName { get; set; }
        public string BestSong { get; set; }
        public int BirthYear { get; set; }
    }
}

Sunday, May 15, 2011

What is MVCC? If you don't know, your boss will fire you and hire someone with nanosecond-perfect timing

First of all, a disclaimer: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED (revoke someone's DBA license if he/she consider it for a money-transacting app) is not even remotely considered in this discussion. We are talking serious business here, we are talking money :p

Simply put, MVCC can solve many of the database timeout woes. The writers never block the readers. The old school Sql Server application developers and even the new ones(those who are not aware that Sql Server 2005 onwards already has MVCC capability) argues that you should not be able to read a row if some code is making a transaction on that particular row. Don't heed that suggestion, especially Sql Server 2005 onwards already has MVCC, don't let yourself lose your job.


Now here's a contrive yet amusing scenario, let's say the transaction(e.g. long running batch job) takes a whole day, yet you still love your flavor of RDBMS, warts and all, as it is very performant on aggregating informations and delivering the results under 1 second. Would you let your boss down and not let him know today's sales just because some code obtained a lock on 1 row only for the entire day?


I know, I know, that bottleneck is very absurd, one day, but guess what, the pain threshold on waiting for the database to return results varies with everyone, a 30 second waiting might be tolerable to you, but a 10 second waiting might irk your boss, especially everyone now is accustomed to speed of thought web(e.g. google, online stock exchange, e-commerce, etc).


If you don't have consistent user experience when it comes to returning information to your users or web audience, as your database experiences recurring timeouts; it might turn off your users and you will lose them to your competitors.


An example, on a fairly busy website, many are repeatedly editing information(think Facebook users who often change their information, motto, slogan, status message, etc), SELECTing all rows (say, through aggregations, e.g. SUM, COUNT) won't have a fighting chance to be able to perform what you told it to do, provide reports. As in-between milliseconds, your database is experiencing UPDATEs.



MVCC can prevent that problem, as it provide row readers the last snapshot of row(s) prior someone is busy transacting on those row(s). Note: the actual implementation of MVCC is, the writers has separate copy of rows they are updating/deleting/inserting, those copy are independent from active rows, and those are not effected to database until the transaction is committed.


If someone suggested SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to make your code able to read rows, tell them it's not a good practice; as your code can potentially read partially finished transaction, think fund transfers. There's 5 bucks on Account A, and 10 bucks on Account B. If you deduct 2 bucks from Account A and increase the Account B with 2 bucks, but however, in-between that two steps, someone perform checking the total money of bank(i.e. SELECT SUM(Amount) FROM BankAccount), your code can read 13 only instead of 15.


With MVCC turned on on Sql Server, your program would be able to read a grand total of 15 no matter how slow your transaction processing is, no matter what place in code your transaction is currently processing at. Even if the other code deleted(think customers who pull-out their account) those two rows inside a transaction, your code will still be able to read 15. Why it should be that way? At that point-in-time (during transaction) say 1:15 PM, your boss is asking how much money you have in bank, and that point-in-time 1:15 PM, your bank still has money, and the transaction(pulling-out accounts) is not yet committed(code-wise and real-world-wise), your system is just honest, you still have money at that point-in-time and reports 15 bucks, and on the report printout it has a footer that indicates Printed on: May 15, 2011 1:15 PM. Then 10 minutes later(say you are unfortunate that your RDBMS of choice performs very slowly) the customer's pull-outs of his two accounts is committed to the database, then that's the only time you shall return money to your customer, and that's the only time(May 15, 2011 1:25 PM) your system can honestly report that your bank don't have money.


You should not let your system be rendered unusable if it is pressed for information, it should still be able to perform reporting values even your business operation has very heavy transactions. Give yourself a fighting chance to be able to keep your job. You: "Boss, I cannot print the report you requested, our business is doing extremely well, we have transactions in between milliseconds and perhaps microseconds, it's hard for me to make a perfect timing around the system so then I can print the report you requested" Boss: "Ok then, you are fired! I'll just hire someone who has nanosecond-perfect timing"


Here's how to turn on MVCC on your database:

ALTER DATABASE <database name>
   SET READ_COMMITTED_SNAPSHOT ON;


So what is MVCC? If you don't know it, learn it so you can concentrate on business logic rather than resolving technical problems such as timeouts. If you are just a humble user, demand it from programmers so you can keep your job.


Oracle already has MVCC since version 3 (1983). Postgresql(my favorite RDBMS) already has it since version 6.5 (1999).


Sql Server is a late-bloomer, be easy on it, don't bash it too much for causing you so much griefs on incessant timeouts. Anyway, on Sql Server 2005 and up, turning MVCC on is just an ALTER command away :-)


UPDATE


This need to be set too:

ALTER DATABASE <database name> SET ALLOW_SNAPSHOT_ISOLATION ON;

Details here:

http://stackoverflow.com/questions/1483725/select-for-update-with-sql-server/1529377#1529377

Monday, May 9, 2011

MySql "is distinct from"

I just saw that search phrase on my blog hit. This is MySql way:

Select * from tbl where fieldA <=> fieldB

And this is Sql Server's IS DISTINCT FROM : http://www.ienablemuch.com/2010/10/sql-server-is-distinct-from.html

Saturday, May 7, 2011

Pass dynamic data to Flexigrid or jqGrid

How to pass dynamic data to Flexigrid? ala-ASP.NET's GridView which columns are auto-populated when you don't statically assigned it columns.

Use Linq. Cast the ADO.NET DataTable's Columns to Linq-able DataColumn. If you are using Flexigrid or jqGrid, use the following:


On Controller, use this:
public class HomeController : Controller
{
    //
    // GET: /Home/

    // public string TableName = "INFORMATION_SCHEMA.COLUMNS";
    public string TableName = "Country";

    public ActionResult Index()
    {
        ViewBag.Columns = TheColumns(TableName);
        return View();
    }

    public JsonResult List(int page, int rp)
    {
        
        int offset = (page - 1) * rp;

        var da = new SqlDataAdapter(                
string.Format(
@"
with a as
(
select ROW_NUMBER() over(order by {0}) - 1 as r, * from {1} 
)
select * from a
where r between {2} and {3}",
            TheColumns(TableName)[0].ColumnName, 
            TableName,
            offset, offset + rp - 1),  TheConnection());
        var dt = new DataTable();
        da.Fill(dt);

        var jsonData = new
        {
            page = page,
            total = RowCount(TableName),
            rows = dt.Select()
                .Select(row =>
                    new
                    {
                        // 0 is row number, 1 is the primary key
                        id = row[1].ToString(),
                        // don't include row number and primary key on display, it is in Ordinal 0 and 1 respectively                            
                        cell = dt.Columns.Cast<DataColumn>().Where(col => col.Ordinal > 1)
                            .Select(col => row[col.ColumnName].ToString())
                    }
                )
        };

        return Json(jsonData);
    }

    long RowCount(string tableName)
    {
        return (long)new SqlCommand("select count_big(*) from " + tableName, TheConnection()).ExecuteScalar();
    }

    DataColumnCollection TheColumns(string tableName)
    {
        var da = new SqlDataAdapter("select * from " + TableName + " where 1 = 0", TheConnection());
        var dt = new DataTable();
        da.Fill(dt);
        return dt.Columns;
    }

    SqlConnection TheConnection()
    {
        var c = new SqlConnection("Data Source=localhost; Initial Catalog=OkSystem; User Id=sa; Password=P@$$w0rd");
        c.Open();
        return c;
    }

}

Then on View, use this:

@using System.Data;

<script src="/Scripts/jQuery/jquery-1.4.4.min.js" type="text/javascript"></script>

<link href="/Scripts/flexigrid/flexigrid.css" rel="stylesheet" type="text/css" />
<script src="/Scripts/flexigrid/flexigrid-google-minified-simple.js" type="text/javascript"></script>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>


<table id="theNavigation"></table>


@{ int i = 0; }

<script type="text/javascript">
    $(function() {

        $('#theNavigation').flexigrid({
            url: '/Home/List',
            dataType: 'json',
            colModel: [ 
                @foreach (DataColumn c in ViewBag.Columns) {
                    ++i;                    
                    if (i == 1) {
                        continue; // skip primary key
                    }
                    <text>{ display: '@c.ColumnName', name: '@c.ColumnName', width: 100}@(i != ViewBag.Columns.Count ? "," : "")</text>
                }
            ],
            singleSelect: true,            
            usepager: true,
            title: 'Sample Dynamic',
            useRp: true,
            rp: 5,
            showTableToggleBtn: true,
            width: 680,
            height: 200

        }); //flexigrid

        $('#theNavigation').click(function() {
            alert('A');
            var items = $('.trSelected', this);
            alert(items.length);
            if (items.length == 1)
            {
                var pk = items[0].id.substring(3);
                alert(pk);
            }
        });
        
    });
</script>

There's no model, columns are dynamic, obtained from ADO.NET DataTable :-)