To enhance or complement the functionality of a Microsoft SQL Server database, you can use an external language. One way you can do this is to use a language that supports the .NET Framework. This is done through the common language runtime (CLR) and referred to as CLR integration.

Integration with the CLR allows you to create database objects or operations using a high-level language such as C#, C++, or Visual Basic. These languages support object-oriented programming with the ability to create classes, populate them with properties and/or take action with methods, control the access levels on members of a class, inherit from a class, line-inherit from classes (parents, grand-parents, etc), and control inheritance (polymorphism and abstraction), etc. Taking advantage of the .NET Framework from these classes, your code can also involve collections, dynamic libraries, etc, which are features not available in Transact-SQL.

Once the code is ready, create a library, called an assembly, then import and use it in Microsoft SQL Server.

Code in the common language runtime (CLR) is written in a language that supports the .NET Framework. This makes it possible to write code in one language and use it in another language. The language is also in charge of memory management. For this, it is referred to as managed code.

In order to use the CLR in a Microsoft SQL Server database, you must enable it. By default, it is not (enabled).

The instructions in this lesson assume that you have Microsoft Visual Studio (or an Express edition) installed on your computer. If you don't, you may have to compile the projects from the Command Prompt.

1. Start Microsoft SQL Server and connect to the server
2. On the Standard toolbar, click the New Query button
3. In the Query window, type the following:
    
   

   sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'clr enabled', 1; GO RECONFIGURE; GO

4. Press F5 to execute
5. To create a new database and a table, type the following:
    

   -- =================================================== -- Database: CeilInn5 -- =================================================== IF EXISTS(SELECT name FROM sys.databases WHERE name = N'CeilInn5') DROP DATABASE CeilInn5; GO CREATE DATABASE CeilInn5; GO USE CeilInn5; GO IF OBJECT_ID(N'Employees', N'U') IS NOT NULL DROP TABLE Employees GO CREATE TABLE Employees ( EmployeeNumber int NOT NULL, DateHired date NULL, FirstName nvarchar(20), LastName nvarchar(20) NOT NULL, HourlySalary money ); GO INSERT INTO Employees VALUES(62480, N'19981025', N'James', N'Haans', 28.02), (35844, N'20060622', N'Gertrude', N'Monay', 14.36), (24904, N'20011016', N'Philomène', N'Guillon', 18.05), (48049, N'20090810', N'Eddie', N'Monsoon', 26.22), (25805, N'20040310', N'Peter', N'Mukoko', 22.48), (58405, N'19950616', N'Chritian', N'Allen', 16.45); GO IF OBJECT_ID('Rooms', 'U') IS NOT NULL DROP TABLE Rooms GO CREATE TABLE Rooms ( RoomNumber nvarchar(10), RoomType nvarchar(20) default N'Bedroom', BedType nvarchar(40) default N'Queen', Rate money default 85.95, Available bit default 1 ); GO USE CeilInn5; GO INSERT INTO Rooms(RoomNumber) VALUES(N'104'); GO INSERT INTO Rooms(RoomNumber, BedType, Rate, Available) VALUES(N'105', N'King', 95.50, 1), (N'106', N'King', 95.50, 1); GO INSERT INTO Rooms(RoomNumber, Available) VALUES(N'107', 1); GO INSERT INTO Rooms(RoomNumber, BedType, Rate) VALUES(N'108', N'King', 95.50); GO INSERT INTO Rooms(RoomNumber, Available) VALUES(N'109', 1); GO INSERT INTO Rooms(RoomNumber, RoomType, Rate, Available) VALUES(N'108', N'Conference Roome', 450, 1), (N'110', N'Conference Roome', 650, 1); GO INSERT INTO Rooms(RoomNumber, BedType, Rate, Available) VALUES(N'201', N'Queen', 90, 1), (N'202', N'Queen', 90, 1), (N'203', N'Queen', 90, 1), (N'205', N'King', 98.85, 1), (N'207', N'King', 98.75, 1); GO

6. To execute, press F5

As stated already, to provide CLR integration, you will write your code in a language that supports the .NET Framework. This means that the language will use classes available from that library.

The .NET Framework primarily supports databases through an object called a data set. This object is represented by a class named DataSet. In reality, a data set is a group of lists exactly like the tables of a Microsoft SQL Server database. Normally, a data set by itself means nothing. It is the objects (tables) it contains that define it.

To support tables, the .NET Framework provides a class named DataTable. As you know already, a table is made of columns and records. The columns of a table are supported in the .NET Framework by a class named DataColumn. A record of a table is represented in the .NET Framework by a class named DataRow. All these classes are defined in a namespace named System.Data. The System.Data namespaces is created in the System.Data.dll assembly. This means you will need this library in your project.

To provide support for Microsoft SQL Server databases, the .NET Framework provides the System.Data.Sql and the System.Data.SqlClient namespaces. Both namespaces are defined in the System.Data.dll library.

The System.Data.Sql contains classes that specically support Microsoft SQL Server.

The System.Data.SqlClient namespace contains classes used to establish a connection to a server, create commands to a database, execute the desired commands, or read data from  database objects (tables, views, etc). To use a Microsoft SQL Server database, you will usually need to reference the System.Data.SqlClient namespace.

One of the primary requirements of a database system is that of data types. The .NET Framework data types that support, or are equivalent to, Microsoft SQL Server values are defined in the System.Data.SqlTypes namespace. This namespace contains various classes and structures that each provides an equivalent to a Microsoft SQL Server data type. Here are the data types we started using in Lesson 5 and here are their .NET Framework equivalents:

The System.Data.SqlTypes namespace contains many other structures you can use to implement functionality available from object-oriented languages but not directly available in Microsoft SQL Server:

• SqlBytes: This class can be used to create a reference to a stream of values
• SqlChars: This class can be used for an array of characters
• SqlTruncateException: This exception is thrown if you use an incompatible value that must be truncated
• SqlNullValueException: This exception is thrown if the value of a variable is set to null (C#) or Nothing (Visual Basic)

The System.Data.SqlTypes namespace is defined in the System.Data.dll assembly. 

To provide additional functionality of a Microsoft SQL Server database, the .NET Framework provides the System.Data.Sql namespace. This namespace is also part of the System.Data.dll library.

Equipped with all these data types, classes, and your knowledge, you can implement the functionality appropriate to integrate your code with a Microsoft SQL Server database. To assist you with the necessary classes for this integration, the .NET Framework provides the Microsoft.SqlServer.Server namespace that is equipped with various interfaces, classes, and enumerations. To use one of the classes of the Microsoft.SqlServer.Server namespace, you can reference it using an attribute.

Although the Transact-SQL library provides many functions, you may need functionality that none of those provides. You can create your own and fully efficient function in C#, C++/CLI, or Visual Basic. A CLR scalar-valued function (SVF) or scalar-valued user-defined function is a function you create using CLR and that is made to be accessed from Microsoft SQL Server.

The C++, Visual Basic, Pascal, Transact-SQL and many other languages support the concept of functions. Some other languages like C# or Java don't share that concept. They use only the word "method". Here, we will use the word function in the first sense. Because we will write our code in C#, we have to create classes and use methods.

To assist you with creating a function, the .NET Framework provides a class named SqlFunction. The SqlFunction class is defined in the Microsoft.SqlServer.Server namespace. To use this class, create a static method marked with the SqlFunctionAtribute attribute.

If you want, you can create a function that will access one or more records on the database. To do this, use the DataAccess property, which is based on an enumeration named DataAccessKind.

To prepare the CLR object, you can create a project using the language of your choice or a programming environment and compile it to create a dynamic link library (DLL). 

If you want to indicate that the function you are defining is deterministic, mark it with the IsDeterministic attribute and assign a value of true or false.

1. Start Microsoft Visual Studio
2. To create a new project, on the main menu, click File -> New -> Project (or File -> New Project)
3. In the Project Types tree view, expand Visual C# if necessary.
   Under Visual C#, click Windows
4. In the Templates list view, click Empty Project
5. In the Name text box, replace the string with CeilInn
    
   
6. To save the project, on the Standard toolbar, click the Save button
7. Set the Name to CeilInn1
8. Set the Location to C:\
9. Uncheck Create Directory For Solution
    
   
10. Click Save
11. In the Solution Explorer, under CeilInn1, right-click References -> Add Reference...
12. In the .NET tab of the Add Reference dialog box, click System
13. Press and hold Ctrl
14. Click System.Data
15. Release Ctrl
     
    
16. Click OK
17. In the Solution Explorer, right-click CeilInn1 -> Add -> New Item...
18. In the Templates list, click Code File
19. Set the Name to CeilInn
     
    
20. Click Add
21. In the Code Editor, type the following:
     

    using System; using Microsoft.SqlServer.Server; public class Payroll { [Microsoft.SqlServer.Server.SqlFunction] public static void Calculate(decimal Week1Time, decimal Week2Time, decimal HourlySalary, out decimal RegularTime, out decimal RegularPay, out decimal Overtime, out decimal OvertimePay, out decimal TotalEarnings) { decimal overtimeSalary = 0.00M; decimal week1RegularTime = 0.00M; decimal week1RegularPay = 0.00M; decimal week1Overtime = 0.00M; decimal week1OvertimePay = 0.00M; decimal week2RegularTime = 0.00M; decimal week2RegularPay = 0.00M; decimal week2Overtime = 0.00M; decimal week2OvertimePay = 0.00M; // The overtime is paid time and half overtimeSalary = HourlySalary * 1.5M; // If the employee worked under 40 hours, there is no overtime if (Week1Time < 40) { week1RegularTime = Week1Time; week1RegularPay = HourlySalary * week1RegularTime; week1Overtime = 0M; week1OvertimePay = 0M; } // If the employee worked over 40 hours, calculate the overtime else if (Week1Time >= 40) { week1RegularTime = 40M; week1RegularPay = HourlySalary * 40M; week1Overtime = Week1Time - 40M; week1OvertimePay = week1Overtime * overtimeSalary; } if (Week2Time < 40) { week2RegularTime = Week2Time; week2RegularPay = HourlySalary * week2RegularTime; week2Overtime = 0M; week2OvertimePay = 0M; } else if (Week2Time >= 40) { week2RegularTime = 40; week2RegularPay = HourlySalary * 40M; week2Overtime = Week2Time - 40M; week2OvertimePay = week2Overtime * overtimeSalary; } RegularTime = week1RegularTime + week2RegularTime; Overtime = week1Overtime + week2Overtime; RegularPay = week1RegularPay + week2RegularPay; OvertimePay = week1OvertimePay + week2OvertimePay; TotalEarnings = RegularPay + OvertimePay; } }

22. In the Solution Explorer, right-click CeilInn1 and click Properties...
23. In the Properties window, click the arrow of the Output Type combo box and select Class Library
     
    
24. Close the window
25. Save all
26. To create the assembly, in the Solution Explorer, right-click CeilInn1 and click Build
27. Use a Windows utility such as Windows Explorer to get the path to the DLL that was created
28. Select that path and copy it or write it down

After creating the library, you must import it to Microsoft SQL Server. To do this, in Microsoft SQL Server, you must create an assembly using the CREATE ASSEMBLY statement. The formula to follow is:

CREATE ASSEMBLY assembly_name
[ AUTHORIZATION owner_name ]
FROM { <client_assembly_specifier> | <assembly_bits> [ ,...n ] }
[ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]
[ ; ]

You start with the CREATE ASSEMBLY expression followed by a name. If you want to specify the user who will own the assembly, type AUTHORIZATION followed by the name of the user or role. This is an option and you can omit it.

To specify the DLL you want to import, type FROM followed by the file name of, or the path to, the DLL. You must specify the file name and its extension.

You should specify the type and level of access that that will be granted on the assembly when it will have been created. To do this, type PERMISSION_SET followed by:

• SAFE: This assembly will not access the external files, the network, or the registry. This is considered the most reliable and secure level of access
• EXTERNAL_ACCESS: This assembly can access the external files, the network, or the registry
• UNSAFE: This assembly will have unrestricted access to the objects on the server. Because this level is highly risky, apply it only if you know what you are doing

1. Return to Microsoft SQL Server
2. Select the whole code you previously wrote and press Delete
3. To create an assembly to import the function, type the following (change the path based on the Location where you created the project in:
    

   USE CeilInn5; GO CREATE ASSEMBLY PayrollProcessing FROM N'C:\CeilInn1\bin\Release\CeilInn1.dll' WITH PERMISSION_SET = SAFE; GO

4. Press F5 to execute.
   Make sure you get a message that the Command(s) Completed Successfully

After importing the library, you can use it in Microsoft SQL Server. One way you can do this consists of creating an object that can access it. For example, you can create a stored procedure that executes the function in the assembly and renders its result(s). If you are creating the stored procedure, The formula to use is:

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
AS EXTERNAL NAME assembly_name.class_name.method_name

Start with CREATE PROC or CREATE PROCEDURE followed by a name of your choice. Then, specify the arguments, if any. The arguments must be the same number, the same types, and the same sequence as those defined in the function of the library you imported. The data types must also be compatible.

After the (list of) argument(s), type AS EXTERNAL NAME followed by this formula:

AssemblyName.ClassName.FunctionName

After creating the whole statement, you can execute it. After creating the procedure, you can execute it to call it and see the result(s).

1. To create a procedure that gets the function ready for use, change the code in the Query window as follows:
    

   USE CeilInn5; GO /* CREATE ASSEMBLY PayrollProcessing FROM N'C:\CeilInn1\bin\Release\CeilInn1.dll' WITH PERMISSION_SET = SAFE; GO */ CREATE PROCEDURE EvaluatePayroll @W1Time decimal(6, 2), @W2Time decimal(6, 2), @HourlyRate money, @RegTime decimal(6, 2) output, @RegPay decimal(6, 2) output, @Over decimal(6, 2) output, @OverPay decimal(6, 2) output, @TotalSalary decimal(6, 2) output AS EXTERNAL NAME PayrollProcessing.Payroll.Calculate; GO

2. Press F5 to execute
3. To use the function that was created in the CLR code, change the code in the Query window as follows:
    

   USE CeilInn5; GO /* CREATE ASSEMBLY PayrollProcessing FROM N'C:\CeilInn1\bin\Release\CeilInn1.dll' WITH PERMISSION_SET = SAFE; GO CREATE PROCEDURE EvaluatePayroll @W1Time decimal(6, 2), @W2Time decimal(6, 2), @HourlyRate money, @RegTime decimal(6, 2) output, @RegPay decimal(6, 2) output, @Over decimal(6, 2) output, @OverPay decimal(6, 2) output, @TotalSalary decimal(6, 2) output AS EXTERNAL NAME PayrollProcessing.Payroll.Calculate; GO */ DECLARE @Week1 decimal(6, 2); DECLARE @Week2 decimal(6, 2); DECLARE @Rate money; DECLARE @RegWork decimal(6, 2); DECLARE @RegWage decimal(6, 2); DECLARE @OverPeriod decimal(6, 2); DECLARE @OverPeriodPay decimal(6, 2); DECLARE @NetPay decimal(6, 2); SET @Week1 = 42.50; SET @Week2 = 36.00; SET @Rate = 25.85; EXECUTE EvaluatePayroll @Week1, @Week2, @Rate, @RegWork output, @RegWage output, @OverPeriod output, @OverPeriodPay output, @NetPay output; PRINT 'Payroll Calculation'; PRINT '------------------------'; PRINT 'Week 1: ' + CONVERT(nvarchar(20), @Week1, 20); PRINT 'Week 2: ' + CONVERT(nvarchar(20), @Week2, 20); PRINT 'Hourly Salary: ' + CONVERT(nvarchar(20), @Rate, 20); PRINT 'Regular Time: ' + CONVERT(nvarchar(20), @RegWork, 20); PRINT 'Regular Pay: ' + CONVERT(nvarchar(20), @RegWage, 20); PRINT 'Overtime: ' + CONVERT(nvarchar(20), @OverPeriod, 20); PRINT 'Overtime Pay: ' + CONVERT(nvarchar(20), @OverPeriodPay, 20); PRINT 'Total Earnings: ' + CONVERT(nvarchar(20), @NetPay, 20); PRINT '------------------------'; GO

4. Press F5 to execute
     

   Payroll Calculation ------------------------ Week 1: 42.50 Week 2: 36.00 Hourly Salary: 25.85 Regular Time: 76.00 Regular Pay: 1964.60 Overtime: 2.50 Overtime Pay: 96.94 Total Earnings: 2061.54 ------------------------

As mentioned already, the CLR supports various types of databases objects. Each one of these objects is available through a specific attribute. This means that, when creating an object, you should (must) specify its type using the appropriate attribute. The most fundamental piece of information you must specify about an attribute is its tag as defined in the .NET Framework. This would be done as follows:

[Microsoft.SqlServer.Server.SqlFunction]
Options Method()
{

}

Because an attribute is created from a class, it may have properties and all classes of the CLR attributes have at least one property.

Each attribute has some parameters and most, if not all, of these parameters are optional, meaning you can ignore them. To specify a parameter, after the attribute, open the parentheses, enter the name of the parameter, and assign the desired but appropriate value to it.

If you omit an option, the compiler would consider a default value for it.

One of the most common parameters of an attribute is its name. This is the name of the CLR object you are creating. To specify the name, in the parentheses, type Name= and specify the name as a string. Here is an example:

using System;
using Microsoft.SqlServer.Server;

public class Accessories
{
    [Microsoft.SqlServer.Server.SqlFunction(Name="CalculatePayroll")]
    public static void CalculatePayroll()
    {
       
    }
}

Besides functions, the CLR allows you to create stored procedures. To support them, the .NET Framework provides the SqlProcedure class defined in the Microsoft.SqlServer.Server namespace. To use this class, create a static method but mark it with the Microsoft.SqlServer.Server.SqlProcedure attribute. As mentioned already, you can specify the optional name in the parentheses. Name is the only option of the procedure attribute.

1. To create a new project, on the main menu, click File -> New -> Project... (or File -> New Project...)
2. In the Project Types tree view, expand Visual C# if necessary.
   Under Visual Basic, click Windows
3. In the Templates list view, click Empty Project
4. In the Name text box, replace the string with RoomInformation1
5. Click OK
6. On the main menu, click Project -> RoomInformation Properties...
7. In the Output Type combo box, select Class Library
8. To save the project
, on the Standard toolbar, click the Save All button
9. If ncessary, set the Name to RoomInformation1. Set the location to C:\. Uncheck Create Directory For Solution
10. Click Save
11. On the main menu, click Project -> Add Reference...
12. In the .NET tab of the Add Reference dialog box, click System
13. Press and hold Ctrl
14. Click System.Data
15. Click System.xml
16. Release Ctrl
17. Click OK
18. In the Solution Explorer, right-click RoomInformation1 -> Add -> New Item ...
19. In the Templates list, click Code File
20. Set the Type RoomInfo
21. Click Add
22. In the Code Editor, type the following:
     

    using System; using System.Data.SqlTypes; using System.Data.SqlClient; using Microsoft.SqlServer.Server; public class RoomInfo { [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetInformation")] public static void GetInformation(out SqlString result, string criterion) { using (SqlConnection conCeilInn = new SqlConnection("context connection=true")) { SqlString roomNumber = ""; SqlString roomType = ""; SqlString bedType = ""; SqlDecimal rate = 0M; conCeilInn.Open(); SqlCommand cmdRoom = new SqlCommand("SELECT RoomNumber, " + "RoomType, BedType, Rate " + "FROM Rooms WHERE RoomNumber = '" + criterion + "'", conCeilInn); SqlDataReader rdrRoom = cmdRoom.ExecuteReader(); using (rdrRoom) { while (rdrRoom.Read()) { roomNumber = rdrRoom.GetSqlString(0); roomType = rdrRoom.GetSqlString(1); bedType = rdrRoom.GetSqlString(2); rate = rdrRoom.GetDecimal(3); } result = "Room #: " + roomNumber + ", " + "Room Type: " + roomType + ", " + "Bed Type: " + bedType + ", " + "Daily Rate: " + rate.ToString(); } } } }

23. To generate the library, on the main menu, click Build -> Build Solution
24. Return to Microsoft SQL Server
25. Select the whole code you previously wrote and press Delete
26. To create an assembly to import the stored procedure, type the following (change the path based on the Location where you created the project in:
     

    USE CeilInn5; GO CREATE ASSEMBLY GetRoomInformation FROM N'C:\RoomInformation1\bin\Release\RoomInformation1.dll' WITH PERMISSION_SET = SAFE; GO

27. Press F5 to execute.
    Make sure you get a message that the Command(s) Completed Successfully
28. To create a procedure that gets the function ready for use, select the code in the Query window and type:
     

    USE CeilInn5; GO CREATE PROCEDURE ShowRoomInfo(@RmNumber nvarchar(100) OUTPUT, @Criterion nvarchar(20)) AS EXTERNAL NAME RoomInfo.RoomInfo.GetInformation; GO

29. Press F5 to execute
30. To use the procedure that was created, change the code in the Query window as follows:
     

    USE CeilInn5; GO /* CREATE ASSEMBLY GetRoomInformation FROM N'C:\RoomInformation1\bin\Release\RoomInformation1.dll' WITH PERMISSION_SET = SAFE; GO CREATE PROCEDURE ShowRoomInfo(@RmNumber nvarchar(100) OUTPUT, @Criterion nvarchar(10)) AS EXTERNAL NAME GetRoomInformation.RoomInfo.GetInformation; GO */ DECLARE @RoomResult nvarchar(100); EXECUTE ShowRoomInfo @RoomResult output, N'108'; SELECT @RoomResult AS 'Room Information'; GO

31. Press F5 to execute