When you’re done with the code development, you need to deploy the assembly and the routines into a SQL Server database:
Build the project into an assembly–a .DLL file on disk with the Intermediate Language (IL) code.
Deploy the assembly into a SQL Server database. The IL code is loaded from the .DLL file into the database, and after the load, you no longer need the external file.
Create routines (functions, stored procedures, and triggers) in the SQL Server database. This process essentially registers routines from the assembly, which already resides in the database.
All these steps can be achieved from Visual Studio with an automated process if you’re working with the SQL Server Project template. If you’re working with the Class Library template, from Visual Studio, you can only build the assembly. The deployment will be a manual process in SQL Server using T-SQL CREATE ASSEMBLY | FUNCTION | PROCEDURE | TRIGGER commands.
Here are the step-by-step instructions for both templates:
SQL Server Project template: (Build>Deploy)
Done
Class Library template: (Build>Build)
In SQL Server, run the CREATE ASSEMBLY | FUNCTION | PROCEDURE | TRIGGER code in Example A-4 (C#), Example A-5 (Visual Basic) (relevant only for the Class Library template).
When you’re done with deployment, you can test and use your new routines:
Run test code in Example A-4 (C#), Example A-5 (Visual Basic) without the CREATE statements.
Example A-1. Enable CLR and create CLRUtilities database and T1 table
SET NOCOUNT ON;
USE master;
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO
IF DB_ID('CLRUtilities') IS NOT NULL
DROP DATABASE CLRUtilities;
GO
CREATE DATABASE CLRUtilities;
GO
USE CLRUtilities;
GO
-- Create T1 table
IF OBJECT_ID('dbo.T1') IS NOT NULL
DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1
(
keycol INT NOT NULL PRIMARY KEY,
datacol VARCHAR(10) NOT NULL
);
GOExample A-2. C# code
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Text.RegularExpressions;
using System.Collections;
using System.Collections.Generic;
using System.Diagnostics;
using System.Reflection;
public partial class CLRUtilities
{
// Validate input string against regular expression
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlBoolean fn_RegExMatch(SqlString inpStr,
SqlString regExStr)
{
if (inpStr.IsNull || regExStr.IsNull)
return SqlBoolean.Null;
else
return (SqlBoolean)Regex.IsMatch(inpStr.Value, regExStr.Value ,
RegexOptions.CultureInvariant);
}
// SQL Signature
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlString fn_SQLSigCLR(SqlString inpRawString,
SqlInt32 inpParseLength)
{
if (inpRawString.IsNull)
return SqlString.Null;
int pos = 0;
string mode = "command";
string RawString = inpRawString.Value;
int maxlength = RawString.Length;
StringBuilder p2 = new StringBuilder();
char currchar = ' ';
char nextchar = ' ';
int ParseLength = RawString.Length;
if (!inpParseLength.IsNull)
ParseLength = inpParseLength.Value;
if (RawString.Length > ParseLength)
{
maxlength = ParseLength;
}
while (pos < maxlength)
{
currchar = RawString[pos];
if (pos < maxlength - 1)
{
nextchar = RawString[pos + 1];
}
else
{
nextchar = RawString[pos];
}
if (mode == "command")
{
p2.Append(currchar);
if ((",( =<>!".IndexOf(currchar) >= 0)
&&
(nextchar >= '0' && nextchar <= '9'))
{
mode = "number";
p2.Append('#'),
}
if (currchar == ''')
{
mode = "literal";
p2.Append("#'");
}
}
else if ((mode == "number")
&&
(",( =<>!".IndexOf(nextchar) >= 0))
{
mode = "command";
}
else if ((mode == "literal") && (currchar == '''))
{
mode = "command";
}
pos++;
}
return p2.ToString();
}
// fn_RegExReplace - for generic use of RegEx-based replace
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlString fn_RegExReplace(
SqlString input, SqlString pattern, SqlString replacement)
{
if (input.IsNull || pattern.IsNull || replacement.IsNull)
return SqlString.Null;
else
return (SqlString)Regex.Replace(
input.Value, pattern.Value, replacement.Value);
}
// Compare implicit vs. explicit casting
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static string fn_ImpCast(string inpStr)
{
return inpStr.Substring(2, 3);
}
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlString fn_ExpCast(SqlString inpStr)
{
return (SqlString)inpStr.ToString().Substring(2, 3);
}
// Struct used in string split functions
struct row_item
{
public string item;
public int pos;
}
// Split array of strings and return a table
// FillRowMethodName = "ArrSplitFillRow"
[SqlFunction(FillRowMethodName = "ArrSplitFillRow",
DataAccess = DataAccessKind.None,
TableDefinition = "pos INT, element NVARCHAR(4000) ")]
public static IEnumerable fn_SplitCLR(SqlString inpStr,
SqlString charSeparator)
{
string locStr;
string[] splitStr;
char[] locSeparator = new char[1];
locSeparator[0] = (char)charSeparator.Value[0];
if (inpStr.IsNull)
locStr = "";
else
locStr = inpStr.Value;
splitStr = locStr.Split(locSeparator,
StringSplitOptions.RemoveEmptyEntries);
//locStr.Split(charSeparator.ToString()[0]);
List<row_item> SplitString = new List<row_item>();
int i = 1;
foreach (string s in splitStr)
{
row_item r = new row_item();
r.item = s;
r.pos = i;
SplitString.Add(r);
++i;
}
return SplitString;
}
public static void ArrSplitFillRow(
Object obj, out int pos, out string item)
{
pos = ((row_item)obj).pos;
item = ((row_item)obj).item;
}
// Stored procedure that returns environment info in tabular format
[SqlProcedure]
public static void usp_GetEnvInfo()
{
// Create a record - object representation of a row
// Include the metadata for the SQL table
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("EnvProperty", SqlDbType.NVarChar, 20),
new SqlMetaData("Value", SqlDbType.NVarChar, 256));
// Marks the beginning of the result set to be sent back to the client
// The record parameter is used to construct the metadata
// for the result set
SqlContext.Pipe.SendResultsStart(record);
// Populate some records and send them through the pipe
record.SetSqlString(0, @"Machine Name");
record.SetSqlString(1, Environment.MachineName);
SqlContext.Pipe.SendResultsRow(record);
record.SetSqlString(0, @"Processors");
record.SetSqlString(1, Environment.ProcessorCount.ToString());
SqlContext.Pipe.SendResultsRow(record);
record.SetSqlString(0, @"OS Version");
record.SetSqlString(1, Environment.OSVersion.ToString());
SqlContext.Pipe.SendResultsRow(record);
record.SetSqlString(0, @"CLR Version");
record.SetSqlString(1, Environment.Version.ToString());
SqlContext.Pipe.SendResultsRow(record);
// End of result set
SqlContext.Pipe.SendResultsEnd();
}
// Stored procedure that returns assembly info
// uses Reflection
[SqlProcedure]
public static void usp_GetAssemblyInfo(SqlString asmName)
{
// Retrieve the clr name of the assembly
String clrName = null;
// Get the context
using (SqlConnection connection =
new SqlConnection("Context connection = true"))
{
connection.Open();
using (SqlCommand command = new SqlCommand())
{
// Get the assembly and load it
command.Connection = connection;
command.CommandText =
"SELECT clr_name FROM sys.assemblies WHERE name = @asmName";
command.Parameters.Add("@asmName", SqlDbType.NVarChar);
command.Parameters[0].Value = asmName;
clrName = (String)command.ExecuteScalar();
if (clrName == null)
{
throw new ArgumentException("Invalid assembly name!");
}
Assembly myAsm = Assembly.Load(clrName);
// Create a record - object representation of a row
// Include the metadata for the SQL table
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("Type", SqlDbType.NVarChar, 50),
new SqlMetaData("Name", SqlDbType.NVarChar, 256));
// Marks the beginning of the result set to be sent back
// to the client
// The record parameter is used to construct the metadata
// for the result set
SqlContext.Pipe.SendResultsStart(record);
// Get all types in the assembly
Type[] typesArr = myAsm.GetTypes();
foreach (Type t in typesArr)
{
// Type in a SQL database should be a class or
// a structure
if (t.IsClass == true)
{
record.SetSqlString(0, @"Class");
}
else
{
record.SetSqlString(0, @"Structure");
}
record.SetSqlString(1, t.FullName);
SqlContext.Pipe.SendResultsRow(record);
// Find all public static methods
MethodInfo[] miArr = t.GetMethods();
foreach (MethodInfo mi in miArr)
{
if (mi.IsPublic && mi.IsStatic)
{
record.SetSqlString(0, @" Method");
record.SetSqlString(1, mi.Name);
SqlContext.Pipe.SendResultsRow(record);
}
}
}
// End of result set
SqlContext.Pipe.SendResultsEnd();
}
}
}
// Generic trigger for auditing DML statements
// trigger will write first 200 characters from all columns
// in an XML format to App Event Log
[SqlTrigger(Name = @"trg_GenericDMLAudit", Target = "T1",
Event = "FOR INSERT, UPDATE, DELETE")]
public static void trg_GenericDMLAudit()
{
// Get the trigger context to get info about the action type
SqlTriggerContext triggContext = SqlContext.TriggerContext;
// Prepare the command and pipe objects
SqlCommand command;
SqlPipe pipe = SqlContext.Pipe;
// Check whether the action is Insert
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
// Collect all columns into an XML type, cast it
// to nvarchar and select only a substring from it
// Info from Inserted
command = new SqlCommand(
@"SELECT 'New data: '
+ SUBSTRING(CAST(a.InsertedContents AS NVARCHAR(MAX))
,1,200) AS InsertedContents200
FROM (SELECT * FROM Inserted FOR XML AUTO, TYPE)
AS a(InsertedContents);",
connection);
// Store info collected to a string variable
string msg;
msg = (string)command.ExecuteScalar();
// Write the audit info to the event log
EventLogEntryType entry = new EventLogEntryType();
entry = EventLogEntryType.SuccessAudit;
// Note: if the following line would use
// Environment.MachineName instead of "." to refer to
// the local machine event log, the assembly would need
// the UNSAFE permission set
EventLog ev = new EventLog(@"Application",
".", @"GenericDMLAudit Trigger");
ev.WriteEntry(msg, entry);
// send the audit info to the user
pipe.Send(msg);
}
break;
case TriggerAction.Update:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
// Collect all columns into an XML type,
// cast it to nvarchar and select only a substring from it
// Info from Deleted
command = new SqlCommand(
@"SELECT 'Old data: '
+ SUBSTRING(CAST(a.DeletedContents AS NVARCHAR(MAX))
,1,200) AS DeletedContents200
FROM (SELECT * FROM Deleted FOR XML AUTO, TYPE)
AS a(DeletedContents);",
connection);
// Store info collected to a string variable
string msg;
msg = (string)command.ExecuteScalar();
// Info from Inserted
command.CommandText =
@"SELECT ' // New data: '
+ SUBSTRING(CAST(a.InsertedContents AS NVARCHAR(MAX))
,1,200) AS InsertedContents200
FROM (SELECT * FROM Inserted FOR XML AUTO, TYPE)
AS a(InsertedContents);";
msg = msg + (string)command.ExecuteScalar();
// Write the audit info to the event log
EventLogEntryType entry = new EventLogEntryType();
entry = EventLogEntryType.SuccessAudit;
EventLog ev = new EventLog(@"Application",
".", @"GenericDMLAudit Trigger");
ev.WriteEntry(msg, entry);
// send the audit info to the user
pipe.Send(msg);
}
break;
case TriggerAction.Delete:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
// Collect all columns into an XML type,
// cast it to nvarchar and select only a substring from it
// Info from Deleted
command = new SqlCommand(
@"SELECT 'Old data: '
+ SUBSTRING(CAST(a. DeletedContents AS NVARCHAR(MAX))
,1,200) AS DeletedContents200
FROM (SELECT * FROM Deleted FOR XML AUTO, TYPE)
AS a(DeletedContents);",
connection);
// Store info collected to a string variable
string msg;
msg = (string)command.ExecuteScalar();
// Write the audit info to the event log
EventLogEntryType entry = new EventLogEntryType();
entry = EventLogEntryType.SuccessAudit;
EventLog ev = new EventLog(@"Application",
".", @"GenericDMLAudit Trigger");
ev.WriteEntry(msg, entry);
// send the audit info to the user
pipe.Send(msg);
}
break;
default:
// Just to be sure - this part should never fire
pipe.Send(@"Nothing happened");
break;
}
}
};Example A-3. Visual Basic code
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Text
Imports System.Text.RegularExpressions
Imports System.Collections
Imports System.Collections.Generic
Imports System.Diagnostics
Imports System.Reflection
Imports System.Runtime.InteropServices
Partial Public Class CLRUtilities
' Validate input string against regular expression
<SqlFunction(IsDeterministic:=True, DataAccess:=DataAccessKind.None)> _
Public Shared Function fn_RegExMatch(ByVal inpStr As SqlString, _
ByVal regExStr As SqlString) As SqlBoolean
If (inpStr.IsNull Or regExStr.IsNull) Then
Return SqlBoolean.Null
Else
Return CType(Regex.IsMatch(inpStr.Value, regExStr.Value, _
RegexOptions.CultureInvariant), SqlBoolean)
End If
End Function
' SQL Signature
<SqlFunction(IsDeterministic:=True, DataAccess:=DataAccessKind.None)> _
Public Shared Function fn_SQLSigCLR(ByVal inpRawString As SqlString, _
ByVal inpParseLength As SqlInt32) As SqlString
If inpRawString.IsNull Then
Return SqlString.Null
End If
Dim pos As Integer = 0
Dim mode As String = "command"
Dim RawString As String = inpRawString.Value
Dim maxlength As Integer = RawString.Length
Dim p2 As StringBuilder = New StringBuilder()
Dim currchar As Char = " "c
Dim nextchar As Char = " "c
Dim ParseLength As Integer = RawString.Length
If (Not inpParseLength.IsNull) Then
ParseLength = inpParseLength.Value
End If
If (RawString.Length > ParseLength) Then
maxlength = ParseLength
End If
While (pos < maxlength)
currchar = RawString(pos)
If (pos < maxlength - 1) Then
nextchar = RawString(pos + 1)
Else
nextchar = RawString(pos)
End If
If (mode = "command") Then
p2.Append(currchar)
If ((",( =<>!".IndexOf(currchar) >= 0) _
And _
(nextchar >= "0"c And nextchar <= "9"c)) Then
mode = "number"
p2.Append("#")
End If
If (currchar = "'"c) Then
mode = "literal"
p2.Append("#")
End If
ElseIf ((mode = "number") And _
(",( =<>!".IndexOf(nextchar) >= 0)) Then
mode = "command"
ElseIf ((mode = "literal") And _
(currchar = "'"c)) Then
mode = "command"
End If
pos = pos + 1
End While
Return p2.ToString
End Function
' fn_RegExReplace - for generic use of RegEx-based replace
<SqlFunction(IsDeterministic:=True, DataAccess:=DataAccessKind.None)> _
Public Shared Function fn_RegExReplace( _
ByVal input As SqlString, ByVal pattern As SqlString, _
ByVal replacement As SqlString) As SqlString
If (input.IsNull Or pattern.IsNull Or replacement.IsNull) Then
Return SqlString.Null
Else
Return CType(Regex.Replace( _
input.Value, pattern.Value, replacement.Value), SqlString)
End If
End Function
' Compare implicit vs. explicit casting
<SqlFunction(IsDeterministic:=True, DataAccess:=DataAccessKind.None)> _
Public Shared Function fn_ImpCast(ByVal inpStr As String) As String
Return inpStr.Substring(2, 3)
End Function
<SqlFunction(IsDeterministic:=True, DataAccess:=DataAccessKind.None)> _
Public Shared Function fn_ExpCast(ByVal inpStr As SqlString) As SqlString
Return CType(inpStr.ToString().Substring(2, 3), SqlString)
End Function
'Struct used in string split functions
Structure row_item
Dim item As String
Dim pos As Integer
End Structure
' Split array of strings and return a table
' FillRowMethodName = "ArrSplitFillRow"
<SqlFunction(FillRowMethodName:="ArrSplitFillRow", _
DataAccess:=DataAccessKind.None, _
TableDefinition:="pos INT, element NVARCHAR(4000) ")> _
Public Shared Function fn_SplitCLR(ByVal inpStr As SqlString, _
ByVal charSeparator As SqlString) As IEnumerable
Dim locStr As String
Dim splitStr() As String
Dim locSeparator(0) As Char
locSeparator(0) = CChar(charSeparator.Value(0))
If (inpStr.IsNull) Then
locStr = ""
Else
locStr = inpStr.Value
End If
splitStr = locStr.Split(locSeparator, _
StringSplitOptions.RemoveEmptyEntries)
Dim SplitString As New List(Of row_item)
Dim i As Integer = 1
For Each s As String In splitStr
Dim r As New row_item
r.item = s
r.pos = i
SplitString.Add(r)
i = i + 1
Next
Return SplitString
End Function
Public Shared Sub ArrSplitFillRow( _
ByVal obj As Object, <Out()> ByRef pos As Integer, _
<Out()> ByRef item As String)
pos = CType(obj, row_item).pos
item = CType(obj, row_item).item
End Sub
' Stored procedure that returns environment info in tabular format
<SqlProcedure()> _
Public Shared Sub usp_GetEnvInfo()
' Create a record - object representation of a row
' Include the metadata for the SQL table
Dim record As New SqlDataRecord( _
New SqlMetaData("EnvProperty", SqlDbType.NVarChar, 20), _
New SqlMetaData("Value", SqlDbType.NVarChar, 256))
' Marks the beginning of the result set to be sent back to the client
' The record parameter is used to construct the metadata for
' the result set
SqlContext.Pipe.SendResultsStart(record)
'' Populate some records and send them through the pipe
record.SetSqlString(0, "Machine Name")
record.SetSqlString(1, Environment.MachineName)
SqlContext.Pipe.SendResultsRow(record)
record.SetSqlString(0, "Processors")
record.SetSqlString(1, Environment.ProcessorCount.ToString())
SqlContext.Pipe.SendResultsRow(record)
record.SetSqlString(0, "OS Version")
record.SetSqlString(1, Environment.OSVersion.ToString())
SqlContext.Pipe.SendResultsRow(record)
record.SetSqlString(0, "CLR Version")
record.SetSqlString(1, Environment.Version.ToString())
SqlContext.Pipe.SendResultsRow(record)
' End of result set
SqlContext.Pipe.SendResultsEnd()
End Sub
' Stored procedure that returns assembly info
' uses Reflection
<SqlProcedure()> _
Public Shared Sub usp_GetAssemblyInfo(ByVal asmName As SqlString)
' Retrieve the clr name of the assembly
Dim clrName As String = Nothing
' Get the context
Using connection As New SqlConnection("Context connection = true")
connection.Open()
Using command As New SqlCommand
' Get the assembly and load it
command.Connection = connection
command.CommandText = _
"SELECT clr_name FROM sys.assemblies WHERE name = @asmName"
command.Parameters.Add("@asmName", SqlDbType.NVarChar)
command.Parameters(0).Value = asmName
clrName = CStr(command.ExecuteScalar())
If (clrName = Nothing) Then
Throw New ArgumentException("Invalid assembly name!")
End If
Dim myAsm As Assembly = Assembly.Load(clrName)
' Create a record - object representation of a row
' Include the metadata for the SQL table
Dim record As New SqlDataRecord( _
New SqlMetaData("Type", SqlDbType.NVarChar, 50), _
New SqlMetaData("Name", SqlDbType.NVarChar, 256))
' Marks the beginning of the result set to be sent back
' to the client
' The record parameter is used to construct the metadata
' for the result set
SqlContext.Pipe.SendResultsStart(record)
' Get all types in the assembly
Dim typesArr() As Type = myAsm.GetTypes()
For Each t As Type In typesArr
' Type in a SQL database should be a class or a structure
If (t.IsClass = True) Then
record.SetSqlString(0, "Class")
Else
record.SetSqlString(0, "Structure")
End If
record.SetSqlString(1, t.FullName)
SqlContext.Pipe.SendResultsRow(record)
' Find all public static methods
Dim miArr() As MethodInfo = t.GetMethods
For Each mi As MethodInfo In miArr
If (mi.IsPublic And mi.IsStatic) Then
record.SetSqlString(0, " Method")
record.SetSqlString(1, mi.Name)
SqlContext.Pipe.SendResultsRow(record)
End If
Next
Next
' End of result set
SqlContext.Pipe.SendResultsEnd()
End Using
End Using
End Sub
' Generic trigger for auditing DML statements
' trigger will write first 200 characters from all columns
' in an XML format to App Event Log
<SqlTrigger(Name:="trg_GenericDMLAudit", Target:="T1", _
Event:="FOR INSERT, UPDATE, DELETE")> _
Public Shared Sub trg_GenericDMLAudit()
' Get the trigger context to get info about the action type
Dim triggContext As SqlTriggerContext = SqlContext.TriggerContext
' Prepare the command and pipe objects
Dim command As SqlCommand
Dim pipe As SqlPipe = SqlContext.Pipe
' Check whether the action is Insert
Select Case triggContext.TriggerAction
Case TriggerAction.Insert
' Retrieve the connection that the trigger is using
Using connection _
As New SqlConnection("Context connection = true")
connection.Open()
' Collect all columns into an XML type,
' cast it to nvarchar and select only a substring from it
' Info from Inserted
command = New SqlCommand( _
"SELECT 'New data: ' + " & _
"SUBSTRING(CAST(a.InsertedContents AS NVARCHAR(MAX)" & _
"),1,200) AS InsertedContents200 " & _
"FROM (SELECT * FROM Inserted FOR XML AUTO, TYPE) " & _
"AS a(InsertedContents);", _
connection)
' Store info collected to a string variable
Dim msg As String
msg = CStr(command.ExecuteScalar())
' Write the audit info to the event log
Dim entry As EventLogEntryType
entry = EventLogEntryType.SuccessAudit
' Note: if the following line would use
' Environment.MachineName instead of "." to refer to
' the local machine event log, the assembly would need
' the UNSAFE permission set
Dim ev As New EventLog("Application", _
".", "GenericDMLAudit Trigger")
ev.WriteEntry(msg, entry)
' send the audit info to the user
pipe.Send(msg)
End Using
Case TriggerAction.Update
' Retrieve the connection that the trigger is using
Using connection _
As New SqlConnection("Context connection = true")
connection.Open()
' Collect all columns into an XML type,
' cast it to nvarchar and select only a substring from it
' Info from Deleted
command = New SqlCommand( _
"SELECT 'Old data: ' + " & _
"SUBSTRING(CAST(a.DeletedContents AS NVARCHAR(MAX)" & _
"),1,200) AS DeletedContents200 " & _
"FROM (SELECT * FROM Deleted FOR XML AUTO, TYPE) " & _
"AS a(DeletedContents);", _
connection)
' Store info collected to a string variable
Dim msg As String
msg = CStr(command.ExecuteScalar())
' Info from Inserted
command.CommandText = _
"SELECT ' // New data: ' + " & _
"SUBSTRING(CAST(a.InsertedContents AS NVARCHAR(MAX)" & _
"),1,200) AS InsertedContents200 " & _
"FROM (SELECT * FROM Inserted FOR XML AUTO, TYPE) " & _
"AS a(InsertedContents);"
msg = msg + CStr(command.ExecuteScalar())
' Write the audit info to the event log
Dim entry As EventLogEntryType
entry = EventLogEntryType.SuccessAudit
Dim ev As New EventLog("Application", _
".", "GenericDMLAudit Trigger")
ev.WriteEntry(msg, entry)
' send the audit info to the user
pipe.Send(msg)
End Using
Case TriggerAction.Delete
' Retrieve the connection that the trigger is using
Using connection _
As New SqlConnection("Context connection = true")
connection.Open()
' Collect all columns into an XML type,
' cast it to nvarchar and select only a substring from it
' Info from Deleted
command = New SqlCommand( _
"SELECT 'Old data: ' + " & _
"SUBSTRING(CAST(a.DeletedContents AS NVARCHAR(MAX)" & _
"),1,200) AS DeletedContents200 " & _
"FROM (SELECT * FROM Deleted FOR XML AUTO, TYPE) " & _
"AS a(DeletedContents);", _
connection)
' Store info collected to a string variable
Dim msg As String
msg = CStr(command.ExecuteScalar())
' Write the audit info to the event log
Dim entry As EventLogEntryType
entry = EventLogEntryType.SuccessAudit
Dim ev As New EventLog("Application", _
".", "GenericDMLAudit Trigger")
ev.WriteEntry(msg, entry)
' send the audit info to the user
pipe.Send(msg)
End Using
Case Else
' Just to be sure - this part should never fire
pipe.Send("Nothing happened")
End Select
End Sub
End ClassExample A-4. Deployment and testing of CLR routines, C#
USE CLRUtilities; GO -- Create assembly CREATE ASSEMBLY CLRUtilities FROM 'C:CLRUtilitiesCLRUtilitiesinDebugCLRUtilities.dll' WITH PERMISSION_SET = SAFE; -- If no Debug folder, use instead: -- FROM 'C:CLRUtilitiesCLRUtilitiesinCLRUtilities.dll' GO --------------------------------------------------------------------- -- Scalar Function: fn_RegExMatch --------------------------------------------------------------------- -- Create fn_RegExMatch function CREATE FUNCTION dbo.fn_RegExMatch (@inpstr AS NVARCHAR(MAX), @regexstr AS NVARCHAR(MAX)) RETURNS BIT EXTERNAL NAME CLRUtilities.CLRUtilities.fn_RegExMatch; GO -- Test fn_RegExMatch function SELECT dbo.fn_RegExMatch( N'[email protected]', N'^([w-]+.)*?[w-]+@[w-]+.([w-]+.)*?[w]+$'), GO --------------------------------------------------------------------- -- Scalar Function: fn_SQLSigCLR --------------------------------------------------------------------- -- Create fn_SQLSigCLR function CREATE FUNCTION dbo.fn_SQLSigCLR (@rawstring AS NVARCHAR(4000), @parselength AS INT) RETURNS NVARCHAR(4000) EXTERNAL NAME CLRUtilities.CLRUtilities.fn_SQLSigCLR; GO -- Test fn_SQLSigCLR function SELECT dbo.fn_SQLSigCLR (N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78;', 4000); GO --------------------------------------------------------------------- -- Scalar Function: fn_RegExReplace --------------------------------------------------------------------- -- Create fn_RegExReplace function CREATE FUNCTION dbo.fn_RegExReplace( @input AS NVARCHAR(MAX), @pattern AS NVARCHAR(MAX), @replacement AS NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) WITH RETURNS NULL ON NULL INPUT EXTERNAL NAME CLRUtilities.CLRUtilities.fn_RegExReplace; GO -- Test fn_SQLSigCLR function SELECT dbo.fn_RegExReplace('(123)-456-789', '[^0-9]', ''), GO --------------------------------------------------------------------- -- Scalar Functions: fn_ImpCast, fn_ExpCast --------------------------------------------------------------------- -- Create fn_ImpCast function CREATE FUNCTION dbo.fn_ImpCast(@inpstr AS NVARCHAR(4000)) RETURNS NVARCHAR(4000) EXTERNAL NAME CLRUtilities.CLRUtilities.fn_ImpCast; GO -- Create fn_ExpCast function CREATE FUNCTION dbo.fn_ExpCast(@inpstr AS NVARCHAR(4000)) RETURNS NVARCHAR(4000) EXTERNAL NAME CLRUtilities.CLRUtilities.fn_ExpCast; GO -- Test fn_ImpCast and fn_ExpCast functions SELECT dbo.fn_ImpCast(N'123456'), dbo.fn_ExpCast(N'123456'), GO --------------------------------------------------------------------- -- Table Function: fn_SplitCLR --------------------------------------------------------------------- -- Create fn_SplitCLR function CREATE FUNCTION dbo.fn_SplitCLR (@string AS NVARCHAR(4000), @separator AS NCHAR(1)) RETURNS TABLE(pos INT, element NVARCHAR(4000)) EXTERNAL NAME CLRUtilities.CLRUtilities.fn_SplitCLR; GO -- Test fn_SplitCLR function SELECT pos, element FROM dbo.fn_SplitCLR(N'a,b,c', N','), GO --------------------------------------------------------------------- -- Stored Procedure: usp_GetEnvInfo --------------------------------------------------------------------- -- Database option TRUSTWORTHY needs to be ON for EXTERNAL_ACCESS ALTER DATABASE CLRUtilities SET TRUSTWORTHY ON; GO -- Alter assembly with PERMISSION_SET = EXTERNAL_ACCESS ALTER ASSEMBLY CLRUtilities WITH PERMISSION_SET = EXTERNAL_ACCESS; GO /* -- Safer alternative: -- Create an asymmetric key from the signed assembly -- Note: you have to sign the assembly using a strong name key file USE master; GO CREATE ASYMMETRIC KEY CLRUtilitiesKey FROM EXECUTABLE FILE = 'C:CLRUtilitiesCLRUtilitiesinDebugCLRUtilities.dll'; -- Create login and grant it with external access permission level CREATE LOGIN CLRUtilitiesLogin FROM ASYMMETRIC KEY CLRUtilitiesKey; GRANT EXTERNAL ACCESS ASSEMBLY TO CLRUtilitiesLogin; GO */ -- Create usp_GetEnvInfo stored procedure CREATE PROCEDURE dbo.usp_GetEnvInfo AS EXTERNAL NAME CLRUtilities.CLRUtilities.usp_GetEnvInfo; GO -- Test usp_GetEnvInfo stored procedure EXEC dbo.usp_GetEnvInfo; GO --------------------------------------------------------------------- -- Stored Procedure: usp_GetAssemblyInfo --------------------------------------------------------------------- -- Create usp_GetAssemblyInfo stored procedure CREATE PROCEDURE usp_GetAssemblyInfo @asmName AS sysname AS EXTERNAL NAME CLRUtilities.CLRUtilities.usp_GetAssemblyInfo; GO -- Test usp_GetAssemblyInfo stored procedure EXEC usp_GetAssemblyInfo N'CLRUtilities'; GO --------------------------------------------------------------------- -- Trigger: trg_GenericDMLAudit --------------------------------------------------------------------- -- Create T1 table IF OBJECT_ID('dbo.T1') IS NOT NULL DROP TABLE dbo.T1; GO CREATE TABLE dbo.T1 ( keycol INT NOT NULL PRIMARY KEY, datacol VARCHAR(10) NOT NULL ); GO -- Database option TRUSTWORTHY needs to be ON for EXTERNAL_ACCESS ALTER DATABASE CLRUtilities SET TRUSTWORTHY ON; GO -- Alter assembly with PERMISSION_SET = EXTERNAL_ACCESS ALTER ASSEMBLY CLRUtilities WITH PERMISSION_SET = EXTERNAL_ACCESS; GO /* -- Safer alternative: -- Create an asymmetric key from the signed assembly -- Note: you have to sign the assembly using a strong name key file USE master; GO CREATE ASYMMETRIC KEY CLRUtilitiesKey FROM EXECUTABLE FILE = 'C:CLRUtilitiesCLRUtilitiesinDebugCLRUtilities.dll'; -- Create login and grant it with external access permission level CREATE LOGIN CLRUtilitiesLogin FROM ASYMMETRIC KEY CLRUtilitiesKey; GRANT EXTERNAL ACCESS ASSEMBLY TO CLRUtilitiesLogin; GO */ -- Create trg_T1_iud_GenericDMLAudit trigger CREATE TRIGGER trg_T1_iud_GenericDMLAudit ON dbo.T1 FOR INSERT, UPDATE, DELETE AS EXTERNAL NAME CLRUtilities.CLRUtilities.trg_GenericDMLAudit; GO -- Test trg_GenericDMLAudit trigger INSERT INTO dbo.T1(keycol, datacol) VALUES(1, N'A'), UPDATE dbo.T1 SET datacol = N'B' WHERE keycol = 1; DELETE FROM dbo.T1 WHERE keycol = 1; -- Examine Windows Application Log GO USE master; GO
Example A-5. Deployment and testing of CLR routines, Visual Basic
USE CLRUtilities; GO -- Create assembly CREATE ASSEMBLY CLRUtilities FROM 'C:CLRUtilitiesCLRUtilitiesinDebugCLRUtilities.dll' WITH PERMISSION_SET = SAFE; -- If no Debug folder, use instead: -- FROM 'C:CLRUtilitiesCLRUtilitiesinCLRUtilities.dll' GO --------------------------------------------------------------------- -- Scalar Function: fn_RegExMatch --------------------------------------------------------------------- -- Create fn_RegExMatch function CREATE FUNCTION dbo.fn_RegExMatch (@inpstr AS NVARCHAR(MAX), @regexstr AS NVARCHAR(MAX)) RETURNS BIT EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].fn_RegExMatch; GO -- Test fn_RegExMatch function SELECT dbo.fn_RegExMatch( N'[email protected]', N'^([w-]+.)*?[w-]+@[w-]+.([w-]+.)*?[w]+$'), GO --------------------------------------------------------------------- -- Scalar Function: fn_SQLSigCLR --------------------------------------------------------------------- -- Create fn_SQLSigCLR function CREATE FUNCTION dbo.fn_SQLSigCLR (@rawstring AS NVARCHAR(4000), @parselength AS INT) RETURNS NVARCHAR(4000) EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].fn_SQLSigCLR; GO -- Test fn_SQLSigCLR function SELECT dbo.fn_SQLSigCLR (N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78;', 4000); GO --------------------------------------------------------------------- -- Scalar Function: fn_RegExReplace --------------------------------------------------------------------- -- Create fn_RegExReplace function CREATE FUNCTION dbo.fn_RegExReplace( @input AS NVARCHAR(MAX), @pattern AS NVARCHAR(MAX), @replacement AS NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) WITH RETURNS NULL ON NULL INPUT EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].fn_RegExReplace; GO -- Test fn_SQLSigCLR function SELECT dbo.fn_RegExReplace('(123)-456-789', '[^0-9]', ''), GO --------------------------------------------------------------------- -- Scalar Functions: fn_ImpCast, fn_ExpCast --------------------------------------------------------------------- -- Create fn_ImpCast function CREATE FUNCTION dbo.fn_ImpCast(@inpstr AS NVARCHAR(4000)) RETURNS NVARCHAR(4000) EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].fn_ImpCast; GO -- Create fn_ExpCast function CREATE FUNCTION dbo.fn_ExpCast(@inpstr AS NVARCHAR(4000)) RETURNS NVARCHAR(4000) EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].fn_ExpCast; GO -- Test fn_ImpCast and fn_ExpCast functions SELECT dbo.fn_ImpCast(N'123456'), dbo.fn_ExpCast(N'123456'), GO --------------------------------------------------------------------- -- Table Function: fn_SplitCLR --------------------------------------------------------------------- -- Create fn_SplitCLR function CREATE FUNCTION dbo.fn_SplitCLR (@string AS NVARCHAR(4000), @separator AS NCHAR(1)) RETURNS TABLE(pos INT, element NVARCHAR(4000)) EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].fn_SplitCLR; GO -- Test fn_SplitCLR function SELECT pos, element FROM dbo.fn_SplitCLR(N'a,b,c', N','), GO --------------------------------------------------------------------- -- Stored Procedure: usp_GetEnvInfo --------------------------------------------------------------------- -- Database option TRUSTWORTHY needs to be ON for EXTERNAL_ACCESS ALTER DATABASE CLRUtilities SET TRUSTWORTHY ON; GO -- Alter assembly with PERMISSION_SET = EXTERNAL_ACCESS ALTER ASSEMBLY CLRUtilities WITH PERMISSION_SET = EXTERNAL_ACCESS; GO /* -- Safer alternative: -- Create an asymmetric key from the signed assembly -- Note: you have to sign the assembly using a strong name key file USE master; GO CREATE ASYMMETRIC KEY CLRUtilitiesKey FROM EXECUTABLE FILE = 'C:CLRUtilitiesCLRUtilitiesinDebugCLRUtilities.dll'; -- Create login and grant it with external access permission level CREATE LOGIN CLRUtilitiesLogin FROM ASYMMETRIC KEY CLRUtilitiesKey; GRANT EXTERNAL ACCESS ASSEMBLY TO CLRUtilitiesLogin; GO */ -- Create usp_GetEnvInfo stored procedure CREATE PROCEDURE dbo.usp_GetEnvInfo AS EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].usp_GetEnvInfo; GO -- Test usp_GetEnvInfo stored procedure EXEC dbo.usp_GetEnvInfo; GO --------------------------------------------------------------------- -- Stored Procedure: usp_GetAssemblyInfo --------------------------------------------------------------------- -- Create usp_GetAssemblyInfo stored procedure CREATE PROCEDURE usp_GetAssemblyInfo @asmName AS sysname AS EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].usp_GetAssemblyInfo; GO -- Test usp_GetAssemblyInfo stored procedure EXEC usp_GetAssemblyInfo N'CLRUtilities'; GO --------------------------------------------------------------------- -- Trigger: trg_GenericDMLAudit --------------------------------------------------------------------- -- Create T1 table IF OBJECT_ID('dbo.T1') IS NOT NULL DROP TABLE dbo.T1; GO CREATE TABLE dbo.T1 ( keycol INT NOT NULL PRIMARY KEY, datacol VARCHAR(10) NOT NULL ); GO -- Database option TRUSTWORTHY needs to be ON for EXTERNAL_ACCESS ALTER DATABASE CLRUtilities SET TRUSTWORTHY ON; GO -- Alter assembly with PERMISSION_SET = EXTERNAL_ACCESS ALTER ASSEMBLY CLRUtilities WITH PERMISSION_SET = EXTERNAL_ACCESS; GO /* -- Safer alternative: -- Create an asymmetric key from the signed assembly -- Note: you have to sign the assembly using a strong name key file USE master; GO CREATE ASYMMETRIC KEY CLRUtilitiesKey FROM EXECUTABLE FILE = 'C:CLRUtilitiesCLRUtilitiesinDebugCLRUtilities.dll'; -- Create login and grant it with external access permission level CREATE LOGIN CLRUtilitiesLogin FROM ASYMMETRIC KEY CLRUtilitiesKey; GRANT EXTERNAL ACCESS ASSEMBLY TO CLRUtilitiesLogin; GO */ -- Create trg_T1_iud_GenericDMLAudit trigger CREATE TRIGGER trg_T1_iud_GenericDMLAudit ON dbo.T1 FOR INSERT, UPDATE, DELETE AS EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].trg_GenericDMLAudit; GO -- Test trg_GenericDMLAudit trigger INSERT INTO dbo.T1(keycol, datacol) VALUES(1, N'A'), UPDATE dbo.T1 SET datacol = N'B' WHERE keycol = 1; DELETE FROM dbo.T1 WHERE keycol = 1; -- Examine Windows Application Log GO USE master; GO
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.