Retrieve varbinary data from SQL Server as Excel DataTable

Question

In our application, we are saving the Excel files uploaded by the users to the database with varBinary data type. But, we would also like to retrieve back the data and be able to parse it to get the data in it (columns, rows).

I already know how to read an Excel File into a DataTable to get the data from it(like the code below), but I don't know how to do it if the DataSource is of type varbinary.

// Getting data from actual Excel File
var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);

var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
var ds = new DataSet();

adapter.Fill(ds);

foreach (DataTable dt in ds.Tables) {
    Response.Write(dt.TableName);
    int i= 0;
    foreach (DataRow dr in dt.Rows)
    {
        if (i < 2) { i++;  continue; }
        foreach (DataColumn dc in dt.Columns)
        {
            Response.Write(dc.ToString() + ": " + dr[dc.ToString()] + "<br />");
        }
        i++;
    }
}

Is it possible to do with SqlServer 2012 and .NET 4? If yes, how?

Any feedback would be greatly appreciated.

Regards, artsylar

Answer 1

The code you posted doesn't save an excel file into a table, it saves the contents of the excel file into a table. These are not the same.

In order to save the actual Excel file into a varbinary member of a table row, read the file from disk into a byte array, and then store the byte array into the varbinary. In the case of a Web application, use a file upload control to retrieve the file the user wishes to store to a temporary file storage area on the web server. Load the file to the database from there.

To reconstitute the file, retrieve the varbinary data into a byte array, and then write the byte array to a disk file. The file must be named appropriately, of course. In the case of a Web applciation, reconstitute the file in a local temporary location on the web server, and then send to the client's browser. They'll see a "save file" dialog, which will allow them to save the file locally on their machine.