SQL Server 2005:
The CLR Integration
What Developers and DBAs need
to know
Who I Am
Shawn Wildermuth (swildermuth@adoguy.com)
Senior Consultant/Architect with Magenic
Technologies (http://www.magenic.com)
C# MVP
INETA Speaker
Book Author
– “Pragmatic ADO.NET”;
Editor of http://ONDotnet.com
This Presentation can be found at:
– http://adoguy.com/presentations
Agenda
CLR Hosting Environment
Assembly Management
Managed Stored Procedures and Functions
InProc Managed Provider
Database connections?
Table Valued Functions
Managed Triggers
Managed Datatypes
Custom Aggregates
CLR Hosting Environment
In .NET 1.1
– Designed for IIS
– Processes could be killed
– Freely creating memory and threads
In .NET 2.0
– Designed for SQL Server 2005
– Processes cannot be killed
– Host can refuse memory or thread creation
– Security critical
Assembly Management
DML Command to add an Assembly
– Can be from a file
But has to have permissions (NT Security)
CREATE ASSEMBLY ExampleYukon
FROM 'd:\ExampleYukon.dll'
– Alternatively, can be a bitstream
CREATE ASSEMBLY ExampleYukon
FROM 0x4D5A90000300000004000000FFFF0000B8000000000000...
– No In-Engine Compilation
Assembly Management (2)
Code Access Security (CAS) for Assemblies
– Three CAS Buckets
SAFE
– Access to the CLR only
– No access to external resources, thread management,
unsafe code or interop
EXTERNAL_ACCESS
– Access to external systems through the .NET Framework
– E.g. EventLog, FileSystem and Network
– No access unsafe or interop
UNSAFE
– No restrictions; similar to extended stored procedures
Assembly Management (3)
Use DML Assembly Commands for CAS
CREATE ASSEMBLY ExampleYukon
FROM 'd:\ExampleYukon.dll'
WITH PERMISSION_SET=SAFE
CREATE ASSEMBLY ExampleYukon
FROM 'd:\ExampleYukon.dll'
WITH PERMISSION_SET=EXTERNAL_ACCESS
CREATE ASSEMBLY ExampleYukon
FROM 'd:\ExampleYukon.dll'
WITH PERMISSION_SET=UNSAFE
Managed Code
Code isn’t available by default
– Must register functions, stored procs, etc.
– Code is not available by default
– Registration takes certain permissions to allow
Managed Code (2)
Attributes
– Hints to VS about how to Deploy
[SqlProcedure]
[SqlFunction]
[SqlUserDefinedType]
[SqlUserDefinedAggregate]
Etc.
– Also used at runtime for behaviors of objects
E.g. SqlMethod(IsMutable=true)
Managed Stored Procedures
To expose a Stored Procedure:
– The containing class must be public
– The exposed method must be public
– The exposed method must be static
public class SqlClr {
public static void MyProc() {
// Put your code here
}
}
Managed Stored Procedures (2)
Managed Stored Procedure DML
– Uses the CREATE PROCEDURE call
– Adds AS EXTERNAL NAME to specify CLR SP
CREATE PROCEDURE
AS EXTERNAL NAME ..
CREATE PROCEDURE MyProc
AS EXTERNAL NAME ExampleYukon.SqlClr.MyProc
Managed Stored Procedures (3)
Parameters
– Must match DML Command:
// Input Parameter
public static void InputProcedure(int number) {
}
// Output Parameter
public static void OutputProcedure(out int number) {
number = 42;
}
// In/Out Parameter
public static void InOutProcedure(ref int number) {
number = 42;
}
// Return Parameter
public static int ReturnProcedure() {
return 42;
}
Managed Stored Procedures (4)
Parameters (continued)
– DML must match the parameters:
CREATE PROCEDURE InputProcedure
@number int
AS EXTERNAL NAME ExampleYukon.SqlClr.InputProcedure
CREATE PROCEDURE OutputProcedure
@number int OUTPUT
AS EXTERNAL NAME ExampleYukon.SqlClr.OutputProcedure
CREATE PROCEDURE InOutProcedure
@number int OUTPUT
AS EXTERNAL NAME ExampleYukon.SqlClr.InOutProcedure
-- NOTE: You don’t specify ReturnParameters
CREATE PROCEDURE ReturnProcedure
AS EXTERNAL NAME ExampleYukon.SqlClr.ReturnProcedure
Managed Functions
Similar to Stored Procedures
– Uses similar DML Syntax:
CREATE FUNCTION
(
)
RETURNS
AS EXTERNAL NAME ..
CREATE FUNCTION Addition
(
@x int,
@y int
)
RETURNS int
AS EXTERNAL NAME ExampleYukon.SqlClr.Addition
Managed Functions (2)
Code is similar to Stored Procedures
[SqlFunction]
public static int Addition(int x, int y) {
return x + y;
}
– [SqlFunction] Parameters
Helps SQL Server know what the function does
– IsDeterministic
– IsPrecise
– DataAccess
– SystemDataAccess
Managed Triggers
Also like Stored Procedures
– DML Triggers
CREATE TRIGGER
ON
AS EXTERNAL NAME ..
CREATE TRIGGER AddContact
ON author FOR INSERT
AS EXTERNAL NAME ExampleYukon.SqlClr.AddContact
– Also, DDL Triggers
CREATE TRIGGER
ON
AS EXTERNAL NAME ..
CREATE TRIGGER AddUser
ON DATABASE FOR CreateUser
AS EXTERNAL NAME ExampleYukon.SqlClr.AddUser
Managed Triggers (2)
TriggerContext for Trigger Info
– ColumnsUpdates to see what columns changed
public static void AddAuthor() {
SqlTriggerContext ctx = SqlContext.TriggerContext;
if (ctx.TriggerAction == TriggerAction.Insert) {
string msg = "";
// Get the data associated with Event
for (int x = 0; x
( @param )
RETURNS
EXTERNAL NAME .
CREATE AGGREGATE CustomAverage
( @value float )
RETURNS float
EXTERNAL NAME ExampleYukon.CustomAverage
Custom Aggregates (2)
CLR Class
– Class is called during the aggregation
– Not just passed a set of values, but one at a time
– Must be serializable (for intermediate results)
– Must implement known methods
Init
– Called at the start of the aggregation of fields
Accumulate
– Called once for each row being aggregated. It is passed the value
from the column being aggregated.
Merge
– Called to merge a temporary result into the aggregate
Terminate
– Called at the end of the aggregation process to retrieve the result
of the aggregation.
Custom Aggregates (3)
Custom Average Example:
[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 8000)]
public class CustomAverage : IBinarySerialize {
SqlDouble _total = 0;
ulong _totalCount = 0;
public void Init() { }
public void Accumulate(SqlDouble Value) {
++_totalCount;
if (_total.IsNull) _total = 0;
_total += Value;
}
public void Merge(StdDev grp) {/*...*/ }
public SqlDouble Terminate() { return _total/_totalCount; }
void IBinarySerialize.Read(BinaryReader r) { /*...*/ }
void IBinarySerialize.Write(BinaryWriter w) { /*...*/ }
}
InProc Managed Provider
Inside the Server, a new Provider
– Very similar to SqlClient Provider
– Follows Managed Provider Model
– Supports
IDbConnection
IDbCommand
IDbDataReader
Etc.
InProc Managed Provider (2)
Dude, where’s my Connection?
– In Out-of-proc ADO.NET:
using (SqlConnection conn = new SqlConnection("..."))
using (SqlCommand cmd = conn.CreateCommand()) {
try {
cmd.CommandText = "...";
conn.Open();
using (SqlDataReader rdr = cmd.ExecuteReader()) {
while (rdr.Read()) {
Console.WriteLine(rdr.GetString(0));
}
} // Reader is disposed
} finally {
conn.Close()
}
} // using statements ensure Disposed is called
InProc Managed Provider (3)
InProc you can assume the connection
using (SqlCommand cmd = new SqlCommand()) {
cmd.CommandText = "...";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
InProc Managed Provider (4)
If you need a connection
– Can create additional connections
– Use “context connection=true” for current
using (SqlConnection conn =
new SqlConnection("context connection=true"))
using (SqlCommand cmd = conn.CreateCommand()) {
cmd.CommandText = @"SELECT * FROM Sales.SalesOrderHeader";
try {
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
total += Convert.ToDecimal(rdr["TotalDue"]);
} finally {
conn.Close();
}
}
InProc Managed Provider (5)
SqlContext
– Used to get active server objects
Pipe
WindowsIdentity
Etc.
InProc Managed Provider (6)
Returning Data
– Use a Pipe to send data back to the client
Use the SqlContext’s Pipe
Use Execute() to Fire a Command into the pipe
Use Send() to send results back from a Reader
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM Customer";
// Send the results to the client
SqlPipe pip = SqlContext.Pipe.Execute(cmd);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM Customer";
SqlDataReader rdr = cmd.ExecuteReader();
// Send the results to the client
SqlContext.Pipe.Send(rdr);
InProc Managed Provider (7)
Returning Custom Results
– Use SqlDataRecord
Must create SqlMetaData to describe the result
SqlMetaData[] columns = new SqlMetaData[3];
columns[0] = new SqlMetaData("ID", SqlDbType.Int);
columns[1] = new SqlMetaData("Name", SqlDbType.NVarChar, 50);
columns[2] = new SqlMetaData("theDate", SqlDbType.DateTime);
SqlDataRecord record = new SqlDataRecord(columns);
record.SetInt32(0, 1);
record.SetString(1, "Bob Higgins");
record.SetDateTime(2, DateTime.Now);
SqlContext.Pipe.Send(record);
InProc Managed Provider (8)
Return Custom Results (Multiple Rows)
– Use SendResultsStart to begin
– Must send all rows with SendResultsRow
– Must end with SendResultsEnd
// Start it
SqlContext.Pipe.SendResultsStart(record); // Only primes pump
SqlContext.Pipe.SendResultsRow(record);
SqlDataRecord anotherRecord = new SqlDataRecord(columns);
anotherRecord.SetInt32(0, 2);
anotherRecord.SetString(1, "Mary Roberts");
anotherRecord.SetDateTime(2, DateTime.Now.AddDays(1));
SqlContext.Pipe.SendResultsRow(anotherRecord);
// Finish ResultSet
SqlContext.Pipe.SendResultsEnd();
InProc Managed Provider (9)
SQL Server Datatypes
– Use System.Data.SqlTypes namespace
– Each SqlXXXXX type is INullable
– Allows Stored Procs that allow DB Nulls
public static void GetContact(SqlString email) {
SqlCommand cmd = new SqlCommand();
cmd.CommandText =
@"SELECT FirstName, LastName
FROM Person.COntact
WHERE EmailAddress = @email";
cmd.Parameters.Add("@email", SqlDbType.NVarChar, 50);
cmd.Parameters["@email"].Value = email;
SqlContext.Pipe.ExecuteAndSend(cmd);
}
InProc Managed Provider (7)
Updating Data
– No Managed Cursor support in Whidbey
– Must do Insert/Update/Delete as necessary
Managed User Datatypes
Pre-SQL Server 2005
– User Datatypes were aliases or restrictions
EXEC sp_addtype N'age', N'tinyint', N'not null'
GO
CREATE RULE age_range
AS
@age >= 0 AND @age
EXTERNAL NAME .
CREATE TYPE Point
EXTERNAL NAME ExampleYukon.Point
Managed User Datatypes (4)
Example:
[Serializable]
[SqlUserDefinedType(Format.UserDefined, MaxByteSize=8)]
public class Point : INullable, IBinarySerialize {
bool isNull = false;
int x;
int y;
// Required constructor
public Point() : this(0, 0) {
}
public Point(int x, int y) {
this.x = x;
this.y = y;
}
// ...
}
Managed User Datatypes (5)
Using a UDT
– Use CONVERT to switch from string to type
DECLARE @p as dbo.Point
SET @p = Convert(dbo.Point, '3,8')
SELECT @p
Managed User Datatypes (5)
Datatype methods
– Marking it with SqlMethod can expose it
IsMutator shows SQL Server that it changes itself:
[SqlMethod(IsMutator=true)]
public void Swap() {
int temp = x;
x = y;
y = temp;
}
Can use with the point syntax:
DECLARE @p as dbo.Point
SET @p = Convert(dbo.Point, '3,8')
SELECT @p
@p.Swap()
SELECT @p
Questions?