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 ??
}

I'm curious about why are you putting bytes into a float array? — Will N
– Will N, Commented Apr 4, 2014 at 18:21
@WillNewton Because the data I'm trying to save is in Varbinary in the database. — user2994144
– user2994144, Commented Apr 4, 2014 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 N
– Will N, Commented Apr 4, 2014 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
– user2994144, Commented Apr 4, 2014 at 18:41

srbrills · Accepted Answer · 2016-05-12 20:58:44Z

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();
}

fnostro · Accepted Answer · 2014-04-04 18:49:55Z

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

Collectives™ on Stack Overflow

How to generate text file with varbinary data

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related