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.