reading / writing binary data to db

I'm trying to read a byte array of an image datatype from sql server, and then to put this in another field in the database. I get a byte array, but somehow the image doesn't get into the db well with the sql parameters. Does anyone have an idea how to tackle this problem?

Thanks a lot, Hugohttp://www.dotnetspider.com/Technology/QA/ViewQuestion.aspx?QuestionId=117

I think that link above has the answers to your question. Make sure you're using Image type, and passing in the byte array, and that you don't have anything blocking your connection (firewall or switch that's set to cap communication sizes).

Otherwise, I don't know what to tell ya. Need to see code.|||Thanks for the link, it helps me somehow although not enough, however...

Basically, what I'm trying to do is to "pump" binary data from one field in one table to another field in another table (both are of the image data type, length 16).

What I have now, is a byte array read from the original field, and set it as a value of an SqlParameter to insert it into the new field. This is the code fragment:

Dim SQLSel As String = "SELECT imgImage FROM tblProducts WHERE autoProductID=" & CategoryID
Dim reader As SqlClient.SqlDataReader
reader = _data.sqlRetrieveDataReader(SQLSel) 'retrieve a data reader via DAL
reader.Read()
'set the binary data of the original image in the byte array
Dim b() As Byte = CType(reader.GetValue(0), Byte())
Dim stream As System.IO.MemoryStream = New System.IO.MemoryStream(b, True)
stream.Write(b, 0, b.Length)
'insert the byte array into the new parameter
sqlComInsert.Parameters.Item("@.imgImage").Value = b

No errors are trapped when this code is executed. However, only red crossed (i.e. bad images) are displayed in the browser. The following code is used to display images from the database (which has proven to work on the original images/binary data):

 Dim ProductID As String = Request.QueryString("ProductID")
 Dim conn As New SqlClient.SqlConnection(CType(configurationAppSettings.GetValue("sqlConn.ConnectionString", GetType(System.String)), String))
 Dim SQLSel As String = "SELECT imgImage FROM tblProducts WHERE autoProductID=" & CategoryID
 Dim com As New SqlClient.SqlCommand(SQLSel, conn)
 conn.Open()
 Try
 Response.ContentType = "image/gif"
 Response.BinaryWrite(com.ExecuteScalar)
 Response.End()
 Finally
 conn.Close()
 End Try

I really have no clue what causes the movement of the images/binary data via asp.net to fail... if anyone knows how to tackle this problem, please help. Thanks a lot in advance, Hugo

Read Write Image data with Sqlceserver

I was wondering if it is possible to read image data from a database in .net compact framework. Since cf does not have image.fromstream(memstream) to work with, I don't know how else to read the image from the database and then place it into a picturebox.

Here is the code I have been trying out:

Dim Img As Image
'
Dim conn As New SqlCeConnection("Data Source = \My Documents\test2.sdf")

conn.Open()

Dim sql As String = "SELECT * FROM Dater"
Dim cmd As New SqlCeCommand(sql, conn)
Dim reader As SqlCeDataReader = _
cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

While reader.Read()

TextBox1.Text = reader.Item("name")
Dim b(reader.GetBytes(1, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte

reader.GetBytes(1, 0, b, 0, b.Length)
Dim ms As New System.IO.MemoryStream(b)
Dim bmp As New Bitmap(ms) <-Error: Value does not fall within expected range
Img = bmp

End While

PictureBox2.Image = Img

I get an error ,Value does not fall within expected range.

Does this mean the image was not save correctly in the database?

Thanks for any helpJust to give somebody an idea on how to do this you can try converting an image to a string and back again using the convertFromBase64 and convertToBase64 functions. Then you can save an image to a database as a string using SQLServer.

Thanks for all of your help, really appreciate it.

sql

Read Write Image data with Sqlceserver

I was wondering if it is possible to read image data from a database in .net compact framework. Since cf does not have image.fromstream(memstream) to work with, I don't know how else to read the image from the database and then place it into a picturebox.

Here is the code I have been trying out:

Dim Img As Image
'
Dim conn As New SqlCeConnection("Data Source = \My Documents\test2.sdf")

conn.Open()

Dim sql As String = "SELECT * FROM Dater"
Dim cmd As New SqlCeCommand(sql, conn)
Dim reader As SqlCeDataReader = _
cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

While reader.Read()

TextBox1.Text = reader.Item("name")
Dim b(reader.GetBytes(1, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte

reader.GetBytes(1, 0, b, 0, b.Length)
Dim ms As New System.IO.MemoryStream(b)
Dim bmp As New Bitmap(ms) <-Error: Value does not fall within expected range
Img = bmp

End While

PictureBox2.Image = Img

I get an error ,Value does not fall within expected range.

Does this mean the image was not save correctly in the database?

Thanks for any helpJust to give somebody an idea on how to do this you can try converting an image to a string and back again using the convertFromBase64 and convertToBase64 functions. Then you can save an image to a database as a string using SQLServer.

Thanks for all of your help, really appreciate it.

read image from Sql Server

Hello!

My real problem is that i want to read an image from an Sql Server DB, resize them and send them, all this through a Web Service. I decide to split my problem in four steps:

1 - read image from Database

2 - Resize that image

3 - Send her back to the DB

4 - Send the image throw a web service after he use a dataset to read the data from the DB.

i try to read the database with this function

Code Snippet

public System.Drawing.Image ReadImgFromDB(Int64 imageID)

{

System.Drawing.Image image = null;

using (SqlConnection conn = new SqlConnection())

{

string connString = WebConfigurationManager.ConnectionStrings

["StringCC"].ConnectionString;

conn.ConnectionString = connString;

SqlCommand myCommand = new SqlCommand(

"SELECT Pic FROM Images WHERE ID='"+ imageID +"'", conn);

conn.Open();

byte[] imageData = (byte[])myCommand.ExecuteScalar();

System.IO.MemoryStream memStream = new System.IO.MemoryStream(imageData);

image = System.Drawing.Image.FromStream(memStream);

}

return image;

}

i introduce a valid image ID and i get this message

" System.ArgumentException: Parameter is not valid.
at System.Drawing.Image.FromStream(Stream stream, Boolean useEmbeddedColorManagement, Boolean validateImageData)
at System.Drawing.Image.FromStream(Stream stream)
at Service.ReadImgFromDB(Int64 chaveI) in e:\Web Projects\WSservice\App_Code\Service.cs:line 48"--

line 48: "image = System.Drawing.Image.FromStream(memStream);"

Thank you!

Seems like the image is corrupt, the code looks ok, though you should use parametrized queries: This will prevent SQL injection

Code Snippet

SqlCommand myCommand = new SqlCommand("SELECT Pic FROM Images WHERE ID= @.imageID", conn);

SqlParameter paramImage = new SqlParameter(@.imageID, SqlType.Varchar,20); // Depends on your parameter type

paramImage.Value = imageID;

myCommand.Parameters.Add(paramImage);

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

thank you for your reply.

Yes i usually use parameters, but for now i'm just "playing" with code.

I didn't check the images, i just assume that they're ok. i'll check them.

Thanks!

sql

Read Image Data From SQL Server

Here is my task I am storing pdf's in sql server. I would like to retrieve the binary data from sql server and write the pdf content into an existing aspx page to the appropriate pageview section. What is the best way to handle this. The code works below but it loads a new browser with the content. I need it to appear in it's tabbed section in the original aspx file. Any assistance you can give me would be greatly appreciated.

Thanks Jerry

oSQLConn.Open()

Dim myreaderAs SqlDataReader

myreader = myCommand.ExecuteReader

Response.Expires = 0

Response.Buffer =True

Response.Clear()

DoWhile (myreader.Read())

Response.ContentType = ("application/pdf")

Response.BinaryWrite(myreader.Item("img_content"))

Loop

Hi,

From your description, it seems that you are going to retrieve the PDF data in tabbed section of the current aspx file, right?

Well, in your scenario, I noticed that you are going to reset the content type to "application/pdf", but the aspx page's content type is "text/html", they cannot be displayed at the same page.

I guess you have comment the "Response.contenttype.." line, right? And you are using Resposne. Binary Write to write the specified information to the current HTTP output. I suggest that you can have a try to wrap all these codes into a user control, use this control in your tabbed section of your current aspx page and try to see if it can work.

Thanks.

|||

Thanks for your response. Maybe this description will help more. I havea tab control on an aspx form. Each tab needs to display the picture, pdf etc etc associated with that particular tab for a specific user. The binary data is stored in a sql server 2005 image field. What would be the best way to achieve the desired result. What would be a good way to handle this? I should also add there may be more then one pdf/image for a particular section.

Thanks

Jerry

|||

Hi,

Well, you can add a Image control in your Tab control container, and create a page which works for displaying the image data from database.

The code snippet on that page to show the image data: (ShowImg.aspx)

// myRead is a DataReader object.
Byte[] Buffer = (Byte[])myRead[0];

//Output
this.Response.Clear();
this.Response.BinaryWrite(Buffer);
this.Response.End();

And then, assign the ImageUrl property of the Image control.

this.Image1.ImageUrl = "showimg.aspx"

Of course, if you want to show different pictures based on the parameters like following:

this.Image1.ImageUrl = "showimg.aspx?imageid=123"

Then, you can receive the parameter on ShowImg.aspx, make a query with that parameter against the database and return the corresponding image data.

Thanks.

Raster Catalog

Does anyone have an idea if it's possible to create a raster/image catalog using SQL Server?

Thx

Yes it is but it depends as to how valuable it is. If you have meaningful meta data that you want to search on, then store that in a relational schema. The open question comes about as to where to store the images, if they are bigger than 2gb then you must put them in the file system. If they are smaller than that then you can put them in the database or put them in the file system and link to them.

The answer as to where depends on the query workload

Raster Catalog

Does anyone have an idea if it's possible to create a raster/image catalog using SQL Server?

Thx

Yes it is but it depends as to how valuable it is. If you have meaningful meta data that you want to search on, then store that in a relational schema. The open question comes about as to where to store the images, if they are bigger than 2gb then you must put them in the file system. If they are smaller than that then you can put them in the database or put them in the file system and link to them.

The answer as to where depends on the query workload