CLR Integration - SQL Server

Configuring the Server to Enable Usage¶

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¶

[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]));
}

[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("");
    }
}

[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¶

• Provides a Pipe.Send(string output) method to echo a message back to the SQL Server Management Studio window.
• Provides a TriggerContext property
  

SqlTriggerContext class¶

• Provides detailed information about the context in which the trigger was fired
  

SqlConnection¶

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

Deployment¶

Configuring External Access¶

• To access the file system on the database server, you must use a Permission Level of external.
  

• An assembly is authorized for non-safe permission level when either (1) the database owner has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; OR (2) the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.
  
  The first condition is met as follows. This script assumes a user profile called MyNewDatabaseOwner exists on the server and you are working in a database called MyDatabase.
  

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¶

VS 2005 Standard Edition¶

-- 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