Transcript SQL Server 2005: The CLR Integration

SQL Server 2005:
The CLR Integration
What Developers and DBAs need
to know
Who I Am
 Shawn Wildermuth ([email protected])
 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 <Procedure Name>
AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method 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 <Function Name>
(
<Parameter List>
)
RETURNS <Return Type>
AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method 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 <TriggerName>
ON <Table or View> <FOR|INSTEAD OF> <INSERT|UPDATE|DELETE>
AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method Name>
CREATE TRIGGER AddContact
ON author FOR INSERT
AS EXTERNAL NAME ExampleYukon.SqlClr.AddContact
– Also, DDL Triggers
CREATE TRIGGER <TriggerName>
ON <ALL_SERVER or DATABASE> <FOR or AFTER> <EventName>
AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method 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 < ctx.ColumnCount; ++x) {
msg += string.Format("Column {0} {1} been updated{2}",
x,
(ctx.IsColumnsUpdated(x) ? "has" : "has not"),
Environment.NewLine)
}
// Use the message to log it somewhere
}
}
Custom Aggregates
Write your own MIN, MAX, COUNT
– Define a CLR Type that does the aggregation
 Use DML command to register
CREATE AGGREGATE <Aggregate Name>
( @param <SQL Type> )
RETURNS <SQL Type>
EXTERNAL NAME <assembly>.<CLR Type>
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 <=140
GO
EXEC sp_bindrule N'age_range', N'age'
GO
SQL Server 2005
– Can store CLR Type as a Datatype
– Rules are embedded in the CLR Type
Managed User Datatypes (2)
Requirements
–
–
–
–
–
Supports the concept of NULL (INullable)
Supports conversion to and from string
Supports serialization
Supports a default constructor
Type and member conform to naming rules
 (128 character maximum)
Managed User Datatypes (3)
Registering the UDT
– Use CREATE TYPE DML Statement
CREATE TYPE <database type name>
EXTERNAL NAME <assembly name>.<CLR type 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?