Stored procedure returns multiple temp table results. I want to copy them into one CSV file using C#

Question

I have a stored procedure that has multiple select statements using temp tables. I want to copy the results into one CSV file. Each result has different columns. I would like to copy them in such a way that each result set should leave two lines of space in CSV file.

Example below :

Sample stored procedure

Create procedure usp_Test_CSV_Report
As
Begin
    select 'Text Sample' as Description, 123 Amount, 20210511 as Joindate
    select GETDATE() as MonthATB
    select 1 as AccountId, 'CI' as Name
    select 'Sample Report'
End

The temp tables have been created within the stored procedure which will be called like Select * from #temp. I have not included real stored procedure which is vast.

I will be running the stored procedure using C#

string query = "EXEC alpha.dbo.usp_Test_CSV_Report";

SqlCommand cmd = new SqlCommand(query, SQLConnection);

SQLConnection.Open();

DataTable d_table = new DataTable();
SqlDataReader sqlReader = cmd.ExecuteReader();
                while (sqlReader.Read())
                {
                    d_table.Load(sqlReader);
                    // Write the Header Row to File
                    int ColumnCount = d_table.Columns.Count;
                    for (int ic = 0; ic < ColumnCount; ic++)
                    {
                        //MessageBox.Show(d_table.Columns[ic].ToString());
                        sw.Write(d_table.Columns[ic]);
                        if (ic < ColumnCount - 1)
                        {
                            sw.Write(FileDelimiter);
                        }
                    }
                    sw.Write(sw.NewLine);

                    // Write All Rows to the File
                    foreach (DataRow dr in d_table.Rows)
                    {
                        for (int ir = 0; ir < ColumnCount; ir++)
                        {
                            if (!Convert.IsDBNull(dr[ir]))
                            {
                                sw.Write(dr[ir].ToString());
                                //MessageBox.Show(dr[ir].ToString());
                            }
                            if (ir < ColumnCount - 1)
                            {
                                sw.Write(FileDelimiter);
                            }
                        }
                        sw.Write(sw.NewLine);

                    }

                  
            }

            sqlReader.NextResult();

            while (sqlReader.Read())
            {
                d_table.Load(sqlReader);
                // Write the Header Row to File
                int ColumnCount = d_table.Columns.Count;
                for (int ic = 0; ic < ColumnCount; ic++)
                {
                    //MessageBox.Show(d_table.Columns[ic].ToString());
                    sw.Write(d_table.Columns[ic]);
                    if (ic < ColumnCount - 1)
                    {
                        sw.Write(FileDelimiter);
                    }
                }
                sw.Write(sw.NewLine);

                // Write All Rows to the File
                foreach (DataRow dr in d_table.Rows)
                {
                    for (int ir = 0; ir < ColumnCount; ir++)
                    {
                        if (!Convert.IsDBNull(dr[ir]))
                        {
                            sw.Write(dr[ir].ToString());
                            //MessageBox.Show(dr[ir].ToString());
                        }
                        if (ir < ColumnCount - 1)
                        {
                            sw.Write(FileDelimiter);
                        }
                    }
                    sw.Write(sw.NewLine);

                }

               
            }

                SQLConnection.Close();
            sw.Close();

So far I have tried this but this is not working!! Any help?

Answer 1

I maintain a nuget package, Sylvan.Data.Csv, that makes this very easy.

    string query = "EXEC alpha.dbo.usp_Test_CSV_Report";

    using SqlConnection conn = GetSqlConnection();
    conn.Open();
    using SqlCommand cmd = new SqlCommand(query, conn);
    using var sw = File.CreateText("usp_Test_CSV_Report.csv");
    using var csvWriter = CsvDataWriter.Create(sw);
    using var sqlReader = cmd.ExecuteReader();

    bool first = true;
    do
    {
        if (!first)
        {
            // write the two lines to separate the result sets.
            sw.WriteLine();
            sw.WriteLine();
        }
        first = false;
        csvWriter.Write(sqlReader);
    } while (sqlReader.NextResult());

The library also supports reading multiple result sets out of a single CSV in much the same way:

    // tell the reader to expect multiple result sets.
    var csvOpts = new CsvDataReaderOptions { ResultSetMode = ResultSetMode.MultiResult };
    var csvReader = CsvDataReader.Create("usp_Test_CSV_Report.csv", csvOpts);
    do
    {
        while (csvReader.Read())
        {
            for(int i = 0; i < csvReader.FieldCount; i++)
            {
                var value = csvReader.GetString(i);
            }
        }
    } while (csvReader.NextResult());