VistaDB 5
CLR Methods and Attributes

Methods

Each CLR Method must be a public static, there is no way to allocate an object through SQL (which is how you call these methods).

We rebuilt the way assemblies are loaded and used in VistaDB 4 to make it more compatible with SQL Server 2005/2008. Our attributes now make it possible to have a single codebase for building clr procs and functions for both VistaDB and SQL Server with only a recompile.

Example Using Statements
Copy Code
#if USEVISTADB
using VistaDB.Provider;
using VistaDB.VistaDBTypes;
using NUnit.Framework;
using VistaDB.Compatibility.SqlServer;
#else
using Microsoft.SqlServer.Server;
#endif

Method Attributes

All methods for CLR Procs and CLR Functions must have an attribute on them marking them for consumption.

The standard attributes are:

Method Attributes
Copy Code
[SqlFunction]
public static bool MyFunction();

[SqlProcedure]
public static void MyProcedure();

[SqlTrigger(Name = "MyTrigger", Target = "TargetTable", Event = "FOR UPDATE")]
public static void MyTrigger()

CLR Sql Procedures

SqlProcedure should not have a return type unless it is just the number of rows modified.

Procedures are meant to be used when modifying rows of data in the database. Return values from a SqlProcedure should be accomplished through the use of OUTPUT parameters. The only return value that is accepted is an integer to represent the number of rows modified (should return 0 if nothing was done). This is to enable the CLR SQL Procedure to operate the same as a subquery in an SQL statement.

Calling a SqlProcedure can be accomplished through EXEC. You are executing the procedure. SELECT on a procedure will results in only the number of rows affected being returned, this is usually not the behavior you wanted. Return values from the OUTPUT parameter can then be processed after the EXEC call.

CLR Sql Functions

A SqlFunction is used when a return type is desired from the function that has meaning outside of the number of rows modified. Functions are intended to perform calculations and operations, not modify data rows.

Good uses include complex calculations, pulling data from external sources, and processing values, performing calculations on a per row basis, and using procedural logic to evaluate tabular data expressed as a part of the FROM clause of a select statement.

Think of sql functions just like a function in your normal CLR Code. A static function should not modify the global state (should be stateless) and should only do one thing.

Values from the database are usually passed in as arguments to the function.

T-SQL Functions are often a good candidate to replace with CLR Functions because the logic can be expressed more easily in CLR Code, and will usually perform better in VistaDB for complex operations.

Calling a Sql Function is done through SELECT. You cannot EXEC a function, it is meant to return a value.

CLR Sql Triggers

CLR Sql Triggers are a specialized type of function that is called during an insert, update, or delete operation. These are triggered by the database internally, and the trigger function can be used to validate the changes and cancel them if the operation is not desired.

Good uses include complex ranges of information validation on specialized fields in the database, updating internal tracking or audit logs, writing out sync entries for remote systems, and much more.

VistaDB only supports DML (Data Manipulation Language) triggers on data modification events in the engine. SQL Server also supports DDL (Data Definition Language) triggers for events on create, alter and drop of database schema.

Triggers can determine the data that has been changed as a result of the operation, and reference data that was present prior to the deletion event.

There is a special context available only to triggers called the TriggerContext. This is the way to get access to the special tables INSERTED and DELETED.

Since each trigger operation is essentially a self contained transaction the trigger may cancel the event to prevent it from occurring.

See Also