Using a string variable in an ASPX SqlDataSource SelectCommand, is this possible?

Question

I know that this IS possible in the codebehind, but changing the selectcommand there and binding it causes gridview issues with refreshing and sorting, so this is not an answer I'm looking for.

I already incorporate parameterized statements for my SQL params, but I need other parts of the query to change that I don't think would work as parameterized statements.

Example:

<asp:SqlDataSource ID="SQLSolutionSource" runat="server" 
 SelectCommand="SELECT id, my_cat1, my_cat2, my_cat3, my_cat4, my_cat5, my_cat6 FROM [my_db].[dbo].[my_table]"
  ConnectionString="<%$ ConnectionStrings:ConnectionString %>" />

I'll have about 10 IF ELSE statements that use the "SELECT id, my_cat1, my_cat2, my_cat3, my_cat4, my_cat5, my_cat6" portion of the command.

If it was codebehind, I could just create a variable with that above string and change the selectcommand to be "" + my_string + " FROM [my_db].[dbo].[my_table]";

Is there a way to do this on the ASPX page? I've tried creating public strings and using SelectCommand="<%= my_string %> FROM [my_db].[dbo].[my_table]" but it doesn't seem to work at all.

I understand people may wonder about security implications and SQL injections, but this variable will be hard coded and not be allowed to change by the user. I simply want to simplify my commands and clean my code, so if i need to change the selected items, I only have to do it in one variable, not 10 different places.

You might want to move away from SqlDataSource entirely...see [my answer](http://stackoverflow.com/questions/43279371/updatecommand-parameter-value-always-a-string/43280341#43280341) to this other question. — mason, Apr 07 '17 at 16:53
Thanks, but I'm not looking for an alternative data implementation, just an answer to my question. — R. StackUser, Apr 08 '17 at 23:42

R. StackUser · Answer 1 · 2017-05-10T19:40:16.877

Ultimately, I was able to build a dynamic SQL query as my selectcommand that works quite well, using EXECUTE sp_executesql.

EDIT: added sample below.

Sample code (untested, may need tweaking, as my production code is completely different)

  <asp:SqlDataSource ID="SQLSolutionSource" runat="server" 
        SelectCommand="
        declare @perm_source varchar(30)
        set @perm_source = @perm_type

        declare @select_query Nvarchar(400)    
        SET @select_query = 'SELECT id, my_cat1, my_cat2, my_cat3, my_cat4, my_cat5, my_cat6 FROM ' + @perm_source
        EXECUTE sp_executesql @select_query

        "
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" />

Now some may argue that the FROM ' + @perm_source is susceptible to injection attacks, but in my case it is not, as the variable (@perm_type) is parameterized and based on only static values from the codebehind.

You might want to add the code that solved your question to this answer, this will help other people with the same problem. — milo526, May 10 '17 at 18:39

Using a string variable in an ASPX SqlDataSource SelectCommand, is this possible?

1 Answers1