Programming SQL 2005 with .Net by ygq15756

VIEWS: 4 PAGES: 40

									     Programming SQL 2005
     with .Net
                        Andrew Novick




New England Code Camp IV: “Developer’s Gone Wild”
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”
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”
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”
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”
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”
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”
CLR is Optional!
   • Disabled by default
   • You must turn it on to use it
   EXEC sp_configure 'show advanced
   options' , '1';
   go
   reconfigure;
   go
   EXEC sp_configure 'clr enabled' , '1'
   go
   reconfigure;

New England Code Camp IV: “Developer’s Gone Wild”
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”
Assemblies
   • .Net Code compiled into an IL DLL
   • Assemblies must be added to S2K5 with
                  CREATE ASSEMBLY
    CREATE ASSEMBLY my_assembly_name
          FROM ‘\\myserver\…path\MyAssembly.dll’


   • Bits are stored in the database’s
     sys.assembly_files table

New England Code Camp IV: “Developer’s Gone Wild”
Assembly Security:PERMISSION_SET

   • SAFE
   • EXTERNAL_ACCESS
   • UNSAFE




New England Code Camp IV: “Developer’s Gone Wild”
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”
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”
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”
.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”
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”
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”
.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”
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”
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”
SQLContext
   • Static methods             Method                  Returns
     for obtaining
     SQL Context           GetConnection            SQLConnection
     objects
                           GetCommand               SQLCommand
   • GetCommand
     returns a             GetTransaction           SQLTransaction
     SQLCommand
     that is enlisted      GetPipe                  SQLPipe
     in any existing       GetTriggerContext SQLTriggerContext
     transaction.


New England Code Camp IV: “Developer’s Gone Wild”
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]
   go
   GRANT EXEC ON dbo.Product TO PUBLIC
   go




New England Code Camp IV: “Developer’s Gone Wild”
   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”
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”
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”
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”
Performance Experiment
   • Test on 1,000,000 row table pinned in memory
   • 2 CPU system

               Test                       CPU       Elapsed
   Query #0    Scan Table                     0.8         0.4
   Query #1    Udf_txt_CharIndexRev        114.0        172.3
   Query #2    Equivalent SQL                 2.9         1.5
   Query #3    CharIndexRev (.Net)          44.9         52.8




New England Code Camp IV: “Developer’s Gone Wild”
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”
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”
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”
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”
Why Not Use .Net in S2K5




New England Code Camp IV: “Developer’s Gone Wild”
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”
Why Not use .Net in S2K5 (3)

   • Compare cost per CPU
                       (with software licenses)
    Compute Location        Extra Costs             ~$/CPU
    Database Server         Advanced OS             $20,000
                            SQL Enterprise               To
                            Management Tools        $50,000
    Web Server              Management Tools         $4,000
    Desktop                                          $1,000



New England Code Camp IV: “Developer’s Gone Wild”
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”
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”
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”
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”
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”
Thanks for Coming
   • Contact me:


            anovick@NovickSoftware.com



            http://www.NovickSoftware.com



New England Code Camp IV: “Developer’s Gone Wild”

								
To top