I know that last posting was a little obtuse, and not only that, but that the form post I made in reference to the problem was also a smidgen aloof. I tried explaining this problem so some wonderful colleagues, none of whom had too much of an idea on the resolution. All of saw the boo-koos of forum posts on the topic, and all in all found ourselves with bloody foreheads, wondering how this slipped through Microsoft's QA department. Somehow, the ObjectDataSource, the DataSet/TableAdapter designer, or a combination of the above has failed us. I've seen a lot of posts all over the web about this problem and so far not a single verifiable solution that makes any sense from an enterprise pespective.
(cough) By the way. I'm pretty angry right now, at the sheer inadequacy of this new toolset I've been given. I love Atlas, I love .Net, and I came to the conclusion the other day that my team should give the native tools a shot instead of working many hours of overtime writing our own data layers. "Let's use these awesome tools they've given us," I said. Today, I've come to the conclusion - and I'm here to proove it in pictures - that the VS 2005 designers have a serious problem that needs resolution. So if I call out your team, if I pee in your cornflakes, or if I piss you off because of the numerous hours you spent working on this tool to make it "better" or more "efficient..." Well, I'm not apologizing. I'm asking you to apologize to me and all the others out there who've found this problem over and over again. You know how you can make this much-needed apology? Fix the problem with a service pack and stop rushing your releases out the door. Wait until the stuff works. And for those of you who insisted on the rush, be more patient. Stuff is better when it works.
With that, let's begin. I'm going to demonstrate these bugs through an end-to-end example, most of which will be told using screenshots.
First and foremost, we'll need to create a demo database. The picture below shows the simple database's structure.
We'll also add a few records to the table. Sorry guys, you helped me, so you get some credit.
Not too complicated, right? Just wait. Next, create a DataSet in the project.
Then drag the People table into the designer to create a datatable.
We're going to proove that the designer just blows itself apart when creating new - and when using existing - stored procedures (because in most enterprise environments I've worked, that's the standard via the nice DBA specifications you're responsible for upholding). So below will demonstrate a few quick steps on how we'll create new procs to be used with this DataTableAdapter implementation.
Next, I click previous, to set this thing up to use new procedures. The next few screens describe this process without the need for much verbiage.
Now we've got a database and some procedures. Just for good measure, below is the SQL we just generated in our SQL 2000 database (there are, surprisingly to Microsoft, a lot of customers who still use this product). Below you'll find the SQL we just generated.
IF
EXISTS (SELECT * FROM sysobjects WHERE name = 'GetPeople' AND user_name(uid) =
'dbo')
DROP PROCEDURE dbo.GetPeople
GO
CREATE
PROCEDURE dbo.GetPeople
AS
SET NOCOUNT ON;
SELECT
PersonId, Firstname, Lastname FROM dbo.People
GO
IF
EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertPerson' AND user_name(uid)
= 'dbo')
DROP PROCEDURE dbo.InsertPerson
GO
CREATE
PROCEDURE dbo.InsertPerson
(
@Firstname nvarchar(50),
@Lastname nvarchar(50)
)
AS
SET NOCOUNT OFF;
INSERT
INTO [dbo].[People] ([Firstname], [Lastname]) VALUES (@Firstname, @Lastname);
SELECT
PersonId, Firstname, Lastname FROM People WHERE (PersonId = SCOPE_IDENTITY())
GO
IF
EXISTS (SELECT * FROM sysobjects WHERE name = 'UpdatePerson' AND user_name(uid)
= 'dbo')
DROP PROCEDURE dbo.UpdatePerson
GO
CREATE
PROCEDURE dbo.UpdatePerson
(
@Firstname nvarchar(50),
@Lastname nvarchar(50),
@Original_PersonId int,
@PersonId int
)
AS
SET NOCOUNT OFF;
UPDATE
[dbo].[People] SET [Firstname] = @Firstname, [Lastname] = @Lastname WHERE
(([PersonId] = @Original_PersonId));
SELECT
PersonId, Firstname, Lastname FROM People WHERE (PersonId = @PersonId)
GO
IF
EXISTS (SELECT * FROM sysobjects WHERE name = 'DeletePerson' AND user_name(uid)
= 'dbo')
DROP PROCEDURE dbo.DeletePerson
GO
CREATE
PROCEDURE dbo.DeletePerson
(
@Original_PersonId int
)
AS
SET NOCOUNT OFF;
DELETE
FROM [dbo].[People] WHERE (([PersonId] = @Original_PersonId))
GO
Moving on, I'd like to take just a moment to show everyone that I'm really using the n-tier model. I'm developing this dataset and the "stuff" within it via a separate project, that's referenced by my webforms project. Here's a snapshot of the Solution Explorer to illustrate this fact.
Next, we'll create an ASPX page, add an ObjectDataSource to it that uses the new datatable adapter, and a GridView that binds to that ODS. The next few steps are most likely pretty self-explanatory.
Now that we've got adatabound gridview on the page, let's browse it to make sure everything's working properly.
Looks good. So I click the edit button...
I'll leave it as-is, and just click the update button to save it again...
POOF. So, here's the deal. As one of the links above (an ASP.Net forum post that goes on longer than even this article) explains, you have to make the primary key field editable or this process will not function. Yes, the primary key field must be editable. ONE MORE TIME. The primary key field must be made editable. Do I need to go into the long list of problems associated with this? Yeah, didn't think so. Anyway, the next few pictures outline this process - the process of allowing people to (what was that?) edit the primary key field. Yes, you heard me.
This time, everything works, as the following pictures will dictate. Keep reading, it gets interesting in a minute.
So there. Its editable alright. Hopefully the users will listen when we tell them not to edit the primary key field. Hopefully. And hopefully, if they ask why not and we tell them "because it'll totally screw up the database if you do so" they won't get too frightened, will still sign off on the application, and the DBA's will chain themselves to their desks and begin the neverending process of caretaking the enterprise database.
But wait! What if we just hide the field containing the primary key from view!?!?!? How about that? Let's try it. We'll leave the field editable, but will simply hide it from the user. The screenshot below shows how this field has been selected, and how clicking the "remove column" link will hide it.
Then, we browse the page...
Edit an item and then click the update button again, and get the following happy screen.
So basically, we can't hide the field from the users, we have to let them edit it, and that's just the way it's going to have to be.
Problem #1: Is this a realistic expectation? Should developers really allow for the editability of a primary key column in order to use these designers? I'd go with no here, but I guess they know better than me.
Moving on. This previous example kind of infers that you allowed the designer the SQL-writing for you. Most time our DBA counterparts aren't too keen on that as an idea, and sometimes will be nice enough to even write the SQL for us. In my case, that's the deal with my team; the developers write the code and the DBA's write the procs the developers interface. So we need to back up a little and change our code so that it looks more like a realistic stored procedure we'd find in an enterprise environment.
Below is a screenshot of the procs created by the wizard. We're about to get rid of that nasty-looking @original_personId parameter. Pay attention to detail on these next two pictures, you'll see how I've changed the procedure so that it looks more realistic, like something you'd find in a real office enterprise database somewhere. This first picture shows the original (no pun intended) procs.
And finally, the new, more realistic procs.
I then need to go back into the VS.Net dataset designer and update the table adapter. Theoretically, I could (and did, to be honest) create a whole new data table adapter. This just shortens the post and demonstrates the variation in the update and delete procedures.
I go back into the ASPX designer and refresh the ODS and the GridView so that they reflect the new changes.
Now, as another post in that ASP.Net forum trail I referenced earlier points out, the ODS designer makes the assumption you're going to have to allow for the editing of original values, so there's a nice little attribute on the ODS object called OldValuesParameterFormatString. I hate to say this about my Redmond colleagues, but it feels like someone stuck this in without thinking up a better solution for the "we have to allow editing of the primary key column" issue. My personal opinion? Laziness or working far too many hours and therein coming up with quick fixes. Who knows, point is, the designer makes some assumptions for us when spitting out the HTML, and if you don't adhere to the original-prefixed nomenclature (haha!) you have to edit this yourself before things will work. First screenshot below shows the original values (once again, no pun intended).
And next, is the change suggested in the ASP.Net forum posts.
If we were to browse this page after having made this change we'd see that updating works. It even works if you hide the primary key column (go figure!!!). But here's the deal, people -
Problem #2: Should developers be forced to edit the designer-generated code? Should they be forced to possibly bork the (assumed) carefully-generated code requisite for a properly-functioning data mapping layer?
So there you have it, in its entirety, with screenshots. In a sentence, the problem is simple. To use the datatable designers and default implementation to do data-mapping/binding one must allow for the editability of the primary key column or be required to edit the code generated by the designer.
Based on the supposition that solution A basically breaks down the very idea of a properly-functioning data-mapping architecture, and that solution B requires that developers not only change the code that's generated, but that they know that the code needs to be changed in the first place, and more importantly just how it should be changed. So what's the point in using it at all?
Especially when you consider the fact that it doesn't work properly!