Reply to comment

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

Submitted by Ben Hodson on Mon, 11/03/2008 - 16:13

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.

Reply