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 ); GO
Example 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 Class
Example 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
18.191.150.231