April 14, 2011 at 11:27 am
Hi,
How can we retrieve the Image data stored in a SQL table?
Can we view it as image without using any .Net code?
I have used following code to upload image into my Table
CREATE TABLE myTable(Document varbinary(max))
INSERT INTO myTable
SELECT * FROM
OPENROWSET(BULK N'C:\Image1.jpg', SINGLE_BLOB) As Document
Thanks.......
April 14, 2011 at 11:49 am
TSQL is the wrong tool for this; TSQL's really designed for set based information, and passing it as recordsets/datatables to an application layer.
TSQL's not designed to be writing to files,; you end up having to use an outside source(like TextCopy.exe or a vbs script via xp_cmdshell) anyway.
you also end up with permissions problems, because the end user typically doesn't have rights to touch files on the server anyway.
you can import and export images with 20 lines of code in VB.NET; that's the right way to do it.
Lowell
April 14, 2011 at 12:15 pm
I agree with Lowell. What good would it do making sql get the image for you? You still have to present it somewhere and that is obviously front end stuff. Just get the byte array from sql and convert that to an image in your front end.
Here is some code I use in a website. This example assumes only one row in the datatable. I stripped out some additional error checking and such but this gives you the basic idea.
DataTable dt = ...whatever code to get your table...
Bitmap bmp = new Bitmap(new MemoryStream((byte[])(dt.Rows[0]["ImageColumn"])));
if (bmp == null)
{
Response.Clear();
Response.StatusCode = 404;
Response.End();
return;
}
else
{
Response.ContentType = "image/jpeg";
bmp.Save(Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg);
bmp.Dispose();
You could just as easily write the stream to a file on disc or some other approach. I just wrote it directly to the response so I didn't have to worry about folder permissions for the web user.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 14, 2011 at 12:59 pm
I just tested this for proof of concept;
the idea here is to loop through all records in a table via a datareader and write each binary object to disk; in this case my test data had .exe and .dll files stored in a database:
'--Imports System.IO
'--Imports System.Data.SqlClient
'--this works whether the datatype for is IMAGe or VARBINARY(max)
Dim sqlcmd As String = "SELECT TOP 3 ModuleName,ModuleData From HDSMODULES;"
Dim mySqlConnectionFormat As String = "data source={0};initial catalog={1};user id={2};password={3};Trusted_Connection=False;Connect Timeout=600;Application Name=GhostInTheMachine;"
Dim MyConn As New SqlConnection
MyConn.ConnectionString = String.Format(mySqlConnectionFormat, ".", "SandBox", "Noobie", "NotARealPassword")
MyConn.Open()
'now lets get a commadn object
Dim mySqlCommand As New SqlCommand
mySqlCommand.Connection = MyConn
mySqlCommand.CommandTimeout = 600
mySqlCommand.CommandType = CommandType.Text
mySqlCommand.CommandText = sqlcmd
Dim myDataReader As SqlDataReader
myDataReader = mySqlCommand.ExecuteReader
While myDataReader.Read
Try
Dim path As String = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)
Dim imageInBytes As Byte() = myDataReader("ModuleData")
Dim memoryStream As System.IO.Stream = New System.IO.MemoryStream(imageInBytes, True)
Dim image As New System.IO.BinaryWriter(File.Open(path & "\" & myDataReader("ModuleName"), FileMode.Create))
'%APPDATA% variable like "C:\Users\Lowell\AppData\Roaming"
image.Write(imageInBytes)
image.Flush()
image.Close()
Catch ex As Exception
Console.WriteLine(ex.StackTrace)
End Try
End While
i'm used to vb.NET, but you can run this code through a vb to CC# converter if you need to. I've also got an example for ASP/vb6 if you need that.
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply