Search code examples
c#asp.netsqldatasource

dynamically build select query for SqlDataSource

I have a drop down list populated with some table names. When the selection changes, GridView should reflect the changes accordingly as well.

I can think of 1 approach that probably can work - using a Stored Procedure with parameter supplied as table name.

But I am also wondering if there'd be any other ways to achieve this, well, without writing too much code preferably. Any ideas? Thanks

Solution

  • I would tend to agree that the stored procedure approach is preferable to the constructed SQL in both of the examples above.

    While not horrible in this case, you're sending a full query across the wire, which is not preferable. Further, you can also secure direct access to the tables themselves but grant access to the SPROC so you could provide this listing capability to certain user groups without allowing them direct access.

    I'd recommend using the CASE statement in your stored procedure rather than constructing SQL. This will guarantee that you're only supporting the tables you are aware of and prohibits any abuse of the system.