working with images as a database table column

Question

Hi,

Ii 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 have written a code for that 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 proceed further. I mean how to extract the column from the reader after executing the above statements? Also I need to go through the entire column row by row and convert each image to an IContent item. Do I have to write my own converter for this or is there any .NET class that does this already?

Heres the scenario. I have a table with like 20 columns one of which is the images_column. That column is the ONLY column that has images stored in as binary data. The remainig columns are usual integers (its size value) and strings (its name, location, etc) associated with that particular image. I hope that it is clear now.

Thanks.

Answer 1

I'm not sure from your question exactly what you're trying to accomplish, but to get the image out of the column as a Bitmap with your reader, you could 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
    }
}

Answer 2

Depending on how you save the image in the database, you could extract them by using the Bitmap class. It takes several different arguments to it's constructors, among others a Stream for instance.

You get the data from the reader by using its Read function. It gives you a bool to let you know if you have a valid row or not. You could use this in a if-statement or a while-statement.

Feed the data from the database column to a MemoryStream for instance and give that to the Bitmap constructor.

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

Answer 3

        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));
                    }
                }
            }
        }