CLR Proc Sample Project

What is this sample?

This sample is intended to show how to use SQL CLR Procs and Functions with VistaDB. The main sample is an export function that will export the data and schema for a database to disk using a CLR Function. See the CLR Proc Overview for a higher level overview.

There are a number of SQL scripts included that demonstrate how to add, update, and delete an assembly using SQL. The SQL Scripts include structured exception handling and some good concepts to build into your own code for dealing with errors and detecting if the assembly already exists in the database.

The project itself can be found on the public download site and is named VistaDB 4 CLR Proc Sample. There may be more than one version. I have also posted the CLR Proc Sample Project on GitHub to allow users to grab the source, or browse it online.

Intro Video

The video above is the quick into video covering the sample.

Watch this video in HD here: VistaDB CLR Procedure Intro on YouTube

Tools Required

This sample is built in Visual Studio 2010, but targets .Net 2. So you should be able to use the assembly with VistaDB 4.0 or higher to load and execute the SQL CLR procs and functions.

Basic Steps using SQL Scripts

The following steps can be taken to build just the CLR Procs assembly, and then manually load it all into the database using only Data Builder. I highly recommend you do these steps one time just to see how they are done.

  • Compile the MyClrProcExport project (just compile entire solution)
  • Load the SampleDB.VDB4 from the debug folder of the CLRProcSampleRunner.
  • Load the AddAssemblyAndProcs.vsql4 file. Run to load the assembly and assign the functions. Also demonstrates how to update an asssembly using only SQL code.
  • Load the CallCLRProcs.vsql4 to see different ways of calling the functions and procedures.
  • Load the RemoveAssemblyAndProcs.vsql4 to remove all of the methods and then drop the assembly.

Basic CLR Procs Steps with C# Source

The source code allows you to step through and look at the code calls within Visual Studio.

  • Compile the MyClrProcExport project. Set a break point in the main of the CLRProcSampleRunner project (Program.cs).
  • Debug the CLRProcSampleRunner as the startup project.
  • Step through the following functions to each each action:
  • RegistrationRoutines.RegisterAssembly();
  • RegistrationRoutines.RegisterAllMethodsDDA();
  • RegistrationRoutines.ShowRegisteredCLRMethods();
  • CallingRoutines.CallAllSQL();
  • RegistrationRoutines.UnregisterAllMethodsDDA();
  • RegistrationRoutines.UnregisterAssemblyDDA();

I think they are pretty self explanatory, and there are lots of comments in the code.