c# - StringBuilder inside large data set this time not store in memory stream inside throug exception out of memory exception

Question

i save data base to csv file inside store record. whenewer i store 1000000 record this time not convert in byte array to memory strem inside. his time throw exception out of memory exception below code

con.Open();
SqlCommand cmd = new SqlCommand("select top 1000000 from temp_paresh", con);
var sb = new StringBuilder();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
    string csvseperator = ";";

    for (int i = 0; i < sdr.FieldCount; i++)
    {
        sb.Append(sdr.GetName(i));

        if (i < sdr.FieldCount - 1)
            sb.Append(csvseperator);
    }
    sb.AppendLine();

    while (sdr.Read())
    {
        sb.Append(Convert.ToString(sdr["rowid"]));
        sb.Append(csvseperator);
        sb.Append(Convert.ToString(sdr["rowname"]));
        sb.Append(csvseperator);
        sb.Append(Convert.ToString(sdr["rcount"]));
        sb.Append(csvseperator);
        sb.AppendLine();
    }
}
con.Close();
MemoryStream str = new MemoryStream(System.Text.Encoding.UTF8.GetBytes(sb.ToString()));

byte[] fileContent = str.ToArray();
str.Flush();
str.Close();
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename="" + ("paresh.csv") + """);
HttpContext.Current.Response.AddHeader("Content-Length", fileContent.Length.ToString());
HttpContext.Current.Response.ContentType = "application/octet-stream";
HttpContext.Current.Response.BinaryWrite(fileContent);

for example MemoryStream(System.Text.Encoding.UTF8.GetBytes(sb.ToString())); i convert byte array to store in memory strem this time throu exception. memory out of stream.

Answer 1

Write directly to the response. Don't use a string builder because it uses up memory. You make matters worse and double up the memory usage by converting the string to a byte array with GetBytes(). You then waste even more memory writing the byte array to a memory stream. The you waste more memory converting the memory stream back to a byte array. You should also wrap your connection in a using statement and running your code inside of a try catch block.

Try this.

    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename="" + ("paresh.csv") + """);
    HttpContext.Current.Response.ContentType = "application/octet-stream";

    con.Open();
    SqlCommand cmd = new SqlCommand("select top 1000000 from temp_paresh", con);
    var sb = new StringBuilder();
    using (SqlDataReader sdr = cmd.ExecuteReader())
    {
        string csvseperator = ";";

        // this is no good unless you are going to write all fields to the output stream.
        //for (int i = 0; i < sdr.FieldCount; i++)
        //{
        //    HttpContext.Current.Response.Write(sdr.GetName(i));

        //    if (i < sdr.FieldCount - 1)
        //        HttpContext.Current.Response.Write(csvseperator);
        //}
        //HttpContext.Current.Response.Write("
");

        HttpContext.Current.Response.Write("rowid;rowname;rcount
");
        // You also need to escape your data if it contains a ";"
        while (sdr.Read())
        {
            HttpContext.Current.Response.Write(Convert.ToString(sdr["rowid"]));
            HttpContext.Current.Response.Write(csvseperator);
            HttpContext.Current.Response.Write(Convert.ToString(sdr["rowname"]));
            HttpContext.Current.Response.Write(csvseperator);
            HttpContext.Current.Response.Write(Convert.ToString(sdr["rcount"]));

            HttpContext.Current.Response.Write("
");
        }
    }
    con.Close();