Changing Columns in the Visual Studio DataSet Designer

06.26.2007

Recommended Reading:
New DataSet Features in Visual Studio 2005

New to Visual Studio 2005, Microsoft has included the new DataSet Designer that is supposed to automate many of our data access tasks and to replace most, if not all, of the data layer in N-tier applications. To be honest though, I think they missed the boat on convenience.

As a quick intro, here’s a sample screenshot of DataSet Designer:
DataSet Designer Screenshot

Today, for example, it turned out that I needed to modify the name and datatype of a column in one of my database tables. It can’t be that hard, right? Wrong. Maybe there’s a better way, but just to change my one, single, measly column name and datatype, here’s what I had to do as far as the DataSet Designer goes (in other words, excluding code changes in other application layers as well as the initial change in the database)

  1. With the column name selected on the dataset, change the “Name” property to match the new column name.
  2. With the column name selected on the dataset, change the “Source” property to match the new column name.
  3. With the column name selected on the dataset, change the “DataType” property to match the new column name.
  4. With the column name selected on the dataset, update the “MaxLength” property to match the new column datatype if needed
  5. With the column name selected on the dataset, update the “MaxLength” property to match the new column datatype if needed
  6. With the tableadapter selected, in the properties window, hit the plus sign next to “DeleteCommand,” highlight the “Parameters” field, click the ellipses next to where it says “(Collection),” select the column name, then change the DbType, ColumnName, Size, SourceColumn, ParameterName, and ProviderType columns.
  7. With the tableadapter selected, in the properties window, hit the plus sign next to “InsertCommand,” highlight the “Parameters” field, click the ellipses next to where it says “(Collection),” select the column name, then change the DbType, ColumnName, Size, SourceColumn, ParameterName, and ProviderType columns.
  8. With the tableadapter selected, in the properties window, hit the plus sign next to “UpdateCommand,” highlight the “Parameters” field, click the ellipses next to where it says “(Collection),” select the column name, then change the DbType, ColumnName, Size, SourceColumn, ParameterName, and ProviderType columns.
  9. With the tableadapter selected, in the properties window, hit the plus sign next to “DeleteCommand,” highlight the “CommandText” field, click the ellipses, and modify the statement to match the new column name.
  10. With the tableadapter selected, in the properties window, hit the plus sign next to “InsertCommand,” highlight the “CommandText” field, click the ellipses, and modify the statement to match the new column name.
  11. With the tableadapter selected, in the properties window, hit the plus sign next to “UpdateCommand,” highlight the “CommandText” field, click the ellipses, and modify the statement to match the new column name.
  12. If you want to keep things consistent, repeat all previous steps for every table that has a foreign key pointing to our modified column. In my case, that was two more tables.

Crazy? Well, considering it would still be a lot of work if we didn’t have the DataSet Designer, not too crazy. Actually…yeah, it is. I’d rather not require Carpal Tunnel therapy every time I change a column name. Going to the underlying XML code (right-click in the DataSet Designer and click View Code) and doing a quick find and replace helps in changing the name where needed, but it can be a risky move and you’re still left with manually changing the datatype. Am I the only one that thinks the designer is crazy ridiculous when it comes to modifying what’s already on the canvas?

So, is there a better way? As you as frustrated as I am? Please…. join my intimate conversation.

Tags: , , ,


Comment

07.06.2007 / Aaron Hardy said:

I noticed today that if you add a column to the main “Fill, GetData()” query on the TableAdapter that it will prompt and ask you if you would like it to update the changes on your other queries too. Not bad! It doesn’t work on all your queries though because VS can’t always read your mind. If you change the type of a column on the main Fill, GetData() queries and/or the datatable, it seems to do some cascading through your other queries as well, but it’s a little bit hit and miss. Here’s an article from Microsoft that does some explaining:

How to: Edit TableAdapters


Leave a Comment

Your email address is required but will not be published.




Comment