Example C# Source for CLR Procs and Functions

What is included in this source?

The source below is included in the CLR Proc Sample Project.  This complete C-Sharp source can be downloaded from the project page.  The source was built using Visual Studio 2010, and targets .net 2.

Each of the following methods is used as a part of the sample to demonstrate different concepts.

• ExportSchemaAndData - SqlFunction that demonstrates exporting all the data and schema to an XML file from a SQL Function
• GetDatabaseVersionFunction - Gets the database version as a SqlFunction.  Used to demonstrate how to call a SqlFunction from SQL and CLR code.
• GetDatabaseVersionProcedure - The same as the above (gets database version), but as a SqlProcedure.  This demonstrates the differnet methods required to call a Stored Procedure instead of a Function.

See the CLR Procedure calling code for how to call each of these from both SQL and CLR methods.

ExportSchemaAndData SqlFunction

/// <summary>
/// This proc uses DDA to get the current context and perform 
/// actions against the database.
/// In this case we are exporting the data and schema to an XML file
/// NOTE: This is a SqlFunction because we are returning the string

/// rather than using an OUTPUT parameter. (See help for more info)
/// </summary>
/// <param name="fileName">Filename target</param>
/// <returns></returns>
[SqlFunction]
public static bool ExportSchemaAndData(string fileName)
{
    try
    {
        //To use DDA within a CLR proc you must create a new 
        // IVistaDBDatabase from the current VistaDBContext using the context
            object.
        // NOTE: DO NOT Dispose this object! It is an internal engine

        // object that you are getting a copy of, you didn't
        // allocate it here, so don't dispose of it.
        IVistaDBDatabase db = VistaDB.VistaDBContext.DDAChannel.CurrentDatabase;
        {
            foreach (string s in db.GetTableNames())
            {
                db.AddToXmlTransferList(s);
            }

            db.ExportXml(string.Format(@"{0}\{1}.xml", 
                Directory.GetCurrentDirectory(), fileName), 
                VistaDBXmlWriteMode.All);
        }
    }
    catch( Exception e )
    {
        throw new ApplicationException("CLR Function failed", e );
    }
    
    return true;
}

GetDatabaseVersionFunction

/// <summary>
/// This proc uses the ADO.NET SQL interface 
/// to execute a command against the database.
/// The command gets the current database version string.
/// NOTE: This is a SqlFunction because we are returning 
/// the string rather than using an OUTPUT parameter. 
/// </summary>
/// <returns></returns>
[SqlFunction]
public static string GetDatabaseVersionFunction()
{
    try
    {
        //To open a SQL connection to VistaDB from within a 
        //CLR Proc you must set the connection string to 
        //Context Connection=true like this....
        //NOTE: We DO want to dispose of this object 
        // because we are the ones allocating it
        using (VistaDBConnection conn = new 
           VistaDBConnection("Context Connection=true"))
        {
            conn.Open();
            using (VistaDBCommand command = new VistaDBCommand())
            {
                command.Connection = conn;
                command.CommandText = "SELECT @@Version";
                return Convert.ToString(command.ExecuteScalar());
            }
        }
    }
    catch (Exception e)
    {
        return e.Message;
    }
}

GetDatabaseVersionProcedure SqlProcedure

Notice that the code looks almost identical except that the method has an OUT param, and is flagged as a SqlProcedure, but the clr proc calling code looks very different.

/// <summary>
/// This proc uses the ADO.NET SQL interface 
/// to execute a command against the database.
/// The command gets the current database version string.
/// </summary>
/// <returns></returns>
[SqlProcedure]
public static int GetDatabaseVersionProcedure(out string versionString )
{
    try
    {
        // To open a SQL connection to VistaDB from within a CLR 
        // Proc you must set the connection string to 
        // Context Connection=true like this....
        // NOTE: We DO want to dispose of this object 
        // because we are the ones allocating it
        using (VistaDBConnection conn = new 
                VistaDBConnection("Context Connection=true"))
        {
            conn.Open();
            using (VistaDBCommand command = new VistaDBCommand())
            {
                command.Connection = conn;
                command.CommandText = "SELECT @@Version";
                versionString = Convert.ToString(command.ExecuteScalar());
                return 0;
            }
        }
    }
    catch (Exception e)
    {
        throw new ApplicationException("Database version error", e);
    }
}

Sample Project

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