C# - Need ideas on executing SQL and exporting to excel

Question

Right now my code exports all the columns that it does the query for into an excel file. The SQL commands that are executed are in stored procedures. I want to have the option to be able to specify which columns from the SQL data I want to export.

One method that I am thinking of would be to have the user be able to pick what they want and concatenate them into a SQL query instead of using the commands in the stored procedure.

I also tried to put parameters into the stored procedures inside of the select area thinking that it would be simple to have parameters in the select field but from what I understand so far is that it's not allowed due to the possibility of SQL injections.

I've seen some examples of people using DataSets and DataTables to hold the SQL data but then export all of it to excel. But if you use it that way wouldn't you need a bunch of conditional statements to check before writing out that column?

Are there any other methods? Code posted below just to show what I am doing so far.

private SqlDataSource GetDataSource(string FruitType)
{

    SqlDataSource tempDataSource = new SqlDataSource();
    tempDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ServerConnectionString"].ToString();
    tempDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
    switch (ReportType)
    {
        case "Oranges":
            tempDataSource.SelectCommand = "getOrange";                                          
            break;
        case "Apples":
            tempDataSource.SelectCommand = "getApples";                
            break;
        case "Pineapples":
            tempDataSource.SelectCommand = "getPineapples";
            break;
        case "Watermelons":                
            tempDataSource.SelectCommand = "getWatermelons";                
            break;
        case "GrapeFruit":                
            tempDataSource.SelectCommand = "getGrapeFruit";                
            break;
    }

    tempDataSource.DataBind();
    return tempDataSource;
}
protected void btnSaveData(object sender, EventArgs e)
{ 
    string attachment = "attachment; filename=";
    attachment += "Fruit Data";
    attachment += ".xls";

    Response.ClearContent();
    Response.Charset = "";
    Response.AddHeader("content-disposition", attachment);
    Response.ContentType = "application/ms-excel";

    StringWriter sw = new StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);

    GridView gvTemp = new GridView();
    gvTemp.Attributes["runat"] = "server";

    SqlDataSource ds = GetDataSource(FruitType);
    gvTemp.DataSource = ds;
    gvTemp.DataBind();
    gvTemp.RenderControl(htw);
    Page.Controls.Add(gvTemp);
    Response.Write(sw.ToString());
    Response.End();

}

Answer 1

You can easily use temp tables to specify which columns do you want to get from stored procedure:

CREATE PROCEDURE sp_GetDiffDataExample
      @columnsStatement NVARCHAR(MAX) -- Needed columns
AS
BEGIN
    DECLARE @query NVARCHAR(MAX)
    SET @query = N'SELECT ' + @columnsStatement + N' INTO ##TempTable FROM dbo.TestTable'
    EXEC sp_executeSql @query
    SELECT * FROM ##TempTable
    DROP TABLE ##TempTable
END

Hope this helps.