Need a simple CRM and Project Management system?
Check out JobNimbus - CRM for Contractors and Service Professionals.

C# Store/Retrieve File in Database Image Field using ODBC

Sometimes you need to actually store a file in your database at the row level. There are a few pro's and con's to this.

Pros:

  • Easy backup of your database. If your application stores files and uses a database, this makes it easy for customers to back up everything without having to worry about both a database backup and file system backup. It also makes your back up (although very large) more portable because it can be a single file.
  • Leverage your database access code to manage files.
  • You don't have to worry about file I/O and permissions when deploying your application. As long as you can write and read from the database, all your code will work.
  • More compact disk space. Your files will be more compact in SQL Server in many cases than they would be on the file system directly.

Cons:



  • Not good for scalability. I highly recommend you to NOT do record level storing of your files if you want to scale your application. Your files are actually stored at the database Page level so large files take up a great deal of the size of hte page and thus your query must traverse more pages to find the data it needs. To be exact, the data row does not store your file byte data, but instead stores a pointer to the BLOB data so performance on smaller applications is probably not even noticably impacted.
  • No direct access to the files. Because your data is stored in your database, it is not directly accessible by a user. They must use your custom tool to view, edit, and delete the files.
  • Considerably more complex to code for and manage than using the file system. This is because whenever you need to access the file field, you have to use byte streams, do conversions, etc. Whereas there are many built-in tools that make it easy to use files directly.
  • If you need to point a URL to your file, since it is in the database, you will need to extract it and put it on your file system or send it back as a stream to the caller. This is more complicated than just linking directly to the file on the file system.
  • Write / Read performance of files. Files read and written to the database using ODBC are considerably slower than writing and reading files on the local file system.

I'll show how to store and retrieve a file in a database using an ODBC data source. This code could easily be adapted to use a SQL data source. The important thing to note is that the SQL Server database field type: Image is actually an array of bytes.

NOTE: NEVER EVER do a select * on a table that stores Image BLOB fields unless you need those files in your query. It will make your queries unusable on high performance sites.

For the samples here, I created a table with this schema:

-- add FileStore table
if not exists (select * from information_schema.columns
where table_name = 'FileStore')
begin
    CREATE TABLE FileStore 
    (
        Id int identity(1000,1) not null,
        FileField image not null,
        DateCreated datetime not null,
        
        constraint PK_FileStore primary key (Id),
    )    
end

To insert a file into this table:

// Read in the file as a byte array
string sPathToFileToSave = @"C:\MyFile.pdf";
 
// use the file stream object to read the file from disk
using (System.IO.FileStream fs = new System.IO.FileStream(
    sPathToFileToSave, 
    System.IO.FileMode.Open, 
    System.IO.FileAccess.Read))
{
    // store the file in a byte array that is the length of the file
    byte[] fileData = new byte[fs.Length];
 
    // read in the file stream to the byte array
    fs.Read(fileData, 0, System.Convert.ToInt32(fs.Length));
    // close the file stream
    fs.Close();
 
    // insert this file in the database
    using (System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand())
    {
        // set up the SQL query text (NOTE: "?" is used for a parameter field).
        cmd.CommandText = "insert into FileStore(FileField, DateCreated) values(?, ?)";
 
        // add parameters to OdbcCommand
        System.Data.Odbc.OdbcParameter paramFileField = new System.Data.Odbc.OdbcParameter();
        paramFileField.OdbcType = System.Data.Odbc.OdbcType.Image;
        paramFileField.Value = fileData;
        cmd.Parameters.Add(paramFileField);
 
        System.Data.Odbc.OdbcParameter paramDateCreated = new System.Data.Odbc.OdbcParameter();
        paramDateCreated.OdbcType = System.Data.Odbc.OdbcType.DateTime;
        paramDateCreated.Value = DateTime.Now;
        cmd.Parameters.Add(paramDateCreated);
 
        // open connection to database and run query
        string sMyConnectionString = "DSN=filetest; UID=username; PWD=password;";
        using (System.Data.Odbc.OdbcConnection con = new System.Data.Odbc.OdbcConnection(sMyConnectionString))
        {
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
        }
    }
}

Now we can read this file back from the database and store it on the file system:

// path to save the file to after we read it from the db
string sPathToSaveFileTo = @"C:\SavedFile.pdf";
 
using (System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand())
{
    // SQL to retrieve the value.  NOTE: 1000 is the primary key of the file we just inserted.
    cmd.CommandText = "select FileField from FileStore where Id = 1000";
 
    // open connection to database and run query
    string sMyConnectionString = "DSN=filetest; UID=username; PWD=password;";
    using (System.Data.Odbc.OdbcConnection con = new System.Data.Odbc.OdbcConnection(sMyConnectionString))
    {
        cmd.Connection = con;
        con.Open();
 
        // we will use a OdbcDataReader to read out the bytes of the file
        using (System.Data.Odbc.OdbcDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.Default))
        {
            if (reader.Read())
            {
                // read in using GetValue and cast to byte array
                byte[] fileData = (byte [])reader.GetValue(0);
 
                // write bytes to disk as file
                using (System.IO.FileStream fs = new System.IO.FileStream(
                    sPathToSaveFileTo,
                    System.IO.FileMode.Create,
                    System.IO.FileAccess.ReadWrite))
                {
                    // use a binary writer to write the bytes to disk
                    using (System.IO.BinaryWriter bw = new System.IO.BinaryWriter(fs))
                    {
                        bw.Write(fileData);
                        bw.Close();
                    }
                }
            }
 
            // close reader to database
            reader.Close();
        }
    }
}

Look at the path where you specified the file to be written to make sure the file was extracted from the database correctly.

C# insert image

C# fullsource code - insert image

http://net-informations.com/csprj/dataset/cs-insert-image.htm

bona.

Reply to comment - Code Samples & Tutorials

Everything is very open with a very clear clarification of the challenges.
It was truly informative. Your site is useful. Many thanks for
sharing!

Check out my blog post http://ekredyt.org

Nice

Thnx...

Mehboob Yousafzai

Excellent again!!!

Excellent again!!!

File saved is not opening

Hi

I am able to save the file into the local system from database .
My file fomat is MPP (Microsoft office project file) But when I am trying to open it.. giving me error that file cann't be open as format is not recognized...

Thanks

I thank All

ماسنجر

It's really the program is to retrieve files ماسنجر

Alternative for storing many pictures on your FS

Hi, great article, and nice summing up of the pro's and con's of using this approach.

I actually tried to use the file system rather than my SQL Server Express, to save the storage space (express is limited to 4 GB I believe by heart). In this approach you can simply make the filestorage part of your backup routines (in my case I store over 100.000 images in one single file storage). The library I created that does just that can be found on CodeProject; (sources included).

Regards,
Gert-Jan

Whay cant we save these lines???

boaz

Excellent

Thank you for your article. I've been seraching for this sort of one long time.