Transcript Why ADO.NET - Wildermuth

DataSets Demystified
Who I am
• Shawn Wildermuth ([email protected])
• Senior Consultant with Magenic Technologies
(http://www.magenic.com)
• C# MVP
• INETA Speaker
• Book Author
– “Pragmatic ADO.NET”;
– Co-author of “Programming SQL Server 2005”
• Editor of http://ONDotnet.com
• This Presentation can be found at:
– http://adoguy.com/presentations
Overview
•
•
•
•
•
•
•
What are DataSets
DataAdapters
DataTables
DataSet Schema
Updating DataSets
Typed DataSets
Better Business Objects
What are DataSets?
•
•
•
•
Disconnected set of Database Data?
In-Memory Database (IMDB)?
Relational data with XML serialization?
All three?
DataSet Structure
DataSet
1
*
DataTable
1
1
1
1
*
Constraint
1
*
DataRow
*
*
*
DataRelation
1
Legend
*
DataColumn
.NET
Class
Composition
The DataAdapter
• Link Between the DataSet & the Database
– Used to fill DataSets and update the database
• Contains Four Command Objects
–
–
–
–
SelectCommand
InsertCommand
UpdateCommand
DeleteCommand
DataAdapter and the DataSet
System.Data.SqlClient
System.Data
SqlConnection
SqlDataAdapter
SqlCommand
SqlParameters
SqlParameter
SqlErrors
SqlError
DataSet
SqlDataReader
Hello DataSet!
using System;
using System.Data;
using System.Data.SqlClient;
class HelloADONET
{
static void Main()
{
SqlConnection conn = new SqlConnection("...");
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = conn.CreateCommand();
da.SelectCommand.CommandText = "SELECT * FROM AUTHORS";
DataSet dataSet = new DataSet();
da.Fill(dataSet);
}
}
DataTables
• Composed of
– DataRows: Data
– DataColumns: Schema
DataTable tbl = dataSet.Tables["Customers"];
object name = tbl.Rows["CustomerID"];
string id = tbl.Rows["CompanyName"] as string;
if (tbl.Columns["CustomerID"].DataType ==
typeof(int) ) {
int name = tbl.Rows["CustomerID"] as int;
}
DataRows
• Two possible versions of all data
– RowState.Original
– RowState.Modified
– Changes stay in Modified until AcceptChanges or RejectChanges
called
DataViews
• Window into a single DataTable
• Supports:
– Sorting
– Filtering
DataView view = new DataView(dataSet.Tables[0]);
view.Sort = "CompanyID";
view.RowFilter = "State = 'MA'";
DataSet Schema
• Composed of
–
–
–
–
DataColumns
Constraints
Relationships
Events (Triggers)
DataColumns
• Schema about a column
–
–
–
–
DataType
Maximum Size
IsNullable
Expressions
• Calculated Columns
DataTable tbl = dataSet.Tables["Customers"];
// Expression Column
tbl.Columns.Add("FullName",
typeof(string),
"LastName + ', ' + FirstName");
Constraints
• Rules about DataColumns
– UniqueConstraint (like Primary Keys)
– ForeignKeyConstraint
DataTable tbl = dataSet.Tables["Customers"];
// Adds a unique constraint
tbl.Columns["CustomerID"].Unique = true;
Relationships
• Navigable Link Between Tables
– Adds a ForeignKeyConstraint
DataColumn first =
tblCustomers.Columns["CustomerID"];
DataColumn second =
tblOrders.Columns["CustomerID"];
dataSet.Relations.Add(first, second);
DataRows[] childRows =
tblCustomers.Rows[0].GetChildRows();
int id = childRows[0]["OrderID"] as int;
Updating the Database
• DataAdapter to Update a DataTable
– Update(DataSet) will update the database
• CommandBuilders
– Creates Insert, Update & Delete Commands
– Inefficient, but quick to prototyping
SqlCommandBuilder bldr = new SqlCommandBuilder(dataAdapter);
dataAdapter.Update(dataSet);
The Hard Part
• Disconnected Concurrency
– Optimistic Concurrency Supported by CommandBuilders
– Optimistic Concurrency could be more efficient
– Pessimistic Concurrency can be achieved with Check-out, Checkin
Disconnected Concurrency
• The Hard-Part:
– Optimistic Concurrency
• Supported by CommandBuilders (but inefficient)
– Pessimistic Concurrency
• Can be achieved with custom Check-out/Check-in code
– Destructive Concurrency
• Can only be handled by writing own update queries
Concurrency Methods
• Three Standard Methods
– Timestamp
– Comparison
– Checksum
CommandBuilder Concurrency
• CommandBuilders
– Allows updates and deletes where every field in the database is the
same as when they were retrieved
– Robust
– Inefficient
CommandBuilder Sample
DELETE FROM CUSTOMER WHERE ( (CustomerID = @p3) AND
((FirstName IS NULL AND @p4 IS NULL) OR (FirstName = @p5)) AND
((LastName IS NULL AND @p6 IS NULL) OR (LastName = @p7)) AND
((MiddleName IS NULL AND @p8 IS NULL) OR (MiddleName = @p9)) AND
((Address IS NULL AND @p10 IS NULL) OR (Address = @p11)) AND
((Apartment IS NULL AND @p12 IS NULL) OR (Apartment = @p13)) AND
((City IS NULL AND @p14 IS NULL) OR (City = @p15)) AND
((State IS NULL AND @p16 IS NULL) OR (State = @p17)) AND
((Zip IS NULL AND @p18 IS NULL) OR (Zip = @p19)) AND
((HomePhone IS NULL AND @p20 IS NULL) OR (HomePhone = @p21)) AND
((BusinessPhone IS NULL AND @p22 IS NULL) OR (BusinessPhone =
@p23)) AND
((DOB IS NULL AND @p24 IS NULL) OR (DOB = @p25)) AND
((Discount IS NULL AND @p26 IS NULL) OR (Discount = @p27)) AND
((CheckedOut IS NULL AND @p28 IS NULL) OR (CheckedOut = @p29)) )
Custom Concurrency
• For SQL Server
– To make it more efficient: use Timestamps
• DELETE FROM CUSTOMER
WHERE (CustomerID = @p3) AND (Stamp = @p4)
• DataAdapter.DeleteCommand.CommandText!
Concurrency Violations
• DataAdapters throw DBConcurrencyException
– This is thrown whenever the affected rows of an update is zero.
– False positives are possible.
• DataAdapter.ContinueUpdateOnError
– Each violated row will have errors associated with it.
– DataRow.HasErrors will tell you which rows are problematic
Handling Concurrency Violations
• Three options:
– Tell the user the data is stale, and offer to ‘freshen’ it and lose their
changes
– Ask the user if (s)he wants to overwrite
• needs to be done without a CommandBuilder
– Field-level collision detection
• Allow field to be updated if it hasn’t changed
Typed DataSets
• Strong Typing
• XSD Based Schema
• Simple to setup schema
– Relationships
– Constraints
– Keys
• Not good for amorphous data
Typed DataSet Structure
TypedDataSet
1
DataSet
1
*
1
*
Customers
DataTable
1
1
1
1
1
1
*
*
*
*
Legend
*
CustomerRow
Constraint
CustomerIDColumn
1
DataRelation
.NET
Class
1
Composition 1*
*
DataRow
*
*
*
DataColumn
Generalization
Type DataSet Demo
Improving Typed DataSets
• Typed DataSets support Annotations
– Annotations improve usability of generated code
– Types can be renamed
• Instead of CustomerTable, CustomerRow, how about Customer
– Null Behavior can be changes
• Throws exception by default
• Returning empty or null references might be better
Annotations
• Annotations in the Typed DataSet XSD:
– typedName: Specifies the name of an object.
– typedPlural: Specifies the name of the collection of objects.
• typedParent: Specifies the name of the parent relationship.
• typedChildren: Specifies the name of the child relationship.
Annotations (2)
• Annotations in the Typed DataSet XSD:
– nullValue: Specifies how to handle a DBNull value.
• Replacement Value: The value to return instead of a null
– codegen:nullValue=""
• _throw: Throws an exception when the value is null
– codegen:nullValue=_throw
• null: Returns a null reference.
– If a value type is encountered an exception is thrown.
• empty: Returns a reference created with an empty constructor.
– For strings, it returns String.Empty. For value types, an exception is
thrown.
Practical Applications of DataSets
• Stop Writing Business Objects!
–
–
–
–
–
Derive from Typed DataSets
Let ADO.NET do the Database work
Write Just Your Business Logic
Deal with data as Relationally
or Hierarchically by using XmlDataDocument
Better Business Objects
• Using Typed DataSets
–
–
–
–
Strong Typing
XSD Based Schema
Simple to Setup relationships, constraints, etc.
Not very much use if you have amorphous data
Better Business Objects(2)
• Writing Business Objects normally involve three things:
– Relational-to-Hierarchical Mapping
– Data Access
– Business Rules
• Traditional Business Objects
– Set of classes
– Strongly Typed
Better Business Objects (3)
• Writing Business Objects with Typed DataSets
– Relational-to-Hierarchical Mapping for free
– Data Access is handled by ADO.NET
– Write your business rules and you’re done!
• Typed DataSet
– ARE a set of classes
– ARE Strongly Typed
Better Business Objects (4)
• Business Rules in Typed DataSets
– Event Driven
• Catch OnRowChanging/OnRowChanged Events
• Handle Business Logic to ensure that rules are enforced
– Derivation
• Derive from Typed DataSet
• Problems with Microsoft’s Generated code to allow this
• See http://adoguy.com/book/AGDataSetGenerator.aspx for a solution
DataSets in Web Services
• Can you use DataSets in Web Services?
– At first glance, the answer is no
• DataSets serialize to DiffGrams, DataSets
• DiffGrams are not platform agnostic
• Do not communicate the full schema
– Would you want to use DataSets anyway?
• Great container for Structured XML
• Database integration
• Easy to intermingle database/XML data
– When *not* to use DataSets
• When creating XML Documents from non-database sources
DataSets in Web Services (2)
• Possible Solutions?
– DataSets can hold XML, so you can:
• return myDataSet.GetXml()
– How about XmlDataDocument?
• return new XmlDataDocument(myDataSet)
Web Services and Schema
• DataSet schema is XSD!
– When using DataSets, DocLiteral is your friend
• DataSets can define and be the holder for the documents that Web
Services throw around
– DataSet schema can define our WSDL Types
• Yassar Shohoud’s book shows you how
• “Real World XML Web Services”
Questions?