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; } } |
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); } } |