Tuesday, March 31, 2009

SQL Server 2005's EXCEPT & INTERSECT Union-like Operators

SELECT name FROM master..sysobjects where name = 'all_columns' UNION SELECT name FROM master..sysobjects where name = 'routes'; Results:name ----------- all_columns routes SELECT name FROM master..sysobjects where name = 'all_columns' EXCEPT SELECT name FROM master..sysobjects where name = 'routes'; Results:name ----------- all_columns SELECT name FROM master..sysobjects where name = 'all_columns' INTERSECT SELECT name FROM master..sysobjects where name = 'routes'; Results:name ----------- SELECT name FROM master..sysobjects where name = 'all_columns' INTERSECT SELECT name FROM master..sysobjects where name = 'all_columns'; Results:name ----------- all_columns

SQL Server 2005's Ranking Functions

You can create columns that provide unique ranking/sorting capabilities. For example, ROW_NUMBER() (produces unique row numbers):

SELECT ROW_NUMBER() OVER (ORDER BY name) AS ROWNUM, name FROM sysobjects; The preceding query produces the following results:ROWNUM name -------------------- -------------------------------------------------------------------------------------------------------------------------------- 1 CoOp 2 Country 3 DF__dtpropert__versi__77BFCB91 4 DF_Carriage_CarriageTypeName 5 DF_CoOp_CoOpName 6 DF_Country_CountryName 7 DF_ExceptionLog_Occurred 8 DF_History_ActionDateTime 9 DF_History_ActionTakenByUserID 10 DF_History_Comments 11 DF_History_ETA 12 DF_History_LastNoticeStatusID ..... Or, there's RANK() (produces non-unique sorting numbers with possible gaps/skips in the sequence)...SELECT RANK() OVER (ORDER BY name asc) AS RANK, name FROM master..syscolumns WHERE LEN(name) > 0; That query produces this:RANK name -------------------- -------------------------------------------------------------------------------------------------------------------------------- 1 @10data 1 @10data 3 @11data 3 @11data 5 @12data 5 @12data 7 @13data 7 @13data 9 @14data 9 @14data 11 @15data 11 @15data 13 @16data 13 @16data ..... Or, there's DENSE_RANK() (produces non-unique sorting numbers with NO gaps/skips in the sequence)...SELECT DENSE_RANK() OVER (ORDER BY name asc) AS RANK, name FROM master..syscolumns WHERE LEN(name) > 0; That will produce this:RANK name -------------------- -------------------------------------------------------------------------------------------------------------------------------- 1 @10data 1 @10data 2 @11data 2 @11data 3 @12data 3 @12data 4 @13data 4 @13data 5 @14data 5 @14data 6 @15data 6 @15data 7 @16data 7 @16data ...... Then there's the interesting NTILE(n) function. This will produce partitioned/divided result sets with equal sequenced numbers, like this:WITH SystemObjects AS ( SELECT TOP 10 name FROM master..sysobjects ) SELECT NTILE(5) OVER (ORDER BY name) AS 'NTILE(5)', name from SystemObjects; That query produces the following output, with the numbers 1 through 5 having a set of 2 for each divided/partitioned section:NTILE(5) name -------------------- -------------------------------------------------------------------------------------------------------------------------------- 1 all_columns 1 all_objects 2 all_parameters 2 all_sql_modules 3 all_views 3 allocation_units 4 assemblies 4 assembly_files 5 assembly_modules 5 assembly_references

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:

DECLARE @TBLTEMP TABLE (Make VARCHAR(32), Model VARCHAR(12), License VARCHAR(12)); INSERT INTO @TBLTEMP VALUES ('BMW', '380i', 'GS45PK1'); INSERT INTO @TBLTEMP VALUES ('BMW', '380i', 'KL45QZ1'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M5', 'QL4AQ91'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M3', '345JK12'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M3', 'RT5JJK2'); SELECT DISTINCT tmp.Make, [380i], [M5], [M3] FROM @TBLTEMP tmp INNER JOIN (SELECT Make, Model FROM @TBLTEMP) PivotTable PIVOT ( COUNT(Model) FOR Model IN ([380i],[M5],[M3]) ) PivotColumns ON tmp.Make = PivotColumns.Make;

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:

Make 380i M5 M3 -------------------------------- ----------- ----------- ----------- BMW 2 1 2

If were were to utilize a simple select statement, it would look something like this:

DECLARE @TBLTEMP TABLE (Make VARCHAR(32), Model VARCHAR(12), License VARCHAR(12)); INSERT INTO @TBLTEMP VALUES ('BMW', '380i', 'GS45PK1'); INSERT INTO @TBLTEMP VALUES ('BMW', '380i', 'KL45QZ1'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M5', 'QL4AQ91'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M3', '345JK12'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M3', 'RT5JJK2'); SELECT Make, Model, COUNT(*) AS [Count] FROM @TBLTEMP GROUP BY Make, Model; The results being this: Make Model Count -------------------------------- ------------ ----------- BMW 380i 2 BMW M3 2 BMW M5 1

Wednesday, March 25, 2009

Random Data Sampling Queries in SQL Server 2005

The following query will return a random sampling of data:

SELECT * FROM TBLAPPLICATIONLOG TABLESAMPLE SYSTEM (10 PERCENT); SELECT * FROM TBLAPPLICATIONLOG TABLESAMPLE SYSTEM (200 ROWS); Not only will the records returned be random, but so will the number of rows.

New Join Types in SQL Server 2005

CROSS APPLY (equivalent to an inner join):

SELECT so.name as ObjectName, sc.ColumnName FROM sysobjects so CROSS APPLY ( SELECT name AS ColumnName FROM syscolumns WHERE so.id = id ) sc ORDER BY so.name, sc.ColumnName You will get no null ColumnName fields because this is equivalent to an inner join, so only objects with matching columns or parameters, in the case of SPROCs and functions, will be returned.

OUTER APPLY (equivalent to a left join):

SELECT so.name as ObjectName, sc.ColumnName FROM sysobjects so OUTER APPLY ( SELECT name AS ColumnName FROM syscolumns WHERE so.id = id ) sc ORDER BY so.name, sc.ColumnName You will get any type of object, even non-tables, non-SPROCs, etc, will have their ColumnName fields as null, as this is 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:

-- Common Table Expressions example: WITH tblDBs AS ( SELECT COUNT(*) AS [Number of Databases] FROM sysdatabases ), tblTables AS ( SELECT COUNT(*) AS [Number of Tables] FROM sysobjects WHERE xtype = 'U' ), tblColumns AS ( SELECT COUNT(*) AS [Number of Columns] FROM syscolumns ) SELECT * FROM tblDBs db, tblTables, tblColumns;

Results from my DB:

Number of Databases Number of Tables Number of Columns ------------------- ---------------- ----------------- 23 6 11258 (1 row(s) affected)

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 that configuration 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:

[NonAction] public ActionResult Index(int AreaCode) { return View(); } The result of calling a non-action method will be an HTTP 404 error: "The resource cannot be found."

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.

  1. Open up the Global.asax file.
  2. 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]" } );
  3. 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:

public ActionResult Index(int AreaCode) { return View(); }

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.

  1. Adding a custom route
  2. Open up the ASP.NET MVC VS.NET project.
  3. Open up the Global.asax file.
  4. Add the following code, right above the default routing code: routes.Add( "DotNetFun", "DotNetFun/{GUID}", new { controller = "DotNetFun", action = "Article" } );
  5. 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.
  6. In the Solution Explorer, right click on the Controllers folder, then click on Add-->Controller.
  7. Name the controller DotNetFunController.
  8. Click on Add.
  9. 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.
  10. Save the file.
  11. Right click inside of the Article method, then click on Add View...
  12. Leave the defaults, then click on Add.
  13. Press Ctrl-Shift-B to compile your project, then click on Ctrl-F5 to test it.
  14. You should be able to get to the related path like this: http://localhost:????/DotNetFun/8599AE31-2409-4875-9ECF-0698FAE23EDA
  15. 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
  16. Returning content other than a View
  17. 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()); }
  18. Recompile the application.
  19. 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)

  1. Download and install MVC 1.0.
  2. Create the MVC project.
  3. Startup VS.NET 2008.
  4. Click on File-->New-->Project.
  5. Choose the Web project type.
  6. Choose the ASP.NET MVC Web Application
  7. Click on OK.
  8. Create the Controller.
  9. In the Solution Explorer, right click on the Controllers tab, then click on Add-->Controller.
  10. Name it Test, then click on OK.
  11. Open up the Test1Controller.cs class file, in the Controllers folder.
  12. Modify the Index() method so that it looks like this: public ActionResult Index(int ID) { return View(); }
  13. Save it.
  14. Create the action.
  15. Right click inside of the Index method, then click on Add view...
  16. Leave the defaults, then click on Add.
  17. In the Views folder, notice that the Index.aspx page was added to the Test1 folder.
  18. Press Ctrl-Shift-B to compile the application.
  19. Press Ctrl-F5 to run the application.
  20. 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)
  21. To make the ID integer options, convert the type from int to a nullable integer: int? public ActionResult Index(int? ID) { return View(); }
  22. After that, you should be able to omit the ID, like this: http://localhost:????/Test1/Index/ or http://localhost:????/Test1/
  23. 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.