How to convert DataSet to StringBuilder

Question

I have a DataSet having 4 Unique tables in it. I want to convert entire DataSet into StringBuilder.

I know we can use below code to convert DataTable to StringBuilder.

DataTable fileData = ***Some Data***

if (fileData.Rows.Count == 0) 
  return (true, "No Records");

StringBuilder builder = new StringBuilder();

IEnumerable<string> columnNames = fileData
   .Columns
   .Cast<DataColumn>()
   .Select(column => column.ColumnName);

builder.AppendLine(string.Join("|", columnNames));

foreach (DataRow row in fileData.Rows)
{
  IEnumerable<string> fields = row.ItemArray
    .Select(field => RemoveSpecialCharacters(field
       ?.ToString()
        .Replace(",",";"))); 

  builder.AppendLine(string.Join("|", fields));
}

How can use above code for DataSet data.

Edit:

                var byteArray = ec.GetBytes(builder.ToString());
                var fileDataStream = new MemoryStream(byteArray);

I'm using fileDataStream to generate excel. Above code will convert DataTable to Stream, now I want to convert DataSet to Stream, so that I can use that new stream(DataSet data) to generate excel.

Update 2:

I have used below code with DataSet but only last table data of DataSet stored in StringBuilder not all 4 tables data.

                   foreach (DataTable dt in dataSetFileDetails.Tables)
                    {
                        if (dt.Rows.Count == 0) return (true, "No Records");
                        builder = new StringBuilder();
                        IEnumerable<string> columnNames = dt.Columns.Cast<DataColumn>().
                                                          Select(column => column.ColumnName);
                        builder.AppendLine(string.Join("|", columnNames));

                        foreach (DataRow row in dt.Rows)
                        {
                            IEnumerable<string> fields = row.ItemArray.Select(field => RemoveSpecialCharacters(field?.ToString().Replace(",", ";")));
                            builder.AppendLine(string.Join("|", fields));
                        }
                    }

Do you mean convert DataSet to pipe delimited string using `StringBuilder`? — mjwills, Sep 18 '19 at 12:52
You can access the DataTable objects through the DataSet.Tables property (See: https://learn.microsoft.com/en-us/dotnet/api/system.data.dataset.tables?view=netframework-4.8) for which you already have the code. — Alex, Sep 18 '19 at 12:55
What does that mean to convert to a StringBuilder? You can use WriteXML to get a string representation (XML) of the entire dataset. Or you might use the Linq To CSV to output as a csv (which looks like the thing you are trying to do). All you need is to loop the tables in dataset. — Cetin Basoz, Sep 18 '19 at 12:59
Ah, you are trying to create excel from a stream. Then look into Ado.Stream and CopyFromRecordSet of Excel. They are ready made functions. You could as well use Linq To Excel or ADO.net directly to create the excel file. Or simpler, use EPPLus from Nuget and put your collections (tables) on to worksheets. — Cetin Basoz, Sep 18 '19 at 13:03
Possible duplicate of [How to convert a DataTable to a string in C#?](https://stackoverflow.com/questions/1104121/how-to-convert-a-datatable-to-a-string-in-c) — Andrei Dragotoniu, Sep 18 '19 at 13:48
I tried looping DataSet, but only last table data stored in StringBuilder — chandra sekhar, Sep 19 '19 at 07:19

score 0 · Answer 1 · answered Sep 18 '19 at 12:57

Loop over the DataTable property of you DataSet.

StringBuilder sb = new StringBuilder();

foreach(var table in DataSet.DataTable)
{
    YourCode(sb);
}

void YourCode(StringBuilder stringBuilder)
{
    DataTable fileData = ***Some Data***

    if (fileData.Rows.Count == 0) 
        return (true, "No Records");

    IEnumerable<string> columnNames = fileData
        .Columns
        .Cast<DataColumn>()
        .Select(column => column.ColumnName);

    stringBuilder.AppendLine(string.Join("|", columnNames));

    foreach (DataRow row in fileData.Rows)
    {
        IEnumerable<string> fields = row.ItemArray
            .Select(field => RemoveSpecialCharacters(field?.ToString().Replace(",",";"))); 

        stringBuilder.AppendLine(string.Join("|", fields));
    }
}

score 0 · Answer 2 · answered Sep 18 '19 at 13:10

This is not a direct answer to your question, as it didn't make sense to convert to a StringBuilder. However, you are saying you would do that for generating Excel. I would suggest you to use EPPlus library from nuget.

Assuming you have a dataset named ds:

ExcelPackage pck = new ExcelPackage();
foreach (DataTable table in ds.Tables)
{
    var ws = pck.Workbook.Worksheets.Add(table.TableName);
    ws.Cells["A1"].LoadFromDataTable(table, true);
}

var fi = new FileInfo(@"c:\temp\MyExcelDataSet.xlsx");
if (fi.Exists)
{
    fi.Delete();
}
pck.SaveAs(fi);

How to convert DataSet to StringBuilder

2 Answers2