How do I insert/retrieve Excel files to varbinary(max) column in SQL Server 2008?

Question

I'm trying to save Excel files into the database, I do not want to use filestream as it is required to have a server for that.

So how do I insert/update/select into the table that has a column of type varbinary(max)?

Solution

If you want to do it in straight ADO.NET, and your Excel files aren't too big so that they can fit into memory at once, you could use these two methods:

// store Excel sheet (or any file for that matter) into a SQL Server table
public void StoreExcelToDatabase(string excelFileName)
{
    // if file doesn't exist --> terminate (you might want to show a message box or something)
    if (!File.Exists(excelFileName))
    {
       return;
    }

    // get all the bytes of the file into memory
    byte[] excelContents = File.ReadAllBytes(excelFileName);

    // define SQL statement to use
    string insertStmt = "INSERT INTO dbo.YourTable(FileName, BinaryContent) VALUES(@FileName, @BinaryContent)";

    // set up connection and command to do INSERT
    using (SqlConnection connection = new SqlConnection("your-connection-string-here"))
    using (SqlCommand cmdInsert = new SqlCommand(insertStmt, connection))
    {
         cmdInsert.Parameters.Add("@FileName", SqlDbType.VarChar, 500).Value = excelFileName;
         cmdInsert.Parameters.Add("@BinaryContent", SqlDbType.VarBinary, int.MaxValue).Value = excelContents;

         // open connection, execute SQL statement, close connection again
         connection.Open();
         cmdInsert.ExecuteNonQuery();
         connection.Close();
    }
}

To retrieve the Excel sheet back and store it in a file, use this method:

public void RetrieveExcelFromDatabase(int ID, string excelFileName)
{
    byte[] excelContents;

    string selectStmt = "SELECT BinaryContent FROM dbo.YourTableHere WHERE ID = @ID";

    using (SqlConnection connection = new SqlConnection("your-connection-string-here"))
    using (SqlCommand cmdSelect = new SqlCommand(selectStmt, connection))
    {
        cmdSelect.Parameters.Add("@ID", SqlDbType.Int).Value = ID;

        connection.Open();
        excelContents = (byte[])cmdSelect.ExecuteScalar();
        connection.Close();
    }

    File.WriteAllBytes(excelFileName, excelContents);
 }

Of course, you can adapt this to your needs - you could do lots of other things, too - depending on what you really want to do (not very clear from your question).

Comments

[B. Clay Shannon-B. Crow Raven]

Would it be worthwhile to compress the file before saving? If the files are large and/or there are many of them, this might be advantageous, if feasible.

[Alan B]

XLSX is compressed anyway,