Justin Cooney

Web Development Tips and Examples

recent posts

about

I am a Senior Applications Programmer / Analyst with years of experience developing enterprise solutions using the Microsoft technology stack including C#, VB.NET, ASP.NET, AJAX, IIS and SQL Server. I specialize in Web application development with a focus on building secure systems, integrating applications, and designing robust database structures.

  • ASP.NET – Handling Null Return Values from the Database using IsDBNull

    ASP.NET
    ASP.NET

    If you are retrieving or adding values from and to your database then you will know the difficulties that can be caused by NULL values. The notorious InvalidCastException is one example of receiving a NULL value from your database into an ASP.NET object that doesn’t know what to do with database NULL values.

    Here’s typical example of an InvalidCastException error caused by setting a String variable to a value that is returned as a NULL from your database query:

    System.InvalidCastException: Conversion from type 'DBNull' to type 'String' is not valid. at Microsoft.VisualBasic.CompilerServices.Conversions.ToString(Object Value) at ASP.myPageName_aspx.Page_Load(Object sender, EventArgs e) in D:\Inetpub\wwwroot\MySite\myPageName.aspx:line 1xx

    If you find this article useful, have a look at some of the other ASP.NET tips and tricks articles I’ve published. (more…)

  • T-SQL – Get a Historical Record of SQL Statements Hitting a Table

    SQL Server
    SQL Server

    Today I’ll review two useful queries that you can use to get information from SQL Server about what SQL statements are hitting one or more of your database tables.

    If you are interested in further T-SQL tips and tricks, please have a look through related articles I have written. (more…)

  • SQL Server Upserts – Using MERGE to INSERT or UPDATE Table Rows

    Microsof SQL Server
    Microsof SQL Server

    The idea of an Upsert was added in the SQL 2003 standard for databases and is implemented using a MERGE statement in both Oracle and SQL Server.

    So You Might Ask: What is an Upsert?

    An Upsert does pretty much does what it sounds like: it runs an update on a row where a condition exists and it runs an insert if the condition does not exist.

    What are the Benefits of Using a MERGE Upsert

    If you look at the difference between a conditional INSERT…UPDATE code snippet and a MERGE statement you will see that the MERGE statement does have its benefits.

    1. For starters, the MERGE statement is less verbose and encapsulates the entire logic of the Insert/Update into a single statement. This is beneficial for readability and maintainability.
    2. Also the MERGE statement is more performant since SQL Server needs to deal with one query rather than a several separate queries and a temporary variable.
    3. The MERGE statement runs as a single atomic statement and so you don’t need to manually encapsulate it in a transaction like you would with the conditional version.

    Upserting in SQL Server

    Since I like to code for SQL Server I thought I’d try out using the MERGE statement as an Upsert. In doing so it’s important to note that Microsoft added support for the MERGE statement as of SQL Server 2008, so the MERGE statement will not work on older versions of SQL Server.

    As an aside, the MERGE statement can be used for more than Upserting. But in this article I will specifically focus in this article about how to use the MERGE statement as an UPSERT to add or update information in a single table.

    If you find this article useful and would like to read more about SQL Server and T-SQL, feel free to check out other SQL Tips and Tricks I have written. (more…)