In our project we had a dilemma if we should store image files in database or in file system directory.
There are some pros' and cons' for each option, but the most important issue that influenced our decision was that we work with ESRI tools. If the image is small it's better to store it in the file system and not in the database. If we store the image in database (using SDE), it will takes some time and this operation couldn't be in real-time reaction (because the SDE will create a pyramids and others statistics operations before the image is being stored in the database).
I want to share with you an example to insert and retrieve images that have been stored in the database in Image type (SQL server data type, from BLOB family).
Insert an image to database:
private void InsertImageToDatabaseColumn(int imageId)
{
MemoryStream mem = new MemoryStream();
byte[] imageData = new byte[1024];
Stream imageStream = FileUpload1.PostedFile.InputStream;
int imageLength = FileUpload1.PostedFile.ContentLength;
string imageType = FileUpload1.PostedFile.ContentType;
int count = 0;
while (0 < (count = imageStream.Read(imageData, 0, imageData.Length)))
{
mem.Write(imageData, 0, count);
}
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ToString()))
{
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = "Insert into Images values (@Id,@Image)";
SqlParameter p1 = new SqlParameter();
p1.ParameterName = "Id";
p1.Value = imageId;
command.Parameters.Add(p1);
p1 = new SqlParameter();
p1.ParameterName = "Image";
p1.Value = mem.GetBuffer();
command.Parameters.Add(p1);
conn.Open();
command.ExecuteNonQuery();
}
}
}
Show an image from database:
private void GetAndBindImageCoulmn(int imageId)
{
byte[] imageBytes;
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ToString()))
{
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = "Select image from Images where id = @ImageId ";
SqlParameter parameter = new SqlParameter("ImageId", SqlDbType.Int);
parameter.Value = imageId;
command.Parameters.Add(parameter);
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
reader.Read();
imageBytes = (byte[])reader.GetValue(0);
MemoryStream tempStream = new MemoryStream(imageBytes, 0, imageBytes.Length);
Bitmap bitmap = new Bitmap(tempStream);
Response.ContentType = "image/gif";
bitmap.Save(Response.OutputStream, ImageFormat.Gif);
Response.End();
}
}
}
}