Changing Columns in the Visual Studio DataSet Designer

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:

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?