Working with images as a column in a database table

Question

I have a database (sql server 2008) and I am working with C # in visual studio 2010.

I want to establish a connection with my db. I wrote the code for this as follows:

SqlConnection mySqlConnection = new SqlConnection ("Data Source=servername\\SQL2008; Initial Catalog=MyData;UID=MyID;PWD=mypassword;"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT image_column FROM images_table "; mySqlConnection.Open(); SqlDataReader productsSqlDataReader = mySqlCommand.ExecuteReader(); 

I do not know how to move on. I mean, how do I retrieve a column from the reader after completing the above statements? I also need to go through the entire column in a row after row and convert each image into an IContent element. Do I have to write my own converter for this, or is there any .NET class that does this already?

Here is the script. I have a table with 20 columns, one of which is image_column. This column is ONLY a column in which there are images stored as binary data. The remainder columns are ordinary integers (its dimensional value) and strings (its name, location, etc.) Associated with this particular image. Hope this is clear now.

Thanks.

Answer 1

  string conn = "Data Source=servername\\SQL2008; Initial Catalog=MyData;UID=MyID;PWD=mypassword;"; using (SqlConnection dbConn = new SqlConnection(conn)) { dbConn.Open(); string sql = "SELECT DATALENGTH(image_column), image_column FROM images_table "; using (SqlCommand cmd = new SqlCommand(sql, dbConn)) { using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { int size = reader.GetInt32(0); byte[] buff = new byte[size]; reader.GetBytes(1, 0, buff, 0, size); MemoryStream ms = new MemoryStream(buff, 0, buff.Length); ms.Write(buff, 0, buff.Length); StoreImage(Image.FromStream(ms, true)); } } } } 

Answer 2

Depending on how you save the image in the database, you can extract them using the Bitmap class. It takes several different arguments for its constructors, including, for example, Stream.

You receive data from the reader using the Read function. This gives you a bool so you know if you have a valid string or not. You can use this in an if or while statement.

Pass the data from the database column into a MemoryStream, for example, and pass it to the Bitmap constructor.

 while (productsSqlDataReader.Read()) { MemoryStream stream = new MemoryStream((byte[])productsSqlDataReader["image_column"]); new Bitmap(stream); } 

Answer 3

I'm not sure about your question what you are trying to accomplish, but to get an image from a column like Bitmap with your reader, you can use the following.

 while (productsSqlDataReader.Read()) { byte[] buf = productsSqlDataReader["image_column"] as byte[]; using (MemoryStream ms = new MemoryStream(buf)) { Bitmap bmp = Bitmap.FromStream(ms); //do whatever with the bitmap BEFORE you dispose the stream } }