Click here to Skip to main content
15,868,115 members
Articles / Programming Languages / SQL

Using CLR functions with T-SQL

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
25 Nov 2011CPOL2 min read 17.3K  
Using CLR functions with T-SQL

Why Use CLR?

Before using CLR, you should question ‘Why’. When using CLR functions within T-SQL, you have to maintain two different programming environments, unless what you gain is worth more than having it. We need to use CLR functions on occasions such as: when we have to access system resources like file system or network. (**Extended Stored Procedures can do the same. But they are deprecated and will be removed from future versions of SQL.) Or when the business logic is too complex to write in T-SQL, which can be easily done using .NET languages (reusing logic already written without duplicating them using T-SQL).

To illustrate this, I will create a CLR function which will create a text file and append the text which is passed from a T-SQL statement. Open Visual Studio and create a new ‘SQL Server Project’.

img_scr_001

In the next dialog, you will be prompted to select the database which you plan to deploy for your extension. You can either select from an existing one or connect to a different one.

img_scr_002

In the next screen, you will be prompted whether you want to debug your .NET code. Select ‘yes’ or ‘no’ depending on your requirement.

img_scr_003

Right click on the newly created project and add a new user-defined function.

img_scr_004

I have named it ‘WriteToTextFileSample’.

img_scr_005

Use the following code:

C#
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

using System.IO;
using System.Text;

public partial class UserDefinedFunctions {
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString WriteToTextFileSample(string filename, string data) {
FileStream fs = File.Open(filename, FileMode.Append);
byte[] b = new UTF8Encoding(true).GetBytes(data);

fs.Write(b, 0, data.Length);
fs.Close();

return new SqlString("Completed");
}
};

**The functions should be 'public static' and all the functions should have the '[Microsoft.SqlServer.Server.SqlFunction]' attribute or you will not be able to call it from SQL.

Go to the project properties and change the ‘Permission Level’ to ‘Unsafe’ in the Database section. (This is only required if you are performing tasks such as writing to files and accessing system related resources. You do not need this if you are performing calculations, etc.)

Save your project. Before deploying this, you have to enable ‘clr enable’ to ‘1’. Use the following T-SQL statement to do so.

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

Once executed, deploy the project (Build—> Deploy Solution). You will be prompted with the SQL credentials.

img_scr_006

Once it’s deployed successfully, use the following sample statement to use the function which we have created.

SQL
select dbo.WriteToTextFileSample(
N'D:\SampleCLRText.txt',
N'Hello from SQL !!!'
)

img_scr_007

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.

My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.

My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.


Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports

Comments and Discussions

 
-- There are no messages in this forum --