Table of Contents [Hide/Show]
Configuring the Server to Enable Usage Writing VS 2005 Professional Edition VS 2005 Standard Edition Useful Classes SqlContext class SqlTriggerContext class SqlConnection Deployment Configuring External Access Option A Option B VS 2005 Professional Edition VS 2005 Standard Edition
ALTER SETTINGS
sysadmin
serveradmin
sp_configure
sp_dbcmptlevel 90 go sp_configure 'clr enabled', 1 go reconfigure go
using
Imports
System.Data.SqlTypes
Microsoft.SqlServer.Server
static
Shared
UserDefinedFunctions
SqlFunction()
StoredProcedures
SqlProcedure()
Triggers
SqlTrigger()
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, CREATE_VIEW, ALTER_VIEW, CREATE_FUNCTION, ALTER_FUNCTION, CREATE_TRIGGER, ALTER_TRIGGER
[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; }
Pipe.Send(string output)
TriggerContext
SqlConnection
SqlConnection conn = new SqlConnection("context connection=true;");
EXTERNAL ACCESS ASSEMBLY
TRUSTWORTHY
MyNewDatabaseOwner
MyDatabase
use master alter database MyDatabase set trustworthy on grant external access assembly to [BUILTIN\Administrators]
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
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
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.