Akash Kava

SQL Assembly brings power of CLR (Common language runtime) inside SQL Database Stored Procedure which can overcome T-SQL programming pains of Intellisense, Debugging, Logging and Versioning.

Traditional Problems with T-SQL and Stored Procedures

I never liked programming in T-SQL, for various reasons listed below,

1. No Intellisense, of course it has been introduced SQL 2008 onwards, but still its far behind what we get in Visual Studio.
2. Source Code Control, although scripts can be added to source code control, usually its unawareness and unwillingness of Database Administrator / Designer to use it correctly and mess things up.
3. Less human readable, T-SQL is very difficult to understand if its not documented well.
4. Source code is open, it can be modified, so in short Database Administrator has complete access to alter business logic.
5. Very less reusable, unless you do copy paste, no way to standardize the development.
6. No support for advanced visual tools to design T-SQL Stored Procedures.
7. Deployment is difficult, minor alternation can cause problems.
8. Most of business logic involves, loops, if-else, switch statements, and it takes 10 times more code to execute such logic in T-SQL then any other programming language in world.
9. You need multiple language talent, one with either VB.NET/C# and T-SQL.
10. Difficult to Log information.

Introduction of SQL Assembly

Since SQL 2005, introduction of SQL Assembly has been of great importance, however not much of people still use it, because of unawareness. But SQL Assembly can be modularized and reused, and it exists as simple CLR assembly inside database.

And following are benefits of SQL Assembly, besides few listed in MSDN here.

1. LINQ in CLR Stored Procedure.
2. Better Source Code control over Visual C#/Visual Basic.NET project through Team System.
3. Intellisense.
4. Closed source, its safe.
5. Highly secure as Database Administrator can not alter business logic.
6. C# and VB.NET along with LINQ are more human readable, understanding and reusing code can be very easy.
7. Support for advanced documentation in comments.
8. Visual Studio Model designer can be used.
9. Reuse your Business Logic for other database vendor products, e.g. Oracle, MySQL (Explained below).
10. You can do advanced logging with log-4-net or custom logging.

Small Example of How to use LINQ in SQL Assembly

1. Create a simple Visual C# Class Library Project.
2. Add reference of “System.Data” Assembly.
3. Create new ADO.NET Entity Model, and you can create it from Database or empty model, the way you want to develop your project.
4. Add few entities, I am going to demonstrate entity “Cart” which is a saved shopping cart for the Customer.

Create a Stored Procedure Class

After creating model, lets create a class called “ShoppingCart” and add a static method as shown below.

public class ShoppingCart
{

    /// <summary>
    /// Creates new Cart if none exists and returns the Cart ID
    /// </summary>
    /// <param name="CustomerID"></param>
    /// <returns></returns>
    [SqlProcedure]
    public static SqlInt64 GetSavedCartID(SqlInt64 CustomerID)
    {

        // Set context connection=true
        ShoppingCartModelContainer context =
            new ShoppingCartModelContainer("context connection=true");

        // Query for exisitng cart
        Cart c = context.Carts.FirstOrDefault(
                        t => t.CustomerID == CustomerID.Value);

        if (c == null) { 

            // create new Cart
            c = Cart.CreateCart(0, CustomerID.Value, DateTime.Now);
            context.AddToCarts(c);
            context.SaveChanges();
        }

        // return the Cart ID
        return new SqlInt64(c.CartdID);

    }

}

This code demonstrates how easy it is to write SQL Assembly, now here is the ultimate benefit of everything.

Ultimate Benefit

You can use your Business Logic in multiple vendor databases, like for example for Oracle and MySQL, you can reuse your SQL Assembly and just change your connection string. You can reuse your business logic in your front end code with simple wrapper.

And with SQL Server, you can reuse this assembly inside SQL Database.