Database Reference
In-Depth Information
newRow["ModifiedDate"] = "2012-04-29";
dt.Rows.Add(newRow);
Obviously, using command builders is preferable to manually coding SQL; however, remember that
they work only on single tables and that the underlying database table must have a primary or unique
key. Also, the data adapter
SelectCommand
property must have a query that includes the key columns.
Note
Though all five of the data providers in the .NET Framework Class Library have command builder classes,
no class or interface exists in the
System.Data
namespace that defines them. So, if you want to learn more about
command builders, the best place to start is the description for the builder in which you're interested. The
System.Data.DataSet
class and the
System.Data.IDataAdapter
interface define the underlying components
that command builders interact with, and their documentation provides the informal specification for the
constraints on command builders.
Concurrency
You've seen that updating a database with data sets and data adapters is relatively straightforward.
However, we've oversimplified things; you've been assuming that no other changes have been made to
the database while you've been working with disconnected data sets.
Imagine two separate users trying to make conflicting changes to the same row in a data set and
then trying to propagate these changes to the database. What happens? How does the database resolve
the conflicts? Which row gets updated first, second, or at all? The answer is unclear. As with so many
real-world database issues, it all depends on a variety of factors. However, ADO.NET provides a
fundamental level of concurrency control that's designed to prevent update anomalies. The details are
beyond the scope of this topic, but the following is a good conceptual start.
Basically, a data set marks all added, modified, and deleted rows. If a row is propagated to the
database but has been modified by someone else since the data set was filled, the data manipulation
operation for the row is ignored. This technique is known as
optimistic concurrency
and is essentially the
job of the data adapter. When the
Update
method is called, the data adapter attempts to reconcile all
changes. This works well in an environment where users seldom contend for the same data.
This type of concurrency is different from what's known as
pessimistic concurrency,
which
locks
rows upon modification (or sometimes even on retrieval) to avoid conflicts. Most database managers use
some form of locking to guarantee data integrity.
Disconnected processing with optimistic concurrency is essential to successful multitier systems.
How to employ it most effectively given the pessimistic concurrency of DBMSs is a thorny problem.
Don't worry about it now, but keep in mind that many issues exist, and the more complex your
application, the more likely you'll have to become an expert in concurrency.
Using Data Sets and XML
XML is the fundamental medium for data transfer in .NET. In fact, XML is a major foundation for
ADO.NET. Datasets organize data internally in XML format and have a variety of methods for reading
and writing in XML. For example:
