http://adoguy.com ado.net in action promises realized
TRANSCRIPT
http://adoguy.comhttp://adoguy.com
ADO.NET in ActionADO.NET in Action
Promises RealizedPromises Realized
http://adoguy.comhttp://adoguy.com
Who I amWho I am
Shawn Wildermuth, Shawn Wildermuth, [email protected]@adoguy.com
INETA SpeakerINETA Speaker Author of “Pragmatic ADO.NET”, Addison-Author of “Pragmatic ADO.NET”, Addison-
WesleyWesley Editor of Editor of http://ondotnet.comhttp://ondotnet.com For More Info: For More Info: http://adoguy.comhttp://adoguy.com This Presentation can be found at:This Presentation can be found at:
– http://adoguy.com/presentationshttp://adoguy.com/presentations
http://adoguy.comhttp://adoguy.com
ADO.NET in ActionADO.NET in Action
Disconnected ConcurrencyDisconnected Concurrency DataSets in Web ServicesDataSets in Web Services Better Business ObjectsBetter Business Objects Improving Typed DataSetsImproving Typed DataSets
http://adoguy.comhttp://adoguy.com
Disconnected ConcurrencyDisconnected Concurrency
The hard-part of a disconnected The hard-part of a disconnected architecture is concurrencyarchitecture is concurrency– Optimistic Concurrency Supported by Optimistic Concurrency Supported by
CommandBuilders (but inefficient)CommandBuilders (but inefficient)– Pessimistic Concurrency can be achieved Pessimistic Concurrency can be achieved
with Check-out, Check-inwith Check-out, Check-in– Destructive Concurrency can only be handled Destructive Concurrency can only be handled
by writing own updatesby writing own updates
http://adoguy.comhttp://adoguy.com
Optimistic Concurrency MethodsOptimistic Concurrency Methods
There are at least three methods:There are at least three methods:– TimestampTimestamp– ComparisonComparison– ChecksumChecksum
http://adoguy.comhttp://adoguy.com
CommandBuilder ConcurrencyCommandBuilder Concurrency
CommandBuildersCommandBuilders– Allows updates and deletes where every field Allows updates and deletes where every field
in the database is the same as when they in the database is the same as when they were retrievedwere retrieved
– RobustRobust– InefficientInefficient
http://adoguy.comhttp://adoguy.com
CommandBuilder SampleCommandBuilder SampleDELETE FROM CUSTOMER WHERE ( (CustomerID = @p3) ANDDELETE FROM CUSTOMER WHERE ( (CustomerID = @p3) AND
((FirstName IS NULL AND @p4 IS NULL) OR (FirstName = @p5)) AND((FirstName IS NULL AND @p4 IS NULL) OR (FirstName = @p5)) AND
((LastName IS NULL AND @p6 IS NULL) OR (LastName = @p7)) AND((LastName IS NULL AND @p6 IS NULL) OR (LastName = @p7)) AND
((MiddleName IS NULL AND @p8 IS NULL) OR (MiddleName = @p9)) AND((MiddleName IS NULL AND @p8 IS NULL) OR (MiddleName = @p9)) AND
((Address IS NULL AND @p10 IS NULL) OR (Address = @p11)) AND((Address IS NULL AND @p10 IS NULL) OR (Address = @p11)) AND
((Apartment IS NULL AND @p12 IS NULL) OR (Apartment = @p13)) AND((Apartment IS NULL AND @p12 IS NULL) OR (Apartment = @p13)) AND
((City IS NULL AND @p14 IS NULL) OR (City = @p15)) AND((City IS NULL AND @p14 IS NULL) OR (City = @p15)) AND
((State IS NULL AND @p16 IS NULL) OR (State = @p17)) AND((State IS NULL AND @p16 IS NULL) OR (State = @p17)) AND
((Zip IS NULL AND @p18 IS NULL) OR (Zip = @p19)) AND((Zip IS NULL AND @p18 IS NULL) OR (Zip = @p19)) AND
((HomePhone IS NULL AND @p20 IS NULL) OR (HomePhone = @p21)) AND((HomePhone IS NULL AND @p20 IS NULL) OR (HomePhone = @p21)) AND
((BusinessPhone IS NULL AND @p22 IS NULL) OR (BusinessPhone = @p23)) AND((BusinessPhone IS NULL AND @p22 IS NULL) OR (BusinessPhone = @p23)) AND
((DOB IS NULL AND @p24 IS NULL) OR (DOB = @p25)) AND ((DOB IS NULL AND @p24 IS NULL) OR (DOB = @p25)) AND
((Discount IS NULL AND @p26 IS NULL) OR (Discount = @p27)) AND((Discount IS NULL AND @p26 IS NULL) OR (Discount = @p27)) AND
((CheckedOut IS NULL AND @p28 IS NULL) OR (CheckedOut = @p29)) )((CheckedOut IS NULL AND @p28 IS NULL) OR (CheckedOut = @p29)) )
http://adoguy.comhttp://adoguy.com
Custom Optimistic ConcurrencyCustom Optimistic Concurrency
To make it more efficient, we can use To make it more efficient, we can use Timestamp: Timestamp: – DELETE FROM CUSTOMER WHERE DELETE FROM CUSTOMER WHERE (CustomerID = @p3) AND (Stamp = @p4)(CustomerID = @p3) AND (Stamp = @p4)
– DataAdapter.DeleteCommand.CommandText!DataAdapter.DeleteCommand.CommandText!
http://adoguy.comhttp://adoguy.com
Concurrency ViolationsConcurrency Violations
DataAdapters throw DataAdapters throw DBConcurrencyExceptionDBConcurrencyException– This is thrown whenever the affected rows of an This is thrown whenever the affected rows of an
update is zero.update is zero.– False positives are possible.False positives are possible.
DataAdapter.ContinueUpdateOnError– Each violated row will have errors associated with it. – DataRow.HasErrors will tell you which rows are
problematic
http://adoguy.comhttp://adoguy.com
Handling Concurrency Violations
Three options:Three options:– Tell the user the data is stale, and offer to Tell the user the data is stale, and offer to
‘freshen’ it and lose their changes‘freshen’ it and lose their changes– Ask the user if (s)he wants to overwrite Ask the user if (s)he wants to overwrite
needs to be done without a CommandBuilderneeds to be done without a CommandBuilder
– Field-level collision detectionField-level collision detection Allow field to be updated if it hasn’t changedAllow field to be updated if it hasn’t changed
http://adoguy.comhttp://adoguy.com
DataSets in Web ServicesDataSets in Web Services
Can you use DataSets in Web Services?Can you use DataSets in Web Services?– At first glance, the answer is noAt first glance, the answer is no
DataSets serialize to DiffGrams, DataSetsDataSets serialize to DiffGrams, DataSets DiffGrams are not platform agnosticDiffGrams are not platform agnostic Do not communicate the full schemaDo not communicate the full schema
http://adoguy.comhttp://adoguy.com
DataSets in WebServices (2)DataSets in WebServices (2)
– Would you want to use DataSets anyway?Would you want to use DataSets anyway? Great container for Structured XMLGreat container for Structured XML Database integrationDatabase integration Easy to intermingle database/XML dataEasy to intermingle database/XML data
– When *not* to use DataSetsWhen *not* to use DataSets When creating XML Documents from non-When creating XML Documents from non-
database sourcesdatabase sources
http://adoguy.comhttp://adoguy.com
Possible Solutions?Possible Solutions?
DataSets can hold XML, so you can:DataSets can hold XML, so you can:– return myDataSet.GetXml()return myDataSet.GetXml()
How about XmlDataDocument?How about XmlDataDocument?– return new XmlDataDocument(myDataSet)return new XmlDataDocument(myDataSet)
http://adoguy.comhttp://adoguy.com
Web Services and SchemaWeb Services and Schema
DataSet schema is XSD!DataSet schema is XSD!– When using DataSets, DocLiteral is your When using DataSets, DocLiteral is your
friendfriend DataSets can define and be the holder for the DataSets can define and be the holder for the
documents that Web Services throw arounddocuments that Web Services throw around
– DataSet schema can define our WSDL TypesDataSet schema can define our WSDL Types Yassar Shohoud’s book shows you howYassar Shohoud’s book shows you how ““Real World XML Web Services”Real World XML Web Services”
http://adoguy.comhttp://adoguy.com
Better Business ObjectsBetter Business Objects
Using Typed DataSetsUsing Typed DataSets– Strong TypingStrong Typing– XSD Based SchemaXSD Based Schema– Simple to Setup relationships, constraints, etc.Simple to Setup relationships, constraints, etc.– Not very much use if you have amorphous Not very much use if you have amorphous
datadata
http://adoguy.comhttp://adoguy.com
DataSet StructureDataSet Structure
Legend
DataSet
DataRow DataColumn
Constraint DataRelation
DataTable
1
*
1
*
1
*
1
*
1
*
1
*
1
*
.NETClass
Composition
http://adoguy.comhttp://adoguy.com
Typed DataSet StructureTyped DataSet Structure
*
TypedDataSet
Customers
CustomerRow CustomerIDColumn
1
*
1
*
1
*
DataRow
DataSet
DataColumn
Constraint DataRelation
DataTable
1
*
1
*
1
1
*
1
*
1
*
1
*Legend
1*
.NETClass
Composition
Generalization
1
*
http://adoguy.comhttp://adoguy.com
Better Business ObjectsBetter Business Objects
Writing Business Objects normally involve Writing Business Objects normally involve three things:three things:– Relational-to-Hierarchical MappingRelational-to-Hierarchical Mapping– Data AccessData Access– Business RulesBusiness Rules
Traditional Business ObjectsTraditional Business Objects– Set of classesSet of classes– Strongly TypedStrongly Typed
http://adoguy.comhttp://adoguy.com
Better Business ObjectsBetter Business Objects
Writing Business Objects with Typed Writing Business Objects with Typed DataSetsDataSets– Relational-to-Hierarchical Mapping for freeRelational-to-Hierarchical Mapping for free– Data Access is handled by ADO.NETData Access is handled by ADO.NET– Write your business rules and you’re done!Write your business rules and you’re done!
Typed DataSetTyped DataSet– ARE a set of classesARE a set of classes– ARE Strongly TypedARE Strongly Typed
http://adoguy.comhttp://adoguy.com
Better Business ObjectsBetter Business Objects
Business Rules in Typed DataSetsBusiness Rules in Typed DataSets– Event DrivenEvent Driven
Catch OnRowChanging/OnRowChanged EventsCatch OnRowChanging/OnRowChanged Events Handle Business Logic to ensure that rules are Handle Business Logic to ensure that rules are
enforcedenforced
– DerivationDerivation Derive from Typed DataSetDerive from Typed DataSet Problems with Microsoft’s Generated code to allow Problems with Microsoft’s Generated code to allow
thisthis See http://adoguy.com/powertoysSee http://adoguy.com/powertoys for a solutionfor a solution
http://adoguy.comhttp://adoguy.com
Improving Typed DataSetsImproving Typed DataSets
Typed DataSets support AnnotationsTyped DataSets support Annotations– Annotations improve usability of generated Annotations improve usability of generated
codecode– Types can be renamedTypes can be renamed
Instead of CustomerTable, CustomerRow, how Instead of CustomerTable, CustomerRow, how about Customerabout Customer
– Null Behavior can be changesNull Behavior can be changes Throws exception by defaultThrows exception by default Returning empty or null references might be betterReturning empty or null references might be better
http://adoguy.comhttp://adoguy.com
AnnotationsAnnotations
Annotations in the Typed DataSet XSD:Annotations in the Typed DataSet XSD:– typedNametypedName: Specifies the name of an object.: Specifies the name of an object.– typedPluraltypedPlural: Specifies the name of the : Specifies the name of the
collection of objects.collection of objects. typedParent: Specifies the name of the parent typedParent: Specifies the name of the parent
relationship.relationship. typedChildren: Specifies the name of the child typedChildren: Specifies the name of the child
relationship.relationship.
http://adoguy.comhttp://adoguy.com
Annotations - ContinuedAnnotations - Continued
Annotations in the Typed DataSet XSD:Annotations in the Typed DataSet XSD:– nullValuenullValue: Specifies how to handle a DBNull value.: Specifies how to handle a DBNull value.
Replacement Value: The value to return instead of a nullReplacement Value: The value to return instead of a null– codegen:nullValue=""codegen:nullValue=""
_throw_throw: Throws an exception when the value is null: Throws an exception when the value is null– codegen:nullValue=_throwcodegen:nullValue=_throw
nullnull: Returns a null reference.: Returns a null reference.– If a value type is encountered an exception is thrown.If a value type is encountered an exception is thrown.
emptyempty: Returns a reference created with an empty : Returns a reference created with an empty constructor. constructor.
– For strings, it returns String.Empty. For value types, an For strings, it returns String.Empty. For value types, an exception is thrown.exception is thrown.
http://adoguy.comhttp://adoguy.com
Questions?Questions?