1 / 40

Programming SQL 2005 with .Net

Programming SQL 2005 with .Net. Andrew Novick. Agenda. Introduction Overview of .Net Programming in S2K5 Using Visual Studio 2005 to Create S2K5 Objects The Inprocess Data Provider Type Considerations Pros and Cons of using .Net in S2K5 Resources. Introduction – Andrew Novick.

diedrick
Download Presentation

Programming SQL 2005 with .Net

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. Programming SQL 2005 with .Net Andrew Novick New England Code Camp IV: “Developer’s Gone Wild”

  2. Agenda • Introduction • Overview of .Net Programming in S2K5 • Using Visual Studio 2005 to Create S2K5 Objects • The Inprocess Data Provider • Type Considerations • Pros and Cons of using .Net in S2K5 • Resources New England Code Camp IV: “Developer’s Gone Wild”

  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 New England Code Camp IV: “Developer’s Gone Wild”

  4. One Programmer’s Wishful Thinking What I’d really like is: • Create a object in either the client or the database • Write business rules in my favorite language • Business rules are enforced (efficiently) when the object was running in client, server, or mid-tier • Save the object in the database • Use SQL as a retrieval/update language whenever I want to. (i.e. in a Report Writer, external program) • Database has high performance New England Code Camp IV: “Developer’s Gone Wild”

  5. Beta Based This presentation is based on beta code. • .Net CLR Beta 2 • SQL Server 2005 CTP • Everything is subject to change! New England Code Camp IV: “Developer’s Gone Wild”

  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 compute intense algorithms • Reuse code • Programmer productivity • Use new capabilities not available in T-SQL • User Defined Types • User Defined Aggregates New England Code Camp IV: “Developer’s Gone Wild”

  7. Hosting the CLR • S2K5 is one of many CLR hosts • Such as: ASP.Net, WinForms • Each database has is its own AppDomain • Goals for CLR hosting: • Security • Reliability • Performance New England Code Camp IV: “Developer’s Gone Wild”

  8. CLR is Optional! • Disabled by default • You must turn it on to use it EXEC sp_configure 'show advanced options' , '1';goreconfigure;goEXEC sp_configure 'clr enabled' , '1'goreconfigure; New England Code Camp IV: “Developer’s Gone Wild”

  9. Types of .Net Code in S2K5 • Stored Procedures • User Defined Functions • User Defined Aggregates • User Defined Types • Triggers • DTS Packages New England Code Camp IV: “Developer’s Gone Wild”

  10. 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’ New England Code Camp IV: “Developer’s Gone Wild”

  11. Assembly Security:PERMISSION_SET • SAFE • EXTERNAL_ACCESS • UNSAFE New England Code Camp IV: “Developer’s Gone Wild”

  12. 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 New England Code Camp IV: “Developer’s Gone Wild”

  13. PERMISSION_SET: EXTERNAL_ACCESS • May access external resources:registry, file system, network, environment variables • May not use: • unmanaged code • PInvoke New England Code Camp IV: “Developer’s Gone Wild”

  14. PERMISSION_SET: UNSAFE • May access external resources • May use unsafe code and PInvoke • Can use SQLClient and other data providers • Can use thread constructs • This is no more unsafe than extended stored procs New England Code Camp IV: “Developer’s Gone Wild”

  15. .Net User-Defined Functions • Scalar • Return a single value • Multi-Statement UDFs • Returns a single result set • No .Net Inline UDFs New England Code Camp IV: “Developer’s Gone Wild”

  16. Creating a Scalar UDF • Create the assembly first. • Then use the CREATE FUNCTION statement. CREATE FUNCTION udf_myFunction ( @Parm1 int -- First Parameter , @Parm2 varcharmax – 2nd Parm ) RETURNS BIT EXTERNAL NAME assemblyname.class.method New England Code Camp IV: “Developer’s Gone Wild”

  17. Creating Table Valued UDF • Create the assembly • Method returns an IDataReader IDataRecord • CREATE FUNCTION script defines the schema of the result New England Code Camp IV: “Developer’s Gone Wild”

  18. .Net Stored Procedures • Capable of doing everything a T-SQL procedure can do. • Uses a SHARED (static in C#) method • Pass parameters both ways • OUTPUT parameters should be byref (ref in C#) • Return multiple result sets New England Code Camp IV: “Developer’s Gone Wild”

  19. Type Considerations • .Net reference types don’t represent NULL • System.Data.SQLTypes represent NULL • Use the SQLTypes when possible • Not sure about NullOf<T> New England Code Camp IV: “Developer’s Gone Wild”

  20. Inprocess Data Provider • System.Data.SQLServer Provider • Implements the IData* interfaces • Parallels the SQLClient Provider • Exposes Additional Classes via SQLContext New England Code Camp IV: “Developer’s Gone Wild”

  21. SQLContext • Static methods for obtaining SQL Context objects • GetCommand returns a SQLCommand that is enlisted in any existing transaction. New England Code Camp IV: “Developer’s Gone Wild”

  22. 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 New England Code Camp IV: “Developer’s Gone Wild”

  23. User Defined Aggregate Class public class myAggregate { public void Accumulate (<input-type> value) { } public <return-type> Terminate() { } public void Init () { } public void Merge (myAggregate) { } } New England Code Camp IV: “Developer’s Gone Wild”

  24. 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     New England Code Camp IV: “Developer’s Gone Wild”

  25. Triggers • SQLTriggerContext has additional information about the environment of the trigger • Can read both the INSERTED and DELETED tables • Coding similar to a stored procedure New England Code Camp IV: “Developer’s Gone Wild”

  26. User Defined Types • Best used for scalar types • Not ideally suitable for “Classes” • Additional presenation later today. New England Code Camp IV: “Developer’s Gone Wild”

  27. Performance Experiment • Test on 1,000,000 row table pinned in memory • 2 CPU system New England Code Camp IV: “Developer’s Gone Wild”

  28. Best Practices • Pick one .Net language • Build assemblies with SQL Server in mind • Use the most restrictive Permission Set possible • Move as much .Net/CLR code to the middle tier as possible • Test via the SQL Interface New England Code Camp IV: “Developer’s Gone Wild”

  29. 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 compute intense algorithms • Reuse code • Programmer productivity • New capabilities not available in T-SQL • User Defined Types • User Defined Aggregates New England Code Camp IV: “Developer’s Gone Wild”

  30. Why .Net in S2K5 • Many tasks that were awkward or difficult to perform in Transact-SQL can be better accomplished by using managed code..." New England Code Camp IV: “Developer’s Gone Wild”

  31. Why Not use .Net We used to joke that SQL stood for "Scarcely Qualifies as a Language" because it has no I/O and can't format output. Its math library is limited because it isn't a computational language. It doesn't do text searching, list processing, or graphics. The only purposes of SQL are data management and retrieval. Period. Joe Cleko New England Code Camp IV: “Developer’s Gone Wild”

  32. Why Not Use .Net in S2K5 $ New England Code Camp IV: “Developer’s Gone Wild”

  33. Why Not Use .Net in S2K5 (2) • Scalability • Application servers MAY scale out easily • Web servers USUALLY scale out easily • Desktops ALMOST ALWAYS scale out • Scaling up SQL Server is expensive New England Code Camp IV: “Developer’s Gone Wild”

  34. Why Not use .Net in S2K5 (3) • Compare cost per CPU (with software licenses) New England Code Camp IV: “Developer’s Gone Wild”

  35. Resources • First Look at SQL Server 2005 for Developers • By Beauchemin, Berglund, & Sullivan • Published June 2004 New England Code Camp IV: “Developer’s Gone Wild”

  36. Resources • Weekly Newsletter about SQL Coding • Theme for Sept-Oct is .Net CLR Programming http://www.novicksoftware.com/coding-in-sql/coding-in-sql-signup.htm New England Code Camp IV: “Developer’s Gone Wild”

  37. Mini Code Camp SQL Server Programming: From 2000 to 2005 Saturday October 22nd 2005 8:30 to 5:00 Adam Mechanic Event Code 1032279560 New England Code Camp IV: “Developer’s Gone Wild”

  38. Resources Download this presentation and samples from: http://www.novicksoftware.com/Presentations/clr-programming-dotnet-sql-server-2005-yukon/clr-dotnet-sql-server-2005-yukon.htm Blogs: Niels Burgland http://staff.develop.com/nielsb/ Bob Beauchemin http://staff.develop.com/bobb/weblog/ New England Code Camp IV: “Developer’s Gone Wild”

  39. New England Visual Basic Professionals • Focused on VB.Net development • Meetings @ MS Waltham • 1st Thursday - 6:15 to 8:30 • Coming up: • October – Developer Utilitys • November – Jesse Liberty – VB.Net 2005 • December INETA – VS 2005 Launch • January – Jason Beres New England Code Camp IV: “Developer’s Gone Wild”

  40. Thanks for Coming • Contact me: anovick@NovickSoftware.com http://www.NovickSoftware.com New England Code Camp IV: “Developer’s Gone Wild”

More Related