What kind of data Type use to save an image in database

Question

I need to insert images i have locally using SQL.

I'm doing it this way :

CREATE TABLE [dbo].[Table_1](
      [SelectedImages] [image] NOT NULL,
      [Path] [ntext] NOT NULL
)

to add an image, do this:

INSERT INTO [testing].[dbo].[Table_1]
           ([SelectedImages]
           ,[Path])
     VALUES
           ('D:\desktop\05022006\free_chart1.gif' ,'D:\desktop\05022006\free_chart1.gif' )

The problem is i need to retrieve the image afterwards...

I use a Telerik control showing an image stored as binary data in a database And i'm not sure the type "Image" is the correct one since it's not working...

                  <telerik:RadBinaryImage runat="server" ID="RadBinaryImage1" DataValue='<%#Eval("Photo") %>'
                        AutoAdjustImageControlSize="false" Width="90px" Height="110px" ToolTip='<%#Eval("ContactName", "Photo of {0}") %>'
                        AlternateText='<%#Eval("ContactName", "Photo of {0}") %>' />

What kind of data type do you guys use to store an image ? Thanks in advance

Do you have to store the images in the database? You could just store the path to where the image is saved. Otherwise, see http://stackoverflow.com/questions/335342/using-sql-server-as-image-store. Note that image will NOT be supported going forward: http://msdn.microsoft.com/en-us/library/ms187993.aspx — dash, Mar 10 '12 at 20:22

score 2 · Answer 1 · answered Mar 11 '12 at 04:07

You can store images in the database using image type as you have it defined in your question. However, you need to convert the image into a byte array first.

string path = "D:\\desktop\\05022006\\free_chart1.gif"
using (System.IO.FileStream fstream = new System.IO.FileStream(path, System.IO.FileMode.Open, System.IO.FileAccess.Read))
{
  // Create a buffer to hold the stream of bytes
  byte[] buffer = new byte[fstream.Length];
  // Read the bytes from this stream and put it into the image buffer
  fstream.Read(buffer, 0, (int)fstream.Length);
  fstream.Close();
  //now you can insert buffer into the database
}

When you want to retrieve the image from the database, you have to convert it back from a byte[].

if (byteArray != null)
{
  if (byteArray.Length > 0)
  {
    // Create a new MemoryStream and write all the information from the byte array into the stream
    using (System.IO.MemoryStream ms = new System.IO.MemoryStream(byteArray, true))
    {
      ms.Write(byteArray, 0, byteArray.Length);

      // Use the MemoryStream to create the new BitMap object
      Bitmap photo = new Bitmap(ms);

      ms.Close();

      //now you can use photo
    }
  }
}

Zo Has · Answer 2 · 2012-03-11T13:31:29.173

What kind of data type do you guys use to store an image ?

It depends upon situation & its your call.
Sometimes just storing the URL of image inside a VARCHAR field would suffice 'C:/Data/UserName/uploads/myGuid+filename.jpg' & actual file is present on disk (transaction management is difficult with this approach but works well for small requirements)
When transaction management is top priority you can store them inside database in VARBINARY(max) field Storing images in SQL Server
SQL Server filestream is also an option with latest SQLServer on which the file is stored on filesystem managed by database SQL Server filestream Overview

What kind of data Type use to save an image in database

2 Answers2