8
\$\begingroup\$

The problem: generating a CSV file that is too large be stored in memory in a 3 tier architecture, without the complexity of saving the file to storage.

The solution: I've managed to write something that will stream directly from the database into the user's browser, through two WebApi controllers (simulating two tiers in this case)

A request is made to the front end WebApi, which makes a HttpClient call to the back end WebApi:

[HttpGet]
[Route("getFrontEndWebApi")]
public async Task<HttpResponseMessage> GetFrontEndWebApi()
{
    var httpClient = new HttpClient();

    var dataApiStream = await httpClient.GetStreamAsync("getBackEndWebApi");

    var response = Request.CreateResponse();

    response.Content = new PushStreamContent(async (stream, content, context) =>
    {
        await dataApiStream.CopyToAsync(stream);
        dataApiStream.Close();
        stream.Close();
    });

    response.Content.Headers.ContentType = new MediaTypeHeaderValue("text/csv");
    response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
    {
        FileName = $"CrossingTheStreams.csv"
    };

    return response;
}

The back end WebApi compiles the CSV by iterating over an IEnumerable and writes it out to a stream:

[HttpGet]
[Route("getBackEndWebApi")]
public async Task<HttpResponseMessage> GetBackEndWebApi()
{
    var results = ReadFromTable();
    var response = Request.CreateResponse();

    response.Content = new PushStreamContent(async (stream, content, context) =>
    {
        using (var writer = new StreamWriter(stream))
        {
            foreach (var result in results)
            {
                await writer.WriteLineAsync($"{result.Id},{result.SomeBullshit}");
            }
        }
    });

    return response;
}

Some junk code simulates a forward only DataReader connection to a database etc.

private IEnumerable<FishyFish> ReadFromTable()
{
    var table = new DataTable();

    table.Columns.Add("Id", typeof(int));
    table.Columns.Add("SomethingElse", typeof(string));

    for (int i = 0; i < 1000000; i++)
    {
        table.Rows.Add(new object[] { i, Guid.NewGuid().ToString() });
    }

    using (var reader = table.CreateDataReader())
    {
        do
        {
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    yield return new FishyFish()
                    {
                        Id = reader.GetInt32(0),
                        SomeValue = reader.GetString(1)
                    };
                }
            }
        } while (reader.NextResult());
    }
}

public class FishyFish
{
    public int Id { get; set; }
    public string SomeValue { get; set; }
}

When you hit a breakpoint on yield return you can see that results have made it to the browser before the foreach has iterated over every value, thus streaming straight to the browser without compiling the file in memory.

Can this be done more sensibly? Are there any obvious flaws in this code?

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.