How to Save Excel Files to SQL Server and Make them downloadable from an ASP.NET Web API Project

Make Local Files Quasi-Instantly "Global" (Public)

This article will show you how to save an Excel file to a SQL Server database, subsequently read the Excel data back out from the database, and download it to a user's machine via REST methods in an ASP.NET Web API app. What it does not show you how to do is create the Excel file itself; however, after much time wrestling with Excel Interop, I would recommend using Spreadsheet Light for your Excel-generation tasks. Excel Interop is good, but Spreadsheet Light seems much more straightforward. To test this code, you can use an .xlsx file created manually/in the usual fashion.

Note, too, that this is not really limited to Excel files, although this article deals specifically with them. You should be able to rather easily work with different types of files, as long as you specify the correct content type (shown later).

Create the SQL Server Table

To end up at the holy grail of downloading, we must start this epic journey with the first step; so, create the table in SQL Server to store the Excel (binary) data and its metadata like so:

CREATE TABLE ReportsGenerated
(
RptGenID int IDENTITY(1,1) PRIMARY KEY,
FileBaseName varchar(100) NOT NULL,
ContentType varchar(50),
BinaryData varbinary(MAX)
);

The field "FileBaseName" is the part you will use to aid in setting up the routing in your Web API project; the field "RptGenID" could be simply "Id" or whatever, if you prefer; the field "ContentType" will contain the "Excel" designation ("application/vnd.ms-excel"), but can be used for other file types if that's what you want to store; finally, the big daddy of them all/the star of the show, is the "BinaryData" field - that is where the Excel file contents is stored.

Populate the Table

Here is how you can insert a record representing an Excel file into the table just created:

string filename = "C:\\Misc\\Bla.xlsx"; 
. . . // The file in filename must exist before the following is called
string RESTFilename = string.Format("deliveryperformance/{0}/{1}/{2}", _unit, fromDate, toDate);
SaveReportDataToDB(filename, RESTFilename); 

The value stored in the ReportsGenerated table's FileBaseName field (the RESTFilename string above) can be whatever you need it to be for your REST routing needs; what is shown is simply an example that will be used for the rest of the code. Here is the actual record insertion code called above:

internal static void SaveReportDataToDB(string filename, string RESTFilename)
{
    if (RecordAlreadyExists(RESTFilename)) return;
    string EXCEL_FILE = "application/vnd.ms-excel"; // if not an Excel file, 
    	//use something else, such as "application/vnd.ms-word" or 
    	//"image/jpeg" or "application/pdf"
    string EXCEL_EXTENSION = ".xlsx";

    // Read the file and convert it to Byte Array
    FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read); 
    BinaryReader br = new BinaryReader(fs);
    Byte[] bytes = br.ReadBytes((Int32)fs.Length);
    br.Close();
    fs.Close();

    // and store it in the DB, along with other necessary identifying info
    using (var sqlConn = new SqlConnection(ReportRunnerConstsAndUtils.CPSConnStr))
    {
        var insertStr = "INSERT INTO ReportsGenerated (FileBaseName, ContentType, BinaryData) " +
                        "VALUES (@FileBaseName, @ContentType, @BinaryData)";

        using (var insertRptsGenerated = new SqlCommand(insertStr))
        {
            insertRptsGenerated.Connection = sqlConn;
            insertRptsGenerated.Parameters.Add
            ("@FileBaseName", SqlDbType.VarChar, 100).Value = RESTFilename;
            insertRptsGenerated.Parameters.Add
            ("@ContentType", SqlDbType.VarChar, 50).Value = EXCEL_FILE;
            insertRptsGenerated.Parameters.Add
            ("@BinaryData", SqlDbType.Binary).Value = bytes;
            sqlConn.Open();
            insertRptsGenerated.ExecuteNonQuery();
        }
    }
}

private static bool RecordAlreadyExists(string RESTFilename)
{
    int countAmount = 0;
    string qry = "SELECT COUNT(FileBaseName) FROM ReportsGenerated WHERE FileBaseName = @FileBaseName";
    using (SqlConnection sqlConn = new SqlConnection(CPSConnStr))
    {
        using (SqlCommand cmd = new SqlCommand(qry, sqlConn))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@FileBaseName", SqlDbType.VarChar, 100).Value = RESTFilename;
            sqlConn.Open();
            countAmount = Convert.ToInt32(cmd.ExecuteScalar());
        }
    }
    return countAmount > 0;
}

Note: The above is Winforms code. The rest of the code is ASP.NET Web API stuff.

So now, there is a record in the ReportsGenerated table. We can switch now to the reading and rendering parts of the code.

Set Up the REST Methods in your Web API Project

The ultimate goal is to provide users a link on a web page, from which they can download the Excel file contained in the "BinaryData" field of the record. Here's how:

Add a method in a Controller (setting up the Controller is beyond the scope of this article/an exercise left to the reader) to set up a page with links to the files to download (one link for every corresponding record in the ReportsGenerated table). Here, it is shown in some context, including a helper method the REST method calls:

namespace PlatypusWebReports.Controllers
{
    [RoutePrefix("api/deliveryperformance")]
    public class DeliveryPerformanceController : ApiController
    {
        // In this case (an URL like "/api/deliveryperformance/Gramps"), display
        // links for all available reports for this Unit
        [Route("{unit}")]
        public HttpResponseMessage Get(string unit)
        {
            String HtmlToDisplay = GetHTMLAsStrForUnit(unit);

            return new HttpResponseMessage()
            {
                Content = new StringContent(
                    HtmlToDisplay,
                    Encoding.UTF8,
                    "text/html"
                )
            };
        }

        private string GetHTMLAsStrForUnit(string unit)
        {
            // Get data from reportsgenerated table to generate a set of links; when any of *those*
            // links are selected, the REST method called will read that table's BinaryData field
            // and download it
            string query = string.Format(
                "SELECT FileBaseName FROM reportsgenerated WHERE 
                FileBaseName LIKE 'deliveryperformance/{0}/%'", unit);
            try
            {
                DataTable dtReportsGeneratedResults =
                    SQLDBHelper.ExecuteSQLReturnDataTable(
                        query,
                        CommandType.Text,
                        new SqlParameter()
                        {
                            ParameterName = "@Unit",
                            SqlDbType = SqlDbType.VarChar,
                            Value = unit
                        });
                if (dtReportsGeneratedResults.Rows.Count <= 0) return string.Empty;
                var _FileBaseNameList = (from DataRow reaux in dtReportsGeneratedResults.Rows 
                select reaux ["FileBaseName"].ToString()).ToList();
                var htmlStr = ConvertDelPerfFileBaseNameListToHtml(_FileBaseNameList, unit);
                return htmlStr;
            }
            catch (Exception ex)
            {
                // Catch code left as an exercise to the reader
            }
            return string.Empty;
        }

Read File Metadata from the Table

Here is the method (called above) for reading from the table:

public static DataTable ExecuteSQLReturnDataTable
(string sql, CommandType cmdType, params SqlParameter[] 
    parameters)
{
    using (DataSet ds = new DataSet())
    using (SqlConnection connStr = new SqlConnection(ConnStr))
    using (SqlCommand cmd = new SqlCommand(sql, connStr))
    {
        cmd.CommandType = cmdType;
        cmd.CommandTimeout = EXTENDED_TIMEOUT;
        foreach (var item in parameters)
        {
            cmd.Parameters.Add(item);
        }

        try
        {
            cmd.Connection.Open();
            new SqlDataAdapter(cmd).Fill(ds);
        }
        catch (SqlException sqlex)
        {
      	    // Catch code left as an exercise to the reader
        }
        return ds.Tables[0];
    }
}

...and the method it calls to generate the html page with the link:

internal static string ConvertDelPerfFileBaseNameListToHtml
(List<string> _FileBaseNameList, string unit)
{
    string startDateYYYYMMDD;
    string endDateYYYYMMDD;

    StringBuilder builder = new StringBuilder();

    builder.Append(String.Format("<h1>{0}</h1>", unit.ToUpper()));

    // Create links for each report
    foreach (String fileBaseName in _FileBaseNameList)
    {
        startDateYYYYMMDD = GetElement(3, fileBaseName);
        endDateYYYYMMDD = GetElement(4, fileBaseName);
        builder.Append("<p></p>");
        builder.Append(string.Format("<a href=\"api/deliveryperformance/excel/{0}/{1}/{2}\">{3}</a>", unit, startDateYYYYMMDD, endDateYYYYMMDD, fileBaseName));
        builder.Append("<p></p>");
    }

    builder.Append("</table>");
    builder.Append("</body>");
    builder.Append("</html>");

    return builder.ToString();
}

Note that each anchor rag href link is yet another REST method call. First, I will add the utility method "GetElement" below, and then the REST method called by those dynamically-generated href links.

The helper method is as follows:

// When called like this: GetElement(3, "deliveryperformance/ABUELOS/20160324/20160331"),
// "20160324" is returned.
private static string GetElement(int Nth, string fileBaseName)
{
    string[] fbnElements = fileBaseName.Split('/');
    return fbnElements[Nth - 1];
}

Read the Excel Data from the Table and Make it Downloadable

The feather in the cap of this article is the method that reads the Excel data out of the table and then downloads it to the user (which is called when the user clicks the link):

// A typical URL might be, "api/deliveryperformance/Excel/ABUELOS/20160324/20160331"
[Route("api/deliveryperformance/{filetype}/{unit}/{begindate}/{enddate}")]
public HttpResponseMessage Get(string filetype, string unit, string begindate, string enddate)
{
    // adapted from 
    // http://stackoverflow.com/questions/11176066/how-do-i-insert-retrieve-excel-files-to-varbinarymax-column-in-sql-server-2008
    byte[] excelContents;

    string selectStmt = "SELECT BinaryData FROM ReportsGenerated WHERE FileBaseName = @fileBaseName";
    string fbn = string.Format("deliveryperformance/{0}/{1}/{2}", unit, begindate, enddate);
    using (SqlConnection connection = new SqlConnection(PlatypusWebReportsConstsAndUtils.CPSConnStr))
    using (SqlCommand cmdSelect = new SqlCommand(selectStmt, connection))
    {
        cmdSelect.Parameters.Add("@fileBaseName", SqlDbType.VarChar).Value = fbn;
        connection.Open();
        excelContents = (byte[])cmdSelect.ExecuteScalar();
        connection.Close();
    }

    // Got this code from 
    // http://stackoverflow.com/questions/36345052/how-can-i-download-a-file-to-a-remote-machine-the-same-or-similarly-to-how-im-d
    HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK)
    {
        Content = new ByteArrayContent(excelContents)
    };
    result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
    {
        FileName = string.Format("{0}.xlsx", fbn)
    };
    result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");

    return result;
}

Plaintive Plea phor Pleasant Programmer People

If this article helps you at all in your professional or personal pursuits, all I ask is that you poignantly ponder the possibilities of peaceful poison-free Platypi paired with punctureless Porcupines proudly parading past purple Persian palaces while peeling pomegranates and posing in plaid pyjamas (pretty please?)