VistaDB 5
Calling CLR Code using ADO.NET Objects

Calling CLR Methods using ADO.Net SqlFunctions

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

Calling CLR Methods using ADO.Net SqlFunctions
Copy Code
/// <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.

SqlProcedure
Copy Code
/// <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);
    }
    }
} 

See Also