C # - need ideas on SQL execution and export to excel

Question

At the moment, my code is exporting all the columns that it executes into an excel file. Executable SQL commands are in stored procedures. I want to be able to specify which columns of SQL data I want to export.

One of the methods that I think of is to let the user select what they need and combine them into an SQL query, rather than using commands in a stored procedure.

I also tried to put parameters into stored procedures inside the selection area, believing that it would be easy to have parameters in the selection field, but from what I understand so far, is that it is not allowed due to the possibility of SQL injection.

I saw several examples of using DataSet and DataTables to store SQL data, but then exported it all to Excel. But if you use it this way, you won’t need a bunch of conditional statements to check before writing this column?

Are there any other methods? The code below is just to show what I'm 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 temporary tables to indicate which columns you want to retrieve from the 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.