Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server
Article

Uploading / Downloading Pictures to / from a SQL Server

Rate me:
Please Sign up or sign in to vote.
4.77/5 (43 votes)
28 Oct 2004CPOL3 min read 424.4K   12K   198   81
Describes how to upload and download pictures to and from a SQL Server database.

Introduction

In order to provide your application with cool pictures, you can employ two techniques (at least). One of them is that you can save the pictures in a folder and store the path to each one in a database or file. The other one is to store the entire file into a database, along with its file name.

Each of them has its ups and downs:

  • If you save your files to a folder, you might accidentally delete a file from that folder. If this happens, you will end up with a broken “link” in your database or configuration file. However, the hard disk storage space is cheap, so you can afford to store a lot of files.
  • If you store your files into a database, you can enforce security by using the security settings of the database. Also, there are no broken links ever. However, the database storage space is more expensive.

Another idea is that you can save a thumbnail of the image on the database for quick access and save the actual picture on hard drive.

Of course, each application has its particularities, and you have to choose which one you will use.

OK. Enough of the philosophical talk! Let’s dive into the good stuff.

The application

The problem of uploading files to a database is not that difficult. You have to use, on the server, a certain data type when creating the table. This data type has to be capable of storing large amounts of binary data.

When using Microsoft SQL Server, this data type is called image.

For more information, see BLOB (Binary Large OBject) for a short definition, and Books Online for a complete reference.

The client has to obtain the data from the file in binary format – a byte array – and call a procedure on the server with that array as a parameter.

The SQL Server procedures

In this presentation, I assume that I have a database Pictures on the server with a table called Pictures. The structure of this table is as follows:

Field Name

Field Type

kFileName

Long

Picture

Image

FileName

Varchar(250)

I also have stored procedures for uploading, downloading, and retrieving the list of uploaded files. These are shown below.

For uploading a file: UploadFile:

SQL
CREATE PROCEDURE [dbo].[UploadFile]
(
 @Picture image,
 @FileName varchar(250),
 @kFileName bigint output
)
AS
insert into Pictures(Picture, FileName) values (@Picture,@FileName)
select @kFileName = SCOPE_IDENTITY()
GO

For downloading a file: DownloadFile:

SQL
CREATE PROCEDURE [dbo].[DownloadFile]
(
 @kFileName bigint,
 @FileName varchar(250) output
)
AS

select Picture, FileName
 from Pictures
 where kFileName=@kFileName
GO

For retrieving the list of uploaded files: getUploadedFiles:

SQL
CREATE PROCEDURE [dbo].[getUploadedFiles]AS
Select ltrim(str(kFileName)) + " - " + FileName as Name 
  from Pictures
GO

The C# Class - with comments

C#
using System;
using System.IO;
using System.Data;
using System.Text;
using System.Data.SqlClient;
/*
 * Autor: Ghiondea Alexandru
 * Date: 08 october 2004
 * Description: Implements methods for uploading and downloading files 
 *  with MS SQL Server
 * */
namespace PicturesInSQLServer
{
 /// <SUMMARY>
 /// This class manages uploads and downloads to and from an SQL Server
 /// </SUMMARY>
 public class TransferPictures
 {
  /// <SUMMARY>
  /// Gets from the server a list of uploaded files into a dataSet
  /// </SUMMARY>
  /// <PARAM name="ds">The dataset</PARAM>
  /// <PARAM name="table">The table in the dataset</PARAM>
  public void GetUploadedFiles(ref DataSet ds, string table)
  {
   //
   // The variables required for connecting to the server.
   //
   SqlConnection conn =null;
   SqlCommand cmd = null;
   SqlDataAdapter da = null;
   // ----------------------------------------------

   try
   {
    //
    // If the table already exists, cleares its content. Else adds a new table.
    //
    if (ds.Tables.Contains(table))
     ds.Tables[table].Clear();
    else
     ds.Tables.Add(table);
    // ----------------------------------------------

    
    //
    // Creates a connection to the database and initilizes the command
    //
    conn = new SqlConnection(ConnectionString());
    cmd = new SqlCommand("getUploadedFiles",conn);
    cmd.CommandType = CommandType.StoredProcedure;
    // ----------------------------------------------

    //
    // Initializes the DataAdapter used for retrieving the data
    //
    da = new SqlDataAdapter(cmd);
    // ----------------------------------------------

    //
    // Opens the connection and populates the dataset
    //
    conn.Open();
    da.Fill(ds,table);
    conn.Close();
    // ----------------------------------------------
   }
   catch(Exception e)
   {
    //
    // If an error occurs, we assign null to the result
    // and display the error to the user,
    // with information about the StackTrace for debugging purposes.
    //
    Console.WriteLine(e.Message + " - " + e.StackTrace);
   }
  }

  /// <SUMMARY>
  /// Uploads a file to the database server.
  /// </SUMMARY>
  /// <PARAM name="fileName">The filename of the picture to be uploaded</PARAM>
  /// <RETURNS>The id of the file on the server.</RETURNS>
  public long UploadFile(string FileName)
  {
   if (!File.Exists(FileName))
   {
    return -1;
   }

   FileStream fs=null;
   try
   {
    #region Reading file

    fs = new FileStream(FileName,FileMode.Open);

    //
    // Finding out the size of the file to be uploaded
    //
    FileInfo fi = new FileInfo(FileName);
    long temp = fi.Length;
    int lung = Convert.ToInt32(temp);
    // ------------------------------------------

    //
    // Reading the content of the file into an array of bytes.
    //
    byte[] picture=new byte[lung];
    fs.Read(picture,0,lung);
    fs.Close();
    // ------------------------------------------
    #endregion
    long result = uploadFileToDatabase(picture,fi.Name);

    return result;
   }
   catch(Exception e)
   {
    Console.WriteLine(e.Message + " - " + e.StackTrace);
    return -1;
   }
  }

  /// <SUMMARY>
  /// Wrapper for downloading a file from a database.
  /// </SUMMARY>
  /// <PARAM name="kFileName">The Unique ID of the file in database</PARAM>
  /// <PARAM name="fileName">The file name as it was stored
  ///                              in the database.</PARAM>
  /// <RETURNS>The byte array required OR null if the ID is not found</RETURNS>
  public byte[] DownloadFile(long kFileName, ref string fileName)
  {
   byte[] result = downloadFileFromDatabase(kFileName, ref fileName);
   return result;
  }

  /// <SUMMARY>
  /// Returns the connection string for connecting to the database
  /// </SUMMARY>
  /// <RETURNS>The Connection string.</RETURNS>
  public static string ConnectionString()
  {
   //
   // We consider that the database is situated
   // on the same computer that runs the program.
   // To connect to a remote server, replace 'Data Source'
   // with the name of that server.
   //
   return "Connect Timeout=600;Integrated Security=SSPI;" + 
          "Persist Security Info=False;Initial Catalog=Pictures;" + 
          "Packet Size=4096;Data Source=" + 
          System.Environment.MachineName.Trim();
  }

  /// <SUMMARY>
  /// Uploades a file to an SQL Server.
  /// </SUMMARY>
  /// <PARAM name="picture">A byte array that contains
  ///        the information to be uploaded.</PARAM>
  /// <PARAM name="fileName">The file name asociated
  ///                  with that byte array.</PARAM>
  /// <RETURNS>The unique ID of the file on the server
  ///              OR -1 if an error occurs.</RETURNS>
  private long uploadFileToDatabase(byte[] picture, string fileName)
  {
   //
   // Defining the variables required for accesing the database server.
   //
   SqlConnection conn = null;
   SqlCommand cmd =null;
   SqlParameter kFileName =null;
   SqlParameter FileName =null;
   SqlParameter pic =null;
   // By default, we assume we have an error. If we succed in uploading
   // the file, we'll change this 
   // to the unique id of the file
   long result=-1; 
    
   try
   {
    //
    // Connecting to database.
    //
    conn = new SqlConnection(ConnectionString());
    cmd = new SqlCommand("UploadFile",conn);
    // We assume there is a stored procedure called UploadFile

    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    // ----------------------------------------------

    //
    // Initializing parameters and assigning
    // the values to be sent to the server
    //
    kFileName = new SqlParameter("@kFileName", 
                    System.Data.SqlDbType.BigInt,8);
    kFileName.Direction = ParameterDirection.Output;
    // This parameter does not have a size because
    // we do not know what the size is going to be.
    pic = new SqlParameter("@picture",SqlDbType.Image); 
    pic.Value = picture; 
    
    FileName = new SqlParameter("@FileName",SqlDbType.VarChar,250);
    FileName.Value = fileName;
    // ----------------------------------------------
   
    //
    // Adding the parameters to the database.
    // Remember that the order in which the parameters 
    //  are added is VERY important!
    //
    cmd.Parameters.Add(pic);
    cmd.Parameters.Add(FileName);
    cmd.Parameters.Add(kFileName);
    // ----------------------------------------------

    //
    // Opening the connection and executing the command.
    //
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
    // ----------------------------------------------

    //
    // The result is the unique identifier created on the database.
    //
    result = (long)kFileName.Value;
    // ----------------------------------------------

    //
    // Disposing of the objects so we don't occupy memory.
    //
    conn.Dispose();
    cmd.Dispose();
    // ----------------------------------------------
   }
   catch(Exception e)
   {
    //
    // If an error occurs, we report it to the user,
    // with StackTrace for debugging purposes
    //
    Console.WriteLine(e.Message + " - " + e.StackTrace);
    result = -1;
    // ----------------------------------------------
   }

   return result; 
  }


  /// <SUMMARY>
  /// Downloades a file from a database according
  // to the unique id in that database.
  /// </SUMMARY>
  /// <PARAM name="kFile">The ID of the file in the database</PARAM>
  /// <PARAM name="FileName">The filename of the file
  ///                         as it was stored in the database.</PARAM>
  /// <RETURNS>A byte array containing
  ///                            the information required.</RETURNS>
  private byte[] downloadFileFromDatabase(long kFile, ref string FileName)
  {
   SqlConnection conn =null;
   SqlCommand cmd = null;
   SqlParameter kFileName = null;
   SqlParameter fileName = null;
   SqlDataReader dr=null;
   byte[] result=null;

   try
   {
    //
    // Connecting to database.
    //
    conn = new SqlConnection(ConnectionString());
    cmd = new SqlCommand("DownloadFile",conn);
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    // ----------------------------------------------

    //
    // Initializing parameters and assigning the values to be sent to the server
    //
    kFileName= new SqlParameter("@kFileName",System.Data.SqlDbType.BigInt,8);
    kFileName.Value = kFile;
    fileName = new SqlParameter("@FileName",SqlDbType.VarChar,250);
    fileName.Direction = ParameterDirection.Output;
    // ----------------------------------------------
   
    //
    // Adding the parameters to the database.
    // Remember that the order in which the parameters 
    // are added is VERY important!
    //
    cmd.Parameters.Add(kFileName);
    cmd.Parameters.Add(fileName);
    // ----------------------------------------------

    //
    // Opening the connection and executing the command.
    // The idea behind using a dataReader is that,
    // on the SQL Server, we cannot assign to a
    // variable the value of an image field.
    // So, we use a querry to select the record we want 
    // and we use a datareader to read that query.
    // Because we are returnig information based
    // on a primary key, we are always returning
    // only one row of data.
    //
    conn.Open();
    dr = cmd.ExecuteReader();
    dr.Read();
    //
    // We are casting the value returned
    // by the datareader to the byte[] data type.
    //
    result = (byte[])dr.GetValue(0);
    //
    // We are also returning the filename associated with the byte array.
    //
    FileName = (string)dr.GetValue(1);

    //
    // Closing the datareader and the connection
    //
    dr.Close();
    conn.Close();
    // ------------------------------------------

    //
    // Disposing of the objects so we don't occupy memory.
    //
    conn.Dispose();
    cmd.Dispose();
    // ------------------------------------------
   }
   catch(Exception e)
   {
    //
    // If an error occurs, we assign null
    // to the result and display the error to the user,
    // with information about the StackTrace for debugging purposes.
    //
    Console.WriteLine(e.Message + " - " + e.StackTrace);
    result = null;
   }
   return result;    
  }
 }
}

A sample application

I have also written a small application to demonstrate how to use these methods. A screenshot of it is shown below:

Application Screeshot

The application uploads a file to the server and displays a list of files on the server. When double-click-ing on the filename in the list, the file is downloaded and displayed in the picture box.

Below are some snippets of relevant code:

C#
private void UploadedFiles_DoubleClick(object sender, System.EventArgs e)
  {
   //
   // Finds the unique id of the file.
   //
   DataRowView drv = (DataRowView) UploadedFiles.SelectedItem;
   string selectedText = drv.Row["Name"].ToString();
   long id=-1;
   id = long.Parse(selectedText.Substring(0,selectedText.IndexOf(" - ",0)).Trim());

   string filename=null;
   TransferPictures up = new TransferPictures();
   byte[] result = up.DownloadFile(id,ref filename);
   up = null;
   try
   {
    //
    // We cannot assign a byte array directly to an image. 
    // We use MemoryStream, an object that creates a file in memory
    //  and than we pass this to create the image object.
    //
    MemoryStream ms= new MemoryStream(result,0,result.Length);
    Image im = Image.FromStream(ms);
    Picture.Image = im;
   }
   catch(Exception ee)
   {
    MessageBox.Show("An error has occured.\n" + ee.Message);
   }
  }
  private void UploadFile_Click(object sender, System.EventArgs e)
  {
   //
   // Gets the file to be uploaded
   //
   OpenFileDialog ofd = new OpenFileDialog();
   ofd.ShowDialog();
   if (ofd.FileName=="" || !File.Exists(ofd.FileName))
   {
    //
    // If the requested file is not ok...
    //
    return;
   }

   TransferPictures up = new TransferPictures();
   long id =up.UploadFile(ofd.FileName);
   string msg=null;
   if (id >0)
   {
    msg = "Upload succesful";
    LoadInformationFromDataBase();
   }
   else
   { 
    msg = "An error has occured";
   }
   MessageBox.Show(msg);
  }

  private void LoadInformationFromDataBase()
  {
   TransferPictures up = new TransferPictures();
   up.GetUploadedFiles(ref ds,"Pictures");
 
   UploadedFiles.DataSource = ds.Tables["Pictures"];
   UploadedFiles.DisplayMember = "Name";
  }

  private void frmMain_Load(object sender, System.EventArgs e)
  {
   LoadInformationFromDataBase();
  }

Conclusion

Well, choosing which type of image – file – storage technique to use is up to the person designing a specific application. I have tried here to show you how you can store them in a database. Happy coding!

Bibliography

  1. Books Online
  2. MSDN

    License

    This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


    Written By
    Software Developer Microsoft
    United States United States
    I am working on the C# compiler at Microsoft since 2007.

    Microsoft Certified Professional since 2006

    Interests: C#, ASP.NET, LINQ

    Comments and Discussions

     
    GeneralRe: creating a connection for sql server in c# Pin
    nomsnoms29-Mar-06 8:47
    nomsnoms29-Mar-06 8:47 
    GeneralWht if i choose Access rather than SqlServer Pin
    pfalgun28-Nov-05 19:46
    pfalgun28-Nov-05 19:46 
    GeneralRe: Wht if i choose Access rather than SqlServer Pin
    S Douglas28-Nov-05 20:54
    professionalS Douglas28-Nov-05 20:54 
    GeneralRe: Wht if i choose Access rather than SqlServer Pin
    Knight_Yun20-Aug-06 20:02
    Knight_Yun20-Aug-06 20:02 
    GeneralError When Double Click The Uploaded File's Name Pin
    XiaoYu27-Oct-05 20:51
    XiaoYu27-Oct-05 20:51 
    GeneralRe: Error When Double Click The Uploaded File's Name Pin
    Alexandru Ghiondea29-Oct-05 6:52
    Alexandru Ghiondea29-Oct-05 6:52 
    GeneralRe: Error When Double Click The Uploaded File's Name Pin
    XiaoYu1-Nov-05 22:27
    XiaoYu1-Nov-05 22:27 
    GeneralRe: Error When Double Click The Uploaded File's Name Pin
    Alexandru Ghiondea3-Nov-05 9:16
    Alexandru Ghiondea3-Nov-05 9:16 
    Can you know if the pictures are stored in the database(look in the database yourself)? Can you provide more information regarding the names of the files? And also a user scenario that I can duplicate and see what is wrong?
    GeneralRe: Error When Double Click The Uploaded File's Name Pin
    XiaoYu3-Nov-05 18:37
    XiaoYu3-Nov-05 18:37 
    GeneralRe: Error When Double Click The Uploaded File's Name Pin
    XiaoYu22-Nov-05 18:22
    XiaoYu22-Nov-05 18:22 
    Questionhow do I get the picture first? Pin
    digitallogic18-May-05 6:44
    digitallogic18-May-05 6:44 
    AnswerRe: how do I get the picture first? Pin
    Alexandru Ghiondea18-May-05 7:26
    Alexandru Ghiondea18-May-05 7:26 
    GeneralRe: how do I get the picture first? Pin
    digitallogic18-May-05 8:38
    digitallogic18-May-05 8:38 
    GeneralRe: how do I get the picture first? Pin
    Alexandru Ghiondea18-May-05 9:27
    Alexandru Ghiondea18-May-05 9:27 
    GeneralRe: how do I get the picture first? Pin
    digitallogic18-May-05 11:22
    digitallogic18-May-05 11:22 
    GeneralRe: how do I get the picture first? Pin
    Alexandru Ghiondea18-May-05 19:25
    Alexandru Ghiondea18-May-05 19:25 
    Generala problem with the code Pin
    Shanyok5-Mar-05 2:59
    sussShanyok5-Mar-05 2:59 
    GeneralRe: a problem with the code Pin
    Alexandru Ghiondea5-Mar-05 8:30
    Alexandru Ghiondea5-Mar-05 8:30 
    GeneralRe: a problem with the code Pin
    Member 15181675-Mar-05 9:21
    Member 15181675-Mar-05 9:21 
    GeneralRe: a problem with the code Pin
    Alexandru Ghiondea5-Mar-05 9:46
    Alexandru Ghiondea5-Mar-05 9:46 
    GeneralTo save all types of file in Sql Server Pin
    Ali John4-Feb-05 3:22
    Ali John4-Feb-05 3:22 
    GeneralRe: To save all types of file in Sql Server Pin
    Alexandru Ghiondea6-Feb-05 21:24
    Alexandru Ghiondea6-Feb-05 21:24 
    GeneralTo upload all file types through ASP.net Pin
    Member 161904529-Dec-04 21:58
    Member 161904529-Dec-04 21:58 
    GeneralRe: To upload all file types through ASP.net Pin
    Alexandru Ghiondea8-Jan-05 1:04
    Alexandru Ghiondea8-Jan-05 1:04 
    Questionis it recomended? Pin
    Jordi Corominas25-Nov-04 0:14
    Jordi Corominas25-Nov-04 0:14 

    General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

    Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.