Hi anirudhp,
Instead of DataTable you need to use DataReader.
Database
For this example I have used of Northwind database that you can download using the link given below.
Download Northwind Database
Refer below example.
private void Export(object sender, EventArgs e)
{
string constr = "Server=.;DataBase=Test;UID=sa;PWD=pass@123";
string query = "SELECT CustomerId,Name,Country From Customers";
SqlCommand cmd = new SqlCommand();
SqlConnection con = new SqlConnection(constr);
cmd.Connection = con;
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
con.Open();
using (SqlDataReader sdr = sqlcmd.ExecuteReader())
{
using (StreamWriter streamWriter = new StreamWriter(@"D:\MyData.csv"))
{
DataTable dt = new DataTable();
for (int i = 0; i < sdr.FieldCount; i++)
{
dt.Columns.Add(sdr.GetName(i));
}
streamWriter.WriteLine(string.Join(",", dt.Columns.Cast<DataColumn>().Select(x=> x.ColumnName)));
while (sdr.Read())
{
streamWriter.WriteLine(sdr["CustomerId"].ToString() + "," + sdr["Name"].ToString() + "," + sdr["Country"].ToString() + ",");
}
}
}
con.Close();
}
