Microsoft SQL Server CLR and Delphi Prism Part 1 : CLR Stored Procedures

January 13, 2011 by Rodrigo 1 Comment

This is the first of a series of articles where i will show you how integrate Delphi Prism and the SQL Server CLR support.

SQL Server (Since version 2005) allow you to create database objects using the integration with the .NET Framework common language runtime (CLR). this mean which you can create a .net assembly and then call the methods defined in your class directly from SQL Server like any SQL Object, you can create Scalar-valued user-defined functions (scalar UDFs), Table-valued user-defined functions (TVFs), User-defined procedures (UDPs) and User-defined triggers.

today we will see how to create CLR Stored Procedures from Delphi prism.

Ok, first you need to create a new Delphi prism project of class library type.

Now before to define the code to be accessed from SQL Server you must know the requirements of the CLR stored procedures.

1) In the common language runtime (CLR), stored procedures are implemented as public static methods on a class in a Microsoft .NET Framework assembly.

2) you must include the Microsoft.SqlServer.Server namespace in your uses list.

3) The Parameters passed to a CLR stored procedure can be any of the native SQL Server types that have an equivalent in managed code

4) all the methods must have the [Microsoft.SqlServer.Server.SqlProcedure()] attribute, this attribute tells to Visual Studio compiler that this is a stored procedure for SQL Server.

Check this sample declaration for a Delphi prism class which contains CLR stored procedures.

namespace MyNameSpace;

interface
uses
  System,
  System.Data,
  System.Data.SqlClient,
  Microsoft.SqlServer.Server,
  System.Data.SqlTypes;

type
  MyClass  = public class
  public
    [Microsoft.SqlServer.Server.SqlProcedure()]
    class method GetSqlVersion(out Intvalue: SqlInt32;out Strvalue: SqlString);
    [Microsoft.SqlServer.Server.SqlProcedure()]
    class method Hello;
    [Microsoft.SqlServer.Server.SqlProcedure()]
    class method GetTables;
    [Microsoft.SqlServer.Server.SqlProcedure()]
    class method GetRecordResult;
  end;

The CLR Stored Procedures can return information in several ways. This includes output parameters, tabular results, and messages.

OUTPUT Parameters and CLR Stored Procedures
in this sample method you can see how can return output parameters. this method return the sql version string and the sql version as a integer value. (off course you can obtain directly from SQL Server this information but this is just a sample)

class method MyClass.GetSqlVersion(out Intvalue: SqlInt32;out Strvalue: SqlString); //look the types used, are SQL Server types remember the requirements?
begin
    Intvalue := 0;
    Strvalue := '';    
    var  connection: SqlConnection := new SqlConnection('context connection=true'); //this is cool in this line you are using the current connection instance of the caller client, so you don't need connect to the SQL server.
    connection.Open();
    var command: SqlCommand := new SqlCommand('Select @@VERSION,@@MICROSOFTVERSION', connection); //get the version info
    var reader: SqlDataReader := command.ExecuteReader(); 
      if reader.Read() then begin
        Strvalue := reader.GetSqlString(0); //assign the values
        Intvalue := reader.GetSqlInt32(1);
      end
end;

Returning Messages
one of the overload versions of the SqlContext.Pipe.Send method allow you to send messages directly to the client or current output consumer.

class method MyClass.Hello;
begin
     SqlContext.Pipe.Send('Hello From delphi Prism'); //send the message to client
end;

Returning Tabular Results directly to the client
You can return directly to the client a result set. this sample method returns a list of the tables and rows in the current database.

class method MyClass.GetTables;
begin 
    var  connection: SqlConnection := new SqlConnection('context connection=true');
    connection.Open();
    var command: SqlCommand := new SqlCommand(
    'SELECT  so.name [TableName],MAX(si.rows) [RowCount] '+
    'FROM '+ 
    'sysobjects so, '+ 
    'sysindexes si '+ 
    'WHERE '+ 
    'so.xtype = ''U'' '+ 
    'AND '+ 
    'si.id = OBJECT_ID(so.name) '+ 
    'GROUP BY '+ 
    'so.name '+ 
    'ORDER BY '+ 
    '2 DESC', connection);
     // send the result set to the client directly.
     SqlContext.Pipe.ExecuteAndSend(command);   
end;

Creating a dynamic result set and send them to the client
You can also create a result set on the fly and return to the client the results. this sample method create a record with 4 columns(fields).

class method MyClass.GetRecordResult;
begin
      // Create a record object with 4 columns that represents an individual row, including it's metadata.
      var ARecord : SqlDataRecord  := new SqlDataRecord(new SqlMetaData('Col1', SqlDbType.NVarChar, 100),
                                                        new SqlMetaData('Col2', SqlDbType.Int),
                                                        new SqlMetaData('Col3', SqlDbType.Float),
                                                        new SqlMetaData('Col4', SqlDbType.DateTime)
      );    
      // Populate the record.
      ARecord.SetString(0, 'Delphi Prism is cool');      
      ARecord.SetInt32 (1, 1024*1024);      
      ARecord.SetDouble(2, Math.PI);      
      ARecord.SetDateTime(3, DateTime.Now);    
      // Send the record to the client.
      SqlContext.Pipe.Send(ARecord);
end;

Ok, now we have our code, we need deploy in the SQL Server.

first you must activate CLR support in the SQL server, because is disabled by default. execute this sentence to activate the CLR support.

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Now we need register our assembly using the CREATE ASSEMBLY sentence.
this is the syntax

CREATE ASSEMBLY assembly_name
[ AUTHORIZATION owner_name ]
FROM { <client_assembly_specifier> | <assembly_bits> [ ,...n ] }
[ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]
[ ; ]
<client_assembly_specifier> :: ='[\\computer_name\]share_name\[path\]manifest_file_name'
  | '[local_path\]manifest_file_name'<assembly_bits> :: =
{ varbinary_literal | varbinary_expression }

in this case the sentence will look like this

CREATE ASSEMBLY MyDelphiPrismAssembly1 from 'C:\Prism Projects\Delphi Prism SqlCLR\MyPrismClr\bin\Release\MyPrismClr.dll' 
WITH PERMISSION_SET = SAFE
GO

if all it’s ok you must can view the registered assembly under the assemblies folder of your SQL Management Studio.

next we need create and register every procedure of our assembly. see this sample, in this sentence we are creating a SQL Server stored procedure called hello where the implementation is defined in our assembly.

CREATE PROCEDURE Hello
AS
EXTERNAL NAME MyDelphiPrismAssembly1.[MyNameSpace.MyClass].Hello

now you can execute this stored procedure in this way

 EXEC Hello

and the result will be

Now the same steps to register our GetTables method

CREATE PROCEDURE GetTables
AS
EXTERNAL NAME MyDelphiPrismAssembly1.[MyNameSpace.MyClass].GetTables

and when you execute this stored procedure the result will be something like this.

now creating the GetRecordResult stored procedure

CREATE PROCEDURE GetRecordResult
AS
EXTERNAL NAME MyDelphiPrismAssembly1.[MyNameSpace.MyClass].GetRecordResult

and the output

finally this is how you must register a stored procedure with output parameters.

CREATE PROCEDURE  GetSqlVersion(@Intvalue int OUTPUT, @Strvalue NVARCHAR(255) OUTPUT)
AS
EXTERNAL NAME MyDelphiPrismAssembly1.[MyNameSpace.MyClass].GetSqlVersion

and to see the results

DECLARE @Intvalue int
DECLARE @Strvalue NVARCHAR(255)
EXECUTE  GetSqlVersion @Intvalue OUTPUT , @Strvalue OUTPUT
SELECT @Intvalue Version_Number,@Strvalue Version_Str

This is the full source-code of the Delphi prism class used in this article.

namespace MyNameSpace;

interface
uses
  System,
  System.Data,
  System.Data.SqlClient,
  Microsoft.SqlServer.Server,
  System.Data.SqlTypes;

type
  MyClass  = public class
  public
    //OUTPUT Parameters and CLR Stored Procedures
    [Microsoft.SqlServer.Server.SqlProcedure()]
    class method GetSqlVersion(out Intvalue: SqlInt32;out Strvalue: SqlString);
    //Returning Messages
    [Microsoft.SqlServer.Server.SqlProcedure()]
    class method Hello;
    //Returning Tabular Results directly to the client
    [Microsoft.SqlServer.Server.SqlProcedure()]
    class method GetTables;
    //Creating a dynamic result set and send them to the client
    [Microsoft.SqlServer.Server.SqlProcedure()]
    class method GetRecordResult;
  end;
  
implementation

//OUTPUT Parameters and CLR Stored Procedures
class method MyClass.GetSqlVersion(out Intvalue: SqlInt32;out Strvalue: SqlString);
begin
    Intvalue := 0;
    Strvalue := '';    
    var  connection: SqlConnection := new SqlConnection('context connection=true');
    connection.Open();
    var command: SqlCommand := new SqlCommand('Select @@VERSION,@@MICROSOFTVERSION', connection);
    var reader: SqlDataReader := command.ExecuteReader();
      if reader.Read() then begin
        Strvalue := reader.GetSqlString(0);
        Intvalue := reader.GetSqlInt32(1);
      end
end;

//Returning Messages
class method MyClass.Hello;
begin
     SqlContext.Pipe.Send('Hello From delphi Prism');
end;

//Returning Tabular Results directly to the client
class method MyClass.GetTables;
begin 
    var  connection: SqlConnection := new SqlConnection('context connection=true');
    connection.Open();
    var command: SqlCommand := new SqlCommand(
    'SELECT  so.name [TableName],MAX(si.rows) [RowCount] '+
    'FROM '+ 
    'sysobjects so, '+ 
    'sysindexes si '+ 
    'WHERE '+ 
    'so.xtype = ''U'' '+ 
    'AND '+ 
    'si.id = OBJECT_ID(so.name) '+ 
    'GROUP BY '+ 
    'so.name '+ 
    'ORDER BY '+ 
    '2 DESC', connection);
     // send the result set to the client directly.
     SqlContext.Pipe.ExecuteAndSend(command);   
end;

class method MyClass.GetRecordResult;
begin
      // Create a record object that represents an individual row, including it's metadata.
      var ARecord : SqlDataRecord  := new SqlDataRecord(new SqlMetaData('Col1', SqlDbType.NVarChar, 100),
                                                        new SqlMetaData('Col2', SqlDbType.Int),
                                                        new SqlMetaData('Col3', SqlDbType.Float),
                                                        new SqlMetaData('Col4', SqlDbType.DateTime)
      );    
      // Populate the record.
      ARecord.SetString(0, 'Delphi Prism is cool');      
      ARecord.SetInt32 (1, 1024*1024);      
      ARecord.SetDouble(2, Math.PI);      
      ARecord.SetDateTime(3, DateTime.Now);      
      // Send the record to the client.
      SqlContext.Pipe.Send(ARecord);
end;


end.

the samples showed in this article are very simple, and just pretend show the basic functionalities, you can find many ways to apply this to your own project. personally I use SQL CLR in real life applications to calculate hashes, encrypt data and make complex calculations.

See you.