Managed Code in SQL Server

User can create the programs supported by the .NET supported languages using the CLR integration with the SQL Server. The managed code can be defined as the code written and supported with any .NET programming language.

Let us take an overview of the details of the CLR integration through SQL Server.

Introduction to SQL Server CLR Integration

The database developers use the T-SQL code for the creation of procedures and functions. The T-SQL does not support object orientation, arrays, collections, use of internal and external resources. CLR allows the database developers to create objects in .NET supported programming languages. Such database objects are known as managed database objects.

The advantages provided by the CLR integration are as follows:

1. Common development environment: The developers use the Visual Studio tool for creating the database objects and scripts.
2. User can define the data types: The database developers can create the user defined data types for enriching the storing capacity of the server.
3. Good Programming model: The .NET programming language contains several constructs which are unavailable in T-SQL. The classes can also be used to implement the pre defined functionality.

Requirement of Managed Code

The managed database objects are useful when the user needs to perform the following tasks.

1. User wants the external resources in the code
2. The complicated programming logic is implemented for which the functionality can be reused
3. The CPU intensive functionality is implemented. It can execute more effectively as managed code.

The Managed Database Objects

For creating the managed database objects, the managed code is to be created in any .NET programming language. It consists of classes and methods that help in implementing the desired functionality. User needs to compile the code for creating an assembly.

The assemblies cannot be directly executed by the SQL Server. User needs to import and configure the assemblies in database engine before using it.

User can import an assembly in the database engine using the CREATE ASSEMBLY statement. The syntax for the create assembly is as shown below:

Code:

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

Where,

• assembly_name is the assembly name to be created in SQL Server.
• AUTHORIZATION owner_name is the name of the user as the assembly owner client_assembly_specifier is the local or network path of the .NET assembly
• PERMISSION_SET is the permissions granted to the assembly. The values that are passed as parameter are as follows:
• SAFE: The secure permission since the code is not accessible to any external resource.
• EXTERNAL_ACCESS: It is used for enabling the .NET code to access the resources such as variables, files, registry and networks.
• UNSAFE: It is used for enabling the code for accessing resource in or out the SQL Server.

User can create the managed database object for validating the account number of the users. The following code is useful for importing the assembly as AccountNumberValidator.dll.

Code:

CREATE ASSEMBLY AccountNumber FROM ‘C:\AccountNumberValidator.dll’ WITH PERMISSION_SET = SAFE

The database engine imports the AccountNumberValidator.dll assembly file from the C drive of the local system and an assembly object is AccountNumberValidator.

The ALTER ASSEMBLY statement is used for referring to the latest version of the assembly. The current process are not affected due to the alternation.

Code:

ALTER ASSEMBLY AccountNumberValidator WITH PERMISSION_SET = EXTERNAL_ACCESS

The DROP ASSEMBLY statement is used for deleting the assembly.

Code:

DROP ASSEMBLY AccountNumberValidator

Creation of Managed Database Objects

Once the assemblies are imported in SQL Server, the managed database objects are created. They help in enabling the CLR integration feature in the database. The following statement is used for enabling the CLR.

Code:

sp_configure CLR_ENABLED, 1;

Execute the following statement after the configure statement is executed.

Code:

RECONFIGURE;

When the user develops the managed database objects, the System.Data.SqlClient, System.Data.SqlTypes and the SqlServer.Server namespaces are present in the .NET base class libraries.

The following classes present in the System.Data.SqlClient namespace are used for accessing the database from the managed code.

1. SqlPipe: It helps the user to send the messages to the client application. The following code shows the code to display the message.

Code:

SqlPipe pipe = SqlContext.Pipe;
pipe.Send (“ A message is sent”);

2. SqlContext: It is used for representing the context under which the assembly is executing. The properties are provided for accessing the objects.

3. SqlTriggerContext: It helps the user for collecting information about the event used for trigger firing in the managed trigger operation. The following code snippet demonstrates the functionality.

Code:

SqlTriggerContext tg = SqlContext.GetTriggerContext ();
SqlPipe pipe = SqlContext.Pipe;
if ( tr.TriggerAction == TriggerAction.Insert )
    pipe.Send (“Record is added “);

4. SqlConnection: It helps the user to connect the database for querying data. The following code is used for the connection.

Code:

SqlConnection con = new SqlConnection (“context connection = true”);

5. SqlCommand: It is used for sending the T-SQL commands to the server. The code for deleting the rows from a table to the database is as shown below:

Code:

SqlCommand cmd = new SqlCommand();
cmd.CommandText = “DELETE FROM Employee;
cmd.Connection = con;
int rows = cmd.ExecuteQuery();

6. SqlDataReader: It helps user to execute on the results of the query. The following code is used for retrieving the records from the server.

Code:

SqlCommand cmd= new SqlCommand( “SELECT StudName from Student”, con);
SqlPipe pipe=new SqlContext.Pipe;
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
    Pipe.Send(dr[“StudName”] );
}
dr.Close();

In the above code, the ExecuteReader() method is used for executing the SELECT statement and the SqlDataReader object is returned. The dr object is stating the beginning of the data in the result set. The Read() method is used for moving the pointer to the first row. The method returns the value as false if no records are found.

The developer can create following types of database objects through the managed code.

1. Functions
2. Stored Procedures
3. Triggers
4. User defined data types

1. Creating Managed Functions in SQL Server

User can create managed functions in SQL Server. The following steps are performed for the creation of the function.

1. A .NET class is created for implementing the functionality of the user defined function. The .NET assembly is produced by combining the class.
2. The CREATE ASSEMBLY statement is used for registering the assembly.
3. The user defined function is created for the association of the assembly with the methods

The function is configured as managed function and can be called as user defined functions.

For creating the managed function, the CREATE FUNCTION statement is used. The syntax for CREATE FUNCTION is as shown below:

Code:

    CREATE FUNCTION <Function Name>
    (
    <Parameter List>
    )
    RETURNS <Return Type>
    AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method Name>

Where,

• <Function Name> is the name of the function
• <Parameter List> is the parameter list the function accepts
• <Return Type> is the type of value returned to the function
• <Assembly Identifier> is the name of the imported assembly
• <Type Name> is the name of the class containing the method that is executed through the stored procedure
• <Method Name> is the name of the method to be executed through the stored procedure

Consider an example of creating the function that accepts the total cost of the product.

Code:

using System;
using System.Data.Sql;
using System.Data.SqlServer;
using Micorsoft.SqlServer.Server;

namespace UserDefinedFunctions
{
    public class UserDefined1
    {
        [ SqlFunction () ]
        public static SqlDouble TotalCost ( SqlDouble initialcost )
        {
            SqlDouble totalcost = initialcost * 12.5 ;
            return totalcost + initialcost;
        }
    };
}

For creating the managed function through the TotalCost.dll file, perform the following steps.

1. User needs to run the following statement in the Query Editor window for creating an assembly known as TotalCostAssembly.dll file.

Code:

CREATE ASSEMBLY TotalCost FROM ‘C:\TotalCostAssembly\TotalCostAssembly\bin\Debug\TotalCost.dll’ WITH PERMISSION_SET = UNSAFE

2. User needs to execute the following statement for creating the managed function as TotalCost from the TotalCostAssembly assembly.

Code:

CREATE FUNCTION TotalCost ( @ic as SqlDouble )
RETURNS SqlDouble 
AS EXTERNAL NAME 
TotalCostAssembly.TotalCost

3. Use the following query to execute the managed function

Code:

SELECT dbo.TotalCost ( initalcost ) FROM ProductDetails

2. Creating Managed Stored Procedures

The stored procedures are used as blocks of database. The CLR integration helps user to manage code for the execution as stored procedure. The stored procedure that is referring to an imported assembly is created.

To create a stored procedure using the managed code, the following steps are performed.

1. Create a .NET class implementing the functionality of the stored procedure. Compile the class for producing the .NET assembly.
2. The CREATE ASSEMBLY statement is used for registering the assembly
3. Create a stored procedure and combine it with the actual methods of the assembly.

The syntax for CREATE PROCEDURE statement is as shown below:

Code:

CREATE PROCEDURE <Procedure Name>
AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method Name>

Where,

• Procedure Name is the procedure user wants to create
• Assembly Identifier is the name of the imported assembly
• Type Name is the name of the class containing the method that is executed through the stored procedure
• Method Name is the name of the method executed through the stored procedure

Consider an example for reading the system time. The code to demonstrate the functionality is as shown below.

Code:

using System;
using System.Data;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace CLRStoredProcedure
{
    public class GetValueProc
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void GetData()
        {
            SqlPipe pipe = SqlContext.Pipe;
            String systemtime = “ The system time is “+System.DateTime.Now.ToString();
            pipe.Send(systemtime);
        }
    };
}

Open the SQL Server Management Studio, add the following script in the Query Editor.

Code:

EXEC dbo.GetData

3. Creating Managed Triggers

The managed trigger helps the user to create logic that cannot be executed using the T-SQL. The CREATE TRIGGER statement is used for creating trigger. The syntax is as shown below:

Code:

CREATE TRIGGER <TriggerName>
ON <Table or View> <FOR|INSTEAD OF|AFTER>
<INSERT|UPDATE|DELETE>
AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method Name>

Where,

• TiggerName is the name of the trigger
• Table or View is the name of the table or view on which the trigger is created
• FOR | INSTEAD OF | AFTER specifies the type of trigger to be created
• Assembly Identifier is the imported assembly name
• Type Name is the name of the class containing the method to be executed

Consider an example to demonstrate the user details maintenance in an administration. User needs to validate the email address in the details. Add a trigger for validating the email address every time it is inserted.

Code:

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer.Server;
using System.Data.SqlTypes;
using System.Transactions;
using System.Text.RegularExpressions;

public class MailAddress
{
    [ SqlTrigger ( Name=”UserAudit” Target=”Users”, Event=”FOR INSERT” ) ]
    public static void ValidMail()
    {
        string username;
        string newname;
        SqlTriggerContext trigg = SqlContext.TriggerContext;
        SqlPipe pipe=SqlContext.Pipe;
        SqlDataReader reader;
        switch ( trigg.TriggerAction )
        {
            case TriggerAction.Insert:
            using ( SqlConnection con = new SqlConnection (“ context connection =true” ))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(“ SELECT * FROM INSERTED;”, con);
                reader=cmd.ExecuteReader();
                reader.Read();
                username=(string) reader[0];
                newname=(string)reader[1];
                reader.Close();
            if( IsValidEmailAddress(username) )
            {
                cmd= new SqlCommand(“INSERT [UserAudit] VALUES (‘” +username+”’ , ‘”+newname+”’);”,
                pipe.Send(cmd.CommandText);
                cmd.ExecuteNonQuery();
                pipe.Send(“Inserted value is”,+username);
            }
            else
            {
                try
                {
                    pipe.Send(“Not a valid Email”);
                    Transaction tran1 = Transaction.Current;
                    tran1.Rollback();
                }
                catch ( SqlException ex )
                {
                }
            }
            break;
        }
    }
public static bool IsValidEmailAddress ( string email )
{
    return RegEx.IsMatch ( email, @”^( [\w]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$”);
}
}

Create an assembly known as ValidateEmailAssembly from the dll file.

Code:

CREATE ASSEMBLY ValidateEmailAssembly FROM ‘C:\ValidateMail\ValidateMail\bin\Debug\ValidateMail.dll’ WITH PERMISSION_SET = UNSAFE

Two tables as Users and UserNameAudit are created. They are coded as shown below.

Code:

CREATE TABLE Users
{
    username nvarchar(100) not null,
    newname nvarchar(100) not null
}

CREATE TABLE UserNameAudit
{
    username  nvarchar(100) not null,
    newname nvarchar(100) not null
}

Execute the statement for creating the managed trigger as shown below:

Code:

CREATE TRIGGER Email1 ON Users FOR INSERT 
AS EXTERNAL NAME ValidateEmailAssembly.MailAddress.ValidMail

Execute the following statements for inserting the records in the Users table.

Code:

INSERT INTO Users VALUES ( ‘john@gmail.com’, ‘John’);

INSERT INTO Users VALUES ( ‘nickgmail.com’, ‘Nick’);

In the above code, the first statement contains a valid email address. In the second command, the trigger is fired. It contains an invalid email address.

4. Creating Managed User Defined Data Types

User can create a user defined data types to be used into SQL Server. The data type can be combined with existing data type and can be applied with some modifications.

The CREATE TYPE statement is used for creating the user defined data type.

Code:

CREATE TYPE [ schema_name. ] type_name
{
    EXTERNAL NAME assembly_name [ .class_name ]
}

Where,

• schema_name is the name of the schema to which the data type belongs
• type_name is the name of the user defined data type
• assembly_name is the name of the server assembly

Consider an example of creating a user defined data type as shown below:

Code:

using System;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;

[ Serializable ]
[ Microsoft.SqlServer.Server.SqlUserDefinedType ( Format.UserDefined, MaxBytesSize=8000) ]
public struct country_code : IBinarySerialize, INullable
{
    private string country_code;
    private bool isNull;

    public bool isNull
    {
        get
        {
            return this.isNull;
        }
    
    }
    public static CountryCode Null
    {
        get
        {
            CountryCode cc = new CountryCode();
            cc.isNull = true;
            return cc;
        }
    }

    public CountryCode ( string s1 )
    {
        isNull = false;
        country_code=s1;
    }

    public override String ToString()
    {
        return country_code;
    }

    public void Read( BinaryReader r )
    {
        country_code = r.ReadString();
    }

    public void Write( BinaryWriter w )
    {
        if ( country_code ==”SA” )
        {
            w.Write ( “The value is South Africa” );
        }
        else if ( country_code == “IND” )
        {
            w.Write(“ The value is India” );
        }
        else
        {
            w.Write(“ The value is not valid”);
        }
    }
}

Execute the following command to create an assembly as CountryAssembly from the CountryCode.dll file

Code:

CREATE ASSEMBLY CountryAssembly FROM ‘C:\CountryCode\CountryCode\biin\Debug\CountryCode.dll’ WITH PERMISSION_SET = UNSAFE 

The following statement is used for creating the user defined data type.

Code:

    CREATE TYPE CountryCode
    EXTERNAL NAME CountryAssembly.CountryCode

Execute the following command for creating the table using the CountryCode.

Code:

CREATE TABLE EmpStatus
(
    EmpName varchar(20),
    code CountryCode
)