SQL Database 2012 AdventureWorks: Creating a SQL CLR Stored Procedure using C#

In this exercise, we will create a SQL CLR stored procedure using C# that helps insert currency data into a table. To begin, open Visual Studio and select 'File' > 'New' > 'Project'. From the dialog box, choose 'SQL Server' under the 'Installed' tab, then select 'SQL Server Database Project' (Figure 1-4).

'Figure 1-4: Empty Project listed in Solution Explorer'

Once you've created an empty project, save it as shown in Figure 1-3.

'Figure 1-3: Showing SQL Server Database Project template'

Next, we will create a new Windows Forms Application project. This will help us add a SQL CLR C# stored procedure item template to our SQL Server database project.

When to Use T-SQL vs. SQL CLR

Both T-SQL and SQL CLR have their own strengths and weaknesses. T-SQL is better suited for set-based operations, whereas SQL CLR is more suitable for recursive, mathematical, or string manipulation types of operations. However, when it comes to complex logic, SQL CLR with C# provides more control over the functionality.

T-SQL vs. SQL CLR Performance

One notable difference between T-SQL and SQL CLR is their performance. T-SQL code executes in the same stack frame as other T-SQL code, whereas SQL CLR code requires its own stack frame, resulting in larger memory allocation but better concurrent performance.

Creating a SQL CLR Stored Procedure using C#

To create a SQL CLR stored procedure using C#, follow these steps:

1. In your SQL Server database project, right-click on the project and select 'Add' > 'New Item'.

2. From the dialog box, choose 'SQL CLR C# Stored Procedure' under the 'SQL Server' tab.

3. Name the item and click 'Add'.

Your new stored procedure will be added to your project. You can now write C# code for the stored procedure.

In conclusion, both T-SQL and SQL CLR have their own strengths and weaknesses. When choosing between the two, consider the type of operation you need to perform. If you need set-based operations, T-SQL is a better choice. However, if you require more complex logic or control over functionality, SQL CLR with C# may be a better option.

Sql database 2012 adventureworks