Using CLR With SQL Server 2012

• Introduction to SQL CLR
• Choosing Between T-SQL and SQL CLR
• Enabling SQL CLR Integration
• Creating a SQL CLR Stored Procedure
• Deploying a SQL CLR Stored Procedure into SQL Server
• Executing the SQL CLR Stored Procedure

Introduction to SQL CLR

• The following types of database objects can be created with SQL CLR Integration:
• Stored Procedures
• User defined aggregates
• Triggers
• User defined types

Choosing Between T-SQL and SQL CLR

• T-SQL is best used to perform declarative, set-based operations (select, insert, update and delete).
• T-SQL also has a procedural capability, in other words can perform procedural operations like While and so on but when it comes to feature rich or more complex logic T-SQL is not the best choice. In such a scenario, SQL CLR with C# allows the programmer to have a better control on functionality.
• T-SQL is interpreted whereas SQL CLR is compiled. Hence; interpreted code is slower than compiled procedural code.
• Before SQL CLR code can be executed, it requires the CLR to be loaded by the SQL Server. Whereas T-SQL does not incur any such overhead.
• When any T-SQL code executes it shares the same stack frame in memory; whereas, every SQL CLR code requires its own stack frame hence result in larger memory allocation but better concurrent performance.
• T-SQL is composed of libraries full of data-centric functions and hence better suited to set-based operations. Whereas SQL CLR is better suited to recursive, mathematical, string manipulation types of operations.

Enabling SQL CLR Integration

1. Open SQL Server 2012 Management Studio; connect using Windows or SQL Authentication based on your setup type.
2. Once connected, click on the New Query button and it will open a query window. Enter the following text in the query window and notice the value of the "config_value" column is:
   
   
   Figure 1-1: Showing default behavior (disabled) of SQL CLR
   
   
3. Next, we need to enable the SQL CLR, to do so modify the code to appear as shown in Figure 1-2 and it will enable the SQL CLR integration.
   
   
   Figure 1-2: Showing SQL CLR enabled
   
   
4. Now your SQL Server is ready to execute the database objects that are built using the C# programming language unlike T-SQL.

Creating SQL CLR Stored Procedure

1. In this exercise, you'll create a SQL Stored Procedure by adding a SQL CLR C# Stored Procedure item template into the SQL Server database project. The SQL CLR C# Stored Procedure we are creating will help you insert currency data into an AdventureWorks.Sales.Currency table.
   
   Create a new Windows Forms Application project. When Solution Explorer opens, save the solution as shown in Figure 1-3.
   
   
   Figure 1-3: Showing SQL Server Database Project template
   
2. This will load an empty project, in other words without any .cs class file in it as shown in Figure 1-4.
   
   
   Figure 1-4: Empty Project listed in Solution Explorer
   
3. Right-click the project, choose "Add New Item", and in the "Add New Item" dialog from the SQL CLR C# tab, select "SQL CLR Stored Procedure" and name it as shown in SQLCLRStoredProcedure in Figure 1-5. Click "Add".
   
   
   Figure 1-5: Adding a SQL CLR C# Stored Procedure as New Item to the project
   
4. Your Visual Studio environment will now look as shown in Figure 1-6.
   
   
   Figure 1-6: Showing Visual Studio after adding the SQL CLR C# Stored Procedure
   
5. Replace the code inside the StoredProcedure class with the code listed under Listing 1-1.
   
   Listing 1-1. SQLCLRStoredProcedure.cs
   
   1. [Microsoft.SqlServer.Server.SqlProcedure()]
   2. public static void InsertCurrency_CS(SqlString currencyCode, SqlString currencyName)
   3. {
   4. SqlConnection conn = null;
   5. try
   6. {
   7. conn = new SqlConnection(@"server = .\sql2012;integrated security = true;database = AdventureWorks");
   8. SqlCommand cmdInsertCurrency = new SqlCommand();
   9. cmdInsertCurrency.Connection = conn;
   10. SqlParameter parmCurrencyCode = new SqlParameter("@CCode",
   11. SqlDbType.NVarChar, 3);
   12. SqlParameter parmCurrencyName = new SqlParameter("@Name", SqlDbType.NVarChar, 50);
   13. parmCurrencyCode.Value = currencyCode;
   14. parmCurrencyName.Value = currencyName;
   15. cmdInsertCurrency.Parameters.Add(parmCurrencyCode);
   16. cmdInsertCurrency.Parameters.Add(parmCurrencyName);
   17. cmdInsertCurrency.CommandText =
   18. "INSERT Sales.Currency (CurrencyCode, CurrencyName, ModifiedCurrencyDate)" +
   19. " VALUES(@CCode, @Name, GetDate())";
   20. conn.Open();
   21. cmdInsertCurrency.ExecuteNonQuery();
   22. }
   23. catch (SqlException ex)
   24. {
   25. SqlContext.Pipe.Send("An error occured" + ex.Message +
   26. ex.StackTrace);
   27. }
   28. finally
   29. {
   30. conn.Close();
   31. }
   32. }
6. Save the project and build the solution. After successful build it will produce a .dll under the project location's \bin\debug folder.