Convert to CSV (not efficient)

Example of how you can implement these optimizations:

SP_ValueCPK = "[SP_CPK_VALUE_DOWNLOAD_CSV]  '" + Dept + "','" + PeriodeAwal + "','" + PeriodeAkhir + "','" + IDProductType + "' ";
using (SqlCommand cmd = new SqlCommand(SP_ValueCPK))
{
    using (SqlDataAdapter sda = new SqlDataAdapter())
    {
        cmd.Connection = con;
        sda.SelectCommand = cmd;

        // Use SqlDataReader to stream data
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            if (reader.HasRows)
            {
                Response.Clear();
                Response.Buffer = true;
                Response.AddHeader("content-disposition", "attachment;filename=" + this.DropDownListDept.SelectedValue + " - Value of CPK (" + this.LabelPeriodeNow.Text + " - " + this.LabelPeriodeLast.Text + ").csv");
                Response.Charset = "";
                Response.ContentType = "application/text";

                // Use a StringBuilder to build the CSV
                StringBuilder csvBuilder = new StringBuilder();

                // Write column headers
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    csvBuilder.Append(reader.GetName(i) + ",");
                }
                csvBuilder.AppendLine();

                // Stream data
                while (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        csvBuilder.Append(reader[i].ToString().Replace(",", ";") + ",");
                    }
                    csvBuilder.AppendLine();
                }

                // Write the CSV to the response
                Response.Write(csvBuilder.ToString());
                Response.End();
            }
            else
            {
                ResinTesterClass.Alert("Data Not Found", this.Page);
            }
        }
    }
}

Thanks

Naimish Makwana

You can use csv Helper
 

public void Generate(SurveyData[] dataList, string fileName)

{
    using (var memoryStream = File.OpenWrite($".\\{fileName}.csv"))
    {
        using (var streamWriter = new StreamWriter(memoryStream, Encoding.UTF8))
        {
            using (var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture))
            {
                csvWriter.WriteRecords(dataList);
                csvWriter.Flush();
            }
        }
    }
}

Test this

            public static string WriteToCSV(string input)
        {
            try
            {
                if (input == null)
                    return string.Empty;
 
                bool containsQuote = false;
                bool containsComma = false;
                int len = input.Length;
                for (int i = 0; i < len && (containsComma == false || containsQuote == false); i++)
                {
                    char ch = input[i];
                    if (ch == '"')
                        containsQuote = true;
                    else if (ch == ',')
                        containsComma = true;
                }
 
                if (containsQuote && containsComma)
                    input = input.Replace("\"", "\"\"");
 
                if (containsComma)
                    return "\"" + input + "\"";
                else
                    return input;
            }
            catch
            {
                throw;
            }
        }



            

using (SqlCommand cmd = new SqlCommand(SP_ValueCPK))
 { using (SqlDataAdapter sda = new SqlDataAdapter()) 
{ 
cmd.Connection = con; 
sda.SelectCommand = cmd; 
using (DataTable dt = new DataTable()) 
{ sda.Fill(dt); 
if (dt.Rows.Count > 0) 
{

StringBuilder sb = new StringBuilder();
           
            foreach (DataRow dr in dt.Rows)
            {
                foreach (DataColumn dc in dt.Columns)
                sb.Append(WriteToCS(dr[dc.ColumnName].ToString()) + ",");
                sb.Remove(sb.Length - 1, 1);
                sb.AppendLine();
            }
            File.WriteAllText(@"folderpath\fileName.csv", sb.ToString());

}
}
}
}