SQL Server 2005 CLR Integration

SQL Server 2005CLR Integration Matthew Roche

Session Overview • Programmability Options in SQL Server • SQLCLR Architecture • Creating Managed Objects • Stored Procedures, Functions and Triggers • Data Access • User Defined Types • User Defined Aggregates • Best Practices

Programmability Options in SQL Server • Transact-SQL • Set-based • Cursors • Extended Stored Procedures • OLE Automation (sp_oe* procedures) • SQLCLR

SQLCLR Architecture • Design Goals • Security • Reliability • Performance • SQL Server as a CLR Host • CorBindToRuntimeEx() • ICLRRuntimeHost • IHostControl

Creating Managed Objects • Cataloging assemblies • Using Transact-SQL • Using Visual Studio 2005 • Interface and data type restrictions • Reference restrictions and the HostProtectionAttribute • Assembly permission sets (SAFE, EXTERNAL_ACCESS, UNSAFE)

Creating Assemblies DEMO

Data Types • Many .NET scalar types are valid as parameters and return values, but cannot handle NULLs • System.Data.SqlTypes are preferred • Isomorphic with native SQL Server data types • Some differences (DateTime, Decimal) • Use any .NET types internally, within restrictions of permission set

Stored Procedures • Static public int or void method • Microsoft.SqlServer.Server.SqlProcedure attribute [SqlProcedure] public static void StoredProcedure() { // Put your code here }

Creating Stored Procedures DEMO

Scalar Functions • Static public method • Scalar return type • Microsoft.SqlServer.Server.SqlFunction attribute [SqlFunction] public static SqlString ScalarFunction() { return new SqlString("Hello"); }

Table-Valued Functions • Static public method • IEnumerable return type • Microsoft.SqlServer.Server.SqlFunction attribute – FillRowMethodName parameter [SqlFunction (FillRowMethodName="FillRow")] public static IEnumerable TableValuedFunction() { // Put your code here return new string[] {"Syracuse", "Rochester"...}; } public static void FillRow(object o, out string name) { name = (string)o; }

Creating Functions DEMO

Triggers • Public static void method • Microsoft.SqlServer.Server.SqlTrigger attribute • TriggerContext object available through SqlContext object [SqlTrigger (Name="Emp_UPD", Target="emp", Event="FOR UPDATE")] public static void Trigger() { if (SqlContext.TriggerContext.IsUpdatedColumn(0)) { //... } }

User Defined Types • Public class or struct • Must implement INullable • Well-defined “interface” public override string ToString(){return "";} public bool IsNull{ get{ return m_Null; }} public static UserDefinedType Null{ get{ return new UserDefinedType(); }} public static UserDefinedType Parse(SqlString s){ UserDefinedType u = new UserDefinedType(); return u;}

User Defined Aggregates • Public class or struct; well-defined “interface” • System.Serializable attribute • Microsoft.SqlServer.Server. SqlUserDefinedAggregate attribute • Serialization options [Serializable] [SqlUserDefinedAggregate(Format.Native)] public struct Aggregate{ public void Init(){} public void Accumulate(SqlString Value){} public void Merge(Aggregate Group){} public SqlString Terminate(){ return new SqlString("");} }

Data Access • Server-side programming model very similar to client-side programming model • Good or bad? • Using the managed data provider and the context connection • Sending data to the client using SqlPipe • Describing data using SqlMetaData

Using the Managed Provider DEMO

Best Practices • Use Transact-SQL for all data access • Call T-SQL stored procedures from managed code • Use set-based operations • Use SQLCLR procedures for problems that cannot be solved using T-SQL • Complex math • Complex string manipulation (RegEx) • External resource access • Replace XPs, not T-SQL • Use SqlTypes for all visible parameters and return values

Performance Testing DEMO

Additional Resources • CLR Integration Team Blog: • http://blogs.msdn.com/sqlclr/default.aspx • Introduction to SQL Server CLR Integration at MSDN: • http://msdn2.microsoft.com/en-us/library/ms254498.aspx • “A First Look at SQL Server 2005 for Developers” by Bob Beauchemin, Niels Berglund and Dan Sullivan • “Customizing the Microsoft .NET Framework Common Language Runtime” by Steven Pratschner • Niels Berglund’s blog: • http://staff.develop.com/nielsb/ • Questions?

Additional SQL Server 2005 Topic Ideas • Transact-SQL Enhancements • Service Broker • XML Data Type and XQuery • Native XML Web Services • Other?

.NET 2.0 and SQL 2005 Beta MCP Pro Exam Promo Codes • Exam 71-442 (“Design & Optimize Data Access by Using MS SQL Server 2005 ”): 442SQL • Exam 71-547 (“Design & Develop Web-Based Applications by Using MS .NET Framework 2.0”): PRO547 • Exam 71-548 (“Design & Develop Windows-Based Applications by Using MS .NET Framework 2.0”): BTA548 • Exam 71-549 (“Design & Develop Enterprise Applications by Using MS .NET Framework 2.0”): 549BTA

SQL Server 2005 CLR Integration