SQL CLR

SQL CLR

OVERVIEW

SQL CLR refers to the use of CLR code within SQL to make SQL more extensible and make things available which are not possible by simple T-SQL code.

ALLOW SQL INSTANCE TO RUN CLR CODE

EXEC sp_configure 'clr enabled',1

RECONFIGURE

CREATING PROJECT

• Open Microsoft Visual Studio >> Click New Project >> Select Visual C# >> Database >> SQL Server Project
• Connect Database
• Right click on Solution Explorer >> Click on Add >> Stored Procedure
• Add new stored procedure template
• Now where it suggest to //Put your code here. Replace it with code displayed in the image. Once the code is complete do following two steps.
• Click on menu bar >> Build >> Build ProjectName
• Click on menu bar >> Build >> Deploy ProjectName

SAMPLE CODE

Check whether given string is proper email or not

public static void StoredProcedure1(SqlString email)

    {

        using (SqlConnection conn = new SqlConnection("context connection=true"))

        {

            SqlBoolean isValid = new SqlBoolean();

            isValid = Regex.IsMatch(email.Value, @"^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$");

            SqlContext.Pipe.Send(isValid.ToString());

        }

    }

fetch data from particular table

                SqlCommand cmd = conn.CreateCommand();

            cmd.CommandText = @"SELECT * FROM IdentityCheck";

            cmd.Parameters.AddWithValue("@email",email);

            conn.Open();

            SqlContext.Pipe.ExecuteAndSend(cmd);

LOAD ASSEMBLY

You can either deploy through VS or you can use following command.

create assembly demo from '<<SoAndSo>>\SqlClassLibrary.dll'

You can check in system table.

select * from sys.assemblies

USING SQL CLR IN DATABASE CODE

EXEC StoredProcedure1 '##abc@gmail.co.in'

OBJECTS CAN BE CREATED USING SQL CLR

• Stored Procedure
• Trigger
• User defined Type
• User defined Aggregate
• Scalar Function
• Table valued Functio