How to make use of memorystream instead of filestream

Question

So what im trying to do is read a Select store procedure from my database save the data in a csv file and make it that the user is able to download it through the web application. I was able to get the requested result by saving the file temporary into my program foldel and using filestream. What i want to do now is skip the part where the file is saved onto my computer and temporary save it in the RAM memory instead. From what i understood i have to make use of memory stream instead of file stream but i dont really understand how i can do that. From what i understood from what i read is that instead of me making use of a file i need to convert my data to bytes make a memorystream out of it and then use it in my FileStreamResult. Am i correct here?

Method when i read from procedure and save to a csvfile:

public static String StoreApproved ()
{          
    string path1 = HttpRuntime.AppDomainAppPath + "Report.csv";
    SqlConnection sqlConnection1 = new SqlConnection("CONNECTIONSTRING");
    SqlCommand cmd = new SqlCommand();
    SqlDataReader reader;
    cmd.CommandText = "ExportApproved";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection = sqlConnection1;
    sqlConnection1.Open();
    reader = cmd.ExecuteReader();
    List<ModelStoreProcedureApproved> TestList = new List<ModelStoreProcedureApproved>();
    ModelStoreProcedureApproved test ;

    while (reader.Read())
    {
        test = new ModelStoreProcedureApproved();
       // test.Id = int.Parse(reader["IdTimeTracker"].ToString());
        test.Month = reader["Month"].ToString();
        test.EmailUser = reader["Email"].ToString();
        test.Project = reader["Name"].ToString();
        test.Approved = reader["Description"].ToString();
        test.Month = reader["Month"].ToString();
        test.Year = reader["Year"].ToString();
        TestList.Add(test);
    }

    File.Create(path1).Close();
    var i = TestList.FirstOrDefault();
    using (TextWriter fileReader = new StreamWriter(path1))
    {
        var csv = new CsvWriter(fileReader);
        csv.Configuration.Encoding = Encoding.UTF8;
        foreach (var value in TestList)
        {
            csv.WriteRecord(value);
        }
        fileReader.Close();
    }
    sqlConnection1.Close();
    return path1;
}

Controller code:

public ActionResult ExportToCSV()
{
    string path = Repositories.UserRepository.StoreApproved();
    var fileStream = new FileStream(path,
                                    FileMode.Open,
                                    FileAccess.Read);

    return new FileStreamResult(fileStream, "text/csv") { FileDownloadName = "export.csv" };
}

Can someone explain me what the best way to do this is? Other posts i have read Serialize and Deserialize using BinaryFormatter

BinaryFormatter and Deserialization Complex objects

Using CSVHelper to output stream to browser

Answer 1

You can make it like this:

public static byte[] StoreApproved ()
{          
    string path1 = HttpRuntime.AppDomainAppPath + "Report.csv";
    SqlConnection sqlConnection1 = new SqlConnection("CONNECTIONSTRING");
    SqlCommand cmd = new SqlCommand();
    SqlDataReader reader;
    cmd.CommandText = "ExportApproved";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection = sqlConnection1;
    sqlConnection1.Open();
    reader = cmd.ExecuteReader();
    List<ModelStoreProcedureApproved> TestList = new List<ModelStoreProcedureApproved>();
    ModelStoreProcedureApproved test ;

    while (reader.Read())
    {
        test = new ModelStoreProcedureApproved();
       // test.Id = int.Parse(reader["IdTimeTracker"].ToString());
        test.Month = reader["Month"].ToString();
        test.EmailUser = reader["Email"].ToString();
        test.Project = reader["Name"].ToString();
        test.Approved = reader["Description"].ToString();
        test.Month = reader["Month"].ToString();
        test.Year = reader["Year"].ToString();
        TestList.Add(test);
    }

    var i = TestList.FirstOrDefault();
    var mem = new MemoryStream();
    using (TextWriter fileReader = new StreamWriter(mem))
    {
        var csv = new CsvWriter(fileReader);
        csv.Configuration.Encoding = Encoding.UTF8;
        foreach (var value in TestList)
        {
            csv.WriteRecord(value);
        }
    }
    sqlConnection1.Close();
    return mem.ToArray();
}

public ActionResult ExportToCSV()
{
    byte[] bytes = Repositories.UserRepository.StoreApproved();
    Stream stream = new MemoryStream(bytes);
    return new FileStreamResult(stream, "text/csv") { FileDownloadName = "export.csv" };
}