Andre's Blog

Personal blog of Andre Perusse

Visual Studio 2005, Auto-Generated DataSets, and Computed Columns

So, as a developer who prefers to roll his own code for data access layers (i.e., build my object model in a Business layer (BLL), write data methods in a data layer (DAL), write stored procedures, etc. with copious use of CodeSmith), I decided to give Typed Datasets a try in one of my recent projects. I was hoping to achieve a huge productivity boost in not having to write any BLL or DAL code. The app was small, not really high-traffic, so I didn't mind giving up all the control I would have had with my usual method.

Well, lo-and-behold it worked quite well! I was able to assemble the app rather quickly and I could concentrate most of my efforts on the actual application as opposed to the data plumbing. Just drag and drop all the tables onto a DataSet designer, add a few queries here and there, and I was done! So impressed was I by this, I decided to use it again in another project.

In doing so, I was reminded why I prefer to roll my own code all the time. I'm using SQL Server 2000, and one of my columns in one of my tables is a computed column. Drag and drop tables onto the DataSet designer, add some queries, everything is going fine so far. Then I try to update a record in my web app. Ka-boom! I get this error message:

Column 'overallValue' cannot be modified because it is a computed column.

I know immediately what this is: the DataSet designer doesn't check the schema for computed columns when dragging and dropping tables onto the designer surface. Thus, its auto-generated INSERT and UPDATE statements include the computed columns. Arg!!!

So, not a big deal really, I dive into the auto-generated code and fix up the INSERT and UPDATE statements for this TableAdapter. Of course, if I ever have to re-gererate the DataTable (due to a schema change, for example), I have to remember to perform this little hack. Grrrr......

While I'm not going to abandon VS 2005's auto-generated Typed Datasets feature (even with this problem, it's still far too productive to just chuck it), this kind of problem is just so typical of auto-generated stuff - it nevers takes into account everything. If you're lucky (like I was this time), you'll be able to figure out what's wrong and fix it up with a little hacking in a short time. However, the more complex they make the auto-generated stuff, the more difficult it becomes to do this.


Comments (1) -

  • Darth Mac

    7/23/2006 6:55:55 AM |

    Bah! Stick to Notepad is what I say!