Nov 8 2011

Intro to SQL # For Twitterizer

In this post we’re talking a little bit about an add-on tool for SQL called SQL #.  The idea is to give programmers useful functions in T-SQL and Enterprise Manager.  It is a library of CLR (Common Language Runtime) functions so it works on SQL 2005+.  Getting started is pretty easy and is on the home page of the website: http://www.sqlsharp.com/

The simplest way to start is to create an empty database called “Sharp”, the run the script they provide and open a new query window.

You can review a lot of the features here http://www.sqlsharp.com/features/, but, what really piqued my interest was RegEx, String, and Twitter features.  I have wasted a lot of coding time in the past re-validating data passed from the middle tier.  Why doesn’t Microsoft put the .Net Framework into Enterprise Manager?  They gave us SMO (SQL Management Objects) so we could mess with SQL in C# (probably to the dismay of all DBA’s that pay attention), so why not the other way?  Solomon Rutzky tired of that question and built SQL#.

Let’s start with a few examples.  I’ll be using the AdventureWorks database on a SQL 2008 R2 (or as we like to call it: SQL 2010…)  If you don’t have that database, you can get it here.  These examples are taken/modified from another great blog.

/* SQL# Calculating business days */
SELECT  wo.DueDate,
        wo.EndDate,
        [WorkingDays]=Sharp.SQL#.Date_BusinessDays(wo.DueDate, wo.EndDate, 3),
        wo.WorkOrderID
FROM    Production.WorkOrder AS wo
WHERE   wo.EndDate > wo.DueDate ;

/* SQL# Calculating the distance between two points */
SELECT  [Meters]=a1.SpatialLocation.STDistance(a2.SpatialLocation),
      [Miles]=Sharp.SQL#.Math_Convert(a1.SpatialLocation.STDistance(a2.SpatialLocation),'meter','mile')
FROM    Person.Address AS a1
        JOIN Person.Address AS a2
        ON a2.AddressID = 2
           AND a1.AddressID = 1;

/* SQL# Delete files older than n-days via T-SQL  */
SELECT SQL#.File_Delete(files.Location + '\' + files.Name)
FROM SQL#.File_GetDirectoryListing(@StartingDirectory, @Recursive, @DirectoryNamePattern, @FileNamePattern) files
WHERE files.LastWriteTime < (GETDATE() - 3)

Let’s move onto selecting your posts from Twitter.  First make sure you’ve followed these instructions: http://www.SQLsharp.com/download/SQLsharp_TwitterSetup.pdf

/* SQL# Return a table of tweets  */
DECLARE		@ConsumerKey		NVARCHAR(100),
		@ConsumerSecret		NVARCHAR(100),
		@AccessToken		NVARCHAR(100),
		@AccessTokenSecret	NVARCHAR(100)
SELECT		@ConsumerKey = 'x',
		@ConsumerSecret = 'y',
		@AccessToken = '7-z',
		@AccessTokenSecret = 'z'

SELECT		StatusText,Created,ScreenName,UserName
FROM		SQL#.Twitter_GetFriendsTimeline(@ConsumerKey, @ConsumerSecret,
                                        @AccessToken, @AccessTokenSecret, NULL)

This is the same functionality I demo’d in C# in my first post.

In conclusion, this gives me an alternative to LINQ for business logic and it allows me to keep it in the stored procedures.  Which is especially nice when I have to write any reports where I don’t get to pass into a C# layer.

-Mike

Leave a comment