Peter
7/19/2007 11:48:00 PM
Thanks Jim. When you're commenting on Enterprise Library, I assume that
you're referring to Enterprise Library 1.0 not Enterprise Library 3.1. I
wonder whether MS has enhanced it to resolve the issues you mentioned.
"Jim Rand" wrote:
> Add a component to your project. Right click the data tab in the toolbox.
> Click customize. Add a SqlDataAdapter and a SqlConnection. Now drag a data
> adapter onto the component designer surface. It works just like VS 2003.
>
> I found the designer to be inadequate for generating sql code. Look at the
> data that it has available and the C# code it generates. It doesn't take a
> whole lot of imagination to create a data structure and code generator using
> that as an idea base that configures data adapters the way you want them.
>
> Well worth the time.
>
> The Enterprise Library is not going to address correctly configuring the
> data adapters with complex SQL that handles auto increment keys and
> timestamp concurrency checking:
>
> SELECT CERegistrationID,NonMemberID,Paid,RealtorID,SmartCard, 1 AS
> Downloaded,CAST(TS AS INT) AS TS
> FROM dbo.CERegistration
> WHERE CERegistrationID IN (SELECT CR.CERegistrationID
> FROM dbo.CourseRegistration AS CR INNER JOIN dbo.Course AS C ON
> CR.CourseID = C.CourseID
> WHERE C.EventID = @EventID)
>
> INSERT INTO dbo.CERegistration (NonMemberID,Paid,RealtorID,SmartCard)
> VALUES (@NonMemberID,@Paid,@RealtorID,@SmartCard);SELECT CERegistrationID,
> CAST(TS AS INT) AS TS FROM dbo.CERegistrationWHERE CERegistrationID =
> SCOPE_IDENTITY()
>
> UPDATE dbo.CERegistration
> SET [NonMemberID] = @NonMemberID,
> [Paid] = @Paid,[RealtorID] = @RealtorID,
> [SmartCard] = @SmartCard
> WHERE CERegistrationID = @Original_CERegistrationID AND CAST(TS AS INT) =
> @Original_TS;SELECT CAST(TS AS INT) AS TS FROM dbo.CERegistration WHERE
> CERegistrationID = @CERegistrationID
>
> DELETE FROM dbo.CERegistration
> WHERE CERegistrationID = @Original_CERegistrationID AND CAST(TS AS INT) =
> @Original_TS
>
> My code generator wrote the above sql automatically except for the SELECT
> statement. For the SELECT statement, I had to manually add the WHERE clause
> to the retrieve only those CERegistration rows that are referenced by the
> many-to-many CourseRegistration table that references the one-to-many Course
> table for an Event.
>
> Just typing that SELECT statement into the Microsoft SQL wizard will cause
> it to chock and will prevent it from continuing on to configure the
> parameter collection.
>
> Also, the Enterprise Library will not address the nasty little traps in the
> adapter itself when updating.
>
> > By the way, I don't know that VS2005 has a data adapter sql wizard.
> >>
>
>
>