SQL Server 2005 CLR Integration

SQL Server 2005 CLR Integration

• 1.
  SQL Server 2005CLR Integration Matthew Roche
• 2.
  Session Overview ProgrammabilityOptions in SQL Server SQLCLR Architecture Creating Managed Objects Stored Procedures, Functions and Triggers Data Access User Defined Types User Defined Aggregates Best Practices
• 3.
  Programmability Options inSQL Server Transact-SQL Set-based Cursors Extended Stored Procedures OLE Automation (sp_oe* procedures) SQLCLR
• 4.
  SQLCLR Architecture DesignGoals Security Reliability Performance SQL Server as a CLR Host CorBindToRuntimeEx() ICLRRuntimeHost IHostControl
• 5.
  Creating Managed ObjectsCataloging 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)
• 6.
  Creating Assemblies DEMO
• 7.
  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
• 8.
  Stored Procedures Staticpublic int or void method Microsoft.SqlServer.Server.SqlProcedure attribute [SqlProcedure] public static void StoredProcedure() { // Put your code here }
• 9.
  Creating Stored ProceduresDEMO
• 10.
  Scalar Functions Staticpublic method Scalar return type Microsoft.SqlServer.Server.SqlFunction attribute [SqlFunction] public static SqlString ScalarFunction() { return new SqlString("Hello"); }
• 11.
  Table-Valued Functions Staticpublic 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; }
• 12.
  Creating Functions DEMO
• 13.
  Triggers Public staticvoid 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)) { //... } }
• 14.
  User Defined TypesPublic 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;}
• 15.
  User Defined AggregatesPublic class or struct; well-defined “interface” System.Serializable attribute Microsoft.SqlServer.Server. SqlUserDefinedAggregate attribute Serialization options [S erializable] [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("");} }
• 16.
  Data Access Server-sideprogramming 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
• 17.
  Using the ManagedProvider DEMO
• 18.
  Best Practices UseTransact-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
• 19.
  Performance Testing DEMO
• 20.
  Additional Resources CLRIntegration 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?
• 21.
  Additional SQL Server2005 Topic Ideas Transact-SQL Enhancements Service Broker XML Data Type and XQuery Native XML Web Services Other?
• 22.
  .NET 2.0 andSQL 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