Archive | May, 2016

Call stored procedures using DbContext

Staying Warm and Dry

Sometimes I find myself needing to create a complicated bit of logic in the SQL layer (say, for statistical reports.) Later, perhaps someone will ask to see these statistics exposed on a screen through the UI. Suddenly I need this same logic further up the stack. While I’m a fan of using LINQ, too much code duplication of complex logic can result in a maintenance headache. This is even more true when the duplication crosses languages, as when going from SQL to C#. Staying DRY is a good thing.

While I’d prefer to confine most of my coding logic to C# and keep my databases as simple as possible, sometimes this is not practical. In this article I will demonstrate how to call stored procedures using DbContext.

Putting Things In Context

My organization likes to Continue Reading →

Generating a number sequence in SQL

There are many reasons you might need a number sequence in SQL. I’m going to show you how to efficiently generate such a sequence in a set-based manner, all in memory, and without using any loops. In future articles I’ll get into some uses for such a sequence, but for now, let’s open up SSMS and just roll with it.

A Loopy Solution

If you are a typical imperative programmer, you’re probably used to thinking of number sequences in terms of loops. Say that we wanted to generate a sequence of 100,000 numbers. A naive first attempt in T-SQL using a loop might look like Continue Reading →