Mike's a long-time, C# developer, with experience in database development, software architecture & design, .Net, ASP.NET, HTML, JavaScript, VB, CSS, Flash, SQL Server, Oracle, and more.
Tuesday, March 31, 2009
SQL Server 2005's EXCEPT & INTERSECT Union-like Operators
SQL Server 2005's Ranking Functions
You can create columns that provide unique ranking/sorting capabilities. For example, ROW_NUMBER() (produces unique row numbers):
Thursday, March 26, 2009
Creating Pivoted Joins in SQL Server 2005
You can take a vertical result set and turn it into a horizontal one, like this:
The preceding query takes a count of how many licenses belong to which BMW models, and displays them horizontally. The output should be something like this:
If were were to utilize a simple select statement, it would look something like this:
Wednesday, March 25, 2009
Random Data Sampling Queries in SQL Server 2005
The following query will return a random sampling of data:
New Join Types in SQL Server 2005
CROSS APPLY (equivalent to an inner join):
OUTER APPLY (equivalent to a left join):
Note that any object with no parameter or column will not have an any records in the syscolumns table (eg, SPROCs with no parameters).
Using Common Table Expressions in SQL Server 2005
Pretty cool stuff:
Results from my DB:
Tuesday, March 24, 2009
ASP.NET MVC - Validation of viewstate MAC failed.
Yup, View State is not supported in MVC, so beware of this. If you try to use a control that relies on it, you'll get the following error:
Validation of viewstate MAC failed. If this application is hosted by a Web Farm or cluster, ensure thatconfiguration specifies the same validationKey and validation algorithm. AutoGenerate cannot be used in a cluster.
Note that you can still use ViewState with ASPX/Web Forms as Views in MVC, but that violates the MVC pattern.
ASP.NET MVC - Preventing an Action Method from Being Called Publicly
By default, any action method defined in a controller class can be called via a browser request. To prevent this, simply mark the method with the [NonAction] attribute:
ASP.NET MVC - Creating Routing Constraints
To restrict the format of the parameter passed to an action method, you can create a custom route with a regular expression to determine in what format you want the parameter.
- Open up the Global.asax file.
- Add the following routing code, right above the default route:
routes.MapRoute( "Test1", "Test1/{AreaCode}", new { controller = "Test1", action = "Index" }, new { AreaCode = @"[1-9][0-9][0-9]" } ); - Save the file and compile (Ctrl-Shift-B) and load the application (Ctrl-F5).
The controller action method (in the Test1Controller.cs class file) for the preceding route should look something like this (provided a View was created for that action result:
Test1 refers to the controller, Index refers to the action, or method, and {AreaCode} refers to the parameter passed to that action/method. When it runs, MVC calls the following method: Test1Controller.Index(AreaCode). The "[1-9][0-9][0-9]" regular expression ensures that the parameter passed contain 3 digits, no more, no less. If the constraint is violated, a "The resource cannot be found." error code is returned.
The following URL is valid for the preceding example: http://localhost:????/Test1/111
The following URLs are NOT valid for the preceding example:
http://localhost:????/Test1/11x
http://localhost:????/Test1/11
http://localhost:????/Test1/1123
Monday, March 23, 2009
ASP.NET MVC - Creating Custom Routes
The following directions will create both a custome MVC route and show how you can return something other than a View.
- Adding a custom route
- Open up the ASP.NET MVC VS.NET project.
- Open up the Global.asax file.
- Add the following code, right above the default routing code:
routes.Add( "DotNetFun", "DotNetFun/{GUID}", new { controller = "DotNetFun", action = "Article" } ); - In the preceding route, DotNetFun refers to the controller, Article refers to the action method, and {GUID} refers to the parameter passed to the method, in the form of a valid Guid.
- In the Solution Explorer, right click on the Controllers folder, then click on Add-->Controller.
- Name the controller DotNetFunController.
- Click on Add.
- Modify the existing Index method so that it looks like the following:
public String Article(Guid GUID) { return GUID.ToString(); } Notice how the name of the method changes completely. - Save the file.
- Right click inside of the Article method, then click on Add View...
- Leave the defaults, then click on Add.
- Press Ctrl-Shift-B to compile your project, then click on Ctrl-F5 to test it.
- You should be able to get to the related path like this: http://localhost:????/DotNetFun/8599AE31-2409-4875-9ECF-0698FAE23EDA
- Again, DotNetFun refers to the controller and 8599AE31-2409-4875-9ECF-0698FAE23EDA to the Guid value passed as a parameter to that method. We don't need to add Article to the URL, since the controller is mapped to it already. We could always demand it by modifying the route, like this:
routes.MapRoute( "DotNetFun", "DotNetFun/{action}/{GUID}", new { controller = "DotNetFun", action = "Article" } ); Then, we could use the following URL: http://localhost:????/DotNetFun/Article/8599AE31-2409-4875-9ECF-0698FAE23EDA - Returning content other than a View
- By default MVC returns a View, which is typically in the form of an ASPX web form, but you can return a string, too. To do this, for example, modify the preceding DotNetFun controller action created above:
public String Article(Guid GUID) { return String.Format("You passed to the Article method this GUID: {0}", GUID.ToString()); } - Recompile the application.
- The result should reveal something like this: You passed to the Article method this GUID: 8599ae31-2409-4875-9ecf-0698fae23eda
Please note that if the parameter passed is not a valid Guid, you'll get the following error:The parameters dictionary contains a null entry for parameter 'GUID' of non-nullable type 'System.Guid' for method 'System.String Article(System.Guid)' in 'MVCTest.Controllers.DotNetFunController'. To make a parameter optional its type should be either a reference type or a Nullable type.
Parameter name: parameters
Getting Started with ASP.NET's MVC (Model View Controller)
- Download and install MVC 1.0.
- Create the MVC project.
- Startup VS.NET 2008.
- Click on File-->New-->Project.
- Choose the Web project type.
- Choose the ASP.NET MVC Web Application
- Click on OK.
- Create the Controller.
- In the Solution Explorer, right click on the Controllers tab, then click on Add-->Controller.
- Name it Test, then click on OK.
- Open up the Test1Controller.cs class file, in the Controllers folder.
- Modify the Index() method so that it looks like this:
public ActionResult Index(int ID) { return View(); } - Save it.
- Create the action.
- Right click inside of the Index method, then click on Add view...
- Leave the defaults, then click on Add.
- In the Views folder, notice that the Index.aspx page was added to the Test1 folder.
- Press Ctrl-Shift-B to compile the application.
- Press Ctrl-F5 to run the application.
- The following URL will map to the Test1 controller, action, and view: http://localhost:????/Test1/Index/1
Test1 is the controller. Index is the action, defined as a method inside the controller class, and 1 is the ID integer passed to that method. When that URL is process, the following method is called: Test1Controller.Index(1) - To make the ID integer options, convert the type from int to a nullable integer: int?
public ActionResult Index(int? ID) { return View(); } - After that, you should be able to omit the ID, like this: http://localhost:????/Test1/Index/ or http://localhost:????/Test1/
- Remember to compile the application every time a code change is performed.
Thursday, March 19, 2009
Be Careful Using OnClientClick with ASP.NET Web Controls
When using the OnClientClick property of Web Controls in ASP.NET, especially when the Web Controls involved have their CausesValidation property set to true, validation and related post-backs will be ugly and not function correctly. OnClientClick seems to work well, however, with Web Controls whose CausesValidation property is set to false.