An all too familiar challenge of today’s applications is to support the mobile user.  A mobile user is not always guaranteed to have a connection to the home office.  They need an application that can support both offline and online connection states.  An application that is occasionally connected is often referred to as an “Occasionally Connected Application” (OCA).  OCAs will often work offline and when brought online will execute synchronization processes.  In this blog, I’ll briefly introduce some nice features included with SQL Server 2008 Change Tracking.

SQL Server has had replication features for quite some time now.  I’m by no means an expert when it comes to SQL Server Transactional replication, but personally I’ve found it difficult to setup, troubleshoot, customize, and unable to easily communicate across N-Tier application environments.  In the past, the next option from replication was to create our own SQL synchronization engines utilizing triggers, timestamp columns, tombstone tables, etc to track database changes on the client and server and then synchronize up the differences.  There are some problems with this solution.  First glaring issue to me is the use of triggers.  Triggers cause transactions to take longer to commit and cause blocking issues.  So basically performance and storage issues result from this solution prior to SQL 2008 Change Tracking.  SQL 2008 now provides a new feature which is available in all versions: SQL Change Tracking. 

SQL Server Change Tracking Advantages/Features:

1)  Easy to Setup.  It does not require timestamp columns, tombstone tables, triggers, etc.  You can simply script or use SQL Management Studio to turn on SQL Change Tracking for a database and then what tables you want to track changes on.

2)  Better Performance.  Changes are tracked at commit time rather than when DML operations occur.  What this basically means is transactions run quicker and this also helps with blocking issues.

3)  Minimal Disk Space Costs.  Change Tracking stores changes in SQL system tables and the disk space cost is minimal.

4)  Integrates with the .NET Sync Framework.  If you are using the .NET Sync Framework to develop your OCA, then .NET/Visual Studio has some nice features that are at the developer’s disposal.

5)  Synchronize with other DB Platforms.  SQL Change Tracking runs independently from other databases.  So if your server is running an Oracle DB and you want to run SQL Compact on your client with Change Tracking enabled, it will support that just fine.  Changes on the client are tracked independently of the server and obviously vice versa.

6)  Packaged Functions.  SQL Server comes with packaged functions that are used to query the SQL Change Tracking system tables to acquire incremental changes.

7)  Auto Clean Up.  When SQL Change Tracking is enabled for a database, you can specify when change history will be purged automatically for you.

8)  Column or Entire Row.  You can enable change tracking to record that an entire row/record had something changed in it or you can even track down to what column was changed to limit the amount of data changes returned when querying for incremental changes.

Example 1 - Turn on SQL Change Tracking for a Database:

ALTER DATABASE [AdventureWorks2008] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 365 DAYS, AUTO_CLEANUP = ON);
GO

Example 2 – Turn on SQL Change Tracking for a Table:

ALTER TABLE HumanResources.[Department] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);
GO

Example 3 – Return Incremental Table Inserts:

DECLARE @sync_last_received_anchor BIGINT, @sync_new_received_anchor BIGINT;

SELECT @sync_last_received_anchor = CHANGE_TRACKING_CURRENT_VERSION();

INSERT INTO [HumanResources].[Department] (Name, GroupName, ModifiedDate) VALUES ('Test1', 'My Group', GETDATE())
INSERT INTO [HumanResources].[Department] (Name, GroupName, ModifiedDate) VALUES ('Test2', 'My Group', GETDATE())
INSERT INTO [HumanResources].[Department] (Name, GroupName, ModifiedDate) VALUES ('Test3', 'My Group', GETDATE())

SELECT @sync_new_received_anchor = CHANGE_TRACKING_CURRENT_VERSION();

--return inserts
SELECT dept.*
FROM [HumanResources].[Department] AS dept 
	INNER JOIN CHANGETABLE(CHANGES [HumanResources].[Department], @sync_last_received_anchor) CT ON CT.[DepartmentID] = dept.[DepartmentID]
WHERE (CT.SYS_CHANGE_OPERATION = 'I' AND CT.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor)

If you’ve ever tried to run a collection of SQL batches via the .NET DbCommand.ExecuteNonQuery method, you probably received an error similar to the following: “Incorrect Syntax near ‘GO’ …”.  DbCommand.ExecuteNonQuery is intended to execute a single SQL batch statement against a database.  The “GO” separator is a command that is used/understood by SQL Server Query Analyzer and Query Editor utilities to separate out SQL batches when ran.

There has been some workarounds that I’ve seen that involve regular expressions and string manipulation/splitting to break out the script with “GO” separators into a collection of SQL statements and run them each individually.  This can work, but you will run into situations where commented areas will contain the “GO” statement and other problems can/will arise.  Such situations would cause the workaround to fail.

The proper way to run scripts with the “GO” separator is to employ SQL Server Management Objects (SMOs).  Microsoft’s SQL Server 2008 Feature Pack contains links to download/install the SMO assemblies (.NET Framework object model) .  Developers can use SMOs to perform SQL Server management routines through their .NET application code.

Steps to Use SMOs:

1) Download and Install SMOs - Download the SQL Server Management Objects found on this page.  Run the installation package.  If you are running SQL Server 2005, you may need to search for the 2005 version as the link I gave is for 2008.

2) Add references - After the SMOs are installed,  we’ll need to add references to them in our project.  The SMO assemblies should appear under your .NET tab when adding references to your project.  If you can’t  find them there, the assemblies were installed to “C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies” for me.  To run scripts with “GO” statement, we’ll need references to the following DLLs:
  a) Microsoft.SqlServer.ConnectionInfo.dll
  b) Microsoft.SqlServer.Management.Sdk.Sfc.dll
  c)  Microsoft.SqlServer.Smo.dll

3) Import the required namespaces and execute your SQL script with “GO” statements like below:

Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

Class Window1

    Public Sub ExecuteSqlStatements(ByVal connectionString As String, ByVal sqlStatements As String)
        If Not String.IsNullOrEmpty(sqlStatements) Then
            Using mySqlConnection As SqlConnection = New SqlConnection(connectionString)
                Dim mySqlServer As Server = New Server(New ServerConnection(mySqlConnection))
                mySqlServer.ConnectionContext.ExecuteNonQuery(sqlStatements)
            End Using
        End If
    End Sub

End Class

SqlBatchExecNonQuery_Soln.zip (965.09 kb)

In an earlier post, I wrote about how to develop a Silverlight custom content control.  Since then I’ve received inquiries as to how to add events to it.  More specifically, how can we add interactivity to the control and have the control raise events which can be handled by the consumer of our control.

Step 1: Add Template UI Elements to Receive User Interaction

In the previous post, I described how to create a generic.xaml file to house our templated control UI (style).  In this example, I am going to create a close HyperlinkButton and raise a close event.  Here’s our style which is defined in the generic.xaml file.  Take not of our new hlbClose HyperlinkButton (line 34).

<ResourceDictionary 
	xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
	xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
	xmlns:local="clr-namespace:ContentControlExample.Controls">
     
	<!-- CustomContentControl -->
	<Style TargetType="local:CustomContentControl">
		<Setter Property="Background" Value="Transparent" />
		<Setter Property="Foreground" Value="Black" />
		<Setter Property="BorderBrush" Value="Transparent" />
		<Setter Property="BorderThickness" Value="0" />
		<Setter Property="HorizontalAlignment" Value="Stretch" />
		<Setter Property="VerticalAlignment" Value="Stretch" />
		<Setter Property="HorizontalContentAlignment" Value="Left" />
		<Setter Property="VerticalContentAlignment" Value="Top" />
		<Setter Property="Template">
		<Setter.Value>
			<ControlTemplate TargetType="local:CustomContentControl">
				<Border Background="White" BorderBrush="#87AFDA" BorderThickness="1">
					<Grid>
						<Grid.RowDefinitions>
							<RowDefinition Height="Auto" />
							<RowDefinition Height="*" />
						</Grid.RowDefinitions>
                                
						<Border Grid.Column="0" Grid.Row="0" Background="#D4E6FC" BorderThickness="0,0,0,1" BorderBrush="#87AFDA">
							<Grid>
								<Grid.ColumnDefinitions>
									<ColumnDefinition />
									<ColumnDefinition Width="20" />
								</Grid.ColumnDefinitions>
                                    
								<ContentControl Grid.Column="0" Content="{TemplateBinding Header}" HorizontalAlignment="Stretch" VerticalAlignment="Stretch" HorizontalContentAlignment="Stretch" VerticalContentAlignment="Stretch" Foreground="#224499" FontWeight="Bold" FontFamily="Arial" FontSize="12" Margin="3,3,3,3" />
								<HyperlinkButton x:Name="hlbClose" Grid.Column="1" Content="[X]" />
							</Grid>
						</Border>
						<ContentControl Grid.Column="0" Grid.Row="1" Content="{TemplateBinding Content}" ContentTemplate="{TemplateBinding ContentTemplate}" Cursor="{TemplateBinding Cursor}" HorizontalAlignment="{TemplateBinding HorizontalAlignment}" HorizontalContentAlignment="{TemplateBinding HorizontalContentAlignment}" FontFamily="Arial" FontSize="{TemplateBinding FontSize}" FontStretch="{TemplateBinding FontStretch}" Foreground="{TemplateBinding Foreground}" Margin="{TemplateBinding Padding}" VerticalAlignment="{TemplateBinding VerticalAlignment}" VerticalContentAlignment="{TemplateBinding VerticalContentAlignment}" />
					</Grid>
				</Border>
			</ControlTemplate>
		</Setter.Value>
		</Setter>
	</Style>
</ResourceDictionary>

Step 2: Get a Handle to Template UI Elements

Once we have our control UI looking the way we want it, the next step is to get a handle to our HyperlinkButton and add a handler for its click event.  When the user clicks the HyperlinkButton, we want to raise our control’s Close event.  The trick is to override the OnApplyTemplate of our control and get our handle to the HyperlinkButton using the GetTemplateChild method.  The GetTemplateChild method accepts the id/name of the control we are looking for.  It traverses the visual tree and returns a DependencyObject if found.  Below I show how we can do this:

Public Class CustomContentControl
    Inherits ContentControl

    Private _hlbClose As HyperlinkButton

    Public Shared ReadOnly HeaderProperty As DependencyProperty = DependencyProperty.Register("Header", GetType(UIElement), GetType(CustomContentControl), Nothing)
    Public Event Close(ByVal sender As CustomContentControl)

    Public Property Header() As UIElement
        Get
            Return DirectCast(Me.GetValue(CustomContentControl.HeaderProperty), UIElement)
        End Get
        Set(ByVal value As UIElement)
            Me.SetValue(CustomContentControl.HeaderProperty, value)
        End Set
    End Property

    Public Sub New()
        MyBase.New()
        Me.DefaultStyleKey = GetType(CustomContentControl)
    End Sub

    Public Overrides Sub OnApplyTemplate()
        MyBase.OnApplyTemplate()
        _hlbClose = DirectCast(GetTemplateChild("hlbClose"), HyperlinkButton)
        AddHandler _hlbClose.Click, AddressOf hlbClose_Click
    End Sub

    Private Sub hlbClose_Click(ByVal sender As Object, ByVal e As RoutedEventArgs)
        RaiseEvent Close(Me)
    End Sub
End Class

Step 3: Raise Control Events

Now that we have acquired a handle to our HyperlinkButton and also have added a handler for its click event, all we have to do now is raise our control’s Close event when the HyperlinkButton is clicked.  The control consumers can handle the close event if they wish.

    Private Sub hlbClose_Click(ByVal sender As Object, ByVal e As RoutedEventArgs)
        RaiseEvent Close(Me)
    End Sub

ContentControlExample_Soln.zip (627.32 kb)

As a programmer, you may have situations where you need a handle to a control to set its property programmatically.  Pretty simple right?  Let’s add some complexity.  What if you are not guaranteed to know exactly where the control resides.  Is it in our Grid at child index 0 or is it in a StackPanel within the Grid?  What if we want to set the background of all Canvas objects on our page to Transparent?

The Silverlight API provides the VisualTreeHelper class to assist with the traversal of the visual object tree.  Silverlight has a logical and visual object tree.  The visual tree is a subset of the logical object tree and is only populated by objects that have rendering implications.  In a general sense, a collection class would not be included in the visual tree however the Grid you are using as your LayoutRoot of your page would be.  The VisualTreeHelper class has 3 methods to assist with the visual object tree: GetChild(), GetChildrenCount(), GetParent().  Their names are self-descriptive and what I’d like to do is create a few recursive helper methods/extensions to these methods that are a bit more powerful.

Helper Method #1: GetParentObject

This method returns the first parent of a given type and/or name of an object’s parent hierarchy.  For instance, let’s say you know that our StackPanel resides somewhere in the bowels of a Grid named LayoutRoot.  To get a handle to LayoutRoot we could do the following:

Dim g As Grid = GetParentObject(Of Grid)(myStackPanel, "LayoutRoot")

Helper Method #2: GetChildObject

This method returns the first child of a given type and/or name of an object’s child hierarchy.  So let’s take the inverse of the above.  We have a StackPanel named myStackPanel that resides somewhere beneath our Grid LayoutRoot.  The get the handle to myStackPanel we could do the following:

Dim sp As StackPanel = GetChildObject(Of StackPanel)(Me.LayoutRoot, "myStackPanel")

Helper Method #3: GetChildObjects

This method returns a list collection of all children of a given type and/or name of an object’s child hierarchy.  So let’s say we want all rectangles which reside somewhere on our page.  Again, our page has a Grid named LayoutRoot.  We could get a list collection of all rectangles on the page by making the following call:

Dim rectangles As List(Of Rectangle) = GetChildObjects(Of Rectangle)(Me.LayoutRoot)

Helper Methods

Below is the actual methods’ code.  I’m also attaching my zipped test solution.

Module Common
    ''' <summary>
    ''' Navigates up the object's parent hierarchy and returns the first parent match of the specified type and/or object name.
    ''' </summary>
    ''' <typeparam name="T"></typeparam>
    ''' <param name="obj"></param>
    ''' <param name="name"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetParentObject(Of T As FrameworkElement)(ByVal obj As DependencyObject, Optional ByVal name As String = "") As T
        Dim parent As DependencyObject = VisualTreeHelper.GetParent(obj)

        While parent IsNot Nothing
            If TypeOf parent Is T AndAlso (CType(parent, T).Name = name Or String.IsNullOrEmpty(name)) Then
                Return CType(parent, T)
            End If

            parent = VisualTreeHelper.GetParent(parent)
        End While

        Return Nothing
    End Function

    ''' <summary>
    ''' Recursively searches an object's child hierarchy and returns the first child match of the specified type and/or object name.
    ''' </summary>
    ''' <typeparam name="T"></typeparam>
    ''' <param name="obj"></param>
    ''' <param name="name"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetChildObject(Of T As FrameworkElement)(ByVal obj As DependencyObject, Optional ByVal name As String = "") As T
        Dim child As DependencyObject = Nothing, grandChild As T = Nothing

        For i As Integer = 0 To VisualTreeHelper.GetChildrenCount(obj) - 1
            child = VisualTreeHelper.GetChild(obj, i)

            If TypeOf child Is T AndAlso (CType(child, T).Name = name Or String.IsNullOrEmpty(name)) Then
                Return CType(child, T)
            Else
                grandChild = GetChildObject(Of T)(child, name)
                If grandChild IsNot Nothing Then Return grandChild
            End If
        Next

        Return Nothing
    End Function

    ''' <summary>
    ''' Recursively searches an object's child hierarchy and returns the all children that are of the specified type and/or object name.
    ''' </summary>
    ''' <typeparam name="T"></typeparam>
    ''' <param name="obj"></param>
    ''' <param name="name"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetChildObjects(Of T As FrameworkElement)(ByVal obj As DependencyObject, Optional ByVal name As String = "") As List(Of T)
        Dim child As DependencyObject = Nothing, childList As List(Of T) = New List(Of T)

        For i As Integer = 0 To VisualTreeHelper.GetChildrenCount(obj) - 1
            child = VisualTreeHelper.GetChild(obj, i)

            If TypeOf child Is T AndAlso (CType(child, T).Name = name Or String.IsNullOrEmpty(name)) Then
                childList.Add(CType(child, T))
            End If

            childList.AddRange(GetChildObjects(Of T)(child))
        Next

        Return childList
    End Function
End Module

VisualTreeHelper_Soln.zip (589.38 kb)

Microsoft SQL 2005 introduced a new construct called CTEs (common table expressions).  CTEs provide a way to break down complex queries into simpler chunks and offer more readable queries.  UDFs, derived tables, and temp tables are all constructs that can be employed to help break up complex queries, however they often muddy up your SQL script.  They also exist beyond the context of the one SQL statement you may have desired to use it for.  CTEs also provide the ability for recursion without the need for recursive stored procedures.

Therefore, at a high level CTEs are used for:

1) Improving readability of your SQL scripts.

2)  Simplifying complex queries.

3)  Recursion.

A Simple CTE Example:

WITH OrderTotals (OrderID, OrderTotal) AS (
	SELECT OrderDetails.OrderID, SUM(((OrderDetails.UnitPrice * OrderDetails.Quantity) - OrderDetails.Discount)) AS OrderTotal 
	FROM dbo.[Order Details] AS OrderDetails WITH (NOLOCK)
	GROUP BY OrderDetails.OrderID
)
SELECT * FROM dbo.Orders WITH (NOLOCK)
	INNER JOIN OrderTotals ON OrderTotals.OrderID = Orders.OrderID
WHERE OrderTotals.OrderTotal > 100;

In this first simple example, I used the Northwind database to find all orders which exceeded $100.  The first step is to use the keyword WITH and then name my CTE, define my columns, then enclose what SQL makes up the CTE within parenthesis.  I then proceed to use the CTE in the final query.  Notice how the final query is much simpler to read without the GROUP BY, SUM, etc logic in the CTE.

Two CTEs Example:

WITH OrderTotals (OrderID, OrderTotal) AS (
	SELECT OrderDetails.OrderID, SUM(((OrderDetails.UnitPrice * OrderDetails.Quantity) - OrderDetails.Discount)) AS OrderTotal 
	FROM dbo.[Order Details] AS OrderDetails WITH (NOLOCK)
	GROUP BY OrderDetails.OrderID
), OrdersMoreThan100 (OrderID, OrderTotal) AS (
	SELECT OrderTotals.* FROM OrderTotals WHERE OrderTotals.OrderTotal > 100
)
SELECT * FROM dbo.Orders WITH (NOLOCK)
	INNER JOIN OrdersMoreThan100 ON OrdersMoreThan100.OrderID = Orders.OrderID;

In this example, we get a little more complex with our CTEs.  Here we can see that you can comma delimit as many CTEs as you want.  I also reference the first CTE within my second CTE and simplify our final SQL statement even further.

CTE Gotchas/Notes:

1) Always terminate SQL with a “;” before you declare a CTE.

2) You can use CTEs with SELECT, UPDATE, INSERT, DELETE queries.

3) CTEs can only be used by the final SQL statement (the statement following the CTE definitions).  Subsequent SQL queries will not be able to use your CTE definitions.