Download
sql server 2005 clr integration n.
Skip this Video
Loading SlideShow in 5 Seconds..
SQL Server 2005 CLR Integration PowerPoint Presentation
Download Presentation
SQL Server 2005 CLR Integration

SQL Server 2005 CLR Integration

187 Vues Download Presentation
Télécharger la présentation

SQL Server 2005 CLR Integration

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. SQL Server 2005CLR Integration Matthew Roche

  2. 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

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

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

  5. 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)

  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 • Static public int or void method • Microsoft.SqlServer.Server.SqlProcedure attribute [SqlProcedure] public static void StoredProcedure() { // Put your code here }

  9. Creating Stored Procedures DEMO

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

  11. 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; }

  12. Creating Functions DEMO

  13. 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)) { //... } }

  14. 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;}

  15. 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("");} }

  16. 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

  17. Using the Managed Provider DEMO

  18. 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

  19. Performance Testing DEMO

  20. 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?

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

  22. .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