Monthly Archives

March 2012

Finding Closest Location by Latitude and Longitude

By | .NET, C# | One Comment

Mobile applications and web sites provide the perfect platform for proximity-based services so users can find the nearest X to their current location.  I will demonstrate some code that will allow you to find the closest location in a list when compared to a current latitude and longitude. While latitude and longitude provide precise coordinates, we can’t use a Euclidean distance formula because latitude and longitude are not on a cartesian plane, but on a sphere (technically, an ellipsoid, but we don’t need to be so precise here).  There are several pages that describe the differences between using the Haversine…

Read More

Using Sitefinity 5 Claims Authentication

By | Sitefinity, Telerik, Web | One Comment

Using Claims Authentication with Sitefinity 5, you no longer “hit the wall” when integrating your site within a larger security context. Prior to version 5.x, Sitefinity used Forms Authentication for verification of user credentials to allow access to Sitefinity applications. Forms Authentication retrieved the user’s name/password and checked it against data stored in the the database for that particular Sitefinity application. That works well enough for a single web site but comes up short in scenarios required by many organizations: “Single Sign On” (SSO) where you have several Sitefinity sites that should have only one login for the user. SSO allows you to show a…

Read More

Telerik Report Designer

By | Telerik | No Comments

Users in the Telerik Reporting Forums have been clamoring for a Stand-Alone Report Designer for a long time now. I’ve been working with Telerik Reporting for the past few years, which means I have spent my share of time researching issues in the forum. I have seen several posts by users asking for a Report Designer that doesn’t require Visual Studio. With the 2012 Q1 release of Telerik Reporting it is finally here. The Telerik Report Designer allows a non-developer/report designer to have a separate tool to start working with the Telerik reports. This will free your developers up to…

Read More

T-SQL: A Simple Way to Build a String from a Column

By | Uncategorized | No Comments

First, some test data: declare @emp table ( name varchar(50) primary key ) insert @emp values ( ‘Lino’ ), ( ‘John’ ), ( ‘Noel’ ) Now, here’s an example of the old, long way to do things. I’ll use a custom while loop instead of cursors, because the cursor-based approach takes even more code than this: select * into #while_loop from @emp declare @name varchar(50), @str varchar(max) while exists ( select * from #while_loop ) begin select top 1 @name = name from #while_loop delete #while_loop where name = @name set @str = case when @str is null then @name…

Read More

T-SQL: How To Obtain SCOPE_IDENTITY() From an Object With an INSTEAD OF INSERT Trigger

By | Uncategorized | No Comments

If you’ve ever worked with an object with an IDENTIY column and an INSTEAD OF INSERT trigger defined, you are familiar with this issue; since the INSERT occurs within the scope of the trigger instead of the scope of the insert statement that caused the trigger to execute, SCOPE_IDENTITY() does not return the value of the IDENTITY column. Here is an example of a common situation: a base table with an IDENTITY column and and extension table with a 1:1 relationship to the base, and the storage details abstracted by a view that encapsulates the join logic: begin tran go…

Read More

T-SQL: Drop All Objects in a SQL Server Database

By | Uncategorized | No Comments

Sometimes you want to clear out a database, but you can’t just drop the entire database and create a new one. Recently, a colleague needed to do just that, but couldn’t because the database was hosted elsewhere. Here is a simple script that works to drop all objects in any database I’ve tested it against so far. If your database contains object types that mine don’t, you might need to add to this script using the same patterns established here. declare @stmt nvarchar(max)   — procedures select top 1 @stmt = isnull( @stmt + ‘ ‘, ” ) + ‘drop…

Read More

How To Return A C# Anonymous Type From A Method

By | Uncategorized | No Comments

I came across an interesting technique earlier this week. Did you know that you can return an anonymous type from a method and still have strongly typed access to its members? I got this technique from Tomas Petricek’s blog. Here is a quick example of how it works; for details of why it works please see the original article. private static object ReturnAnon() { return new { FirstName = “Captain”, MiddleName = “Jack”, LastName = “Sparrow” }; } private static T Cast<T>(object o, T type) { return (T)o; } static void Main(string[] args) { var anon = Cast(ReturnAnon(), new {…

Read More

How To Replace The Default TFS Diff/Merge Tool

By | Uncategorized | No Comments

I don’t like the diff/merge tool that comes with TFS. At all. It isn’t just that it’s feature-poor; it also isn’t good at the one thing it’s supposed to do. I’ve had a quite a few merge operations go horribly wrong, resulting in data loss and syntactically incorrect merge results. Today I found two excellent articles that described how to replace the default TFS diff/merge with an alternative one: TFS: Using Alternative Diff/Merge Tools diff/merge configuration in Team Foundation – common Command and Argument values There are a lot of alternatives out there. I chose to use KDiff3 because it…

Read More

How To Cast A Generic Parameter To A Concrete Subclass

By | Uncategorized | No Comments

It was brought to my attention today that given the following setup: public class BaseClass { public override string ToString() { return “BaseClass”; } } public class SubClass : BaseClass { public override string ToString() { return “SubClass”; } public string SubClassProp { get; set; } } That the following statement will not compile: public static void HandleClass(T cls) where T : BaseClass { Console.WriteLine(cls.ToString()); if (cls is SubClass) { SubClass sc = (SubClass)cls; sc.SubClassProp = “Foo”; Console.WriteLine(sc.SubClassProp); } } However, a quick little bit of experimentation showed that you can cast a generic parameter to a concrete class using…

Read More

T-SQL: Rewrite row-based cursor logic with set-based logic

By | Uncategorized | No Comments

While I was maintaining one of our larger projects the other day, I came across something like this in an update trigger. I’ve anonymized the table and field names, but otherwise the logic is unaltered: declare @id int, @val_old varchar(10), @val_new varchar(10) declare ChangeCursor cursor for select new.id, new.val, old.val from deleted old join inserted new on new.id = old.id open ChangeCursor fetch next from ChangeCursor into @id, @val_new, @val_old while @@fetch_status = 0 begin if ( @val_new <> @val_old ) begin raiserror( ‘The value may not be changed for id %d; old value: %s, new value: %s’, 16, 1,…

Read More

SQL Schema Comparison Tools

By | Uncategorized | No Comments

Lately I’ve needed to compare the schemas of two or more databases and highlight the differences. Based on internet research, I settled on two contenders: AdeptSQL Diff and Red Gate SQL Compare. Both are reasonably fast. AdeptSQL’s UI looks very 16-bit and has limited reporting options. Red Gate’s UI is very attractive and offers more reporting options. However, Red Gate’s product currently has a bug that considers quoted and unquoted trigger identifiers to be different, even if the identifier itself is the same. This resulted in a large number of false differences, forcing me to go with AdeptSQL for now….

Read More

Opera 9.5 is out!

By | Uncategorized | No Comments

OK, so I’m a little late to the party. I’ve been using it for a few days now, and I’m very pleased. Browsing is even faster, and there are new features like the Quick Find address bar that performs full-text searches on every page in your browsing history. Give it a try at www.opera.com/download.

Read More

MSSQL System Stored Procedures Everyone Should Know

By | Uncategorized | One Comment

Introduction Why should everyone know these stored procedures? Let me explain by telling a story. Up until recently, I’ve done a lot of my SQL development work right inside Visual Studio. The Server Explorer did a good job of displaying the information I wanted and made it easy to create and modify tables and stored procedures. This was up until VS 2008, when the Server Explorer started to get a lot more finicky. The biggest problems seemed to happen if the database connection dropped for some reason (VPN disconnect, connection terminated by server, laptop went to sleep). In prior versions,…

Read More

Dealing with #1.INF (Positive Infinity) in SQL Server

By | Uncategorized | No Comments

A customer recently had a problem where their app couldn’t access certain rows in a database. Attempting to select these rows yielded a SQL Server error: An error occurred while executing batch. Error message is: Arithmetic Overflow. Further investigation revealed that the error was caused by attempting to select the value in a column of type float. Casting the column to a varchar yielded the value #1.INF, which some research revealed was a value representing positive infinity. Since I was tasked with the job of cleansing these infinity values from the database and also ensuring that all logic accessing these…

Read More

How To Write Secure Dynamic SQL

By | Uncategorized | No Comments

Writing secure, injection-proof SQL is an important skill that every developer should practice. In this article, I am going to assume that you, the reader, are already aware of what SQL injection is and how to prevent it in static queries by using parameters. Today’s topic is how to apply the same techniques to dynamic SQL. Technique 1: Parameters The same technique of parameterizing static queries applies just as much to dynamic ones. Here is a contrived example of the wrong way to write a dynamic query: create procedure Customer_DynamicSelect @where_col sysname, @where_val varchar(max) as set nocount on declare @stmt…

Read More

Get More Out of Your Foreign Keys

By | Uncategorized | No Comments

Foreign keys are a must in any database to ensure that all references to values in other tables are valid in that they actually exist, but they can be used for more than mere existence checks; they can also be used to enforce business rules. Let’s trot out the age-old example of Customers and Orders, and add another table that holds customer Addresses. Pardon me while I whip out a Visio diagram: So we see here that we have a simplified Customer table, an Order table holding each Customer’s many Orders, an Address table holding each Customer’s multiple addresses, and…

Read More

T-SQL: How to Invert a Bit

By | Uncategorized | No Comments

This is embarassing. I’ve been coding in T-SQL for years, and when I’ve had to invert a bit, I’ve been writing stuff like this: case @bit when 0 then cast ( 1 as bit ) when 1 then cast ( 0 as bit ) else null end because the NOT operator doesn’t work on bits. Today, I thought to myself: “Bitwise NOT. Didn’t I see that in Books Online?” And yep, there it is. Instead of typing the above to invert a bit, all you need is the bitwise NOT operator “~”. ~@bit Forehead, meet palm.

Read More

Turn row-based operations into set-based ones with the OUTPUT clause

By | Uncategorized | No Comments

In many database applications, it is common to generate a set of rows based on another set of rows. For example, in a sales and billing system, one might take a customer’s order and save the ordered products as rows in an order detail table, and then later generate an invoice based on that order, listing all the ordered products as invoice details. Consider a scenario such as this, where you want to relate the invoice details to their originating order details, but perhaps a 1:1 relationship is not guaranteed, so you can’t simply include the order detail PK in…

Read More

T-SQL: The difference between @@identity, scope_identity(), and ident_current()

By | Uncategorized | No Comments

First of all, when discussing the three methods, you need to understand two concepts: session and scope. Session means the current connection that’s executing the command. Scope means the immediate context of a command. Every stored procedure call executes in its own scope, and nested calls execute in a nested scope within the calling procedure’s scope. Likewise, a SQL command executed from an application or SSMS executes in its own scope, and if that command fires any triggers, each trigger executes within its own nested scope. Now that these two concepts are defined, here are the differences between the three…

Read More

T-SQL: What to do with columns in a SELECT clause that are neither GROUPED BY nor aggregated

By | Uncategorized | No Comments

When writing queries that group and aggregate, it is not uncommon for there to be many columns in the SELECT clause that are neither in the GROUP BY clause nor being aggregated. For example, in a customer orders database, suppose you want a query that returns customer names, telephone numbers, and total order amount. A correct, but somewhat naive solution would go like this: select c.Name, c.Phone, sum( o.Amount ) as Amount_sum from Customers c join Orders o on o.CustomerID = c.CustomerID group by c.Name, c.Phone This will return the desired results, but in my experience, will not deliver optimal…

Read More

Opera’s answer to FireBug

By | Uncategorized | No Comments

I’m a big fan of the Opera browser for its speed and excellent out-of-the-box functionality. One of the few things I have been willing to concede that it was lacking was a good interactive debugger like FireBug, until now. Opera Dragonfly has arrived!

Read More

T-SQL: An alternative to loops

By | Uncategorized | No Comments

Here is an interesting article on a way to turn many looping row-based operations into set-based ones: http://www.sqlservercentral.com/articles/TSQL/62867/ I think the idea is a clever one, and I love the “Row By Agonizing Row” (RBAR) acronym!

Read More