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.