Export DataSet or DataTable to Excel

Function to get the results in datatable

private DataTable GetData(SqlCommand cmd)
{
    DataTable dt = new DataTable();
    String strConnString = System.Configuration.ConfigurationManager.
         ConnectionStrings["conString"].ConnectionString;
    SqlConnection con = new SqlConnection(strConnString);
    SqlDataAdapter sda = new SqlDataAdapter();
    cmd.CommandType = CommandType.Text;
    cmd.Connection = con;
    try
    {
        con.Open();
        sda.SelectCommand = cmd;
        sda.Fill(dt);
        return dt;
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        con.Close();
        sda.Dispose();
        con.Dispose();
    }
}

Export to Excel

protected void ExportToExcel(object sender, EventArgs e)
{
    //Get the data from database into datatable
    string strQuery = "select CustomerID, ContactName, City, PostalCode" +
        " from customers";
    SqlCommand cmd = new SqlCommand(strQuery);
    DataTable dt = GetData(cmd);
 
    //Create a dummy GridView
    GridView GridView1 = new GridView();
    GridView1.AllowPaging = false;
    GridView1.DataSource = dt;
    GridView1.DataBind();
 
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition",
     "attachment;filename=DataTable.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    StringWriter sw = new StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(sw);
 
    for (int i = 0; i < GridView1.Rows.Count; i++)
    {
        //Apply text style to each Row
        GridView1.Rows[i].Attributes.Add("class", "textmode");
    }
    GridView1.RenderControl(hw);
 
    //style to format numbers to string
    string style = @"<style> .textmode { mso-number-format:\@; } </style>";
    Response.Write(style);
    Response.Output.Write(sw.ToString());
    Response.Flush();
    Response.End();
}

Source from ASP Snippets

SqlCommand and AddWithValue – The Proper Way Of Adding Parameters To SQL Queries

When adding parameters to SQL queries in code behind ASP.NET pages, the ideal syntax is to use a SQL parameter to help build your query string, rather than creating a text string on the fly.

Example (Bad usage of inline substitution)

mySqlCommand = new SqlCommand("SELECT * FROM [PollAnswers] WHERE ([PollID] = '" + PollID + "') ORDER BY [SortOrder]", mySqlConnection);

Instead use Parameters.AddWithValue and substitute the values in place.

 mySqlCommand = new SqlCommand("SELECT * FROM [PollAnswers] WHERE ([PollID] = @PollID) ORDER BY [SortOrder]", mySqlConnection);

mySqlCommand.Parameters.AddWithValue("@PollID", PollID);

Using SqlCommand and AddWithValue Parameters To Execute SQL Insert

public bool PollCastVote(int PollAnswerID)
    {

        bool returnValue = false;

        Guid userID = Guid.NewGuid();
        DateTime dateTimeStamp = DateTime.Now;

        // connection string
        string connectionString = ConfigurationManager.ConnectionStrings["PollConnectionString"].ConnectionString;

        // Sql connection object initialized with connection string used to connect
        using (SqlConnection mySqlConnection = new SqlConnection(connectionString))
        {
            try
            {
                // open the Sql connection
                mySqlConnection.Open();

                // Sql Command object initialized with SQL INSERT
                SqlCommand mySqlCommand = new SqlCommand("INSERT INTO PollUserResponses (UserID, PollAnswerID, DateTimeStamp) VALUES (@UserID, @PollAnswerID, @DateTimeStamp)", mySqlConnection);
                mySqlCommand.Parameters.AddWithValue("@UserID", userID);
                mySqlCommand.Parameters.AddWithValue("@PollAnswerID", PollAnswerID);
                mySqlCommand.Parameters.AddWithValue("@DateTimeStamp", dateTimeStamp);

                // Execute
                mySqlCommand.ExecuteNonQuery();

                returnValue = true;

            }
            catch (Exception ex)
            {

            }
            finally
            {
                // close the Sql Connection
                mySqlConnection.Close();
            }

            return returnValue;

        }
    }