Asp.net Read Line by Line Blob File
Introduction
Binary Large Objects (Blob) information tin be a graphical image, a pdf document, a music file or any of a wide range of information types, which tin by and large be saved into a SQL Server database.
As function of a series of investigations I'm currently performing inside SQL Server, I take looked at how BLOB data can exist saved and retrieved.
Saving the information is reasonably simple. Retrieving information technology, less and so….
Background
I take never needed to save Hulk information within a SQL Server database. At that place are diverse arguments for and against storing such items within a database but I'm not going to business organisation myself with that in this article; I merely want to learn how it can exist washed.
Detail
For this example, I'thou going to save a jpg paradigm into a database and then extract it in a diverseness of means:
- BCP via SQL Server Management Studio (SSMS)
- BCP in a control window
- OLE object creation via SSMS
- PowerShell
The code used in this article tin exist downloaded from here.
Preparing the Example
The starting time step is to create a test database, with a table that has a varbinary(max) cavalcade inside information technology.
Listing 1: SQL code to create database and tabular array
CREATE DATABASE BLOB_Test; GO USE BLOB_Test; GO CREATE Tabular array dbo.PicturesTest ( ID INT IDENTITY(1, 1), PictureName VARCHAR(50) Not NULL, PictureData VARBINARY(MAX) Non Nothing ); GoSaving the Image to the Database
In this example, I'thousand saving a jpg by using OPENROWSET to save the SINGLE_BLOB. An additional column has a suggested name for the image file, which will be used later, within the PowerShell example.
Listing ii: Storing the image
USE BLOB_Test; Get INSERT INTO dbo.PicturesTest ( PictureName, PictureData ) SELECT 'Wile_E_Coyote.jpg', BulkColumn FROM OPENROWSET(Bulk 'C:\BLOBTest\BLOBIn\Oops.jpg', SINGLE_BLOB) Every bit Hulk; GOThe outcome of this is a binary column containing the image data.
Image 1: Upshot of Listing 2
Saved jpg
BCP via SQL Server Management Studio (SSMS)
The Majority Copy Programme (BCP) utility can be used to re-create data between SQL Server and data files.
Using this within SSMS requires the use of xp_cmdshell, which in turn requires that xp_cmdshell is permitted within SQL Server. Not all sites allow this, so this pick might not be permitted.
Exporting via BCP can exist problematic and is improved by using a 'format file'. This example has a format file supplied ('BLOB.fmt').The SQL code to save the image data from SQL Server, to a file is:
Listing 3: Saving the image data to a file
Apply BLOB_Test; GO DECLARE @sql VARCHAR(1000); SET @sql = 'BCP "SELECT PictureData FROM BLOB_Test.dbo.PicturesTest " QUERYOUT C:\BLOBTest\BlobOut\WC.jpg -T -f "C:\BLOBTest\FormatFile\BLOB.fmt" -S ' + @@SERVERNAME; SELECT @sql; EXEC master.dbo.xp_cmdshell @sql;If xp_cmdshell is not permitted, you volition receive the bulletin:
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information most enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.To enable xp_cmdshell, the easiest method is to execute sp_configure, which changes the settings for the entire instance, not just the database that we're using.
Listing 4: Enabling xp_cmdshell
EXEC sp_configure 'Show Advanced Options',1; RECONFIGURE; Go EXEC sp_configure 'xp_cmdshell',1; RECONFIGURE; BecomeIf this completes without any errors, execute the code in List 2 again and a file volition exist created in the target binder.
Image 2: Result of successful List 3
Saved Image
BCP in a command window
Where it isn't possible to activate xp_cmdshell, BCP can be executed from a command window.
Firstly, ensure the BLOBOut folder is empty, so nosotros know that this command completed successfully.
Make certain that you replace 'ServerName' and 'InstanceName' with values appropriate for your environment.Listing 5: BCP in a CMD Window
BCP "SELECT PictureData FROM BLOB_Test.dbo.PicturesTest " QUERYOUT C:\BLOBTest\BlobOut\WC.jpg -T -f "C:\BLOBTest\FormatFile\Hulk.fmt" -South <ServerName>\<InstanceName>Image 3: Effect of List five
BCP via CMD Window Results
OLE object creation via SSMS
Equally with using xp_cmdshell, this example requires that sp_configure is used to allow 'Ole Automation Procedures', otherwise a mistake message similair to the post-obit will exist raised:
Msg 15281, Level 16, State one, Procedure sp_OACreate, Line 1 [Batch Kickoff Line 2]
SQL Server blocked access to process 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator tin enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.If Ole Automation Procedures is not enabled and information technology is permitted to change this, then the code below will activate it. As with the activation of xp_cmdshell, this is an instance-wide command.
List 6: Reconfigure to let Ole Automation Procedures
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', one; GO RECONFIGURE; BecomeThe example below saves the BLOB data to a specified file location and name by using a serial of OLE objects. Check that the output location is empty, then you are sure that information technology has completed successfully.
Listing 7: Saving the BLOB data to a file via OLE processes
Utilise BLOB_Test; Get DECLARE @init INT, @Data VARBINARY(MAX), @FilePath VARCHAR(MAX); Begin SELECT @data = PictureData, @FilePath = 'C:\BLOBTest\BLOBOut\WC.jpg' FROM dbo.PicturesTest; EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- Create Object EXEC sp_OASetProperty @init, 'Blazon', 1; EXEC sp_OAMethod @init, 'Open'; EXEC sp_OAMethod @init, 'Write', NULL, @data; EXEC sp_OAMethod @init, 'SaveToFile', Naught, @FilePath, 2; EXEC sp_OAMethod @init, 'Close'; EXEC sp_OADestroy @init; -- Destroy Object Finish;PowerShell
This example has been taken from a Technet article, with minor editing for the instance, file location and suchlike. It loops through the source table to extract all Hulk fields, using the 'PictureName' column to dictate the resultant filename. You will need to modify the entry for $Database to the instance for your environment.
Listing 8: Saving the Blob information to a file via PowerShell
## https://social.technet.microsoft.com/wiki/contents/articles/890.export-sql-server-blob-data-with-powershell.aspx ## Export of "larger" Sql Server Blob to file ## with GetBytes-Stream. # Configuration information $Server = ".\<Instance>"; # SQL Server Instance. $Database = "Blob_Test"; $Dest = "C:\BLOBTest\BLOBOut\"; # Path to consign to. $bufferSize = 8192; # Stream buffer size in bytes. # Select-Statement for name & blob # with filter. $Sql = "SELECT [PictureName] ,[PictureData] FROM dbo.PicturesTest"; # Open ADO.Internet Connectedness $con = New-Object Data.SqlClient.SqlConnection; $con.ConnectionString = "Data Source=$Server;" + "Integrated Security=True;" + "Initial Catalog=$Database"; $con.Open up(); # New Command and Reader $cmd = New-Object Information.SqlClient.SqlCommand $Sql, $con; $rd = $cmd.ExecuteReader(); # Create a byte array for the stream. $out = [array]::CreateInstance('Byte', $bufferSize) # Looping through records While ($rd.Read()) { Write-Output ("Exporting: {0}" -f $rd.GetString(0)); # New BinaryWriter $fs = New-Object System.IO.FileStream ($Dest + $rd.GetString(0)), Create, Write; $bw = New-Object Organisation.IO.BinaryWriter $fs; $first = 0; # Read first byte stream $received = $rd.GetBytes(ane, $start, $out, 0, $bufferSize - 1); While ($received -gt 0) { $bw.Write($out, 0, $received); $bw.Flush(); $offset += $received; # Read adjacent byte stream $received = $rd.GetBytes(1, $commencement, $out, 0, $bufferSize - 1); } $bw.Close(); $fs.Close(); } # Closing & Disposing all objects $fs.Dispose(); $rd.Shut(); $cmd.Dispose(); $con.Shut(); Write-Output ("Finished");Prototype 4: Upshot of Listing 8
Saved via PowerShell
Summary
I'm non overly corking on the methods required to salvage or excerpt BLOB data with SQL Server but it does work. Hopefully one of the examples in this article should be suitable, depending upon the surroundings.
References
Technet – Saving BLOB Data with PowerShell
Format Files
Source: https://sqlrambling.net/2020/04/04/saving-and-extracting-blob-data-basic-examples/
0 Response to "Asp.net Read Line by Line Blob File"
Post a Comment