Crosstab Query (3)

DECLARE @Names VARCHAR(8000)  
SELECT @Names = COALESCE(@Names + ', ', '') + Name FROM People

Or this post about FOR XML PATH
Posted in SQL | Leave a comment

Southern California .NET user groups

Orange County

Master Calendar of many developer user groups in the OC and in San Diego –

V_Next Orange County –
XPSoCal (Agile) –
Orange County .NET User Group —
Orange County Android UG –
SoCal .Net Architecture Users Group –
Orange County SQL User Group –

Amazon Web Services AWS OC –
OC-HUG (Hadoop) –
MongoDB OC –


San Gabriel Valley (Pasadena) .NET Developers Group —
LA C# Developers Group —
Los Angeles .NET Developers Group —
Los Angeles SQL Server Professionals Group —
South Bay .NET User Group —
LA Ruby –

MongoDB LA — AWS (Amazon Web Services) LA –
R (Data Science Language) LA –
LA Coding Dojo (Live coding practices) –
Educelerate LA (LA technical education meetup) —


Inland Empire .NET User’s Group –

San Diego and South

San Diego .NET Developers Group –
San Diego .NET User Group —
San Diego SQL Server Users Group (also San Diego SQL BI) –
Agile San Diego –
SD JUG (Java) –
San Diego Tech Immersion (Study) Group –
SD Ruby –
San Diego GTUG (Google Developer User Group) –

Posted in Uncategorized | Leave a comment

Injecting Values for Default MVC Model Binding

In a recent project, due to security concerns, we want to encrypt all QueryString values into a single encrypted value before placing the link in web pages, then decrypt it back to individual name/value pairs and pass them to default MVC model binder to bind (to both simple types and complex types).
Default MVC model binder reads from form data, query string, route data etc. Among those, only route data is editable. So the natural choice is to inject these decrypted values to route data collection:

HttpContext.Current.Request.RequestContext.RouteData.Values["AccountId"] = 1234;

But the key is where do we do that? It must be before the model binder kicks in.
I tried Application_BegineRequest and found it is too early. Any RouteData I set there will be wiped out in MVC’s routing set up process, where MVC initializes the route data with controller, action, and other values.
After some research I found I can implement a custom model binder (by inheriting the DefaultModelBinder) and register it in glabal.asax to replace the default one. I will have the opportunity to populate the route data before I pass the control to the default/base implementation:

public class CalvinModelBinder : DefaultModelBinder
 public override object BindModel(ControllerContext controllerContext, 
 ModelBindingContext bindingContext)
 if( encryptionKeyFoundInQueryString )
 controllerContext.RouteData.Values["accountId"] = myDecryptedValue;
 return base.BindModel(controllerContext, bindingContext);

And register it in global.asax:

ModelBinders.Binders.DefaultBinder = new CalvinModelBinder();

However, this didn’t work. The controllerContext DID have my route data inserted, but bindingContext’s ValueProvider property didn’t have it, and this very bindingContext is what MVC’s default model binding uses to get values. I dived into the ValueProvider property (a ValueProvider collection consists of FormDataValueProvider, QueryStringDataValueProvider, JsonDataValueProvider etc.) and found that the RouteDataValueProvider keeps a local copy of the route data and doesn’t query controllerContext.RouteDate.Values.
After checking MVC source code, I found that RouteDataValueProvider constructs it’s local value list from controllerContext at a very early stage, before the model binding is even being called. Any changes to controllerContext.RouteData after that will not affect the internal list in RouteDataValueProvider.
Here is the final solution. First in global.asax, I registered a custom ValueProviderFactory:

ValueProviderFactories.Factories.Insert(0, new CalvinValueProviderFactory());

It’s very important that my own factory is registered at the first place, so that my object will be constructed before all other ValueProviders. Here is my value provider and its factory:

public class CalvinValueProviderFactory : ValueProviderFactory
 // this will be called when controllerContext first initialize ValueProvider property, 
 // which will call all registered factories. Make sure ours is the first one
	public override IValueProvider GetValueProvider(ControllerContext controllerContext)
		return new CalvinValueProvider();
public class CalvinValueProvider : IValueProvider
	public CalvinValueProvider()
		if (!String.IsNullOrEmpty(HttpContext.Current.Request["calvinKey"]))
			var values = HttpContext.Current.Request.RequestContext.RouteData.Values;
			values["AccountId"] = "4";
			values["page"] = 1;
			values["GroupName"] = "testgroup";
 // still have to implement these method of this interface but we don't provide any values.
 // the whole purpose of this class to inject to route data before RouteDataValueProvider is being constructed
 // and we leave the binding to MVC's default mechanism
	public bool ContainsPrefix(string prefix)
		return false;
	public ValueProviderResult GetValue(string key)
		return null;

The binding works for both simple types and complex types.
Initially I was trying to implement a complete customized value provider but the logic in ContainsPrefix() became very tricky. Injecting values to route data and having default MVC mechanism to handle it is much easier.

Posted in ASP.NET, MVC | Tagged , | Leave a comment


A good link:

Useful commands:

SELECT SESSIONPROPERTY(‘numeric_roundabort’)

NUMERIC_ROUNDABORT seems is a server level (or user session level) setting. Because when I change it on one DB, all other DBs are changed too.

If use Visual Studio Database project, make sure to check the database settings in project properties. Otherwise the changes in DB may be overwritten by next publish.


Posted in SQL | Tagged | Leave a comment

Why the view doesn’t display the new values set in the post back method

In MVC, when you post back any changes, it’s recommended that you follow the PRG (Post-Redirect-Get) pattern to the View page. However, if you want to display the same model data back in the same request, there might be something you have to do.

Say if you have Edit method handles both Create and Update request, and you have a @Html.HiddenFor(m=>m.Id) in your view so that the update method knows which record to update:

 if( model.Id == 0 )
    int newId = InsertNewToDataBase( model );
   model.Id = newId; // <= this will not work

return View(model);

After a new record is created, you want to set the new Id to the model so that the view will pick it up for the following edit request to work. No, this doesn’t work, @Html.HiddenFor(m=>m.Id) will still give you a zero.

Why is that?

MVC maintains a ModelState collection which holds the posted back data along with validation result. When you display the same view back with the posted back model, MVC will display the original value user enters on the browser with possible validation errors. For data integrity reasons, MVC ignores your direct change on the view model data when it binds the data using the Html helper method. So your model.Id = newId will NOT work.

The workaround is either use pure HTML control

<input type=’hidden’ value=’@Model.Id’ />

or remove the individual entry in the ModelState collection


ModelState.Clear() works too. It will remove everything including the validation messages.

Posted in MVC | Tagged | Leave a comment

TFS – “The path … is already mapped in workspace …”

After installing some power too, my VS 2010 now always run under another admin account of me. So my previous mapping of the projects are lost and I can’t check out files to the original local path. The above error occurs if I try to remap the project to the same location. After some googling these tips helps:

In VS command line, use

  • c:\>tf workspaces /owner:*  (to show all workspace)
  • c:\>tf workspace /delete COMPUTER_NAME;USER_NAME (to remove the workspace)
  • c:\>tf workspace COMPUTER;USERNAME (this will open a GUI dialog to edit the workspace. The BEST option!)

I change my workspace to public so that any user can work on it (in case my VS switches back to my own account).

Posted in Tools | Tagged | Leave a comment

SQL command always times out after 30 seconds

One of my query takes more than 30 seconds to run. Even I set the “Connection Timeout=120” in my connection string, it still throwing timed out exception. It turned out that the time out setting in the connection string is only for setting up the connection (as the name implies). To change the default 30 seconds time out for each individual database call, I need to set it on the SqlCommand object:

sqlCmd.CommandTimeout = 120; // 2 minutes

Posted in .NET, SQL | Tagged | Leave a comment

Src=” causes double page requests

A html tag with src=” (ie img) will cause the browser to try to request that resource at the same URL as the current page, hence causes double requests.

Posted in ASP.NET | Tagged , | Leave a comment

Formatting Dates, Times and Numbers in C#

Good reference (with examples)

Posted in ASP.NET | Tagged , | Leave a comment

Crosstab query (2)

Part one here.

Another way to do vertical to horizontal query. Let’s have a table with sales_id, prod_id, sales_date, and sales_amount fields. This query will return 4 latest sales_id in 3 columns:

SELECT prod_id, (SELECT TOP 1 sales_id FROM table WHERE prod_id=s.prod_id ORDER BY sales_date) as sales_id1,

(SELECT TOP 1 sales_id FROM table WHERE prod_id=s.prod_id AND sales_id NOT IN (SELECT TOP 1 sales_id FROM table WHERE prod_id=s.prod_id ORDER BY sales_date) ORDER BY sales_date) as sales_id2,

(SELECT TOP 1 sales_id FROM table WHERE prod_id=s.prod_id AND sales_id NOT IN (SELECT TOP 2 sales_id FROM table WHERE prod_id=s.prod_id ORDER BY sales_date) ORDER BY sales_date) as sales_id3,

(SELECT TOP 1 sales_id FROM table WHERE prod_id=s.prod_id AND sales_id NOT IN (SELECT TOP 3 sales_id FROM table WHERE prod_id=s.prod_id ORDER BY sales_date) ORDER BY sales_date) as sales_id3

FROM table as s GROUP BY prod_id

Then you can join this view/sub-query to get detailed data for each row.


Posted in SQL | Tagged | Leave a comment