Create CLR objects CS.sql

USE architectureChapter
go
-------------------------------
-- GetToken user-defined function
-------------------------------
IF EXISTS(SELECT * FROM sys.assemblies WHERE name = 'UDFGetToken')
BEGIN
  DROP FUNCTION dbo.GetToken
  DROP ASSEMBLY UDFGetToken
END

CREATE ASSEMBLY UDFGetToken
FROM 'c:\ProDatabaseDesignSqlClr\cs\UDFGetToken\bin\UDFGetToken.dll'
go

CREATE FUNCTION dbo.GetToken(@s nvarchar(4000),
             @delim nvarchar(10), @tokennum tinyint)
RETURNS nvarchar(4000)
AS EXTERNAL NAME [UDFGetToken].[Apress.ProSqlServerDatabaseDesign.UserDefinedFunctions].GetToken
go

-------------------------------
-- IsValidSsn user-defined function
-------------------------------
IF EXISTS(SELECT * FROM sys.assemblies WHERE name = 'UDFSsn')
BEGIN
  DROP FUNCTION dbo.IsValidSsn
  DROP ASSEMBLY UDFSsn
END

CREATE ASSEMBLY UDFSsn
FROM 'c:\ProDatabaseDesignSqlClr\cs\UDFSsn\bin\UDFSsn.dll'
go

CREATE FUNCTION dbo.IsValidSsn(@s nvarchar(11))
RETURNS bit
AS EXTERNAL NAME [UDFSsn].[Apress.ProSqlServerDatabaseDesign.UserDefinedFunctions].IsValidSsn
go

-------------------------------
-- TitleCase user-defined function
-------------------------------
IF EXISTS(SELECT * FROM sys.assemblies WHERE name = 'UDFTitleCase')
BEGIN
  DROP FUNCTION dbo.TitleCase
  DROP ASSEMBLY UDFTitleCase
END

CREATE ASSEMBLY UDFTitleCase
FROM 'c:\ProDatabaseDesignSqlClr\cs\UDFTitleCase\bin\UDFTitleCase.dll'
go

CREATE FUNCTION dbo.TitleCase(@s nvarchar(4000))
RETURNS nvarchar(4000)
AS EXTERNAL NAME [UDFTitleCase].[Apress.ProSqlServerDatabaseDesign.UserDefinedFunctions].TitleCase
go

-------------------------------
-- GetFilesInFolder table-valued function
-------------------------------
IF EXISTS(SELECT * FROM sys.assemblies WHERE name = 'TVFFileList')
BEGIN
  DROP FUNCTION dbo.GetFilesInFolder
  DROP ASSEMBLY TVFFileList
END

CREATE ASSEMBLY TVFFileList
FROM 'c:\ProDatabaseDesignSqlClr\cs\TVFFileList\bin\TVFFileList.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
go

CREATE FUNCTION dbo.GetFilesInFolder(@path nvarchar(255), @pattern nvarchar(255))
RETURNS TABLE (FileName nvarchar(255), FileSize int, FileDate datetime)
AS EXTERNAL NAME [TVFFileList].[Apress.ProSqlServerDatabaseDesign.UserDefinedFunctions].GetFilesInFolder
go

-------------------------------
-- UDTDate user-defined type
-------------------------------
IF EXISTS(SELECT * FROM sys.types WHERE name = 'Date')
BEGIN
  DROP TYPE Date
END

IF EXISTS(SELECT * FROM sys.tables WHERE name = 'testDate')
BEGIN
    DROP TABLE dbo.testDate
END

IF EXISTS(SELECT * FROM sys.assemblies WHERE name = 'UDTDate')
BEGIN
  DROP ASSEMBLY UDTDate
END

CREATE ASSEMBLY UDTDate
FROM 'c:\ProDatabaseDesignSqlClr\cs\UDTDate\bin\UDTDate.dll'
go

CREATE TYPE Date
EXTERNAL NAME [UDTDate].[Apress.ProSqlServerDatabaseDesign.DateUdt]
go

-------------------------------
-- UDTSsn user-defined type
-------------------------------
IF EXISTS(SELECT * FROM sys.types WHERE name = 'SSN')
BEGIN
  DROP TYPE SSN
END

IF EXISTS(SELECT * FROM sys.tables WHERE name = 'testSsnUDT')
BEGIN
    DROP TABLE dbo.testSsnUDT
END

IF EXISTS(SELECT * FROM sys.assemblies WHERE name = 'UDTSsn')
BEGIN
  DROP ASSEMBLY UDTSsn
END

CREATE ASSEMBLY UDTSsn
FROM 'c:\ProDatabaseDesignSqlClr\cs\UDTSsn\bin\UDTSsn.dll'
go

CREATE TYPE SSN
EXTERNAL NAME [UDTSsn].[Apress.ProSqlServerDatabaseDesign.SsnUdt]
go


USE AdventureWorks
go
-------------------------------
-- List user-defined aggregate
-------------------------------
IF EXISTS(SELECT * FROM sys.assemblies WHERE name = 'UDAggList')
BEGIN
  DROP AGGREGATE dbo.List
  DROP ASSEMBLY UDAggList
END

CREATE ASSEMBLY UDAggList
FROM 'c:\ProDatabaseDesignSqlClr\cs\UDAggList\bin\UDAggList.dll'
go

CREATE AGGREGATE dbo.List(@value nvarchar(1000))
RETURNS nvarchar(4000)
EXTERNAL NAME [UDAggList].[Apress.ProSqlServerDatabaseDesign.List]
go

USE architectureChapter
go
-------------------------------
-- CopyFile stored procedure
-------------------------------
IF EXISTS(SELECT * FROM sys.assemblies WHERE name = 'SPCopyFile')
BEGIN
  DROP PROCEDURE dbo.CopyFile
  DROP ASSEMBLY SPCopyFile
END

CREATE ASSEMBLY SPCopyFile
FROM 'c:\ProDatabaseDesignSqlClr\cs\SPCopyFile\bin\SPCopyFile.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
go

CREATE PROCEDURE dbo.CopyFile(@source nvarchar(1000), @destination nvarchar(1000), @overwrite bit)
AS EXTERNAL NAME [SPCopyFile].[Apress.ProSqlServerDatabaseDesign.StoredProcedures].CopyFile
go

USE AdventureWorks
go
-------------------------------
-- sales$orderCount stored procedure
-------------------------------
IF EXISTS(SELECT * FROM sys.assemblies WHERE name = 'SPDataAccess')
BEGIN
  DROP PROCEDURE dbo.sales$orderCount
  DROP ASSEMBLY SPDataAccess
END

CREATE ASSEMBLY SPDataAccess
FROM 'c:\ProDatabaseDesignSqlClr\cs\SPDataAccess\bin\SPDataAccess.dll'
go

CREATE PROCEDURE dbo.sales$orderCount(@CustId int)
AS EXTERNAL NAME [SPDataAccess].[Apress.ProSqlServerDatabaseDesign.StoredProcedures].GetSalesOrderCount
go

USE architectureChapter
go
-------------------------------
-- SsnTrigger trigger
-------------------------------
IF EXISTS(SELECT * FROM sys.assemblies WHERE name = 'TRSsn')
BEGIN
  DROP TABLE testTriggerCLR
  DROP ASSEMBLY TRSsn
END
go

CREATE ASSEMBLY TRSsn
FROM 'c:\ProDatabaseDesignSqlClr\cs\TRSsn\bin\TRSsn.dll'
go

CREATE TABLE dbo.testTriggerCLR (ssn varchar(11))
go

CREATE TRIGGER SsnTrigger ON dbo.testTriggerCLR FOR INSERT, UPDATE
AS EXTERNAL NAME [TRSsn].[Apress.ProSqlServerDatabaseDesign.Triggers].SsnTrigger
go