How to generate text file with varbinary data

Question

I'm trying to retrieve the varbinary array of values from SQL Server than trying to store them in a text file which the user can download but not sure how to return the file back to Response.Redirect() or how to return the text file.

public byte[] GenerateTextFile()
{
    if (m_Template == CFCTemplate.GETTXTDATA)
    {
       SqlCommand cmd = new SqlCommand("SELECT SpecData FROM Spec WHERE TestID=" + SpecTestID);
       InternalSalesDB.Instance.query(cmd);

       DataTable dt = InternalSalesDB.Instance.query(cmd).Tables[0];

       if (dt.Rows.Count > 0)
       {
           byte[] binarySpec = (byte[])dt.Rows[0][0];

           FileStream fileStream = new FileStream("TestData.txt",FileMode.Append);  
           fileStream.Write(binarySpec, 0, binarySpec.Length);
           fileStream.Flush();
           fileStream.Close();
      }
    }
   //need return statement here ??
}

put the byte[] into the response like [this SO question](http://stackoverflow.com/questions/848679/reading-a-binary-file-and-using-response-binarywrite) — Will Newton, Apr 04 '14 at 18:20
I'm curious about why are you putting bytes into a float array? — Will Newton, Apr 04 '14 at 18:21
@WillNewton Because the data I'm trying to save is in Varbinary in the database. — user2994144, Apr 04 '14 at 18:29
but why are you converting byte[] binarySpec into something else before sending it? Why not just write those bytes as they are to the response? — Will Newton, Apr 04 '14 at 18:30
@WillNewton I'm just trying to the change the data in the format needed but that's not the problem I have right now. I'm first just trying to get it to save the binarySpec data than I'll work on changing the format/conversion of it. — user2994144, Apr 04 '14 at 18:41

score 1 · Answer 1 · edited May 12 '16 at 20:58

Hope this will help to get an idea. Here it creates a CSV file from a Data Table and downloads it using Respose.

private void WriteToCSV(DataTable dt, string fileName, string delimiter)
{
    // Prepare the Output Stream
    Response.Clear();
    Response.ContentType = "text/csv";
    Response.AppendHeader("Content-Disposition",
        string.Format("attachment; filename={0}", fileName));

    string value = "";
    StringBuilder builder = new StringBuilder();

    // Write the CSV Column Headers
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        value = dt.Columns[i].ColumnName;
        // Implement special handling for values that contain comma or quote
        // Enclose in quotes and double up any double quotes
        if (value.IndexOfAny(new char[] { '"', ',' }) != -1)
            builder.AppendFormat("\"{0}\"", value.Replace("\"", "\"\""));
        else
        {
            builder.Append(value);
        }

        Response.Write(value);
        Response.Write((i < dt.Columns.Count - 1) ? delimiter : Environment.NewLine);
        builder.Clear();
    }

    //write the data
    foreach (DataRow row in dt.Rows)
    {
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            value = row[i].ToString();
            // Implement special handling for values that contain comma or quote
            // Enclose in quotes and double up any double quotes

            if (value.IndexOfAny(new char[] { '"', ',' }) != -1)
                builder.AppendFormat("\"{0}\"", value.Replace("\"", "\"\""));
            else
            {
                builder.Append(value);

            }

            Response.Write(builder.ToString());
            Response.Write((i < dt.Columns.Count - 1) ? delimiter : Environment.NewLine);
            builder.Clear();
        }
    }

    Response.End();
}

score 0 · Answer 2 · answered Apr 04 '14 at 18:49

Why not just have your user download the varbinary directly as a text file?

Very easy to accomplish with a generic handler (.ashx) And most of the code is provided by the template when you add a generic handler from Project > Add > New Item... > Generic Handler

Public Class DocHandler
  Implements IHttpHandler, IRequiresSessionState

Declare a byte array

  Dim buffer() As Byte

The context parameter allows you to access session data, if needed. In this example I access the filename but that could just as easily come from the DB call or as url parameters to the handler.

  Sub ProcessRequest(ByVal context As HttpContext) _
    Implements IHttpHandler.ProcessRequest

    If context.Response.IsClientConnected Then
      If context.Request.UrlReferrer IsNot Nothing Then
        If context.Session("case_document_key") IsNot Nothing Then

Initialize your DB call here, I'm presuming a SqlCommand variable called Cmd

          Dim yourvarbinaryfield = Cmd.ExecuteScalar()

Cast the returned varbinary field into a byte array buffer = DirectCast(yourvarbinaryfield , Byte())

This is the magic.
Set the handler response header content disposition to download the file as text:

          context.Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", context.Session("YourFileName")))
          context.Response.ContentType = "text/plain"
          context.Response.BinaryWrite(buffer)
          context.Response.OutputStream.Write(buffer, 0, buffer.Length)
          context.Response.Flush()

        End If
      End If
    End If
  End If
End Sub

ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
  Get
      Return False
  End Get
End Property

End Class

How to generate text file with varbinary data

2 Answers2