-- Enable SQLCLR
-- *************
sp_configure
GO
'clr_enabled', 1
RECONFIGURE
GO
-- Install and register HelloSQLCLR
-- ********************************
USE
GO
Northwind
-- Install (change the directory to the actual location)
CREATE
ASSEMBLY MyUDFsLibFROM 'C:\SQL2005Core\SQLCLR\HelloSQLCLR\HelloSQLCLR.dll'
GO
-- Register
CREATE
FUNCTION dbo.HelloSQLCLR()
RETURNS NVARCHAR(50)
EXTERNAL NAME MyUDFsLib.MyUDFs.HelloSQLCLR;
-- EXTERNAL_NAME is assembly name, namespace/class,
-- and method name
-- Test it
SELECT
dbo.HelloSQLCLR()
-- Create a stored procedure for comparison to SQLCLR
-- **************************************************
USE
GO
Northwind
-- Create a Get Product List stored procedure in T-SQL
IF
EXISTS (
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'GetProductList' )
GO
DROP PROCEDURE dbo.GetProductList
CREATE
PROCEDURE dbo.GetProductList
AS
GO
SELECT [ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued]FROM [Products]ORDER BY [ProductName]
-- Test dbo.GetProductList
EXECUTE
dbo.GetProductList
-- Test CLR version after deployment
EXECUTE
dbo.GetProductListCLR
-- Test GenerateData
EXECUTE
dbo.GenerateData 5
-- Test Customers table trigger
BEGIN
TRY
INSERT INTO [Northwind].[dbo].[Customers]([CustomerID] ,[CompanyName], [ContactName])
VALUES ('appde', 'AppDev', NULL)
END
BEGIN
TRY CATCH
SELECT ERROR_NUMBER() AS ErrorNum, ERROR_MESSAGE() AS ErrorMessage
END
CATCH;
BEGIN
TRY
INSERT INTO [Northwind].[dbo].[Customers]([CustomerID] ,[CompanyName], [ContactName])
VALUES ('appde', 'AppDev', 'Don Kiely')
END
BEGIN
TRY CATCH
SELECT ERROR_NUMBER() AS ErrorNum, ERROR_MESSAGE() AS ErrorMessage
END
CATCH;
-- Test HashString
SELECT
dbo.HashString('AppDev trains developers!')
-- Test Concatenate
SELECT
dbo.Concatenate([ProductName]) FROM ProductsWHERE CategoryID = 4
-- Keeping Track
-- *************
USE
GO
Northwind
-- System catalog views
SELECT
* FROM sys.assemblies
SELECT
* FROM sys.assembly_files
SELECT
* FROM sys.assembly_modules
SELECT
* FROM sys.procedures WHERE type = 'PC'
SELECT
* FROM sys.triggers WHERE type = 'TA'
SELECT
-- Add a text file to the assembly
-- Chagne path as necessary
* FROM sys.types
ALTER
ASSEMBLY SQLCLR ADD FILE FROM 'C:\SQLCLR Info.txt'
-- View saved source code
SELECT
f.Name, CAST(content AS VARCHAR(MAX)) AS SourceFROM sys.assembly_files f JOIN sys.assemblies aON f.assembly_id = a.assembly_id
WHERE
f.name LIKE '%.cs' OR f.name LIKE '%.vb'
-- View all loaded assemblies
SELECT
a.name, a.assembly_id, a.clr_name, a.permission_set_desc, l.load_timeFROM sys.assemblies a INNER JOIN sys.dm_clr_loaded_assemblies lON (a.assembly_id = l.assembly_id)
-- Security
-- *************
-- Change the path and file name to a small text file to read
SELECT
dbo.ReadFile('C:\SQL2005Core\SQLCLR\SQLCLR Info.txt')
ALTER
DROP
GO
DATABASE NorthwindSET TRUSTWORTHY ON FUNCTION dbo.ReadFile
DROP
GO
ASSEMBLY ReadFile
CREATE
ASSEMBLY ReadFileFROM 'C:\SQL2005Core\SQLCLR\ReadFile\C#\ReadFile\ReadFile\bin\Debug\ReadFile.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
CREATE
FUNCTION dbo.ReadFile(@filename NVARCHAR(1000))
RETURNS NVARCHAR(4000)
EXTERNAL NAME ReadFile.UserDefinedFunctions.ReadFile