1 / 65

SQLCLR Programming with SQL Server 2005

SQLCLR Programming with SQL Server 2005. Andrew Novick. The Subject: . What is SQLCLR programming? Why would you use it? Why not to use it?. Introduction – Andrew Novick. Novick Software Business Application Development SQL Server and .Net specialization www.NovickSoftware.com Books:

sloan
Download Presentation

SQLCLR Programming with SQL Server 2005

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQLCLR Programming with SQL Server 2005 Andrew Novick SQLCLR Programming with SQL Server 2005 Andrew Novick

  2. The Subject: • What is SQLCLR programming? • Why would you use it? • Why not to use it? SQLCLR Programming with SQL Server 2005 Andrew Novick

  3. Introduction – Andrew Novick • Novick Software • Business Application Development • SQL Server and .Net specialization • www.NovickSoftware.com • Books: • Transact-SQL UDFs • SQL 2000 XML Distilled SQLCLR Programming with SQL Server 2005 Andrew Novick

  4. Authorship This presentation is based in part on material developed for the Mini Code-Camp “Programming SQL Server: From 2000 to 2005” By Andrew Novick and Adam Machanic Adam’s Web site is: http://www.datamanipulation.net Andrew Novick’s Web Site is: http://www.NovickSoftware.com SQLCLR Programming with SQL Server 2005 Andrew Novick

  5. What Is SQLCLR? • Writing code using .Net Languages that runs inside SQL Server. • User-Defined Functions • Stored Procedures • Aggregates • User-Defined Types • Triggers • SSIS Packages • Expressions and Data Providers in Reports SQLCLR Programming with SQL Server 2005 Andrew Novick

  6. Why Use .Net in S2K5 • Replace Extended SP’s with a safer alternative • Replace COM automation: sp_OA_* • Take advantage of .Net Framework classes • Run computationally intense algorithms • Reuse code • Programmer productivity • Use new capabilities not available in T-SQL • User Defined Types • User Defined Aggregates SQLCLR Programming with SQL Server 2005 Andrew Novick

  7. Hosting the CLR • S2K5 is one of many CLR hosts • Such as: ASP.Net, WinForms • Goals for CLR hosting: • Security • Reliability • Performance SQLCLR Programming with SQL Server 2005 Andrew Novick

  8. SQLCLR Overall Architecture • Runtime hosted within SQL Server’s process space • “OS” layer provided by SQL Server’s scheduler, SQLOS • Memory • Processor • IO • Hosting enabled on a per-server basis SQLCLR Programming with SQL Server 2005 Andrew Novick

  9. SQLCLR AppDomains • App Domains created per database, per assembly owner • SQL Server will restart an app domain if it detects: • Deadlocks • Memory leaks • Hung threads • … And other abnormal conditions SQLCLR Programming with SQL Server 2005 Andrew Novick

  10. CLR is Optional! • Disabled by default • You must turn it on to use it • SQL Server 2005 Surface Area Configuration • This script: EXEC sp_configure 'show advanced options' , '1';goreconfigure;goEXEC sp_configure 'clr enabled' , '1'goreconfigure; SQLCLR Programming with SQL Server 2005 Andrew Novick

  11. Assemblies • .Net Code compiled into an IL DLL • Assemblies must be added to S2K5 with CREATE ASSEMBLY • Bits are stored in the database’s sys.assembly_files table CREATE ASSEMBLY my_assembly_name FROM ‘\\myserver\…path\MyAssembly.dll’ SQLCLR Programming with SQL Server 2005 Andrew Novick

  12. Assemblies 2 • Viewing Assemblies • SQL Management Studio • Reflector with the SQL 2005 Browser SQLCLR Programming with SQL Server 2005 Andrew Novick

  13. Assembly Security: PERMISSION_SET • SAFE • Allows data access, basic libraries • EXTERNAL_ACCESS • Allows access to IO libraries • UNSAFE • Allows access to unmanaged code SQLCLR Programming with SQL Server 2005 Andrew Novick

  14. PERMISSION_SET: Safe • May not access external resources: registry, file system, or network • May not use unmanaged code or PInvoke • May access data using the current context but not via SQLClient or any other data provider • No thread processing SQLCLR Programming with SQL Server 2005 Andrew Novick

  15. PERMISSION_SET: EXTERNAL_ACCESS • May access external resources:registry, file system, network, environment variables • May not use: • unmanaged code • PInvoke SQLCLR Programming with SQL Server 2005 Andrew Novick

  16. PERMISSION_SET: UNSAFE • Can do anything… • Know your code! SQLCLR Programming with SQL Server 2005 Andrew Novick

  17. SQLCLR User-Defined Functions SQLCLR Programming with SQL Server 2005 Andrew Novick

  18. Overview of CLR UDFs • Two Types • Scalar • Table-Valued • Similar Rules • No side effects • More flexible • Can use the .Net Base Classes • Can use user created classes • Can execute Dynamic SQL • Performance • Similar to better than T-SQL SQLCLR Programming with SQL Server 2005 Andrew Novick

  19. T-SQL or SQLCLR? • TSQL • Best for pure data access tasks. • SQLCLR • Low overhead for calling functions • Faster for math operations • Better for accessing external data SQLCLR Programming with SQL Server 2005 Andrew Novick

  20. CLR Scalar Functions • Simple and Flexible • .Net Functions • Useful for encapsulating BCL functionality SQLCLR Programming with SQL Server 2005 Andrew Novick

  21. CLR Table-Valued Functions • “Streaming”: Returns one row at time • Requires a method with: • Returns IEnumerable • SqlFunctionAttribute • A second method “shreds” the data • FillRowMethod • Columns defined by output parameters SQLCLR Programming with SQL Server 2005 Andrew Novick

  22. CLR Table-Valued Function Lifecycle • SQL Server invokes IEnumerable method • Which returns a reference to the collection • SQL Server enumerates the collection • Enumerate() calls the FillRowMethod • Input object is mapped to output parameters by the SQLCLR Programming with SQL Server 2005 Andrew Novick

  23. SQLCLR Stored ProceduresAnd Data Access SQLCLR Programming with SQL Server 2005 Andrew Novick

  24. SQLCLR Stored Procedures • Can do everything that T-SQL sprocs can • Multiple result sets • Output Parms • Messages • Return Code • Can do what other SQLCLR objects can: • C# or VB.net code SQLCLR Programming with SQL Server 2005 Andrew Novick

  25. The ADO.NET In-Proc Provider • Access to almost every ADO.NET object you know and love • No access to some ADO.NET features, such as MARS and asynchronous queries • All of this is possible via the “context connection”: SqlConnection conn = new SqlConnection(“context connection=true”); SQLCLR Programming with SQL Server 2005 Andrew Novick

  26. Reading Data • DataSet • Disconnected • Implements IEnumerable • Easy to use in a table-valued UDF; not easy to use in a stored procedure • DataReader • Connected (Streaming) • Directly supported by SqlPipe • Easy to use in a stored procedure; not easy to use in a table-valued UDF SQLCLR Programming with SQL Server 2005 Andrew Novick

  27. The Same Rules Apply..! • ADO.NET in-process provider makes it easy to move code between tiers • Best practice in the application tier: • Use T-SQL stored procedures to encapsulate database data access • Best practice in the data tier (CLR stored procedure or UDF): • Use T-SQL stored procedures to encapsulate database data access SQLCLR Programming with SQL Server 2005 Andrew Novick

  28. ADO.NET: Sending Back Data • SqlPipe • Send() • Result Sets (SqlDataReader) • Rows (SqlDataRecord) • Messages (System.String) • ExecuteAndSend() • Executes SqlCommand in the connected context • Should probably be called SendAndExecute() SQLCLR Programming with SQL Server 2005 Andrew Novick

  29. CLR Stored Procedures • Best for: • External Data Sources • Multiple output parameters from external data sources • complex math/logic • Not indicated for • single result sets • single scalar output SQLCLR Programming with SQL Server 2005 Andrew Novick

  30. SQLCLRUser Defined Aggregates SQLCLR Programming with SQL Server 2005 Andrew Novick

  31. User Defined Aggregates • Aggregates scalar values into another scalar • Uses the SqlUserDefinedAggregate attribute CREATE AGGREGATE [Product](@Value float)                    Returns [float]        EXTERNAL NAME [SampleAggregate].[SampleAggregate.Product]goGRANT EXEC ON dbo.Product TO PUBLICgo SQLCLR Programming with SQL Server 2005 Andrew Novick

  32. Possible UDAGGs • Product • CountOfNulls • BitwiseOr • Concatenation • RegExMatchCount SQLCLR Programming with SQL Server 2005 Andrew Novick

  33. Impossible UDAGG • First • Last • Impossible due to possible Parallelism SQLCLR Programming with SQL Server 2005 Andrew Novick

  34. User Defined Aggregate Class public class myAggregate { public void Accumulate (<input-type> value) { } public <return-type> Terminate() { } public void Init () { } public void Merge (myAggregate) { } } SQLCLR Programming with SQL Server 2005 Andrew Novick

  35. UDAGG: Init • Initialize the aggregate • Reinitializes the when SQL Server chooses to reuse it.     Public Sub Init()        m_Accumulator = 1.0        m_HasNonNull = False    End Sub SQLCLR Programming with SQL Server 2005 Andrew Novick

  36. UDAGG: Accumulate • Called once per row to be aggregated. Public Sub Accumulate(ByVal value As SqlDouble) if value.isnull then exit sub              m_Accumulator *= value        m_HasNonNull = True  End Sub SQLCLR Programming with SQL Server 2005 Andrew Novick

  37. UDAGG: Merge • SQL Server may use multiple threads for an aggregation. • Each thread would need it’s own instance of the UDAGG class. • Results combined by the Merge method. SQLCLR Programming with SQL Server 2005 Andrew Novick

  38. UDAGG: Merge Public Sub Merge(ByVal value As Product)        if value is nothing then exit sub        if not value.m_HasNonNull then exit sub        m_Accumulator *= value.m_Accumulator        m_HasNonNull = True End Sub SQLCLR Programming with SQL Server 2005 Andrew Novick

  39. UDAGG: Terminate • Terminate method returns the results. Public Function Terminate() As SqlDouble if not m_HasNonNull then            return sqldouble.null        else            Return m_Accumulator        end if End Function SQLCLR Programming with SQL Server 2005 Andrew Novick

  40. Using the User Defined Aggregate SELECT dbo.product(sample) [Three integers]    FROM (   SELECT CAST(1.0 as float) as Sample          UNION ALL SELECT 3          UNION ALL SELECT 5         ) Numbers (Result) Three integers  --------------- 15     SQLCLR Programming with SQL Server 2005 Andrew Novick

  41. Best Practices: UDAGG • Consider T-SQL alternatives • Use SQLTypes • Code efficiently • No SQL input if you can help it. • If you need data, create a cache. • Remember Parallel execution SQLCLR Programming with SQL Server 2005 Andrew Novick

  42. User-Defined Types UDT SQLCLR Programming with SQL Server 2005 Andrew Novick

  43. Two Types of UDTs • Alias Types • CLR Types • Both use the CREATE TYPE statement SQLCLR Programming with SQL Server 2005 Andrew Novick

  44. Alias User Defined Types • Alias Types • These give an alternate name to a SQL type • Have existed in SQL Server since Sybase • Example: CREATE TYPE CompletionCODE FROM CHAR(2) NOT NULL SQLCLR Programming with SQL Server 2005 Andrew Novick

  45. CLR User-Defined Types (UDTs) • Created with the .Net CLR in VB.Net or C# • New to SQL Server 2005 • Example: CREATE Assembly myAssembly FROM ‘\\server\share\myAssembly.dll Go CREATE TYPE myType EXTERNAL NAME myAssembly.[myAssembly.myType] go SQLCLR Programming with SQL Server 2005 Andrew Novick

  46. Why CLR User-Defined Types • Encapsulate application logic and data in the database. • Benefits: • Programmer productivity • Consistency over the long term SQLCLR Programming with SQL Server 2005 Andrew Novick

  47. Steps to create a CLR UDT • Create a Class in a .Net language (VB or C#) • Create the ASSEMBLY database object • CREATE TYPE statement SQLCLR Programming with SQL Server 2005 Andrew Novick

  48. CLASS or Structure • Class is a reference type • Structure is a Value type SQLCLR Programming with SQL Server 2005 Andrew Novick

  49. Create your CLASS Required attributes: <Serializable()> _ <Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)> Required Interface: INullable Required Methods: ToString Parse SQLCLR Programming with SQL Server 2005 Andrew Novick

  50. Your Class • Public and Private fields • Static (Shared in VB) and instance members SQLCLR Programming with SQL Server 2005 Andrew Novick

More Related