CLR Integration - SQL Server




Configuring the Server to Enable Usage

To enable CLR integration, you must have the ALTER SETTINGS server-level permission, which is implicitly held by members of the sysadmin and serveradmin fixed server roles. To view the current setting, use sp_configure. To enable CLR integration, use the following script.

sp_dbcmptlevel 90
go
sp_configure 'clr enabled', 1
go
reconfigure
go

Writing

VS 2005 Professional Edition

  1. Create/add new project and select one of the following project types
    • Visual Basic > Database > SQL Server Project
    • C# > Database > SQL Server Project
  2. On the Project menu, select one of the following
    • Add User-Defined Function
    • Add Stored Procedure
    • Add Aggregate
    • Add Trigger
    • Add User-Defined Type

VS 2005 Standard Edition

1. Create/add a new project using a project type of Class Library

2. For each class you create reference (using in C#; Imports in VB) the System.Data.SqlTypes and Microsoft.SqlServer.Server namespaces.

3. For each object you want to expose to the database, you must place the code in a static (Shared in VB) method in a static (Shared in VB) class with an appropriate attribute on the method, as indicated below. (The names of these classes seems to be fixed.)

ItemClass NameMethod Attribute
User Defined FunctionsUserDefinedFunctionsSqlFunction()
Stored ProceduresStoredProceduresSqlProcedure()
TriggersTriggersSqlTrigger()

4. Parameters for the SqlTrigger() attribute

a) Target parameter = table name or "database"

b) Event parameter = one of the following: "FOR CREATE_PROCEDURE, ALTER_PROCEDURE, CREATE_VIEW, ALTER_VIEW, CREATE_FUNCTION, ALTER_FUNCTION, CREATE_TRIGGER, ALTER_TRIGGER".

5. Table-Valued Function Example

[SqlFunction(FillRowMethodName="FillRow", DataAccess=DataAccessKind.Read)]
public static IEnumerable GetNewBalances(SqlGuid accountId)
{
	//- Inits --------------------------------------------------------------
	decimal runningSum = 0;
	DataTable result = new DataTable();
	result.Columns.Add(new DataColumn("TransactionId", typeof(Guid)));
	result.Columns.Add(new DataColumn("NewBalance", typeof(Decimal)));

	//-- Load Account Transactions -----------------------------------------
	SqlConnection conn = new SqlConnection("context connection=true;");
	SqlCommand cmd = new SqlCommand("GetAccountTransactionsSorted", conn);
	cmd.CommandType = CommandType.StoredProcedure;
	cmd.Parameters.AddWithValue("@accountId", accountId);
	conn.Open();
	SqlDataReader reader = cmd.ExecuteReader();

	//- Loop Through Rows, Calculating the New Balance as We Go ------------
	while (reader.Read())
	{
		decimal signedAmount = (decimal)reader[1];
		runningSum += signedAmount;

		DataRow newRow = result.NewRow();
		newRow["TransactionId"] = (Guid)reader[0];
		newRow["NewBalance"] = runningSum;

		result.Rows.Add(newRow);
	}
	return result.Rows;
}
public static void FillRow(Object dataRow, out SqlGuid transactionId, out SqlMoney 
newBalance)
{
	transactionId = new SqlGuid((Guid)(((DataRow)dataRow).ItemArray[0]));
	newBalance = new SqlMoney((Decimal)(((DataRow)dataRow).ItemArray[1]));
}

6. Aggregates: use the following template

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
public struct Aggregate1
{
    // This is a place-holder member field
    private int var1;
    public void Init()
    {
        // Put your code here
    }
    public void Accumulate(SqlString Value)
    {
        // Put your code here
    }
    public void Merge(Aggregate1 Group)
    {
        // Put your code here
    }
    public SqlString Terminate()
    {
        // Put your code here
        return new SqlString("");
    }
}

7. User Defined Types: use the following template

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct Type1 : INullable
{
    public override string ToString()
    {
        // Replace the following code with your code
        return "";
    }
    public bool IsNull
    {
        get
        {
            // Put your code here
            return m_Null;
        }
    }
    public static Type1 Null
    {
        get
        {
            Type1 h = new Type1();
            h.m_Null = true;
            return h;
        }
    }
    public static Type1 Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        Type1 u = new Type1();
        // Put your code here
        return u;
    }
    // This is a place-holder method
    public string Method1()
    {
        //Insert method code here
        return "Hello";
    }
    // This is a place-holder static method
    public static SqlString Method2()
    {
        //Insert method code here
        return new SqlString("Hello");
    }
    // This is a place-holder field member
    public int var1;
    // Private member
    private bool m_Null;
}

Useful Classes

SqlContext class


SqlTriggerContext class


SqlConnection

A SqlConnection object can refer to the existing connection as follows.

SqlConnection conn = new SqlConnection("context connection=true;");

Deployment

Configuring External Access

If you want to deploy your assembly with any Permission Level but safe, you must configure the database accordingly.



Option A

use master
alter database MyDatabase set trustworthy on
grant external access assembly to [BUILTIN\Administrators]

Option B

use master
alter database [MyDatabase] set trustworthy on

use [MyDatabase]
-- For this next line to work, a Server Login called 
-- 'MyNewDatabaseOwner' must exist.
exec sp_changedbowner 'MyNewDatabaseOwner'

use master
grant external access assembly to MyNewDatabaseOwner

VS 2005 Professional Edition

1. On the project's Property page, navigate to the Database tab

2. Set the Connection String, Permission Level, and Assembly Owner according to how and where you want to deploy the assembly and its objects.

3. On the Build menu, click Deploy your-project-name.

VS 2005 Standard Edition

Use this script template. The "three-part-name" is the form: AssemblyName.[Namespace.ClassName].MethodName.

-- Dropping a trigger ----------------------------------------------------
if  exists (select * from sys.triggers where name = N'{trigger-name}' and 
parent_class=0)

drop trigger [{trigger-name}] on database
go

-- Dropping an assembly (all its objects must be dropped first) ----------
if  exists (select * from sys.assemblies asms where asms.name = 
N'{assembly-name}')

drop assembly [{assembly-name}]

go

-- Creating an assembly --------------------------------------------------
create assembly {assembly-name} from '{full-path-to-DLL}' 
with permission_set={external_access | safe | unsafe}
go

-- Creating a function ---------------------------------------------------
-- Note: The SqlString data type corresponds to NVARCHAR(4000)
create function SortLetters(@inputText nvarchar(4000)) returns 
nvarchar(4000) with execute as caller as external name {three-part-name}.
go

-- Creating a trigger ----------------------------------------------------
create trigger {trigger-name} on database
for create_procedure, alter_procedure, create_view, alter_view, create_function, 
alter_function, create_trigger, alter_trigger as external name {three-part-name}
go

-- Enabling a trigger ----------------------------------------------------
enable trigger {trigger-name} on database
go

Events for the CREATE TRIGGER statement

a) Database-Level events that can be audited

1) DDL Table events: Create table, Alter table, Drop table
2) DDL view events : Create view, Alter view, Drop view
3) DDL trigger events :Create trigger, Drop trigger, Alter trigger
4) DDL synonym events: Create synonym, drop synonym
5) DDL Index events: Create index, Alter index, Drop Index
6) DDL Database level security events:
7) Create User, Drop user, Alter user
8) Create role, Drop role, Alter role
9) Create application role, Drop application role, Alter Application role
10) Create Schema, Drop Schema, Alter Schema
11) Grant database access, Revoke database access, Deny Database access
12) DDL Service broker events:
13) Create Message type, Alter Message type, Drop Message type
14) Create contract, Drop contract, Alter contract
15) Create Service, Alter service, Drop Service
16) Create route, Drop route, Alter route

b) Server-Level events that can be audited

1) Create Database, Drop Database
2) Create Login, Drop Login, Alter Login