SQL Server 2005 to 2012 Differences for Developers

by JBrooks 14. November 2015 12:24

We are upgrading from SQL Server 2005 to 2012. There are a lot of features for developers that have been added over those 7 years.  Here are some you might find useful.

1. You can assign a value to a variable when you declare it.

DECLARE @Status varchar(50) = 'Success', @Today Date = Getdate()

2. You can insert multiple rows without needing to repeat the column list

INSERT INTO MyTable ( ID, LastName ) VALUES ( 100, 'Smith' ), ( 200, 'Jones' ), ( 300, 'Watson' )

(Up to 1,000 sets.)

3. Among the new data type there are DATE and TIME types.

No more having to use a DateTime when all that you really want is a date.

DECLARE @Today Date = Getdate()

4. IIF() function

SELECT IIF(1 > 10, 'TRUE', 'FALSE' )


You can use the IIF() with an IN

DECLARE @LastName Varchar(50) = 'Smith' SELECT IIF(@LastName IN ('Smith','Jones'), 'Likes SQL', 'Likes ORACLE' )


5. CHOOSE() function

CHOOSE ( index, val_1, val_2 [, val_n ] ) SELECT CHOOSE(3,'Spring','Summer','Autumn','Winter')

6. CONCAT() function

CONCAT( string1, string2 [,stringN])

- All arguments are converted to a string first

- All nulls are converted to an empty string

DECLARE @FirstName VARCHAR(20) = 'Bob', @LastName VARCHAR(20), @Age int = 30 SELECT CONCAT(@FirstName, ' ', @LastName, ' is ', @Age)

7. You have the “C” like short cuts +=, -=, *=, /= and %=

DEClARE @x INT = 2, @y INT = 2 SET @x += @y SELECT @x as x, @y as y


8. Paging is now much easier with OFFSET and FETCH NEXT

DECLARE @PageNumber int = 4, @PageSize int = 40 SELECT WNumber, AuditTime, AuditTypeCode, ActivityCode FROM tb_AuditLog ORDER BY AuditTime DESC OFFSET (@PageNumber1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;

9. FORMAT() function

SELECT FORMAT( GETDATE(), 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result', FORMAT(123456789,'###-##-####') AS 'Custom Number Result';

10. TRY_PARSE()and TRY_CONVERT() functions

IF TRY_PARSE('xxx' as DATETIME) IS NOT NULL SELECT 'TRY_PARSE: Conversion Successful' ELSE SELECT 'TRY_PARSE: Conversion Unsuccessful'

11. You can now create your own types in the database (just like VARCHAR and INT are types.)  You can even define a table type.


(Could have many more columns.)

You can then pass that type as a parameter to a stored procedure.


You can also pass the table in as a parameter from C#.

DataTable myIds = new DataTable(); myIds.Columns.Add("Id", typeof(Int32)); myIds.Rows.Add(100); myIds.Rows.Add(110); myIds.Rows.Add(2200); myIds.Rows.Add(40); dbCommand.Parameters.Add(new SqlParameter("@MyIds", myIds) { SqlDbType = SqlDbType.Structured }); DataSet ds = (DataSet) Db.ExecuteDataSet(dbCommand);


When you re-throw it keeps the correct line number of the error.

This can also be used with transactions.


13. New virtual Windows FileTable commands in T-SQL lets you manage a folder like a table of documents, but still have external control over the contents

UPDATE C:\Docs\*.* SET ReadOnly = 1 WHERE Author = ‘Bob’ AND Created <20100101';

14. Reporting Services no longer requires IIS, it makes direct calls to HTTP.SYS.

15. Now have Oracle like sequences

CREATE SEQUENCE MySequence START WITH 1 INCREMENT BY 1; INSERT @Person (ID, FullName) VALUES (NEXT VALUE FOR MySequence, 'Steve Luna'), (NEXT VALUE FOR MySequence, 'John Peter'), (NEXT VALUE FOR MySequence, 'Jim Brooks');


Development | Silverlight

Silverlight Page Titles

by jbrooks 9. September 2013 17:21

In the past I would use part of the page for the page title while the MainPage had a lot of empty space in the center.  I wanted to move the title from the page to the MainPage as shown below.



To do this I first made a property on MainPage like this:

public partial class MainPage : UserControl
    public string Title
        get { return tbTitle.Text; }
        set { tbTitle.Text = value; }

Then I added this property to the App class:

public partial class App : Application
    public static string Title
            MainPage mp = App.GetMainPage();
            mp.Title = value;

A supporting method in this same App class is GetMainPage() where it gets the MainPage. I use this for other things too.

public static MainPage GetMainPage()
    if (Application.Current.RootVisual.GetType().Name == "BusyIndicator")
        return (MainPage)((ChangeAlerts.Controls.BusyIndicator)
        return (MainPage)Application.Current.RootVisual;

Then on each individual page I have something like this:

protected override void OnNavigatedTo(NavigationEventArgs e)
    App.Title = "Manage Accounts";


Silverlight | Development

Binding a Silverlight DataGrid’s ComboBox To a Model

by JBrooks 31. July 2013 11:49

I have a DataGrid that is bound to my Employee entity and I needed to be able to select his building from a list of buildings in that same model. There is a path to the Employee’s Building through buildingId, but no path to the full list of buildings so it could the source for the ComboBox.

The simple solution was to have the Buildings in a ViewModel and then reference that as a resource in my XAML.  The ViewModel retrieves the data in it’s constructor and it has a property EntitySet<Building> Buildings;

        <vm:BuildingsModel x:Key="buildingsModel"  />

Then on the CheckBox inside the <DataTemplate> I have its ItemSource use the resource.

<ComboBox Name="cb" ItemsSource="{Binding Source={StaticResource buildingsModel}, Path=Buildings}" 
    SelectedValue="{Binding Path=buildingId, Mode=TwoWay}">
            <StackPanel Orientation="Horizontal">
                <TextBlock Text="{Binding name, Mode=OneWay}" MinWidth="140" />
                <TextBlock Text="  " />
                <TextBlock Text="{Binding address, Mode=OneWay}" />
                <TextBlock Text=", " />

                <TextBlock Text="{Binding city, Mode=OneWay}" />
                <TextBlock Text=", " />
                <TextBlock Text="{Binding state, Mode=OneWay}" />


Development | Silverlight | XAML

Silverlight and Window Authentication

by JBrooks 4. June 2013 08:38

I deployed my Silverlight application with Windows Authentication.  I then set IIS to only use Windows Authentication. When I navigated to the web site I would get the error:

“Load operation failed for query ‘GetUser’. The remote server returned an error: NotFound.”


The fix that worked for me was to edit the file MyProject.Web/Services/AuthenticationService.cs.  I just changed this one method.

public class AuthenticationService : AuthenticationBase<User> {

    protected override User GetAuthenticatedUser(System.Security.Principal.IPrincipal principal)
        User newUser = new User();
        newUser.Name = principal.Identity.Name;

        return newUser;

This makes sense if you think about it, but the base class AuthenticationBase should have detected the authentication used and changed based on that.


Development | Silverlight

Show Child Count in a DataGrid

by jbrooks 11. March 2013 17:49

I have an application with the following as part of the data model:


So an Account can have many Users and a user can belong to many Accounts. I wanted to show the Accounts in a DataGrid along with the number of Users that were linked to that account like show in the 3rd column here:


The way I did this is to use a converter to count the number of entities in each Account.xrefAccountsUser set.  The DataGrid is bound to Accounts and the the XAML for the 3rd column is:

<sdk:DataGridTextColumn  Header="Users" 
Binding="{Binding xrefAccountsUsers, Converter={StaticResource setToCountConverter}}"

The converter is where we count up the number of entities in the related entity set (xrefAccountsUsers in this case.)  I could not find a type that I could cast the value to that had a Count property, so I  ended up casting it to IEnumerable and then loop thru the set and I do a count manually.  There should never be very many accounts so performance isn’t an issue.

public class SetToCountConverter : IValueConverter
    public object Convert(object value,
                                Type targetType,
                                object parameter,
                                System.Globalization.CultureInfo culture)
        int cnt = 0;
        IEnumerable set = value as IEnumerable;
        if (set == null)
            return "0";
        foreach (var item in set)
        return cnt.ToString();
    public object ConvertBack(object value,
                                Type targetType,
                                object parameter,
                                System.Globalization.CultureInfo culture)
        throw new NotImplementedException();


Silverlight | Development | XAML

Disable CheckBoxes in Silverlight’s DataGrid

by jbrooks 11. March 2013 12:41

I have an application where the administrator can edit a user’s roles.


I wanted to reuse this same grid to show the user what roles they have, but I didn’t want them to be able to change the data. There is a IsReadOnly property on each of the columns, but this still allows the user to change the checkboxes.

If I set the IsEnabled property on the datagrid to false, then the user can’t change the checkboxes but the datagrid looks washed out and hard to read.


So this is too washed out to go with.  I realized that another way to disable CheckBoxes is from the style:

    <Style TargetType="CheckBox">
        <Setter Property="IsThreeState" Value="False" />
        <Setter Property="HorizontalAlignment" Value="Center" />
        <Setter Property="VerticalAlignment" Value="Center" />

I wanted to see if there was a way to programmatically add a setter to this and there was.

// find the CheckBox style resource.
var resource = dgAccounts.Resources.FirstOrDefault(x => x.Key == typeof(CheckBox));
if (resource.Key != null)
    Style s = resource.Value as Style;
    (s.Setters as SetterBaseCollection).Add(new
    Setter(CheckBox.IsEnabledProperty, value));

In the code above, “value” can be true or false.  This gives me a little better presentation:


But, hey, this is Silverlight – why am I using those dinky check boxes anyway.  The better solution is to create a user control that acts like a checkbox and you can style it as big as you want.


And then the code to set the disable style just needs to change the type it is looking for on the 1 line:

var resource = dgAccounts.Resources.FirstOrDefault(x => x.Key == typeof(ucCheckBox));

Now, the disabled version is a lot more readable.



Development | Silverlight | XAML

Silverlight's DatePicker / MessageBox Bug

by JBrooks 26. December 2012 10:49

Silverlight has an annoying bug that has been around since at least 2008. If you have a MessageBox popup in a DatePicker’s SelectedDateChanged event, then the calendar will stay open and the SelectedDateChanged will fire for each time the mouse pointer hovers over a new date.  See HERE for someone asking for help on this and their sample code.

It happens because the DatePicker still flags the left mouse button as being down – the up button event was eaten by the message box.

I have a page where the user works on data for a given day, and if they change the date the page will be refreshed with the new day’s data.

My work flow was that if a user changed data for a day and then changed the date, I would prompt them with “Save Changes?” – Yes, No, Cancel.  If they selected Cancel I would just set the DatePicker’s date back. 

No matter what they selected the DatePicker’s calendar would remain down and the SelectedDataChanged would fire for each date that was hovered over.

I didn’t like any of the answers to this problem that I found.  So I changed the workflow so that the prompt to save changes wasn’t necessary anymore.  So this is the new workflow.

1. New page, you can change the date, refresh the data, or start editing the data.


2. The data was changed. Now changing the date is disabled until you make a choice between Save and Cancel. I like how this visibly tells the user that the data is dirty.


This is done by capturing the KeyUp event for the parent control that holds all of the editing controls. In my example it is a ContentControl but it could be a grid, etc.  Note that the control’s in the images above are outside of my ContentControl for the data.

private void ccData_KeyUp(object sender, System.Windows.Input.KeyEventArgs e)
            switch (e.Key)
                case Key.Left:
                case Key.Right:
                case Key.Down:
                case Key.Up:
                case Key.Tab:

                    if (Status != PageStatus.Changed)
                        Status = PageStatus.Changed;

When the Status property gets set the showing, hiding and disabling of controls takes place depending on the new value of PageStatus.

If the user clicks “Save” then the data is saved, the page is refreshed and they are back at step 1 with the date selection enabled. If they click “Cancel”, then I go to step 3.

3. They clicked “Cancel”.  Now they are free to change the date which pulls up another day’s data and throws away their changes (step 1).   Or they can continue editing, which puts them back to step 2.  Or they can click Refresh which will pull the old data from the database and the page will be back to step 1.


I like doing it this way so I don’t have to mess with the SelectedDateChanged event and I like the visual cues.


Development | Silverlight

Generic Boolean to Text Converter

by JBrooks 20. July 2012 11:52
I’ve seen this done a few different ways, but I think this is the best way because you can reuse it. Below is the converter, notice the 2 public properties.
public class BoolToTextConverter : IValueConverter
    public string TrueText { get; set; }
    public string FalseText { get; set; }

    public object Convert(object value, Type targetType, object parameter, 
                                                   System.Globalization.CultureInfo culture)
        return ((bool)value) ? TrueText : FalseText;

    public object ConvertBack(object value, Type targetType, object parameter,
                                                  System.Globalization.CultureInfo culture)
        throw new NotImplementedException();

Then in your XAML resource section you would set the properties:
<localHelpers:BoolToTextConverter x:Key="boolToTextConverter">
        Not Sent
So this is setting the 2 public properties. The final part is to bind this to the TextBox, in this example I’m binding to a boolean property named “sent”. The result is that the text will be “Sent” if it is true and “Not Sent” if it is false.
<TextBlock Text="{Binding Path=sent, Converter={StaticResource boolToTextConverter}}"
            VerticalAlignment="Center" Margin="4"  
            TextAlignment="Center" />


Silverlight | Development | XAML

One Click CheckBox in a Silverlight DataGrid

by JBrooks 16. December 2011 09:54

I found this code on the internet to create a CheckBox in a DataGrid that wouldn’t require more than one click to change.  But it didn’t work for me.

            <CheckBox IsThreeState="False" IsChecked="{Binding Path=IsActive, Mode=TwoWay}" 
                      HorizontalAlignment="Center" VerticalAlignment="Center" />
The reason it didn’t work is because I also had this as part of my DataGrid
<sdk:DataGrid CurrentCellChanged="dgTotals_CurrentCellChanged" …


That method had a dgTotals.BeginEdit(); in it to allow the user to begin editing the DataGrid cells without having to first click on them.  The simple solution was to just skip that for my CheckBox column.

if(dgTotals.CurrentColumn != null && dgTotals.CurrentColumn.DisplayIndex != 1)


Development | Silverlight

Troubleshooting the Silverlight to RIA Services Connection

by JBrooks 6. December 2011 11:41

The first thing I do when troubleshooting a Silverlight startup error (like where it just has the startup progress bar sitting at 100% forever) is to make sure I can reach the RIA Service and it doesn’t return an error.

This example shows how to decode the URL back to the service:


This is just the path delimited with dashes to the service as seen in your project and also with all of the periods changed to dashes and then just add “.svc” at the end.


You should see a page for the RIA Service if you put this URL in a browser.

When calling a method it is ClientBin first, followed by the name space, followed by “binary” and then your method name.  Here are 2 examples:



I just needed to put this somewhere so I can reference it in the future.


Silverlight | Development