1 / 24

Sql Server 2005 CLR Objects

Sql Server 2005 CLR Objects. Wallace B. McClure www.scalabledevelopment.com http://weblogs.asp.net/wallym http://www.aspnetpodcast.com http://www.morewally.com. About INETA. International .NET Association. Supports .NET User Groups. Formed in 2002. 860+ Groups World Wide. North America:

osman
Download Presentation

Sql Server 2005 CLR Objects

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. Sql Server 2005 CLR Objects Wallace B. McClure www.scalabledevelopment.com http://weblogs.asp.net/wallym http://www.aspnetpodcast.com http://www.morewally.com

  2. About INETA • International .NET Association. • Supports .NET User Groups. • Formed in 2002. • 860+ Groups World Wide. • North America: • US: 211 User Groups. • Canada: 25 User Groups. • 58 Members of the Speaker’s Bureau. • http://www.ineta.org/

  3. INETA • http://www.ineta.org. • Sponsor of tonight’s meeting. • Resources: • Speaker’s Bureau. • Videos. • Access to people. • Additional Resources.

  4. Who the heck am I? • “Building Highly Scalable Apps…..” - OOP • “Professional ADO.NET V2…..” - Now • “Beginning AJAX with ASP.NET” - Now • “Beginning ASP.NET 2.0 AJAX” – 2007 • MVP – Visual ASP.NET Developer. • ASPInsider. • ASP.NET Podcast – Get your Red Hot TShirts….. • INETA Speaker’s Bureau.

  5. Sql Server 2005 • Latest version of Sql Server. • Run the CLR within the database. • Create CLR objects within the database. • Triggers. • Stored Procedures. • Functions. • Aggregates. • User Defined Data Types (UDTs).

  6. Web Search Example (background material) • URLs, Text, PK(int), Dates, ……… • Wrote a web spider. • Threads. • Networking. • MSMQ. • Needed data processing close to the source. • Can’t index a URL due to its size. • Hashcode. • Server. • What if we only want to spider certain sites?

  7. Which should I use? • TSql • Relational language • Sets of Data. • Based on mathematical set theory. • CLR • Procedure language. • One line at a time. • Computer Science. • Good for calculational operations.

  8. Objects of Importance • Connection Context. • SqlPipe. • SqlContext. • SqlTriggerContext.

  9. Connections • Why do you need a new connection when the object already has one? – Context Connection. • Can connect to other databases.

  10. Context Connection • New keyword in the connection string. • Only one per object can be open at a time. • Causes the SqlClient to bypass all of the unnecessary layers.

  11. Transactions • Run within the existing transaction. • System.Transactions namespace. • Auto-promotion of the transaction. • Distributed Transactions are only used when necessary.

  12. Stored Procedures • Can access user or system tables. • All CRUD operations are possible. • SqlProcedure Attribute. • Name. • Example.

  13. Triggers • SqlTrigger Attribute. • Name. • Target. • Event. • Example.

  14. Functions • Read only. • SqlFunction Attribute. • DataAccess. • IsDeterministic. • IsPrecise. • Name. • SystemDataAccess. • TableDefinition. • Scalars and Tables. • Example.

  15. Aggregates • Min, Max, Avg, and such. • Build your own. • Methods. • Init. • Accumulate. • Merge. • Terminate. • Example.

  16. User Defined Data Types • Impedance mismatch between DBMS and programmable objects. • SqlUserDefined attribute. • Format.UserDefined. • IsByteOrdered. • IsFixedLength. • MaxByteSize. • No Alter support. • I’m not their biggest fan.

  17. Integration of TSql and CLR • Objects may call each other. • DECLARE @ServerName varchar(100)DECLARE @SearchCode intSET @SearchCode = dbo.CalculateSearchCode(@SearchUrl)SET @ServerName = dbo.CalculateServerName(@SearchUrl)if exists ( select tblSearchServerId from tblSearchServer where ServerName=@ServerName )BEGIN IF not( exists (SELECT * FROM tblSearchUrl WHERE ServerName=@ServerName AND SearchCode=@SearchCode AND UrlAddress=@SearchUrl) ) BEGIN INSERT INTO tblSearchUrl (UrlAddress, UrlStatus, ServerName, SearchCode) SELECT @SearchUrl, 'NEW_URL', @ServerName, @SearchCode END END

  18. Visual Studio 2005 Integration • TSql Commands to put in assemblies. • Visual Studio 2005 can be used to build and deploy. • Debugging is first rate! • Similar to debugging a Windows Service. • Breakpoints. • Watch Variables. • Do all that debugging stuff!

  19. TSql vs. CLR? • CRUD – TSql. • Operating on sets of data – TSql. • Complex calculations – CLR or TSql. • Developer knowledge – teach them SQL.

  20. Performance suggestions • CLR w/ Datatables. • CLR w/ custom data store. • TSql Cursor with CLR functions. • TSql statement with CLR functions.

  21. Languages and .NET BCL Support • C#. • VB.NET. • Not all namespaces are supported.

  22. Additional Sources of Information • Bob Beauchemin - http://www.sqlskills.com/blogs/bobb/. • Derek Comingore & Doug Hinson – Professional Sql Server 2005 CLR Programming. • http://blogs.msdn.com/. • http://weblogs.asp.net/wallym. • http://www.aspnetpodcast.com/

  23. http://www.scalabledevelopment.com • Custom Training. • Mentoring. • Business Process Automation. • Software Development. • Questions/Comments: wbm@wallym.com

More Related