VistaDB 6
In This Topic
    Example CLR Function and CLR Procedure
    In This Topic

    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