![public partial class StoredProcedures{ [Microsoft.SqlServer.Server.SqlProcedure] public static void HelloWorld() { // Put your code hereusing (SqlConnectioncn = new SqlConnection("Context Connection=true")) {cn.Open();SqlCommandcmd = new SqlCommand("SELECT * FROM DEMO", cn);SqlContext.Pipe.Send(cmd.ExecuteReader());}}Sample](https://image.slidesharecdn.com/04sqlclr-100824180616-phpapp01/85/SQL-Server-CLR-integration-19-320.jpg)
![public partial class UserDefinedFunctions{ [Microsoft.SqlServer.Server.SqlFunction]public static boolIsValidPostCode(string postcode) { return System.Text.RegularExpressions.Regex.IsMatch( postcode, ^(((2|8|9)\d{2})|((02|08|09)\d{2})|([1-9]\d{3}))$"); }};Sample](https://image.slidesharecdn.com/04sqlclr-100824180616-phpapp01/85/SQL-Server-CLR-integration-24-320.jpg)
![public partial class Triggers{ [Microsoft.SqlServer.Server.SqlTrigger(Name = "EmailTrigger", Target = "Customers", Event = "FOR UPDATE")] public static void SalaryFraudTrigger() { SqlTriggerContext context = SqlContext.TriggerContext; using (SqlConnectioncnn = new SqlConnection("context connection=true")) {cnn.Open();SqlCommand command = cnn.CreateCommand();command.CommandText = "SELECT * FROM inserted";SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { for (intcolumnNumber = 0; columnNumber<context.ColumnCount; columnNumber++) {SqlContext.Pipe.Send(string.Format("Col: {0} = {1}", columnNumber, reader[columnNumber].ToString())); } };reader.Close(); } }}Sample](https://image.slidesharecdn.com/04sqlclr-100824180616-phpapp01/85/SQL-Server-CLR-integration-28-320.jpg)
![public partial class StoredProcedures{ [Microsoft.SqlServer.Server.SqlProcedure] public static void HelloWorld() { // Put your code hereusing (SqlConnectioncn = new SqlConnection("Context Connection=true")) {cn.Open();SqlCommandcmd = new SqlCommand("SELECT * FROM DEMO", cn);SqlContext.Pipe.Send(cmd.ExecuteReader());}}Sample](https://image.slidesharecdn.com/04sqlclr-100824180616-phpapp01/75/SQL-Server-CLR-integration-19-2048.jpg)
![public partial class UserDefinedFunctions{ [Microsoft.SqlServer.Server.SqlFunction]public static boolIsValidPostCode(string postcode) { return System.Text.RegularExpressions.Regex.IsMatch( postcode, ^(((2|8|9)\d{2})|((02|08|09)\d{2})|([1-9]\d{3}))$"); }};Sample](https://image.slidesharecdn.com/04sqlclr-100824180616-phpapp01/75/SQL-Server-CLR-integration-24-2048.jpg)
![public partial class Triggers{ [Microsoft.SqlServer.Server.SqlTrigger(Name = "EmailTrigger", Target = "Customers", Event = "FOR UPDATE")] public static void SalaryFraudTrigger() { SqlTriggerContext context = SqlContext.TriggerContext; using (SqlConnectioncnn = new SqlConnection("context connection=true")) {cnn.Open();SqlCommand command = cnn.CreateCommand();command.CommandText = "SELECT * FROM inserted";SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { for (intcolumnNumber = 0; columnNumber<context.ColumnCount; columnNumber++) {SqlContext.Pipe.Send(string.Format("Col: {0} = {1}", columnNumber, reader[columnNumber].ToString())); } };reader.Close(); } }}Sample](https://image.slidesharecdn.com/04sqlclr-100824180616-phpapp01/75/SQL-Server-CLR-integration-28-2048.jpg)
Uploaded byPeter Gfader
SQL Server - CLR integration
This document contains notes from a SQL Server 2008 for Developers course taught by Peter Gfader. The course covered topics such as high availability, online index operations, mirrored backups, and SQL CLR integration. SQL CLR integration allows writing database queries using .NET code. It provides benefits like complex calculations, custom types and aggregates, and leveraging .NET debugging. However, T-SQL is better suited for core data operations. The document provides examples of stored procedures, functions, triggers and other SQL CLR code.
More Related Content
Similar to SQL Server - CLR integration
SQL Server - CLR integration
- 1. SQL Server 2008for DevelopersUTS Short Course
- 2. Peter GfaderSpecializes inC# and .NET (Java not anymore)TestingAutomated testsAgile, ScrumCertified Scrum TrainerTechnology aficionado SilverlightASP.NETWindows Forms
- 3. Course WebsiteCourse Timetable& Materialshttp://www.ssw.com.au/ssw/Events/2010UTSSQL/Resourceshttp://sharepoint.ssw.com.au/Training/UTSSQL/
- 4.
- 5. What we didlast weekHigh availability?What can go wrong? What can we do?Implementing Database SnapshotsConfiguring a Database MirrorPartitioned TablesSQL Agent ProxiesPerforming Online Index OperationsMirrored Backups
- 6.
- 7.
- 8. Agenda - CLRIntegrationWhat is .NET?What is CLR Integration?Requirements on SQL boxSamplesInternalsCLR Integration: ProsCLR Integration: ConsReal world - When to use CLR Integration
- 9. What is .NET?Anapplication development platform from MicrosoftTools, Languages, Runtime (Virtual machine), IDE, …Rapidly develop secure and robust softwareWeb and WindowsFull support for object-oriented programming
- 10. .NET OverviewIL =Intermediate LanguageCLR = Runtime
- 11.
- 12. .NET FrameworkEvolutionThe whole.NET FXhttp://shrinkster.com/1515(PDF Poster)
- 13. What is CLRIntegration?Lets you write your database queries using .NETCreate and debug using VS 2008 IDEBrand new in SQL 2005 (Standard and Express)Support for large UDT (User defined types) in SQL 2008 (up to 2GB)Support for multiple inputs on UDA (User defined aggregators)e.g. string concatenator that takes in a column and separator charAny .NET language (C#, VB, C++)
- 15.
- 16. CLR IntegrationYou cando the same thing as SQL Server using .NET codeStored ProceduresTriggersUser-defined functionsUser-defined typesAggregate functions
- 17. Server - EnablingCLR IntegrationEnabled on an instance (not per database)SQL ScriptExecute sp_configure ‘clr enabled’, ‘1’reconfigure
- 18.
- 19. public partial classStoredProcedures{ [Microsoft.SqlServer.Server.SqlProcedure] public static void HelloWorld() { // Put your code hereusing (SqlConnectioncn = new SqlConnection("Context Connection=true")) {cn.Open();SqlCommandcmd = new SqlCommand("SELECT * FROM DEMO", cn);SqlContext.Pipe.Send(cmd.ExecuteReader());}}Sample
- 20. What you doEnableCLRCreate a new database project in Visual StudioCreate a new stored procedure in Visual StudioConnect to current context using “Context Connection=true”Add a simple SELECT statementDeploy and run it
- 21. InternalsAssembly collated asset of filesStored within SQL Server system tables Assembly, references, program database (pdb), source filesDeployed to SQL ServerManuallyCatalogued with CREATE ASSEMBLYDropped with DROP ASSEMBLYAutomaticallyDeployed from VS 2008
- 22. Security LevelsSafe (default)Accessonly to CLR code. No access is allowed to external resources, thread management, unsafe code or interop.External_AccessAccess is allowed to external systems, such as the EventLog, File System, and network. Still no access to unsafe code or interop code.UnsafeAccess is not limited whatsoever. User-defined types
- 23.
- 24. public partial classUserDefinedFunctions{ [Microsoft.SqlServer.Server.SqlFunction]public static boolIsValidPostCode(string postcode) { return System.Text.RegularExpressions.Regex.IsMatch( postcode, ^(((2|8|9)\d{2})|((02|08|09)\d{2})|([1-9]\d{3}))$"); }};Sample
- 25. What you doCreateIsValidPostCode in C# (.NET)Deploy it to SQL ServerRun it
- 26. Stored procedure vs.FunctionWhat is the difference?Function Base functionalityIndependent of Database itselfStored procedureMany operations at onceNormally database specific
- 27.
- 28. public partial classTriggers{ [Microsoft.SqlServer.Server.SqlTrigger(Name = "EmailTrigger", Target = "Customers", Event = "FOR UPDATE")] public static void SalaryFraudTrigger() { SqlTriggerContext context = SqlContext.TriggerContext; using (SqlConnectioncnn = new SqlConnection("context connection=true")) {cnn.Open();SqlCommand command = cnn.CreateCommand();command.CommandText = "SELECT * FROM inserted";SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { for (intcolumnNumber = 0; columnNumber<context.ColumnCount; columnNumber++) {SqlContext.Pipe.Send(string.Format("Col: {0} = {1}", columnNumber, reader[columnNumber].ToString())); } };reader.Close(); } }}Sample
- 29. What you doCreateEmailTrigger in C# (.NET)Deploy it to SQL ServerTest it
- 30. CLR Integration: Pros(Continued)Take advantage of the powerful .NET Framework.NET is a full-featured programming languageSupports things like “for each” loops, arrays, collectionsObject Oriented programming model to organise your queriesObtaining data from external resourcesThe File SystemThe Event LogA Web ServiceThe Registry
- 31. CLR Integration: ProsForcomplex calculationsParsing strings (like the regular expression code)User-defined typesDate, time, currency, and extended numeric typesGeospatial applicationsEncoded or encrypted data (see books online)User-defined aggregatesPowerful Intellisense and debuggingGenerally fasterE.g. CLR aggregate 100x faster than cursor
- 32. CLR Integration: Cons(Continued)NON MAINSTREAMLots of programming for simple operationsSome overhead in communicating with assembliesRemember – T-SQL is designed and optimised for data, use it!Not useful if your guys do not know any .NETPotentially costly to rewrite logicCompanies (including us) have invested a lot in T-SQL
- 33. CLR Integration: ConsThereare some restrictions to observe when calling between T-SQL and SQL-CLR.You must only use T-SQL supported data types (No streams)You can't use inheritance or polymorphism.NET cannot easily represent either VARCHAR or TIMESTAMP.NET strings are Unicode, the equivalent of NVARCHARThe CLR decimal type is not the same as SQL_DECIMAL
- 34. When to useCLR IntegrationDo I need to manipulate data before it is displayed?.NET code and SQLCLRDo I need to do set-based operations such as pivoting?T-SQL Do I need to do extensive computation or custom algorithms? .NET code and SQLCLRAre my developers SQL gurus but .NET newbies?T-SQLDo I have loads of stored procs that are becoming hard to manage?.NET code and SQLCLR
- 35. CLR IntegrationThe BottomLineUse T-SQL for all data operations
- 36. Use CLR assembliesfor any complex calculations and transformationsQuick tipsSQL Management Studio ShortcutsCtrl + L – Display query execution planF5 – Run/Show result gridRules for SQL Serverhttp://www.ssw.com.au/SSW/Standards/default.aspxSQL Server Cheat sheet http://www.pinaldave.com/sql-download/SQLServerCheatSheet.pdfhttp://www.addedbytes.com/cheat-sheets/sql-server-cheat-sheet/
- 37. Session 4 LabCLR IntegrationDownload from Course Materials Site (to copy/paste scripts) or type manually:http://tinyurl.com/utssql2009
- 38.
- 39.
- 40. Thank You!Gateway CourtSuite 10 81 - 91 Military Road Neutral Bay, Sydney NSW 2089 AUSTRALIA ABN: 21 069 371 900 Phone: + 61 2 9953 3000 Fax: + 61 2 9953 3105 info@ssw.com.auwww.ssw.com.au