VistaDB 5
Example CLR Function and CLR Procedure

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 different 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
Copy Code
/// <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
Copy Code
/// <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.

Example Title
Copy Code
/// <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);
    }
}
See Also