1) Use no keys in a table.
2) Try to use an SqlDataSource with a run-time-provided table name.
No Keys, No Functionality
The first one may seem fairly obvious. However, we have a database that does not have keys, because it has simply not needed them until I came along to program a front end for it. Any time you try to take advantage of some of those shiny new objects like SqlDataSource or even a GridView bound to a DataTable, the lack of keys will bite you every time you attempt an update or delete. If you have to go keyless for some strange reason, you will have to write your own event handlers and run the queries manually. You might as well go back to .NET 1.1 and handle it all yourself. SqlDataSource comes closest to working without keys, so long as you specify custom update and delete statements, and the delete statement doesn't depend on using a parameterized WHERE clause. You see, .NET 2.0 won't pass the parameters unless they are keys... even if you specify those column names as parameters.
Gotta Know Up Front
The second one was a little more elusive. The first thing I tried was to use a parameter for the table name. Essentially, you can't do that. This is because the parameter would have to be of type string (because nothing else makes sense), and .NET wraps string parameters in single quotes before executing the query. As of this writing, I have not found a way to prevent it from doing this, and your SQL server sees anything with quotes around it as a string value, rather than a database object. Also string-based functions like REPLACE or SUBSTRING return strings, not database objects, so you can't run that quoted string through them to fix it either. Perhaps it would be possible with stored procedures. How about writing new database stored procedures to work around the inflexibility of your shiny new .NET 2.0 object? Yeah, that sounds pretty lame to me too.
So after giving up on using a parameter for a table name, my next step was to try to dynamically generate SqlDataSource properties each time a different table was chosen. After all, I couldn't be sure when new tables were added, so I didn't want to create one for each table and then later go back to create more as they were added. Well, let me just say again: You might as well go back to .NET 1.1 and handle the queries yourself. You see, even if you declaratively (i.e. with a asp:SqlDataSource tag in your .aspx page) create an SqlDataSource, any properties that you assign to it programmatically are lost between trips to the server. At first I tried just changing the SelectCommand programmatically (i.e. in the codebehind page) to select from a different table, but when the user goes to page through or sort the data, the SelectCommand reverts back to it's original statement as written in the asp:SqlDataSource tag. So then in order for you to show the user the data they expect, you have to setup a page variable to store the data for those properties, and use handlers to fix the SqlDataSource after every trip to the server, like so:
<asp:GridView ID="GridView1"
...
OnPageIndexChanging="fixDataSource"
OnSorting="fixDataSource"
...
Oh, but it gets worse. When you change tables, the GridView still remembers the user's last sorting and page index, so you have to reset those when you change tables.
// remember these will also call fixDataSource as well
GridView.Sort("", SortDirection.Ascending);
GridView.PageIndex = 0;
Then there's the "what if" of wanting the user to be able to actually edit the data in the GridView.
<asp:GridView ID="GridView1"
...
OnPageIndexChanging="fixDataSource"
OnSorting="fixDataSource"
OnRowCancelingEdit="fixDataSource"
OnRowDeleting="fixDataSource"
OnRowEditing="fixDataSource"
OnRowUpdating="fixDataSource"
...
But wait, there's more:
<asp:SqlDataSource ID="SqlDataSource1"
...
OnDeleting="fixDataSource"
OnUpdating="fixDataSource"
...
So in the end, I'm left with a "What's the point?" mentality. Dynamically picking your table to select from at run time is for the hard core, not for me.
Why is that a problem? After all isn't it "job security" since they have to depend on me to update the code with a new declarative SqlDataSource when a new table is added to the database.
Well honestly Microsoft, I'd rather be less annoyed with trivial code updates. How about a way to pick the table to select from at run-time in 3.0?
