Archive

Archive for April, 2008

Windows Live Writer – Code Formatter Plugin

April 27, 2008 Leave a comment
Found this cool plugin for Windows Live Writer created by Steve Dunn. The author provides binaries as well as the source code for the plugin.
 
This plug-in formats and highlights code and also does the following:
  • The ability to format the code ‘live’
  • The ability to wrap lines
  • The ability to change the background color
  • The ability to just quickly paste what’s in the clipboard as code
  • The ability to change the font, including the font name, size, weight, and style.

The only annoyance with this plugin is that I have to manually remove the background style from the div for the source code so that the default background of my space is used. Perhaps there is some configuration setting which disables this which I have overlooked. Other than that this is a must have plugin for anyone using Windows Live Writer.

The best part is that you can edit the xml files for each language to change the styles for the tokens and you can also add new keywords etc. The xml files are located at:
%PROGRAM FILES%\Windows Live\Writer\Plugins\Languages\ActiproSoftware.<LanguageName>.xml

Table-Value Parameters in SQL Server 2008

April 26, 2008 2 comments

One of the cool new data types added in SQL Server 2008 is Table-Value Parameters. You can use table value parameters to send multiple rows of data to a Transact-SQL statement, or to a routine such as a stored procedure or function. This can be done without creating a temporary table or many parameters. Table-value parameters are like parameter arrays in Object Linking and Embedding Database (OLE DB) and Open Database Connectivity (ODBC), but offer better flexibility and closer integration with Transact-SQL. Table-value parameters can participate in set-based operations.

Table-value parameters allow you to pass user-defined tables between queries and also between the client and the server. In SQL Server 2008, you can use table-value parameters to pass an entire set of data into functions and procedures.

In earlier versions of SQL Server in order to pass in a set of values one had to create a temporary table, populate it with data using INSERT, and then just use it in the procedure or function since they are created for the current session and are available to all processes in that session.

With table-value parameters you need to declare a table variable, populate it with data using standard routines such as INSERT, and then pass it into a procedure or a function. The database engine handles this data by using a reference marked with the READONLY property to ensure that the data does not get copied again.

Table-value parameters have two primary components – a SQL Server type and a parameter. The following are the steps to create and use table-value parameters:

  1. Create a table type and define the table structure.
  2. Declare a routine that has a parameter of the table type.
  3. Declare a variable of the table type and then reference the table type.
  4. Fill the table variable by using an INSERT statement.
  5. Pass the variable to a routine.

The following code shows how you can create and use table-value parameters.

/* Create a table type. */ CREATE TYPE DeptTableType AS TABLE( DeptName VARCHAR(50), DeptCode VARCHAR(2)); GO /* Create a procedure to receive data for the table-value parameter.*/ CREATE PROCEDURE uspDepartmentInsert @TVP DeptTableType READONLY AS SET NOCOUNT ON; INSERT INTO Dept(Name, DeptCode, ModifiedDate) SELECT *, GETDATE() FROM @TVP; GO /* Declare a variable that references the type. */ DECLARE @DeptTVP AS DeptTableType; /* Add data to the table variable. */ INSERT INTO @DeptTVP(DeptName, DeptCode) VALUES('Software Development', 'SD'); INSERT INTO @DeptTVP(DeptName, DeptCode) VALUES('Quality Assurance', 'QA'); /* Pass the table variable data to a stored procedure. */ EXEC uspDepartmentInsert @DeptTVP; GO

Table-value parameters offer more flexibility and better performance than temporary tables to pass a list of parameters. Table-value parameters do not acquire locks for the initial population of data from a client and do not cause a statement to recompile.

This is all great but in any real application the parameters are usually passed in from .NET code. How are table-value parameters supported in .NET? ADO.NET 3.0 allows full support using SqlDbType.Structured type. The parameters which can be passed in from ADO.NET are DataTables, IEnumerable<SqlDataRecord> and DbDataReader. There is also support for ODBC, SSMS, and within Replication.

Adding a DataTable as a parameter to the stored procedure from C# would have something like:

DataTable dt = new DataTable(); dt.Columns.Add.... dt.Rows.Add..... SqlCommand cmd = new SqlCommand("uspDepartmentInsert", sqlConn); cmd.Parameters.AddWithValue("@DeptTVP", dt); cmd.ExecuteNonQuery();

Now that’s cool.

Table-value parameters offer the following benefits:

  • Provide a simple programming model.
  • Enable inclusion of complex business logic in a single routine.
  • Reduce round trips to the server.
  • Include a table structure of different cardinality.
  • Enable strongly typed and set-oriented queries.
  • Enable the client to specify sort order and unique keys.

Table-value parameters have the following restrictions:

  • Statistics are not maintained on columns of table-value parameters.
  • Table-value parameters must be passed as input READONLY parameters to Transact-SQL routines.
  • DML operations such as UPDATE, DELETE, or INSERT cannot be performed on a table-value parameter in the body of a routine. This may actually be a good thing if the data is not meant to change and a read-only copy of the data is all that is needed. However, if the routine needs to update the rows then you’d have to use temporary tables instead in procedures.
  • Table-value parameters cannot be used as the target of a SELECT INTO or INSERT EXEC statement. Table-value parameters can be in the FROM clause of SELECT INTO, or in the INSERT EXEC string or stored procedure.

 

GOTCHA – LINQtoSQL Paging Problem

April 20, 2008 Leave a comment

A quick search for how to display paged records in ASP.NET will yield a ton of links and how to’s ranging from as simple as automatic paging with the grid controls to custom paging and server side paging in SQL Server, but what about paging in LINQ?

Consider a simple LINQ query to find all employees with marital status single using the AdventureWorks db

var query = from e in Employees
where e.MaritalStatus == "S"
select e;

To get to the first page is simple as we just need to add a TOP(n) clause to the generated SQL. In LINQ we do this with the Take(n) function. Assuming we have a page size of 10 our first page would be

query.Take(10);

To get subsequent pages we need to somehow tell SQL to skip a certain number of records and then send us the TOP 10 records. i.e.

Skip Rows = Page Size * (Required Page – 1)

Again in LINQ this is extremely simple

query.Skip(10).Take(10); //page 2

query.Skip(20).Take(10); //page 3

Now the problem we have just created exists in the generated SQL when we apply Skip() / Take(); the resulting SQL queries have an unstable sort order.

For the first page the generated query is

SELECT TOP (10) [t0].[EmployeeID], [t0].[NationalIDNumber], [t0].[ContactID], [t0].[LoginID], [t0].[ManagerID], [t0].[Title], [t0].[BirthDate], [t0].[MaritalStatus], [t0].[Gender], [t0].[HireDate], [t0].[SalariedFlag], [t0].[VacationHours], [t0].[SickLeaveHours], [t0].[CurrentFlag], [t0].[rowguid] AS [Rowguid], [t0].[ModifiedDate]
FROM [HumanResources].[Employee] AS [t0]
WHERE [t0].[MaritalStatus] = @p0
ORDER BY [t0].[ManagerID]
— @p0: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [S]

And for subsequent pages where Skip(n) is specified it generates:

SELECT [t1].[EmployeeID], [t1].[NationalIDNumber], [t1].[ContactID], [t1].[LoginID], [t1].[ManagerID], [t1].[Title], [t1].[BirthDate], [t1].[MaritalStatus], [t1].[Gender], [t1].[HireDate], [t1].[SalariedFlag], [t1].[VacationHours], [t1].[SickLeaveHours], [t1].[CurrentFlag], [t1].[rowguid] AS [Rowguid], [t1].[ModifiedDate]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ManagerID]) AS [ROW_NUMBER], [t0].[EmployeeID], [t0].[NationalIDNumber], [t0].[ContactID], [t0].[LoginID], [t0].[ManagerID], [t0].[Title], [t0].[BirthDate], [t0].[MaritalStatus], [t0].[Gender], [t0].[HireDate], [t0].[SalariedFlag], [t0].[VacationHours], [t0].[SickLeaveHours], [t0].[CurrentFlag], [t0].[rowguid], [t0].[ModifiedDate]
    FROM [HumanResources].[Employee] AS [t0]
    WHERE [t0].[MaritalStatus] = @p0
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p1 + 1 AND @p1 + @p2
ORDER BY [t1].[ROW_NUMBER]
— @p0: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [S]
— @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [10]
— @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [10]

Why different queries?

By definition ‘SELECT’ returns an unordered set, as SQL is set based, and sets have no order. If you want an order in the returned set, you have to define the ordering, with ORDER BY. The ROW_NUMBER() is required to determine which rows are in which page to return.

The problem is that different queries for page 1 vs page n returns the rows in a different sort order. Which means that some of the records displayed on the first page are displayed again on subsequent pages while other records may be missing entirely i.e. don’t show up at all on any page. Adding a Skip(0) to our first page query doesn’t solve things either because LINQ optimizes out the ROW_NUMMBER() from the generated SQL query for Skip(0).

query.Skip(0).Take(10); // doesn’t solve the problem

SELECT TOP (10) [t0].[EmployeeID], [t0].[NationalIDNumber], [t0].[ContactID], [t0].[LoginID], [t0].[ManagerID], [t0].[Title], [t0].[BirthDate], [t0].[MaritalStatus], [t0].[Gender], [t0].[HireDate], [t0].[SalariedFlag], [t0].[VacationHours], [t0].[SickLeaveHours], [t0].[CurrentFlag], [t0].[rowguid] AS [Rowguid], [t0].[ModifiedDate]
FROM [HumanResources].[Employee] AS [t0]
WHERE [t0].[MaritalStatus] = @p0
ORDER BY [t0].[ManagerID]
— @p0: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [S]

From the looks of it the problem seems to be that, for page 1, skip is 0, and the query is processed as if the skip wasn’t applied at all, all the row numbering, and order stabilizing stuff is optimized out. Skip(0) should generate the same row numbering SQL that skip(n) does so that paging systems can rely on the first page of a paged view operates on the same query, with the same row order that will be used for the other pages. Skip(n) imposes a consistent row order. Skip(0) should use the same row order.

But looks can be deceiving. No, you think that the ordering you see in the ROW_NUMBER() column is a valid order, but that’s applied to a set which happens to be not properly ordered! The order in which the rows are in that set with ROW_NUMBER() is determined based on the ordering in the query! The ROW_NUMBER() are added to the final, ordered set. So if in that set there are some rows with undeterminable ordering it can be that the order between queries flips.

So how to solve this? By adding an ORDER BY clause to our original query which forces a sort on the query and stabilizes the row order

var query = from e in Employees
where e.MaritalStatus == "S"
orderby e.NaionalIDNumber
select e;

Without a stated sort order, any standard SQL query is of indeterminate order, and thus in theory unstable. In reality, most databases will (as an implementation detail) honor a clustered index as a default sort (assuming it can pick one from the numerous tables involved in joins), but even this isn’t typically guaranteed. In reality, one should always specify an order condition if you are paging. Not specifying an ORDER BY clause in a query with TOP (n) specified is not recommended (and detected by the SQL Best Practices Analyzer tool) because the set of rows that will be returned will be plan dependent.

Specifying a mere order condition isn’t sufficient. If I were to sort by last name and there were multiple people with same last name I don’t want the rows with the same last name trading places on the first page and between subsequent pages. The order by should be 100% deterministic i.e. be as specific down to the unique column as possible.

 

Technorati Tags: ,,,

Tags: ,

CA1816 – Dispose Pattern

April 13, 2008 Leave a comment

If you have code analysis enabled in your project you might get the following code analysis message on a bunch of your classes even when you have not implemented any finalizers.

Warning CA1816 : Microsoft.Usage : Change ‘xxx.Dispose()’ to call ‘GC.SuppressFinalize(object)’. This will prevent unnecessary finalization of the object once it has been disposed and it has fallen out of scope.

The explanations for this rule at MSDN Dispose methods should call SuppressFinalize for .NET Framework 2.0 says:

"The Dispose method lets users release resources at any time prior to the object becoming available for garbage collection. If the Dispose method is called, it frees the resources of the object, making finalization unnecessary. Dispose should call SuppressFinalize so the garbage collector does not call the finalizer of the object."

The .NET Framework 3.5 version Call GC.SuppressFinalize correctly is a bit different and says:

"The IDisposable.Dispose method lets users release resources at any time before the object becoming available for garbage collection. If the IDisposable.Dispose method is called, it frees resources of the object. This makes finalization unnecessary. IDisposable.Dispose should call GC.SuppressFinalize so the garbage collector does not call the finalizer of the object. To prevent derived types with finalizers from having to re-implement [System][.][IDisposable] and to call it, unsealed types without finalizers should still call GC.SuppressFinalize."

Does it mean that in .NET 3.5 they added some kind of code optimization that realizes I didn’t overload the finalizer object and thus the SuppressFinalizer line basically does nothing, so just add it anyway to be safe for the future is what code analysis is telling me?

Looking more carefully at the explanation of the latter. In particular, this part:    

"To prevent derived types with finalizers from having to re-implement [System][.][IDisposable] and to call it, unsealed types without finalizers should still call GC.SuppressFinalize."

In other words, they are saying that if your class isn’t sealed, then even if you don’t implement your own finalizer, you should still call SuppressFinalize() in your Dispose() method. That way, derived types only have to implement the protected Dispose(bool) method, and can rely on your base class implementation to deal with finalizer issues.

A simpler explanation of the above rules would be "A disposable type needs to implement IDisposable and provide a public Dispose(void) method that ends the object’s lifetime. If the type is not sealed, it should provide a protected Dispose(bool disposing) method where the actual cleanup logic lives. Dispose(void) then calls Dispose(true) followed by GC.SuppressFinalize(this). If your object needs a finalizer, then the finalizer calls Dispose(false). The cleanup logic in Dispose(bool) needs to be written to run correctly when called explicitly from Dispose(void), as well as from a finalizer thread. Dispose(void) and Dispose(bool) should be safely runnable multiple times, with no ill effects ."

//example dispose pattern using System; using System.Data.SqlClient; namespace Samples { public class DatabaseConnector : IDisposable { private SqlConnection _Connection = new SqlConnection(); public void Dispose() { Dispose(true); GC.SuppressFinalize(this); } protected virtual void Dispose(bool disposing) { if (disposing) { if (_Connection != null) { _Connection.Dispose(); _Connection = null; } } } } }

The call to SuppressFinalize() is very inexpensive in any case. If you don’t have a finalizer, then I’d agree that strictly speaking it’s not necessary. However it seems to be a good idea to go ahead and use it and that’s what the warning is telling.

 

Tags: ,

Conditional INNER Joins

April 12, 2008 2 comments

Recently on  a newsgroup I found a question about conditional joins in TSQL. The OP was trying to get rid of dynamic SQL and was looking for a way to change the following example script

DECLARE @SQL VARCHAR(MAX) SET @SQL = 'SELECT * FROM T1' IF LEN(@Param1) > 0 SET @SQL = @SQL + ' INNER JOIN T2 ON T1.Col1 = T2.Col2' IF @Param2 > 0 SET @SQL = @SQL + ' WHERE T1.Col2 = @Param2' EXEC (@SQL)

What the OP was asking for was if it was possible to conditionally join to a table given a certain parameter value. What he was looking for was a way to do something like this

SELECT * FROM T1 IF LEN(@Param1) > 0 BEGIN INNER JOIN T2 ON T1.Col1 = T2.Col2 END IF @Param2 > 0 BEGIN WHERE T1.Col2 = @Param2 END

Now converting the second if statement is easy; just add a case statement to check the value of @Param2 and change the RHS of the equality operator based on value of @Param2

SELECT * FROM T1 IF LEN(@Param1) > 0 BEGIN INNER JOIN T2 ON T1.Col1 = T2.Col2 END WHERE T1.Col2 = CASE @Param2 WHEN 0 THEN T1.Col2 ELSE @Param2 END

Conditionally adding the INNER JOIN is very easy when building up a SQL string but the above would not work because of syntax errors. So how do we do this in pure TSQL? simple; we change our where clause to check for existence of matching rows in the second table T2 and OR with the parameter

SELECT * FROM T1 WHERE ( EXISTS( SELECT * FROM T2 WHERE T1.Col1 = T2.Col2 ) OR LEN(@Param1) = 0 ) AND T1.Col2 = CASE @Param2 WHEN 0 THEN T1.Col2 ELSE @Param2 END

The above generates an optimal query plan because SQL Server knows it needs to access T2 and it will appear in the plan but the LEN(@Param1) = 0 condition prevents it from being accessed when there is no need to.

An alternate approach would be to write separate queries and use IF statements to determine which query needs to be executed

IF LEN(@Param) > 0 BEGIN SELECT * FROM T1 INNER JOIN T2 ON T1.Col1 = T2.Col2 WHERE T1.Col2 = CASE @Param2 WHEN 0 THEN T1.Col2 ELSE @Param2 END END ELSE BEGIN SELECT * FROM T1 WHERE T1.Col2 = CASE @Param2 WHEN 0 THEN T1.Col2 ELSE @Param2 END END

But this can get pretty nasty if the number of parameters / conditions grows. An example would be a search SP which searches against Keyword, Product Category, Price, Inventory Quantity etc. Imagine the number of separate queries and the nightmare to maintain / modify them if needed. The conditional inner join version has the benefit of being a single query which can be easily modified if need be. But do experiment and perhaps a hybrid solution would be the best in your case where a couple of IF statements with separate queries each with some conditional inner joins are used.

 

Technorati Tags: ,

Tags:

Welcome

April 10, 2008 Leave a comment

I’ve finally got around to acquire a blog, thanks to much charging of my workplace colleagues. I’ll be using this as a ground for articles, tips and tricks for better coding / programming. Please stand by for some C#, ADO.NET, ASP.NET and TSQL related posts…