Calling SQL CLR Methods

There are multiple ways to call CLR Procs

This page discusses how to call methods in a CLR Assembly as CLR Functions and CLR Procedures. See the CLR Proc Overview for a high level overview of CLR Procs in general. Make sure you have loaded the CLR Assembly into the database, and Registered the CLR Methods, before doing the steps below.

SQL CLR Functions and Procedures are slightly different

CLR SqlFunctions are the simplest to call because they are meant to modify data like a column or aggregate function. This means you can call them simply by using them in a SELECT statement and the results are returned as a part of the rowset.

CLR SqlProcedures are not quite as easy because all of the results should be returned through parameters. The setup of the parameters is different in SQL and CLR code.

Calling CLR Methods using SQL

The following SQL code executes each of the CLR Functions and CLR Procedures setup in the previous steps. Calling these from SQL code is pretty straightforward.

-- Test Function 1 - ExportSchemaAndData - File will be called test.xml on disk 
-- Call the function and the return value will be returned from the select 
SELECT ExportSchemaAndData( 'test2' ); 
-- Call the function without getting the return value can also be done like this 
-- ExportSchemaAndData( 'test2' ); 
-- Call the Function using a local variable for the result 
DECLARE @exportok AS BIT; 
SELECT @exportok = ExportSchemaAndData( 'test' ); 
IF ( @exportok = 1 ) 
    PRINT 'Data Exported OK'; -- See the LOG output pane for the PRINT output 
ELSE 
    RAISERROR( 'Data Export Failed', 1, 1 ); 
-- Test GetVersionFunction. It will return the database version using ado.net internally 
-- Will display the version in a column named VersionString 
select GetVersionFunction() as VersionString; 
-- THINGS NOT TO DO: 
-- EXEC on a function will not display anything! 
EXEC GetVersionFunction(); 
-- Calling direct will not display anything! 
--GetVersionFunction(); 
-- Test Procedure version of GetVersion 
-- You have to declare the variable for the output and then exec the procedure 
-- then you can select the variable to display it 
DECLARE @versionout AS NVARCHAR(4000); 
EXEC GetVersionProcedure( @versionout ); 
SELECT @versionout as VersionString; 
PRINT 'Version returned: ' + @versionout; 
-- THINGS NOT TO DO: 
-- SELECT on a procedure will return the number of rows affected (0 in this case) 
-- this is usually NOT the desired behavior. 
SELECT GetVersionProcedure(@versionout); 
SELECT @versionout as VersionString; 
-- this still works, but you already returned a result set from the select above

 

The -- lines above are comments and are meant to explain how the SQL code works. PRINT is a command to output the text to the LOG window of Data Builder.

Calling CLR Methods using ADO.Net

SqlFunctions

The following code shows how to execute SqlFunctions from C# code using the ADO.Net provider.

/// <summary> 
/// Call the export schema and data sql function to write out the xml file /// </summary> 
/// <param name="outputFilename">Name of the file to write to disk</param> 
public static void CallExportSchemaAndDataSQL(string outputFilename) 
{ 
    Console.WriteLine("Attempting to execute CLR Proc ExportSchemaAndData"); 
    using (VistaDBConnection connection = new VistaDBConnection()) 
    { 
        connection.ConnectionString = SampleRunner.ConnectionString; 
        connection.Open(); 
    try 
    { 
        using (VistaDBCommand command = new VistaDBCommand()) 
        { 
            // Straight forward way to call a function is just using SELECT            // You cannot EXEC a SqlFunction, and you cannot set the command 
            // here to be a stored proc 
            // Setting this command to a stored proc is a common error,  
            // the two are not the same 
            // SqlFunction = SELECT to call 
            // SqlProcedure = EXEC or direct call using StoredProcedure command type 
            command.Connection = connection; 
            command.CommandText = string.Format("SELECT 
                ExportSchemaAndData('{0}');", 
                    outputFilename); 
            // This command does not return anything in the rowset 
            command.ExecuteNonQuery(); 
         } 
         Console.WriteLine(string.Format("Schema 
             and Data export to {0}\\{1}.xml", 
             Directory.GetCurrentDirectory(), outputFilename)); 
        } 
        catch (Exception e) 
        { 
            Console.WriteLine("Failed to CLR-Proc ExportSchemaAndData, Reason: " 
                + e.Message); 
        } 
        } 
} 
/// <summary> 
/// Call the Sql Function version to get the database version 
/// </summary> 
public static void CallGetDatabaseVersionFunctionSQL() 
{ 
    Console.WriteLine("Attempting to execute CLR Function GetDatabaseVersionFunction"); 
    using (VistaDBConnection connection = 
        new VistaDBConnection(SampleRunner.ConnectionString)) 
    { 
        connection.Open(); 
    try 
    { 
        // Straight forward way to call a function is just using SELECT 
        // You cannot EXEC a SqlFunction, and you cannot set the  
        // command here to be a stored proc 
        // Setting this command to a stored proc is a common error,  
        // the two are not the same 
        // SqlFunction = SELECT to call 
        // SqlProcedure = EXEC or direct call 
        using StoredProcedure command type using (VistaDBCommand command = new VistaDBCommand()) 
        { 
            command.Connection = connection; 
            command.CommandText = "SELECT GetVersionFunction();"; 
            // The results are returned as a part of the standard rowset,  
            // so we only need to get back the first entry 
            Console.WriteLine(Convert.ToString(command.ExecuteScalar())); 
        } 
    } 
    catch (Exception e) 
    { 
        Console.WriteLine("Failed CLR Function GetVersionFunction, Reason: " 
            + e.Message); 
    } 
    } 
}

SqlProcedure

The remaining code shows how to execute a CLR Stored Procedure with a parameter. Remember that this is a special attribute that has to be set on the functions themselves.

/// <summary> 
/// Call the Stored Proc version to get the database version 
/// </summary> 
public static void CallGetDatabaseVersionProcedureSQL() 
{ 
    Console.WriteLine("Attempting to execute CLR Procedure GetVersionProcedure"); 
    using (VistaDBConnection connection = 
        new VistaDBConnection(SampleRunner.ConnectionString)) 
    { 
        connection.Open(); 
    try 
    { 
        // Setup a command against the database like any other command,  
        // but then you have to change the command type 
        // to tell it you are calling a stored proc directly 
        using (VistaDBCommand command = new VistaDBCommand()) 
        { 
            // Use our connection from above 
            command.Connection = connection; 
            // Put the name of the stored proc, you don't need to EXEC.  
            // This command will be called directly as a proc 
            // Be sure to include all the parameters 
            command.CommandText = "GetVersionProcedure(@versionout);"; 
            // Normally this is just text that is being executed 
            command.CommandType = System.Data.CommandType.StoredProcedure; 
            // Build up the parameter to the clr proc 
            VistaDBParameter outparam = new VistaDBParameter(); 
            // This name has to match the entry in the commandtext 
            outparam.ParameterName = "@versionout"; 
            // Telling it that this is an OUTPUT parameter 
            // This is how you should always get values back from a stored proc. 
            // The return value in a stored proc is really only 
            // meant to tell you the number of rows affected, not values. 
            outparam.Direction = System.Data.ParameterDirection.Output; 
            // Add it to the command command.Parameters.Add(outparam); 
            // We are not expecting any return values, and the output  
            // parameters will still be filled out 
            // using ExecuteNonQuery. This saves object setup and  
            // teardown of a reader when we don't need it. 
            command.ExecuteNonQuery(); 
            // Make sure the outparam is not null 
            if (outparam.Value != null) 
            { 
                // Print it to the console 
                Console.WriteLine(Convert.ToString(outparam.Value)); 
            } 
        } 
    } 
    catch (Exception e) 
    { 
        Console.WriteLine("Failed CLR GetVersionProcedure, Reason: " 
            + e.Message); 
     } 
     } 
}

Sample Project

We have a sample CLR Proc project page for more detailed information and links to the current project download.