Category Archives: SQL Server

Efficiently serving binary content from SQL Server in ASP.NET MVC – local user group talk

This will be a local user group talk that I’ll be having, in Bucharest on Tuesday 13th of November.

Storing large binary objects (usually image files) in the RDBMS has been a blessing but for some is unconceivable. We will explore different ways to do this, from worse to best and we will take advantage of a new feature introduced by SQL Server 2012.

The location is TeamNet Int’l HQ – Sema Parc, Splaiul Independenţei nr. 319, clădirea RiverView, etaj 8
Except an ID there is nothing else that you need to bring in order to participate to the event.
Further geographical details.

The most popular way to get there is by taking the subway as there is a station right near the building.

The official announcement can be found on RONUA’s site.

See you there!

——–

Later edit : It’s been great! Not too many people but keen to learn new stuff. Here’s two pictures from the talk. Notice a new generation of programmers forming 🙂

CallerMemberName – an easier way to do INotifyPropertyChanged AND MORE

In WPF, when applying the MVVM (an arhitectural pattern) we often need to implement the INotifyPropertyChanged on certain classes (ViewModel classes), which means something like this :


public class PersonViewModel : INotifyPropertyChanged
{
    private string _name;
    public string Name
    {
        get { return _name; }
        set
        {
            if (_name == value) return;
            _name = value;
            NotifyPropertyChanged("Name");
        }
    }

    private void NotifyPropertyChanged(string propertyName)
    {
        var evt = PropertyChanged;
        if (evt != null) evt(this, new PropertyChangedEventArgs(propertyName));
    }

    public event PropertyChangedEventHandler PropertyChanged;
}

In case you’re wondering why I copied the PropertyChanged value to the local variable called “evt” and then tested it for null is that you can have race conditions, in general, triggering events (i.e.: you test the attribute value, it is not null and before you trigger it some other thread sets it to null and bang, NullReferenceException when you trigger it). More details on this CodeProject.

The next step is to pull the NotifyPropertyChanged method and PropertyChanged event into a base class (let’s call it ViewModelBase) and you’ve eliminated redundancy between several ViewModel classes.

The not-so-nice part is having the call to NotifyPropertyChanged stringly-typed. That means that if later you rename (via Visual Studio or ReSharper) the Name property to “FullName” the call will still pass “Name” as the argument.

Some blog posts around the web show how you can use a Func to make it type-safe (refactor safe etc).

More or less they’re doing the same thing :


public abstract class ViewModelBase : INotifyPropertyChanged
{
    public event PropertyChangedEventHandler PropertyChanged;

    protected void NotifyPropertyChanged(Expression<Func<object>> propertyAccessor)
    {
        var evt = PropertyChanged;
        if (evt == null) return;
        var propertyName = propertyAccessor.GetName();
        evt(this, new PropertyChangedEventArgs(propertyName));        
    }
}

public static class Utils
{
    public static string GetName(this LambdaExpression expression)
    {
        MemberExpression memberExpression;
        if (expression.Body is UnaryExpression)
        {
            var unaryExpression = (UnaryExpression)expression.Body;
            memberExpression = (MemberExpression)unaryExpression.Operand;
        }
        else if (expression.Body is MemberExpression)
        {
            memberExpression = (MemberExpression)expression.Body;
        }
        else
        {
            return null;
        }
        return memberExpression.Member.Name;
    }
}

This is definitely nicer, not-redundant and type-safe. It does have the drawback of having some runtime performance penalty associated with the reflection of the expression. You could cache the property name string in a private field but then you’d have to write more code in the ViewModel classes which would… suck. In practice this performance penalty is negligible so you can just ignore this.

Then came .NET 4.5 and among other improvements a new mechanism has been introduced : CallerMemberName.

Historically some folks tried to get programatically the name of the caller method by inspecting the StackTrace (for example using System.Environment.StackTrace) but this is prone to errors since in Release mode the compiler could eliminate some methods by inlining them and you’ll be screwed. Plus the penalty would be higher than reflecting an expression.

The new mechanism in .NET 4.5 is type-safe, has no runtime performance penalty and it’s more elegant. Here’s how you can use it :

public abstract class ViewModelBase : INotifyPropertyChanged
{
    protected void NotifyPropertyChanged([CallerMemberName] string propertyName = null)
    {
        var deleg = PropertyChanged;
        if (deleg != null)
        {
            deleg(this, new PropertyChangedEventArgs(propertyName));
        }
    }

    public event PropertyChangedEventHandler PropertyChanged;
}

public class PersonViewModel : ViewModelBase
{
    private string _name;
    public string Name
    {
        get { return _name; }
        set
        {
            if (_name == value) return;
            _name = value;
            NotifyPropertyChanged();
        }
    }
}

I’ve recently built a very small GuidGen utility (which as the name implies generates GUIDs, copies it in the Windows Clipboard and stores a history of past generated GUIDs). You can browse some of the code and check out the project.

Much nicer, isn’t it?

Funny thing, this new mechanism can be used for non-UI tasks. For example if you have a project that uses and RDBMS and you use stored procedures. Let’s say you have one method in a repository class for each stored procedure, and even more, the method’s name matches the stored procedure’s name :


public VerificationResult VerifyUser(VerificationData verificationData)
{
    if (EmailValidator.IsEmailInvalid(verificationData.EmailAddress)) throw new FormatException("emailAddress");

    var result = CreateNewCommand("VerifyUser").GetEnumResult<VerificationFailReason>(
        CreateEmailAddressParameter(verificationData.EmailAddress),
        CreateUniqueIdentifierParam("@VerificationCode", verificationData.VerificationCode));

    return new VerificationResult(result);
}

Observe on line 5 how the call to CreateNewCommand passes a string which matches the current method’s name. This can also be simplified (and become refactor-safe) using the new CallerMemberName mechanism.

So you can’t really say that CallerMemberName is useful only for UI tasks 🙂

OUTPUT clause in UPDATE statements

Sometimes you need to update data in a table and then update data in another table but based on a filter condition found from the first table. Specifically have you had to do this in the past?


-- ...

UPDATE Users
SET    Verified     = 1
FROM   Logins
WHERE  EmailAddress = @EmailAddress

DECLARE @UserId INT;

SELECT TOP 1
       @UserId = UserId
FROM   Logins
WHERE  EmailAddress = @EmailAddress

UPDATE  Users
SET     State = 2 -- Verified
WHERE   Id = @UserId

-- ...

This is not only inefficient (from an execution plan perspective) but also prone to race conditions and requires more code. The simpler and safer alternative is to use the OUTPUT clause of the UPDATE.

Here’s how :

DECLARE @UserIdTable TABLE ( Id INT );

UPDATE Users
SET    Verified     = 1
OUTPUT UserId
INTO   @UserIdTable
FROM   Logins
WHERE  EmailAddress = @EmailAddress

DECLARE @UserId INT = SELECT TOP 1 Id FROM @UserIdTable;

UPDATE  Users
SET     State = 2 -- Verified
WHERE   Id = @UserId

In the above code sample I take advantage of the new declare and initialize syntax introduced in SQL Server 2008. The OUTPUT clause has been introduced in SQL Server 2005 so nothing here is really news.

Another simplification that I hoped it was possible was to avoid the declaration of the local table variable and just push the OUTPUT into the local variable (@UserId) but it seems you can’t.

I found out about the OUTPUT clause recently from Remus Rusanu’s blog post about implementing queues with tables. These have, usually, high concurrency and any race condition that might occur will occur. OUTPUT is usually the best way to solve it.

New features for database developers in SQL Server 2012 : simpler paging, sequences and FileTables

TL;DR :

  • Paging got simpler and more efficient
  • Sequences have been introduced; better performance for auto-generated IDs and easier to have IDs unique across tables
  • FileTables have been introduced : building upon the FileStream feature now we can have non-transactional access to files stored in the DB as a windows share along with transactional access via T-SQL

Lengthier version :

SQL Server 2012, in my opinion does not come with earth-shaking changes but comes with performance improvements, feature improvements and a some new features.

First of all, Management Studio has the same engine as Visual Studio which means you get a nice WPF experience, better font rendering and CTRL-scroll quick zoom-in/zoom-out.

Let’s say you want to retrieve data from the database in a paged way (that means chunks of data of a requested size or smaller). Typically you would write this in SQL Server 2008 R2 or older :


DECLARE	@Offset		AS INT = 6
DECLARE @PageSize	AS INT = 5

SELECT	Id,
		Name
FROM
(
	SELECT	Id,
			Name,
			ROW_NUMBER()	OVER (ORDER BY Id)	AS	RowNumber
	FROM	Users
) UsersSelection

WHERE	UsersSelection.RowNumber >  @Offset
	AND	UsersSelection.RowNumber <= @Offset + @PageSize

In SQL Server 2012 the T-SQL syntax has been updated introducing keywords that facilitate a simpler and more efficient paging, keywords such as OFFSET, FETCH, NEXT ROWS and ONLY. A script that would retrieve the same data would be :


DECLARE	@Offset		AS INT = 6
DECLARE @PageSize	AS INT = 5

SELECT		Id,
			Name
FROM		Users
ORDER BY	Id
OFFSET		@Offset		ROWS
FETCH NEXT	@PageSize	ROWS ONLY

Observe the simpler, clearer syntax. Also, considering that the subselect has been eliminated (the subselect was required because the ROW_NUMBER column could not be addressed in the same select – for the WHERE clause), also the query cost was improved :

2008 :

2012 :

Read more »

SQL Server 2012 local talk

Tuesday, July 10th, I’ll hold a small presentation on the new features of SQL Server 2012 for the database developer, consisting mainly of the sequences, new paging semantics and filetables.

I’ll post the code and slides soon after the presentation. I decided to drop (oh the irony) the slides.

Anyone close to Bucharest is more than welcome to come! Details can be found here.

The opening of the evening will be made by Alex Peta, presenting a cool notification system built upon JS and ASP.NET MVC 3.