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 Function