استخراج و درج تصویر در SQL Server به کمک T-SQl

نیازمندی :یکی از مواردی که معمولا تجربه برخورد با اون رو داریم، درج تصاویر در پایگاه داده SQL Server و گاهی هم برعکس استخراج این تصاویر از دیتابیس SQL Server است. اما معمولا به دنبال راه حلی هستیم که بدون نیاز به ابزار های کمکی (third party tools) و همچنین بدون بکارگیری راهکارهایی مثل SSIS – Integration Service ، یا BCP – Bulk Copy Program استخراج و درج تصویر در SQL Server رو انجام بدیم.راهکار :راه حل به این صورت خواهد بود که اقدام به ساخت دو Stored procedure می‌کنیم ، یکی برای درج تصویر در جدول SQL و Procedure دوم برای استخراج تصویر از جدول SQL.هر دو procedure شامل سه ورودی یکسان هستند :@PicName :نام عکس که یه صورت unique key تعریف شده و نگهداری میشه.@ImageFolderPath :هنگام Export به عنوان محل ذخیره فایل تصاویر استخراج شده بکار می ره و هنگام درج محلی هست که باید فایل های تصاویر رو به اونجا منتقل کنیم، دقت داشته باشین که این آدرس باید در ماشین SQL Server وجود داشته باشه و نه ماشین لوکال (درصورت وصل شدن به SQL به صورت ریموت ) تا امکان استخراج و درج تصویر در SQL Server رو فراهم کنه.@Filename :نام فایل ورودی یا خروجیProcedure درج تصویر ، از ترکیب Function با عنوان OPENROWSET و قابلیت Bulk استفاده میکنه برای درج فایل در SQL Server. درواقع تا زمانی که پارامتر ورودی وجود داشته باشه، کوئری به صورت داینامیک از تابع SQL EXEC استفاده میکنه.Procedure استخراج تصویر، از قابلیت OLE Automation Procedure ها استفاده میکنه تا تصویر ذخیره شده در متغیر های varbinary رو براساس نام تصاویر برروی OS ذخیره کنه. برای اینکار به سراغ sp_OAMethod که از procedure های سیستمی هست میره.ساختن جدول و Stored Procedure هابرای استخراج و درج تصویر در SQL Server جدول ساده ای با نام dbo.Picture درست میکنیم . این جدول شامل ستون pictureName ، picFileName و PictureData هست.کوئری ساخت جدول :CREATE TABLE Pictures ( pictureName NVARCHAR(40) PRIMARY KEY NOT NULL ,picFileName NVARCHAR (100),PictureData VARBINARY (max) ) GOتوجه داشته باشیم که چند تنظیم سطح دسترسی نیاز هست تا OLE Automation Procedures در SQL Server برای استخراج تصاویر فعال بشه. هم چنین دسترسی BulkAdmin برای کاربری که T-SQL رو اجرا میکنه داده بشه.Use master Go XEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO ALTER SERVER ROLE [bulkadmin] ADD MEMBER [Enter here the Login Name that will execute the Import] GOStored Procedure درج تصویر :CREATE PROCEDURE dbo.usp_ImportImage ( @PicName NVARCHAR (100) , @ImageFolderPath NVARCHAR (1000) , @Filename NVARCHAR (1000) ) AS BEGIN DECLARE @Path2OutFile NVARCHAR (2000); DECLARE @tsql NVARCHAR (2000); SET NOCOUNT ON SET @Path2OutFile = CONCAT ( @ImageFolderPath ,'\' , @Filename ); SET @tsql = 'insert into Pictures (pictureName, picFileName, PictureData) ' + ' SELECT ' + '''' + @PicName + '''' + ',' + '''' + @Filename + '''' + ', * ' + 'FROM Openrowset( Bulk ' + '''' + @Path2OutFile + '''' + ', Single_Blob) as img' EXEC (@tsql) SET NOCOUNT OFF END GOStored Procedure استخراج تصویر :CREATE PROCEDURE dbo.usp_ExportImage ( @PicName NVARCHAR (100) ,@ImageFolderPath NVARCHAR(1000) ,@Filename NVARCHAR(1000) ) AS BEGIN DECLARE @ImageData VARBINARY (max); DECLARE @Path2OutFile NVARCHAR (2000); DECLARE @Obj INT SET NOCOUNT ON SELECT @ImageData = ( SELECT convert (VARBINARY (max), PictureData, 1) FROM Pictures WHERE pictureName = @PicName ); SET @Path2OutFile = CONCAT ( @ImageFolderPath ,'\' , @Filename ); BEGIN TRY EXEC sp_OACreate 'ADODB.Stream' ,@Obj OUTPUT; EXEC sp_OASetProperty @Obj ,'Type',1; EXEC sp_OAMethod @Obj,'Open'; EXEC sp_OAMethod @Obj,'Write', NULL, @ImageData; EXEC sp_OAMethod @Obj,'SaveToFile', NULL, @Path2OutFile, 2; EXEC sp_OAMethod @Obj,'Close'; EXEC sp_OADestroy @Obj; END TRY BEGIN CATCH EXEC sp_OADestroy @Obj; END CATCH SET NOCOUNT OFF END GOمثال استفاده از SP های ساخته شده :فایل عکس نمونه ای رو در مسیر C:\MyPictures\Input folder. قرار میدیم و برای استخراج و درج تصویر در SQL Server این تصویر کوئری زیر رو استفاده میکنیم .exec dbo.usp_ImportImage 'DRAGON','C:\MyPictures\Input','Dragon.jpg'و برای استخراج تصویر از دیتابیس از کوئری زیر استفاده میکنیم :exec dbo.usp_ExportImage 'DRAGON','C:\MyPictures\Output','Dragon.jpg'حالا تصویر در مسیر C:\MyPictures\Output\Dragon.jpg قرار دارد.منبع :www.mssqltips.com