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.