Save Images To SQL Server in .NET

By Robbe D. Morris

Printer Friendly Version

Robbe Morris
Robbe & Melisa Morris
Many, many developers have posted questions to forums asking how to save images to a SQL Server database.  The sample code below is written as a console application class that will read an image from disk, save it to the database, retrieve the newly saved image from the database, and write it back to disk as a new filename.  That should just about cover most of your needs.


In order to run the sample code, you'll need to execute the SQL Server script directly below to create the Images table and stored procedures SaveImage and GetImage.  Of course, you'll also want to set the appropriate permissions for the database user you use.  Then, open up a new console application in Visual Studio.NET and copy and paste the Console Application source code below.  You'll notice a variable for the connection string, image path, and image files that will need to be adjusted accordingly.
These links may also be of use to you
Dyamically Write Image to Response.OutputStream
Resize Images To Thumbnails in .NET
Write ActiveReports.NET to PDF Format In Browser
If you have additional questions, please post them to our forums for Article Discussions.
 
SQL Server Script To Create Images Table And Stored Procedures
  
 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Images]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Images]
GO

CREATE TABLE [dbo].[Images] (
	[ImageID] [int] IDENTITY (1, 1) NOT NULL ,
	[ImageFile] [image] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetImage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetImage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SaveImage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SaveImage]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.GetImage
(
    @ImageID int
)
as 

  select ImageFile
     from Images
     where ImageID = @ImageID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.SaveImage
(
  @ImageID int = 0,
  @ImageFile image,
  @NewID int output
)
AS
 
 declare @rc int
 
 select @rc = 0

if (@ImageID = 0)
BEGIN

      insert Images (ImageFile) values (@ImageFile)
     
      select @NewID = @@identity

      if (@@Error <>  0) select @rc=1
     
      Goto OnExit

END
  
     Update Images
        Set ImageFile = @ImageFile
        where ImageID = @ImageID

        if (@@Error <> 0) select @rc=1
     
        Goto OnExit

        select @NewID = @ImageID

OnExit:

    return @rc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Console Application
 
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace SaveImagesToSQLServer
{
	 
  class Class1
  {
	  
   [STAThread]
   static void Main(string[] args)
   {
     Class1 oMain = new Class1();
     string sImagePath = @"c:\temp";
     string sConStr = "Data Source=localhost;User ID=myuser;Password=mypwd;Initial Catalog=MYDATABASE";	
     string sImageFileName1 = "test1.gif";
     string sImageFileName2 = "test2.gif";
     FileStream oImg;
     BinaryReader oBinaryReader;
     byte[] oImgByteArray;
     int nImageID=0;
     DataTable oTable;
            
     try
     {
            
       oImg = new FileStream(sImagePath + @"\" + sImageFileName1,FileMode.Open,FileAccess.Read);

       oBinaryReader = new BinaryReader(oImg);

       oImgByteArray = oBinaryReader.ReadBytes((int)oImg.Length); 
 
       oBinaryReader.Close();
 
       oImg.Close();

       nImageID = oMain.SaveImage(sConStr,nImageID,oImgByteArray);

       if (nImageID > 0)
       {
                   
         oTable = oMain.GetDataTable("GetImage " + nImageID.ToString(),sConStr);
 
         foreach(DataRow oRow in oTable.Rows)
         {
           oImgByteArray = (byte[])oRow["ImageFile"];
           FileStream oOutput = File.Create(sImagePath + @"\" + sImageFileName2,oImgByteArray.Length);
           oOutput.Write(oImgByteArray,0,oImgByteArray.Length);
           oOutput.Close(); 
         } 
       }
              
     }
     catch (Exception e) { Console.WriteLine(e.Message); }
     Console.ReadLine();
  }
	 
  public DataTable GetDataTable(string sQuery,string sConnectionString)
  {
         
    SqlConnection oConn = new SqlConnection();
    DataTable oTable = new DataTable();
		 
    try
    {   
      oConn.ConnectionString = sConnectionString;
      oConn.Open();	  
      SqlDataAdapter oDA = new SqlDataAdapter("EXEC " + sQuery,oConn);
      oDA.Fill(oTable);
      oConn.Close();
    }
    catch (Exception e) { throw new Exception(e.Message);}  
    finally { if (oConn.State == ConnectionState.Open) { oConn.Close(); } } 
    return oTable;
  }
 

  public int SaveImage(string sConnectionString,int nImageID, byte[] oImage)
  {
			
    string sRet="";
    int nRet=0;
    int nNewID=0;
       
    SqlConnection oConn = new SqlConnection();

    try
    {  

      oConn.ConnectionString = sConnectionString;
      oConn.Open();
				   
      SqlCommand oCmd = new SqlCommand("dbo.SaveImage",oConn);
        
      oCmd.CommandType=CommandType.StoredProcedure;
      oCmd.Parameters.Add(new SqlParameter("@RETURN_VALUE",SqlDbType.Int,0));
      oCmd.Parameters.Add(new SqlParameter("@ImageID",SqlDbType.Int,0));
      oCmd.Parameters.Add(new SqlParameter("@ImageFile",SqlDbType.Image, 2147483647));
      oCmd.Parameters.Add(new SqlParameter("@NewID",SqlDbType.Int,0));
      oCmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;  
      oCmd.Parameters["@NewID"].Direction = ParameterDirection.Output; 
      oCmd.Parameters["@ImageID"].Value = nImageID;
      oCmd.Parameters["@ImageFile"].Value = oImage;
      oCmd.ExecuteNonQuery();	
               
      sRet = oCmd.Parameters["@RETURN_VALUE"].Value.ToString();
      nRet = int.Parse(oCmd.Parameters["@NewID"].Value.ToString());

      if ((nRet > 0) && (sRet == "0"))
       {
         nNewID = nRet;
       }
    }
    catch (Exception e) { Console.WriteLine(e.Message);}
    finally { if (oConn.State == ConnectionState.Open) { oConn.Close(); } }
    return nNewID;
   }
 }
}

Robbe has been a Microsoft MVP in C# since 2004.  He is also the co-founder of NullSkull.com which provides .NET articles, book reviews, software reviews, and software download and purchase advice.