Welcome Emmablakes
You will have to let the user choose a file save location and filename.
The solution is as follows: (sorry I dummied some code to get a quick one working on my machine for you)
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Web.UI;
namespace WebFormsGridViewCommand
{
public partial class _Default : Page
{
private string Cs = @"data source=(localdb)\mssqllocaldb;initial catalog=webformstest;integrated security=true";
private string path;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void BtnExport_Click(object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection(Cs))
{
conn.Open();
try
{
SqlDataAdapter sda = new SqlDataAdapter("GetDataToExportToCsv", conn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable dt = new DataTable();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
sda.Fill(dt);
path = "C:\\excel\\CBN_CCMS_" + DateTime.Now.ToString("ddMMyyyy") + DateTime.Now.Millisecond + ".csv";
CreateCSVFile(dt, path);
}
catch (Exception ex)
{
ex.ToString();
}
finally
{
conn.Close();
}
}
}
private void CreateCSVFile(DataTable dt, string path)
{
var sb = new StringBuilder();
foreach (DataColumn dc in dt.Columns)
{
sb.Append(dc.ColumnName).Append(",");
}
sb.Remove(sb.Length - 1, 1);
sb.AppendLine();
for (int i = 0; i < dt.Rows.Count; i++)
{
foreach (DataColumn dc in dt.Columns)
{
sb.Append(dt.Rows[i][dc.ColumnName]).Append(",");
}
sb.Remove(sb.Length - 1, 1);
sb.AppendLine();
}
string filename = Path.GetFileName(path);
Response.Clear();
Response.AddHeader("content-disposition", string.Format("attachment;filename={0}", filename));
Response.ContentType = "application/csv";
Response.Write(sb.ToString());
}
}
}