Convert datatable to byte array

Question

I want to convert DataTable to byte array in WCF services. After returning value to presentation layer, I want to convert byte array to excel. That is to say; I want to do below at presentation layer that is user interface side (web site).

Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");

Response.OutputStream.Write(s, 0, s.Length);
Response.OutputStream.Flush();
Response.OutputStream.Close();
Response.Flush();
Response.Close();

I didnot do it. How can I convert datatable to byte array?

It's not really clear why you want to convert it to a byte array on the WCF side - all you really care about is getting the DataTable itself to the web site, right? That can convert it to an Excel format. Or did you want to actually do the conversion to Excel at the WCF side, so that the web site can just send the response directly? — Jon Skeet
– Jon Skeet, Commented Dec 11, 2013 at 6:49
whilst I agree with what @JonSkeet has said - why do you want to convert a heavy object to byte[] instead of using POCO's or the like? it will also be easily interpreted for non .NET Langs — Ahmed ilyas
– Ahmed ilyas, Commented Dec 11, 2013 at 6:51

Necvetanov · Accepted Answer · 2018-11-30 10:21:27Z

4

DataTable to Byte [ ]

System.IO.MemoryStream stream = new System.IO.MemoryStream(); 
System.Runtime.Serialization.IFormatter formatter = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
formatter.Serialize(stream, dtUsers); // dtUsers is a DataTable

byte[] bytes = stream.GetBuffer();

Hope this helps to solve a part of your problems.

can you try this

private byte[] ConvertDataSetToByteArray(DataTable dataTable)
    {
        byte[] binaryDataResult = null;
        using (MemoryStream memStream = new MemoryStream())
        {
            BinaryFormatter brFormatter = new BinaryFormatter();
            dataSet.RemotingFormat = SerializationFormat.Binary;
            brFormatter.Serialize(memStream, dataTable);
            binaryDataResult = memStream.ToArray();
        }
        return binaryDataResult;
    }

edited Nov 30, 2018 at 10:21

Necvetanov

4044 silver badges17 bronze badges

answered Dec 11, 2013 at 6:55

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

UserStackk Over a year ago

thanks but this return xml when I write the response.

Jade · Accepted Answer · 2014-02-03 02:20:47Z

0

concatenate all columns in your data row and save it as csv (csv is readable in excel)

private string (DataTable dt)
{
string rw = "";
StringBuilder builder = new StringBuilder();

foreach(DataRow dr in dt.Rows)
{
  foreach(DataColumn dc in dr.Columns)
  {
      rw = dc[0].ToString();
      if (rw.Contains(",")) rw = "\"" + rw + "\"";
      builder.Append(rw + ",");
  }
  builder.Append(Environment.NewLine);
}
return builder.ToString()
}

Then serialize the return string value

edited Feb 3, 2014 at 2:20

answered Dec 11, 2013 at 7:06

Jade

2,9941 gold badge14 silver badges9 bronze badges

3 Comments

Jade Over a year ago

This might not be the exact answer but it may serve the same purpose.

Michele Over a year ago

in rw = dr[0].ToString(); inside the second foreach you mean dc not dr, right?

Jade Over a year ago

@Michele, Yes and thank you so much! I've already corrected it.

stratovarius · Accepted Answer · 2013-12-11 09:39:20Z

thanks but this return xml when I write the response.

I'm just thinking from that comment of yours; If your intention is to benefit from a binding method such as MTOM Encoding etc. by using byte[]; then this is not the way you should transform your data into byte[].

[DataContract]
class MyData
{
    [DataMember]
    byte[] binaryBuffer;
    [DataMember]
    string someStringData;
}

And the configuration for desired bind type such as:

<system.serviceModel>
     …
    <bindings>
      <wsHttpBinding>
        <binding name="ExampleBinding" messageEncoding="Mtom"/>
      </wsHttpBinding>
    </bindings>
     …
<system.serviceModel>

You can refer to This Post for more detailed information, there is a very informative dialog about binding types using byte[] and related pages with the code snippets that I gave..

Apollo · Accepted Answer · 2018-02-26 18:55:49Z

Here is the way I did it. Works great!

        //Get datatable from db 
        DataTable dt = new DataTable();
        dt.TableName = "TABContacts";
        SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["DBConnectionString"]);
        SqlCommand cmd = new SqlCommand("SELECT * FROM TABContacts", connection);
        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
        {
            adapter.Fill(dt);
        }

        StringBuilder builder = new StringBuilder();

        foreach (DataRow row in dt.Rows)
        {
            for (int i = 0; i < row.ItemArray.Length; i++)
            {
                string rowText = row.ItemArray[i].ToString();
                if (rowText.Contains(","))
                {
                    rowText = rowText.Replace(",", "/");
                }

                builder.Append(rowText + ",");
            }
            builder.Append(Environment.NewLine);
        }
        //Convert to Byte Array 
        byte[] data = Encoding.ASCII.GetBytes(builder.ToString());

Collectives™ on Stack Overflow

Convert datatable to byte array

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related