/*=====================================================================

  File:      InstallVB.sql for Large Binary Object SQLCLR Example
  Summary:   Installs SQLCLR based stored procedures for storing and 
             retrieving large binary objects between the database and
             filestores accessible from the server.  Also defines a T-SQL 
             helper function for demonstrating calling T-SQL stored procedures 
             from managed code.
  Date:      May 24, 2004

---------------------------------------------------------------------

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.

 This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= */

USE AdventureWorks
GO

-- Drop procedures defined in this script if they exist

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_UpdateImage')
DROP PROCEDURE [dbo].[usp_UpdateImage];
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'GetPhotoFromDB')
DROP PROCEDURE [dbo].[GetPhotoFromDB];
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'PutPhotoIntoDB')
DROP PROCEDURE [dbo].[PutPhotoIntoDB];
GO

-- If the assembly we want to add already exists, drop it.

IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'HandlingLOBUsingCLR')
DROP ASSEMBLY HandlingLOBUsingCLR;
GO

-- Add the assembly which contains the CLR methods we want to invoke on the server.
DECLARE @SamplesPath nvarchar(1024)
-- You may need to modify the value of the this variable if you have installed the sample someplace other than the default location.
SELECT @SamplesPath = replace(physical_name, 'Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\') 
    FROM master.sys.database_files 
    WHERE name = 'master';

CREATE ASSEMBLY HandlingLOBUsingCLR
FROM @SamplesPath + 'HandlingLOBUsingCLR\VB\HandlingLOBUsingCLR\bin\HandlingLOBUsingCLR.dll'
WITH permission_set = external_access;
GO

-- Register the CLR method for retrieving thumbnail photos from the ProductPhoto table

CREATE PROCEDURE [dbo].[GetPhotoFromDB]
(
    @ProductPhotoID int
    ,@CurrentDirectory nvarchar(1024)
    ,@FileName nvarchar(1024)   
)
AS EXTERNAL NAME HandlingLOBUsingCLR.[Microsoft.Samples.SqlServer.LargeObjectBinary].GetPhotoFromDB;
GO

-- Register the CLR method for storing thumbnail photos into the ProductPhoto table

CREATE PROCEDURE [dbo].[PutPhotoIntoDB]
(
    @ProductPhotoID int
    ,@CurrentDirectory nvarchar(1024)
    ,@FileName nvarchar(1024)   
)
AS EXTERNAL NAME HandlingLOBUsingCLR.[Microsoft.Samples.SqlServer.LargeObjectBinary].PutPhotoIntoDB;
GO


-- Add a helper T-SQL method which does the actual work of updating the row

CREATE PROCEDURE [dbo].[usp_UpdateImage]
/* **********************************************************************************
   Date:    April 22, 2003
   Purpose: Change a thumbnail picture stored in the ProductPhoto table.
   ********************************************************************************** */
    @ProductPhotoID int
    ,@ThumbNailPhoto AS VARBINARY(max)
AS
BEGIN
    SET NOCOUNT ON;

	BEGIN TRY
    UPDATE Production.ProductPhoto 
        SET ThumbNailPhoto = @ThumbNailPhoto
        WHERE ProductPhotoID = @ProductPhotoID;
    IF(@@ROWCOUNT < 1)
        RAISERROR ('Update failed.', 16, 1);
	END TRY
	BEGIN CATCH
        SELECT 
			ERROR_NUMBER() AS ErrorNumber,
			ERROR_SEVERITY() AS ErrorSeverity,
			ERROR_STATE() as ErrorState,
			ERROR_PROCEDURE() as ErrorProcedure,
			ERROR_LINE() as ErrorLine,
			ERROR_MESSAGE() as ErrorMessage;
        RAISERROR ('Update failed.', 16, 1);
	END CATCH;
END  -- END of sp_InsertDocument
GO