Microsoft Ado.net (core Reference)

The definitive guide to using Microsoft Visual C#™ .NET to develop stand-alone applications for Microsoft Windows and Web-enabled Microsoft .NET applications

Microsoft ActiveX® Data Objects .NET (ADO.NET) is a set of libraries included with the Microsoft .NET Framework that helps you communicate with data from .NET-based applications—including XML data. Discover how to access, sort, and manipulate data in Web-enabled enterprise applications with this book. You’ll get expert guidance and insider tips from a leading database and ADO authority about how to write, test, and debug database code with the tools and wizards in Visual Studio .NET. You’ll also learn about the improved ADO.NET object model, its XML features for Web extensibility, its enhanced integration with Microsoft SQL Server™ 2000, and more. Topics covered in this guide include:

• Overview of ADO.NET
• Building applications with the Data Form Wizard
• Connecting to your database
• Querying your database
• Working with DataAdapters
• Storing data in DataSets
• Filtering and searching data in DataSets
• Working with relational data
• Submitting updates to your database
• Advanced updating scenarios
• Using strongly typed DataSets
• Interacting with XML data
• Building effective Windows® database applications
• Building effective Web database applications

INCLUDED ON CD-ROM:
• A fully searchable electronic copy of the book
• Sample code in C# and Microsoft Visual Basic® .NET (also available from the link on this page)

Table of Contents

Chapter 10: Submitting Updates to Your Database

• A History Lesson
• • Benefits of Submitting Updates Using ADO Recordset Objects
  • Drawbacks of Submitting Updates Using ADO Recordset Objects
• Using ADO.NET Command Objects to Submit Updates
• Using ADO.NET DataAdapter Objects to Submit Updates
• • Manually Configuring Your DataAdapter Objects
  • Introducing Bound Parameters
  • Using Stored Procedures to Submit Updates
  • Supplying Your Own Updating Logic
• Using the CommandBuilder Object to Generate Updating Logic
• • How the CommandBuilder Generates Updating Logic
  • Benefits and Drawbacks of Using the CommandBuilder
• Using the Data Adapter Configuration Wizard to Generate Updating Logic
• • Examining the Structure of the DataAdapter
  • Options for Building Updating Logic
  • Using Stored Procedures to Submit Updates
  • Benefits and Drawbacks of Using the Wizard
• Other Updating Concerns
• • Optimistic Concurrency Options
  • Working with Null Values
• Submitting Updates in Transactions
• • Using the TableMappings Collection
  • The Best Way to Update
• OleDbCommandBuilder Object Reference
• • Properties of the OleDbCommandBuilder Object
  • Methods of the OleDbCommandBuilder Object
• Questions That Should Be Asked More Frequently

10   Submitting Updates to Your Database

Stevie Wonder

Although Stevie Wonder probably wasn't talking about submitting updates, the quote is still relevant to the topic. ADO.NET gives database programmers unprecedented power and control over submitting updates. However, based on the questions I've handled personally on internal and external newsgroups and at conferences during the .NET beta, I'd say that few developers really understand how to effectively wield this control and power.

So many of the ADO.NET code snippets I've seen rely on the CommandBuilder object to generate updating logic. Sometimes the code snippet comes with a warning that says you should generate your own updating logic instead, but those comments rarely explain why or how this is done.

How many times have you asked someone how they got their code to work only to have them shrug, smile, and say, "It just works"? That's the sort of superstition I want to dispel in this chapter and the following chapter.

The more you understand how you can use ADO.NET to submit updates, the more comfortable you'll become generating your own updating logic and/or submitting updates via stored procedures. This chapter will help you understand how to use a DataAdapter to submit the pending changes from your DataSet to your database. Along the way, you'll also learn how and when to use tools to save time without sacrificing performance or control.

If you've been reading the chapters of this book in sequence, you should already be comfortable creating untyped and strongly typed DataSet objects to store the data returned by DataAdapter objects. You should also be comfortable modifying the contents of a DataSet. This chapter will help you understand the basics of using DataAdapter objects to submit the changes stored in your DataSet to your database.

Let's look at an order from the sample Northwind database. Figure 10-1 shows the query issued in SQL Server Query Analyzer to retrieve information for the order. For the sake of argument, let's say the customer calls and wants to change the order. Tofu isn't selling, but bottles of hot sauce are flying off the shelves and people keep asking for chai tea.

Click to view graphic

Figure 10-1 Contents of an order in the Northwind database.

Thanks to the knowledge you gained in Chapter 5, you know how to fetch the results of a query into a DataSet. You can use that knowledge to build an application that allows the user to fetch the customer's order into a DataSet. Based on what you learned in Chapter 5, you can enable your application to modify the data in the DataSet per the customer's instructions. However, as I've noted, changing the contents of a DataSet doesn't change the corresponding rows in the database.

In Chapter 5, you also learned that the DataAdapter exposes an Update method that you can use to submit pending changes to your database. So, you can build an application that uses code such as this to submit the changes to the order:

Visual Basic .NET

'Retrieve the contents of the order into a DataTable.

Dim strConn, strSQL As String

strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _

          "Initial Catalog=Northwind;Trusted_Connection=Yes;"

strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " & _

         "FROM [Order Details] WHERE OrderID = 10503 " & _

         "ORDER BY ProductID"

Dim da As New OleDbDataAdapter(strSQL, strConn)

Dim tbl As New DataTable("Order Details")

da.Fill(tbl)

 

'Modify the contents of the order.

tbl.Rows(0).Delete()

tbl.Rows(1)("Quantity") = CShort(tbl.Rows(1)("Quantity" )) * 2

tbl.Rows.Add(New Object() {10503, 1, 24, 18})

 

'Submit the pending changes.

Try

    da.Update(tbl)

    Console.WriteLine("Successfully submitted new changes")

Catch ex As Exception

    Console.WriteLine("Call to DataAdapter.Update " & _

                      "threw exception:" & vbCrLf & ex.Message)

End Try

Visual C# .NET

//Retrieve the contents of the order into a DataTable.

string strConn, strSQL;

strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +

          "Initial Catalog=Northwind;Trusted_Connection=Yes;";

strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " +

         "FROM [Order Details] WHERE OrderID = 10503 " +

         "ORDER BY ProductID";

OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn);

DataTable tbl = new DataTable("Order Details");

da.Fill(tbl);

 

//Modify the contents of the order.

tbl.Rows[0].Delete();

tbl.Rows[1]["Quantity"] = (short) (tbl.Rows[1]["Quantity"]) * 2;

tbl.Rows.Add(new object[] {10503, 1, 24, 18});

 

//Submit the pending changes.

try

{

    da.Update(tbl);

    Console.WriteLine("Successfully submitted new changes");

}

catch (Exception ex)

{

    Console.WriteLine("Call to DataAdapter.Update threw exception:\n"

                      + ex.Message);

}

This code will successfully compile, but it will not successfully submit the changes to the order to your database. Instead, you'll receive an exception that says, "Update requires a valid DeleteCommand when passed DataRow collection with deleted rows."

Exceptions such as these confused many developers during the Microsoft .NET Framework beta. Previous data access technologies such as ADO include features that let you submit changes automatically. With ADO.NET, you can submit changes using the DataAdapter object, but the DataAdapter does not automatically include the logic required to submit updates.

So how do you add the necessary updating logic to your ADO.NET DataAdapter? You have three basic options: you can write your own code, ask ADO.NET to generate the updating logic for you, or rely on a code generation tool such as the Visual Studio .NET Data Adapter Configuration Wizard.

This chapter will cover all three of these options and explain the pros and cons of each.

Index

Send feedback about this index to mspindex@microsoft.com.

Symbols
* (asterisk), 341
@ (at sign), 111
\ (backslash), 30, 342, 343
[] (delimiters), 342
% (percent sign), 122, 341
? (question mark), 111
' (single quote), 341-42
_ (underscore), 29

A
AcceptChanges method
   DataSet object and, 263-64, 272-73, 287-88
   Merge method and, 496
   submitting updates and, 408, 414, 473, 496
   XML and, 550, 554
AcceptChangesDuringFill property, 186, 190-91
AcceptRejectRule property, 294, 295, 327
Access (Microsoft), 50, 180
   autoincrement values and, 474
   batch queries and, 470
   BLOB data and, 599
   Connection object and, 73
   databases, specifying, on the Connection tab, 31-32
   paging features and, 628-29
   SELECT@@IDENTITY query and, 478
   submitting updates and, 454, 463, 470, 472, 478, 514
   transactions and, 514
   Web applications and, 609-10, 628-29
accounts, creating, 30
action queries, 98-101, 106-9, 242-43
   executing, 99-101
   XML and, 553
Active Server Pages (Microsoft). See ASP
ActiveX Data Objects (Microsoft). See ADO
Add button, 38, 571-72, 575
Add Dataset dialog box, 252-54
Add method, 585, 620-21
   DataSet object and, 219, 221, 223-24, 236, 281
   Parameter object and, 141
Add New Item button, 27
Add New Item dialog box, 27
Add New Project Item dialog box, 250-51
Add Reference dialog box, 58-59
Add References dialog box, 51, 526
Add Table dialog box, 120, 181
Added constant, 243, 499-500, 509
AddHandler statement, 92-93
AddNew method, 355, 361, 571
AddRange method, 173, 188
ADO (ActiveX Data Objects), 191, 302-4, 362
   Connection object and, 57, 58, 84
   cursor engine, 404, 406-7, 434, 444-45
   database queries and, 101-3, 106, 109, 139
   DataSet object and, 210, 211, 383
   disconnected data and, 158-59
   introduction of, 4
   relational data access and, 158-60, 191, 332
   submitting updates and, 404-7, 419, 430-31, 434, 444-45, 465
   XML and, 524
Advanced Options button, 584
aliases, 56, 57, 162
Allow Saving Password check box, 30, 74
AllowCustomPaging property, 625-27
AllowDBNull property, 15, 177, 215, 218, 222-21, 278-79
AllowDelete property, 357, 358
AllowEdit property, 357, 358
AllowNew property, 357, 358
AllowPaging property, 624, 625
annotations, 398-99
ANSI (American National Standards Institute), 221, 446
AppendChunk method, 596
Application object, 620-21, 623
ApplyDefaultSort property, 357, 358
arrays
   creating, 138
   relational data access and, 306, 331
ASP (Active Server Pages), 524
asterisk (*), 341
at sign (@), 111
attributes
   choosing, 531
   names of, 530
   XML and, 530-31
authentication
   Forms, 609
   integrated security and, 74-75
   Passport, 609
   Windows, 609
AutoComplete attribute, 518
AutoIncrement property, 15, 177
   DataSet object and, 218, 226-30, 278, 279-80
   generating dummy values and, 481
autoincrement values, 226-30, 564. See also AutoIncrement property
   cascading, 489-90
   Merge method and, 497-503
   pseudokeys and, 500-502
   relational data and, 488-90
   retrieving, 474-82
   SQL Server and, 474, 475-78
   submitting hierarchical changes with, 488
AutoIncrementSeed property, 226-30, 278-80, 564, 577, 588
AutoIncrementSql application, 481
AutoIncrementStep property, 278-80, 488, 564, 577
Available Items list, 32

B
backslash (\), 30, 342, 343
bandwidth, saving, 491-503
batch queries
   DataAdapter object and, 174-75
   retrieving data with, after updating, 469-71
   row-returning, 470
   XML and, 553-54
BeginEdit method, 17, 238, 240, 574
   DataRowView object and, 355, 367
   DataSet object and, 245-46, 263-65, 272-73, 286, 288-89
BeginInit method, 361-62
BeginLoadData method, 272, 274
BeginTransaction method, 11, 67-68, 84-85, 117
Bernstein, Philip A., 512
bin directory, 378, 514
Binary Large Objects. See BLOB data
binding data, 562-88, 601-2, 610-15, 634-36
   binding forms to the same data source with, 581-83
   controlling the format of bound data, 585-87
   DataGrid controls and, 613-15
   DataSet object and, 390-91, 601-2
   Eval method and, 610-13
   statelessness and, 615
   TextBox controls and, 611-13
Binding object, 585
BindingContext property, 568
black-box technology, 26, 407, 419
BLOB (Binary Large Object) data, 443-44, 461-63, 595-601
   DataReader object and, 597-99
   DataSet object and, 596-97
   fetching, delaying, 595
   sample application for, 600-601
bookmark features, 606
Boolean data type, 102-3, 135, 138, 191, 575
Both constant, 129, 470, 471
Broken constant, 83
browsers, 526-28, 555-56
Building Web Solutions with ASP.NET and ADO.NET (Esposito), 605
Button controls, 606

C
C++ (high-level language), 221
C# (high-level language), 528
Cache object, 620
caching
   clients and, 616-19
   DataSet object and, 207, 242-43
   output, 621, 623
   submitting updates and, 404
   Web applications and, 614-23, 636
CALL syntax, 128
CallSlowQuery method, 152
Cancel button, 573-75, 632
Cancel method, 129, 130-31, 152
CancelEdit method, 17, 238, 245-46, 286-89, 355, 367
CancelQuery method, 152
Caption property, 278, 280
cascading changes, 304, 323-24
CaseSensitive property, 258, 259, 262, 267, 268
ChangeDatabase method, 84, 85
char data type, 144
child data, displaying, 57-59, 576-80
ChildColumns property, 329
ChildKeyConstraint property, 329
ChildRelations collection, 269
ChildRelations property, 267, 268-69
ChildTable property, 329
Choose A Data Connection page, 29
Choose A Query Type screen, 437-39
class files. See also classes
   generating, 376, 378
   locating, 381
classes. See also class files; classes (listed by name)
   base, 6
   collections of, .NET data providers as, 6-7
   initialization code for, 390
   libraries for, registering, 514
   separate, reasons for, 8-9
   strongly typed, 577-78
   transactions and, 518
   valid names of, 29
classes (listed by name). See also classes
   clsSlowQuery, 152
   Command, 6
   Connection, 6, 65
   CurrencyManager, 568
   CustomersDataTable, 381
   DataAdapter, 39
   DataColumn, 15-16
   DataColumnMappingCollection, 163
   DataReader, 6
   DataRowCollection, 241, 338
   DataSet, 21-23, 202, 209, 394-96, 398, 564, 577-78
   DataTable, 211
   DBNull, 240
   Decimal, 585
   ForeignKeyConstraint, 216
   HttpSessionState, 619
   InternetExplorer, 528
   MarshalByValueComponent, 297
   OleDbConnection, 64, 90
   OrdersDataTable, 381
   Page, 617, 619
   Parameter, 6
   PrimaryKey, 216
   ServicedComponent, 514
   String, 342
   Transaction, 6
   UniqueConstraint, 216-17
   xsdChapter13, 564
Clear method, 263, 265, 272, 274
ClearErrors method, 287, 289
Click event, 37, 39
   Web applications and, 606
   Windows-based applications and, 567, 572-73, 580
clients. See also client/server systems
   caching and, 616-19
   SQL and, 558
   XML and, 558
client/server systems, 50, 206, 606-7. See also clients; Web servers
ClientSideXml property, 558
Clone method, 220, 263, 265, 273, 274
Close method, 64, 84, 86, 90, 135, 138-39
   DataAdapter objects and, 170
   Windows-based applications and, 567-68
CloseConnection constant, 132
Closed constant, 83
clsSlowQuery class, 152
cmd.exe, 378
Collection Editor, 254-57
Collection method, 64
ColumnChanged event, 238, 277
ColumnChanging event, 238, 277
ColumnMapping property, 187, 278, 280-81, 531
ColumnMappings collection, 13
ColumnName property, 252, 269, 278, 280-81, 530
columns
   adding, to DataTable objects, 220
   defining default values for, 464-65
   expression-based, 230-32
   including all, in the WHERE clause, 442-46
   primary key, 442, 444-46
   searches based on, 352-53, 354
   selecting, 33-34, 184
   setting the value of, 237
Columns collection, 14
Columns property, 14, 210, 267, 269, 293-95
COM (Component Object Model). See also COM+
   Connection object and, 51, 54, 58
   interoperability, 58, 528
   libraries, 51
COM+
   Data Form Wizard and, 42
   context information, 517
   transaction processing and, 512
COM tab, 58, 526
ComboBox controls, 583-85
Command class, 6
Command object, 10-11, 65-66
   asynchronous queries and, 151-52
   Cancel method and, 130
   creating, 97-120, 124-25
   DataAdapter object and, 12-13, 158-59, 161-62, 164, 176, 459
   database queries and, 110-14, 117-20, 124-25, 129-32, 151-52
   disconnected data and, 158-59
   ExecuteNonQuery method and, 131-32
   extensibility and, 9
   NOCOUNT setting and, 483, 485
   null values and, 446
   parameterized, 12
   retrieving rows and, 176
   run time usage of, 122-23
   submitting updates and, 407-18, 425, 438-39, 446-47, 470- 72, 483, 485
   transactions and, 129, 447
   Visual Studio .NET and, 118-20
   XML and, 550
Command windows, creating, 378
CommandBehavior enumeration, 131-32, 612
CommandBuilder object, 481, 593
   benefits and drawbacks of, 434
   Refresh method and, 150-51
   reliance upon, avoiding, 455
   submitting updates and, 401, 431-34, 441, 443, 449, 455, 463-64
   transactions and, 449
commands
   child, 162
   creating, 65-67, 118-20
CommandText property, 11, 126, 182
   BLOB data and, 461-62
   database queries and, 99, 115, 119-20, 124, 127-28, 149
   null values and, 446
   RefreshSchema method and, 458
   sequences and, 479
   submitting updates and, 435-36, 446, 458, 461-62, 469-71, 475, 479
   XML and, 542, 545-46, 548, 551
CommandTimeout property, 126-27, 134
CommandType property, 11, 114, 115, 124-28, 149
Commit method, 118
CompanyName field, 34
ComplexHierarchy application, 502-3
Component Object Model. See COM
Component Services
   benefits of using, 519
   distributed transactions and, 513
   moving business objects into, 515-16
components. See also COM
   building, 514-18
   for distributed transactions, 514-18
Components Tray, 43-44, 178, 182, 185
Compute method, 273, 274-75
concurrency, optimistic, 495, 636
   failed updates and, 503
   GetChanges method and, 491
   Merge method and, 495
   options, 441-46
ConflictAdapter object, 506-8
ConflictDataSet object, 508-9
Conflicts application, 509-10
Connect Timeout attribute, 81
Connecting constant, 83
Connection class, 6, 65
Connection object, 10, 593-95, 607-10
   adding, to applications, 75-77
   closing, 169-70
   connection pooling and, 61-65
   connection strings and, 55-60
   creating, 54-55
   creating other objects with, 65-68
   DataAdapter object and, 165-66, 169-70
   database queries and, 97-98, 109-10, 119-20, 124, 138
   defined, 49-53
   opening, 169-70
   transactions and, 11, 67-68, 518
connection pooling, 90, 94-96, 594-95, 609
   defined, 61-63
   enabling, 63-64
   Prepare method and, 134
Connection property, 65, 86, 99, 119-20, 124, 126
connection strings, 55-60. See also ConnectionString property
Connection tab, 29-31, 74
connections. See also Connection object; connection pooling
   closing, 60-61, 86, 169-70
   creating, with the Toolbox, 76-77
   destroying, 65
   opening, 60-61, 89-90, 169-70
   Server Explorer and, 70-75
   specifying, 72-78, 119-20, 178-79
   state constants for, 83-84
   strategies for, 593-95
   trusted, 607-9
   Web applications and, 607-10
ConnectionState enumeration, 83-84
ConnectionString property, 6, 54, 76-77, 80-81, 89
ConnectionTimeout property, 80, 81
Console window, 525, 526
constants (listed by name)
   Added, 243, 499-500, 509
   Both, 129, 470, 471
   Broken, 83
   CloseConnection, 132
   Closed, 83
   Connecting, 83
   Current, 244, 245, 246
   Default, 244, 246
   Deleted, 243
   Detached, 243
   Disabled, 515
   Executing, 83
   Fetching, 83
   FirstReturnedRecord, 129, 470
   KeyInfo, 132-33
   Modified, 243, 496
   None, 129, 470
   NonSupported, 515
   Open, 83
   Original, 244
   OutputParameters, 129, 470, 471
   Proposed, 244, 245
   Required, 516
   RequiresNew, 516
   SchemaOnly, 132-33
   SequentialAccess, 132, 133
   SingleResult, 132, 133
   SingleRow, 132, 133, 612
   StoredProcedure, 127, 128
   Supported, 516
   TableDirect, 127-28
   Text, 127
   Unchanged, 243
Constraint object, 16
ConstraintCollection object, 269
ConstraintException, 216
ConstraintName property, 269, 293, 294, 296
constraints
   adding, 223-25
   creating, 311-13
   creating objects without, 315, 332
   DataTable object and, 201-2
   defined, 215
   existing, utilizing, 313-14
   foreign key, 216-17, 223-25, 257-58, 311-16
   primary key, 216, 223-25
   referential integrity, 265
   relational data access and, 307, 317
   unique, 216-17, 223-25, 257, 311-17, 331
   Visual Studio .NET and, 256-57
Constraints collection, 216-17, 223-25, 256
Constraints property, 14, 256, 267, 269
constructors
   Connection object and, 55-56
   DataRelation object and, 305-7
   DataView object and, 348-49
   relational data access and, 305-7, 328
   signatures for, 307, 349
   submitting updates and, 431
ContactTitle field, 34
ContextUtil object, 516-17
ContinueUpdateOnError property, 186, 191, 261, 270, 504
Controls. See also DataGrid controls; TextBox controls
   binding single-value, 610
   Button, 606
   ComboBox, 583-85
   Label, 567
   PictureBox, 599
cookies, 616-17
Cookies collection, 616-17
Copy method, 220, 263, 265, 273, 274
CopyTo method, 361, 362
Count property, 351, 358-59, 568, 634
Create New Stored Procedures option, 439-40
CreateChildView method, 367-68, 636
CreateCommand method, 65-66, 84, 86-87, 97-98
CreateParameter method, 129, 141, 547
CreateProcessingInstruction method, 557
CultureInfo object, 262
CurrencyManager class, 568
CurrencyManager object, 568-88, 600
   adding buttons and, 568-75
   improving the user interface and, 583-87
   submitting changes and, 572-73
   viewing child data and, 576-80
Current constant, 244, 245, 246
Current property, 364, 582
CurrentPageIndex property, 625
cursor engine, 404, 406-7, 434, 444-45
cursors
   firehose, 101-2, 139
   server-side, 23
CustomerID field, 33-34, 38-39, 101, 144
Customers object, 381
Customers table, 611, 628-29
   database queries and, 101, 121-22, 144
   DataSet object and, 214-16, 232-35, 376, 381, 387-88
   DataTable object and, 232-35
   hierarchical data and, 387-88
   relational data access and, 166, 300-303, 314, 333
   submitting updates and, 444, 446, 488
CustomersDataTable class, 381
CustOrderHist procedure, 124
cylinders, representation of databases as, 72

D
DAO (Data Access Objects), 109-10, 139
   Connection object and, 84
   CopyTo method and, 362
   DataAdapter object and, 158-60
   Database object and, 10
   DataSet object and, 210, 211
   disconnected data and, 158-59
   introduction of, 4
   QueryDef object and, 10, 12
Data Adapter Configuration Wizard, 178-83, 195-96, 404, 434-41, 459-60
   benefits and drawbacks of, 441, 454
   dummy values and, 481
   join queries and, 459-60
   optimistic concurrency and, 442, 443
   primary key columns and, 442, 444
   submitting changes and, 572-73
   query syntax and, 577
   WHERE clause and, 442-43, 447
data binding, 562-88, 601-2, 610-15, 634-36
   binding forms to the same data source with, 581-83
   controlling the format of bound data, 585-87
   DataGrid controls and, 613-15
   DataSet object and, 390-91, 601-2
   Eval method and, 610-13
   statelessness and, 615
   TextBox controls and, 611-13
data definition language (DDL) queries, 99
Data Form Wizard
   building data-bound forms with, 26-36
   choosing connections with, 29-32
   code generated by, viewing, 37-38
   component tray and, 43-44
   creating relationships with, 32-33
   DataSet object and, 32-33, 206-7, 232-35
   described, 25-26
   displaying data with, 37-38
   isolating modified rows with, 41-42
   reintegrating changes with, 42-43
   selecting columns with, 33-34
   selecting display styles with, 34-35
   selecting tables with, 32
   as a starting point, 45
   submitting changes with, 39-43
Data Link dialog box, 58-60, 72-74
Data Link Properties dialog box, 29-31, 120
data links, 29-31, 58-60, 72-74
data manipulation language. See DML
data providers
   adding connections and, 73-74
   coverage of, in this book, 10
   described, 6-8
   namespaces for, 6
   OLE DB, 6, 8-10, 463, 556, 595
   SQL Client, 6, 8-9, 595
   SQL XML, 541-54, 558
Data Source attribute, 56, 82
Data tab, 44, 76, 118, 178, 379, 563
data types. See also data types (listed by name)
   DataColumn object and, 220-21
   database queries and, 104, 144
   submitting updates and, 444
data types (listed by name). See also data types
   Boolean, 102-3, 135, 138, 191, 575
   char, 144
   Decimal, 144, 578, 585
   Decimal double, 104
   integer, 104
   numeric, 144
   Object, 104, 133, 143, 275, 282, 364, 611
   string, 104, 221
   timestamp, 444
   varchar, 144
   wchar, 144
DataAdapter class, 39
DataAdapter object
   anatomy of, 161-63
   autoincrement values and, 475-76
   batch queries and, 174-75
   BLOB data and, 461-63
   caching changes and, 207
   child commands and, 162
   ComplexHierarchy application and, 502-3
   component tray and, 44
   constructors, 165-66
   creating, 164-68, 177-84, 563-64
   Data Form Wizard and, 38, 39, 42, 44
   database queries and, 126-27, 129, 137
   DataSet object and, 159, 206-11, 217-18, 247-51, 389-90, 458-59
   described, 12-13, 158-64
   design of, to work with disconnected data, 158-59
   dragging and dropping, from the Toolbox, 178
   events, 198-201
   examining the structure of, 435-36
   failed updates and, 503-4
   fetching schema information and, 176-77, 218
   generating dummy values and, 481
   GetChanges method and, 491
   GetValues method and, 137
   hierarchical changes with, 486-87
   manual configuration of, 419
   methods, 191-98
   NOCOUNT setting and, 482, 483-85
   null values and, 446, 464-65
   paging features and, 625-26
   parameterized queries and, 445
   previewing the results of, 184-85
   properties, 185-91, 419
   reference, 185-202
   reintegrating changes and, 42
   relational data access and, 325
   retrieving data with, 157
   retrieving query results with, 166-72
   retrieving rows and, 175-76
   RowUpdating event and, 446
   sequences and, 479, 480
   statelessness and, 606-7
   submitting updates and, 159-61, 402, 418-31, 441, 458-59, 468-91, 502-9
   TableMappings collection and, 172-73, 451-54
   transactions and, 447-51
   uniqueness of, 158-61
   UpdatedRowSource property and, 129
   Web applications and, 606-7, 611-12, 625-26, 632-33
   Windows-based applications and, 563-67, 571-72, 576-77, 583-85, 589-95
   XML and, 532
Database property, 80, 81-82
DataBind method, 632
DataBinder object, 610, 611-13
DataColumn class, 15-16
DataColumn object, 565, 578
   accessing column contents with, 392-94
   annotations and, 398-99
   autoincrement values and, 226-30, 488-90
   creating, 167-69, 252
   data types for, 220-21
   DataSet object and, 210-16, 220-21, 249, 254-58
   described, 14-15
   expression-based, 230-32, 320-23
   Fill method and, 192
   locating rows and, 339
   making structural changes to, 394
   Parameter object and, 144, 420-21
   properties, 215-16, 226-30, 278-85
   relational data access and, 305, 320-23
   sequences and, 481
   submitting updates and, 465
   XML and, 530, 531, 557-58
DataColumnCollection object, 269, 281
DataColumnMapping object, 163, 188
DataColumnMappingCollection class, 163
DataColumnMappingCollection object, 163, 173, 189
DataGrid controls
   data binding and, 581-83, 613-15
   displaying child data with, 578-79
   editing events for, 631-32
   failed updates and, 504-5
   paging features and, 624-27, 629
   simplifying data editing with, 630-31
   statelessness and, 606-7
   Web applications and, 606-7, 610, 613-15, 624-31
   Windows-based applications and, 576, 578-79, 581-84
DataMember property, 578-79, 610, 614, 624-31
DataReader class, 6
DataReader object
   BLOB data and, 597-99
   Cancel method and, 130
   Close method and, 138-39
   current row concept and, 211
   data binding and, 614-15
   DataAdapter object and, 161
   database queries and, 108-9, 112, 122, 130-31, 136-39, 148-49
   DataSet object and, 205, 207, 209-11, 213
   described, 11
   examining query results with, 101-10
   ExecuteNonQuery method and, 131
   GetName method and, 139
   GetValues method and, 137
   Read method and, 136
   read-only data in, 205, 207
   Recordset object and, 23
   return parameters and, 148-49
   splitting query results with, 334
   TextBox controls and, 612-13
   Web applications and, 612-15, 625-27, 634-35
DataRelation object, 19-20, 390-91, 576, 578, 636. See also relational data access
   adding, 318, 325-28
   advantages and disadvantages of, 304
   autoincrement values and, 489, 490
   cascading changes with, 38-39
   creating, 305-7, 325-28, 332
   DataColumn object and, 320-23
   DataSet object and, 327-28, 377, 380, 387-88
   described, 303-304
   expression-based objects and, 320-23
   GetChanges method and, 491
   hierarchical data and, 387-88
   join queries and, 300-301
   locating related data and, 308-11
   properties, 328-31
   simulated joins and, 347
   submitting updates and, 462
   untyped objects and, 327-28
   validating data with, 311-15
   XML and, 531
DataRelationCollection object, 262
DataRelationship object, 315-17
DataRow object, 14, 16-18, 211-14, 237-46
   adding, 235-37
   adding rows and, 382-84
   class files and, 381
   described, 16-18
   editing rows and, 385-86
   examining data stored in, 212-13
   examining pending changes in, 244-46
   hierarchical data and, 387-88
   methods, 286-93, 308-10, 493-96
   modifying rows and, 237-40
   null values and, 386-87
   properties, 285-86
   removing, 241-42
   searches and, 338, 344
   sequences and, 479, 480
   submitting updates and, 408, 414, 418-19, 468-70, 473, 486-88, 493-96, 503, 506-9
   Web applications and, 632, 636
   Windows-based applications and, 562, 574, 590-91, 596
   XML and, 531, 536, 550, 551
DataRowCollection class, 241, 338
DataRowCollection object, 211, 236, 352, 354, 384
DataRowState enumeration, 243, 244
DataRowVersion enumeration, 246
DataRowView object, 227-30, 353, 582
   GetEnumerator method and, 364-65
   Item property and, 358
   methods, 367-69
   modifying, 355-56
   properties, 366-67
   returning, with the DataView object, 350-51
DataSet class, 21-23, 202, 394-96
   inheritance and, 394
   location of, 209
   moving data to and from instances of, 396
   validation and, 398
   Windows-based applications and, 564, 577-78
DataSet object, 18-19, 532-41, 551, 554, 557-58
   adding pseudokeys to, 500-502
   adding rows and, 382-84
   applying changes to, 632-33
   autoincrement values and, 488-90
   batch queries and, 174-75
   BLOB data and, 596
   caching changes with, 207
   changing primary keys in, 500-502
   ComplexHierarchy application and 502-3
   conflicting rows and, 506-10
   Conflicts application and, 509-10
   converting, 394-96
   creating, 28-29, 208-9, 219-35, 376-82, 563-64
   creating relationships in, 32-33
   data binding and, 611-15
   Data Form Wizard and, 32-34, 37, 39, 41-42, 45
   DataAdapter object and, 12, 41, 159, 169, 247-50
   database queries and, 110, 126
   DataColumn object and, 210-11
   DataRelation object and, 19
   DataRow object and, 235-46
   DataTable object and, 232-35
   described, 3
   design-time benefits of, 390-91
   disconnected data and, 206
   events, 267
   examining data returned by, 211-14
   failed updates and, 503, 509-10
   features of, 205-8
   filling, 37-38, 169, 194-95, 209-11
   filtering data with, 206
   generating dummy values and, 481
   GetChanges method and, 491-503
   hierarchical data and, 206-7, 387-88
   mapping query results to, 172-174
   metadata and, 21
   methods, 263-66, 493-96, 498-503, 508-9
   modifying the contents of, 235-46
   null values and, 386-87
   paging features and, 625-26
   pessimistic concurrency and, 460-61
   properties, 258-63
   queries to, mapping the results of, 172-74
   Recordset object and, 6, 23
   refreshing data in, 170-72
   reintegrating changes and, 42-43
   returning untyped data with, 396-98
   run-time benefits of, 391-94
   schema information and, 218
   scrolling with, 206
   searching with, 206
   selecting columns and, 33-34
   sorting with, 206
   storing query results in, 157-202
   strongly typed, 21-23, 29, 254, 325-28, 375-99
   submitting updates and, 402-7, 430, 452, 455, 460-61, 480- 91, 498-510
   templates and, 251
   untyped, 254-58, 327-28
   validation and, 214-18, 398
   Visual Studio .NET and, 247-58, 379-83, 390-92
   Web applications and, 610-15, 619, 621, 625-26, 632-33
   Web services and, 490-91
   Windows-based applications and, 562, 565-68, 572-76, 584-87, 590, 600-602
   XML and, 18-19, 206-7, 248-52, 262, 271, 376
DataSet property, 220, 267, 269, 307-10, 325-30
DataSetName property, 208-9, 259-60, 530
DataSetToHTML application, 556
DataSource property, 80-82, 578, 584, 610, 614
DataTable class, 211
DataTable object, 14, 209-16, 219-23
   adding, to a Tables collection, 219-20
   adding columns and, 220-21
   adding rows and, 296, 382-84
   annotations and, 398-99
   autoincrement values and, 226-30, 489-90
   batch queries and, 174-75
   class files and, 381
   ComplexHierarchy application and, 502-3
   conflicting rows and, 506-8
   Connection object and, 69-70, 88
   constraints and, 216-17, 222-25
   creating, 167-69, 201-2, 219-20, 232-35
   Data Form Wizard and, 32, 38
   DataAdapter object and, 161, 167-77, 192-93, 196, 201-2
   DataColumn object and, 210-11
   DataRelation object and, 20
   DataRow object and, 17, 213-14
   DataSet object and, 254-58
   DataView object and, 20, 346-52
   delimiters and, 343
   disconnected objects and, 13, 14
   events, 277-78
   expression-based columns and, 230-32
   Fill method and, 192-93
   FillSchema method and, 196
   filtering features, 337-44, 347, 349
   Find method and, 384-85
   GetChanges method and, 491-503
   GetSchemaTable method and, 140-41
   hierarchical data and, 387-88
   locating rows in, 338-39
   methods, 272-77, 493-96, 498-502
   modifying the contents of, 235-46
   properties, 267-72
   Recordset object and, 23
   relational data access and, 305-8, 311-15, 318, 321-23, 325
   removing rows and, 240
   retrieving rows and, 175-76
   schema information and, 176-77, 196
   searching, 344-45, 369
   statelessness and, 606-7
   strongly typed, 384
   submitting updates and, 414, 418, 451-53, 472, 486-88, 493-503, 506-9, 514
   Visual Studio .NET and, 249, 251-52
   Web applications and, 606-7, 619, 636
   Windows-based applications and, 562-68, 576-78, 584, 587, 590, 600-602
   XML and, 530-31, 550, 554, 557-58
DataTableCollection object, 219, 262
DataTableMapping object, 172, 175
DataTableMappingCollection object, 163, 172-73, 188
DataTableMappings property, 187-88
DataType property, 141, 210, 220-21, 275, 278, 281-82
DataView object, 20, 346-47, 350-51, 369-70
   adding, to your designer, 356
   creating, 356
   DataSet object and, 227-30
   described, 346
   events and, 365
   examining the contents of, 351-52
   methods, 361-65
   properties, 356-61
   searching for data in, 352-54
   Windows-based applications and, 582, 585, 588, 602
DataViewManager object, 370
DataViewManager property, 357, 359
DataViewRowState enumeration, 339-40, 345, 360
DataViewSettings object, 370
date/time values, 444
dBASE, 514
DBConcurrencyException, 504
DBNull class, 240
DbType property, 141, 144, 148
DDL (data definition language) queries, 99
Debug menu, 37, 63
debugging, 37, 63, 95. See also errors
Decimal class, 585
Decimal data type, 144, 578, 585
Decimal double data type, 104
Default constant, 244, 246
DEFAULT keyword, 465
Default property, 15
DefaultValue property, 246, 278, 282, 465
DefaultView property, 267, 269-70
Delete button, 38, 571-72
Delete method
   DataRow object and, 240
   DataRowView object and, 355, 367, 369
   DataSet object and, 240, 287, 290
   DataView object and, 361
DELETE statement, 45, 160, 551
   CommandBuilder object and, 432, 434
   DataAdapter object and, 435
   primary key columns and, 442
   submitting updates and, 406, 432, 434-35, 442, 454
DeleteCommand property, 12-13, 162, 186, 187
   NOCOUNT setting and, 485
   submitting updates and, 419, 435-36, 439, 447, 470, 485
   transactions and, 447
Deleted constant, 243
DeleteRule property, 294, 295, 324, 327
deletions, pending, 485-88
delimiters ([]), 342
Depth property, 134, 135, 141
DeriveParameters method, 150, 456
DESC keyword, 360
DesignMode property, 259, 260, 267, 270
Detached constant, 243
DiffGram enumeration, 532-33
diffgrams, 526, 532-33, 551
Dim statement, 92
Direction property, 12, 116, 141, 143
Disabled constant, 515
disconnected data structures, 206
display styles, selecting, 34-35
DisplayMember property, 584
DisplayRow procedure, 213-14
Dispose method, 65, 67, 297
Disposed event, 297
distributed transactions. See transactions
DistributedTransaction application, 518-19
DLLs (dynamic link libraries), 378
DML (data manipulation language), 98-99, 101, 109
Driver property, 82
dummy values, 498-500
dynamic link libraries. See DLLs

E
Edit button, 573-75, 581, 590, 632
Edit Relation dialog box, 326-27, 328, 380
EditCommand event, 632
EditDetail method, 581-82
EditItemIndex property, 632
elements
   choosing, 531
   names of, 530
   XML and, 530-31
EmployeeID field, 34
Employees table, 162-63, 583-85
   autoincrement values and, 474
   PictureBox controls and, 599, 600-601
   relational data access and, 315-16
End event, 622
EndCurrentEdit method, 574
EndEdit method, 17, 238, 240
   DataRowView object and, 355, 367
   DataSet object and, 245, 287, 288-89
EndInit method, 263, 264-65, 273, 361-62
EndLoadData method, 273, 274
EnforceConstraints property, 202, 259, 260, 339, 536, 557
Enterprise Manager, 50, 65, 608
errors. See also debugging; exceptions
   conflicting rows and, 506-8
   DataAdapter object and, 177, 190
   database queries and, 100, 109, 139
   DataSet object and, 259, 261, 267, 270, 285
   submitting updates and, 510, 512-13, 518
   transactions and, 512-13, 518
   treatment of, as informational messages, 92
   XML and, 554
escape character. See backslash (\)
Esposito, Dino, 604
Eval method, 611-13
Event logs, 70, 71
events
   adding code to handle, 92
   DataSet object and, 238
   post-back, 605-7
Excel (Microsoft), 205, 599
exceptions. See also errors
   DataAdapter object and, 177
   DataSet object and, 216, 267
   locating rows and, 339
   submitting updates and, 401
   validating data and, 216
EXEC syntax, 115, 128
ExecuteNonQuery method, 11, 129, 131, 553-54
   database queries and, 99-101, 135
   NOCOUNT setting and, 483, 485
   submitting updates and, 408
ExecuteReader method, 11, 130-35
   database queries and, 101-2, 108-10, 112, 132-33
   submitting updates and, 432
ExecuteScalar method, 130, 133, 472
ExecuteXmlReader method, 11, 539
Executing constant, 83
execution plans, 577
Expires property, 617
Expression property, 15-16, 230-32, 278, 282-84, 320-23, 578
ExtendedProperties collection, 398
ExtendedProperties property, 259-61, 267, 270, 278, 283, 293-96, 329-30
extensible Markup Language. See XML
Extensible Stylesheet Language Transformations. See XSLT

F
Fetching constant, 83
Field object, 210, 407
FieldCount property, 134, 135, 213
fields
   counting, 134, 135, 213
   hidden, 617-19
Fields collection, 20
Fields property, 210
file extensions, 381
File menu
   Add New Item command, 27
   New command, 26
Fill Dataset button, 185
Fill method, 6, 12, 192-95, 590, 592-94
   autoincrement values and, 226-27
   constraints and, 202
   Data Form Wizard and, 37-38
   DataAdapter object and, 159, 166-72, 174, 202
   DataSet object and, 37-38, 209-13, 217, 226-27
   DataTable object and, 14
   making multiple calls to, 170-72
   MissingMappingAction property and, 174
   overloaded, 168-69
   paging with, 168-69, 625-26
   submitting updates and, 451-52, 454
   XML and, 543-44
FillDataSet procedure, 37
FillError event, 198-99
FillMyDataSet procedure, 526, 534-35
FillSchema method, 177, 192, 195-96, 390
   DataSet object and, 217, 229, 248-49, 280, 377
   generating dummy values and, 481
filtering
   DataRelation object and, 304
   DataTable object and, 337-44, 347, 349
   DataView object and, 347
   join queries and, 301
Find method, 337-38, 354, 384-85
   DataSet object and, 216, 237
   DataView object and, 352-53, 361, 363-64, 370-72
   relational data access and, 334
FindRows method, 354, 361, 363-64, 372
firehose cursors, 101-2, 139
FireWire, 621
FirstReturnedRecord constant, 129, 470
For Each loops, 213-14
For loops, 213-14, 414
FOR XML query, 543
ForeColor property, 566
foreign key constraints, 216-17, 223-25, 257-58, 311-16. See also foreign keys; ForeignKeyConstraint object
foreign keys. See also foreign key constraints
   cascading changes and, 323-24
   relational data access and, 311-17, 323-24, 327, 329
ForeignKeyConstraint class, 216
ForeignKeyConstraint object, 224, 324, 327. See also foreign key constraints
   ChildKeyConstraint property and, 329
   properties, 294-96
Format event, 585
Format method, 611
FormatDetailsGrid procedure, 579
FormBorderStyle property, 563
forms. See also Data Form Wizard; specific controls
   building, 26-35
   data-bound, 26-44
   displaying data in new, 37-38
Forms package (Windows), 562, 568
frmEditOrders form, 563-64
FrontPage (Microsoft), 556

G
garbage collection, 65
Generate DataSet dialog box, 247-49, 577-78
Generate SQL Statement page, 379
Get method, 104-5, 135
GetBytes method, 136, 597-98
GetChanges method, 18, 41-42, 396
   DataSet object and, 263, 265, 273, 276
   Merge method and, 494, 496, 497-98
   saving bandwidth with, 491-503
   submitting hierarchical changes with, 488
GetChars method, 136, 598
GetChildRows method, 287, 290, 308-10, 387-88, 636
GetChunk method, 596
GetClass method, 598
GetColumnError method, 287, 290
GetColumnsInError method, 287, 290-91
GetCustomer procedure, 115-16
GetCustomersRow method, 387-88
GetData method, 135, 136, 141
GetDataTypeName method, 136, 139
GetDeleteCommand method, 450, 456, 457-58
GetEnumerator method, 351-52, 361, 364-65
GetErrors method, 273, 276
GetFieldType method, 136
GetFillParameters method, 192, 196-97
GetInsertCommand method, 456, 457-58
GetName method, 136, 139
GetName property, 213
GetOleDbSchemaTable method, 68-70, 84, 87-89
GetOrdersRows method, 387-88
GetOrdinal method, 103, 136, 139
GetParentRow method, 287, 291, 308-10, 322, 387-88
GetParentRows method, 287, 291, 308, 310
GetRows method, 372
GetSchemaTable method, 132, 136, 139-41
GetString method, 104, 137, 597
GetType function, 221
GetUpdateCommand method, 450, 456, 457-58
GetValue method, 136, 137-138
GetValues method, 136, 137-138
GetXML method, 263, 265, 524-26
GetXMLSchema method, 263, 265
GUIDs (globally unique identifiers), 503, 603-4

H
HasChanges method, 263, 265-66
HasErrors property, 259, 261, 267, 270, 285, 504
HasVersion method, 287, 292
hidden fields, 636
HTML (HyperText Markup Language), 605-6, 623
   caching and, 621, 623
   converting data into, 35, 555-56, 610-15
   tables, 35
HttpCookie object, 617
HttpSessionState class, 619
HyperText Markup Language. See HTML

I
IEnumerator object, 351, 364
IIS (Microsoft Internet Information Services), 51, 606
ImportRow method, 273, 276
InferXmlSchema method, 263, 266, 528
InfoMessage event, 91-92
Initial Catalog attribute, 82
InitializeComponent procedure, 44, 78, 185
INNER JOIN syntax, 577
INSERT INTO query, 160, 479, 622
Insert method, 620-21
INSERT statement
   CommandBuilder object and, 432, 434
   DataAdapter object and, 435
   sequences and, 478, 479, 480
   submitting updates and, 406, 408, 432, 434-35, 454, 465
   XML and, 551
InsertCommand property, 12-13, 41, 162, 186-87
   generating dummy values and, 481
   NOCOUNT setting and, 482, 485
   sequences and, 479
   submitting updates and, 419, 436, 439, 447, 470-71, 475, 479, 481-82, 485
   transactions and, 447
integer data type, 104
IntelliSense, 163, 384
Internet Explorer browser (Microsoft), 526-28, 555-56
InternetExplorer class, 528
intRecordsAffected variable, 107
IParameter object, 196-97
IsClosed property, 134, 135
IsContactNameNull method, 386-87
IsDBNull method, 136
IsEdit property, 366
ISerializable interface, 619
IsNew property, 366
IsNull function, 386
IsNull method, 240, 287, 292-93
IsNullable property, 142
isolation levels, for transactions, 591-92
IsPrimaryKey property, 293, 294
Item method, 213, 310
Item property, 103, 104, 134-36
   BLOB data and, 597
   class files and, 381
   DataRow object and, 17-18
   DataRowView object and, 350-51, 366
   DataSet object and, 212-13, 235-37, 244-46, 285
   DataView object and, 357, 358-59
ItemArray property, 238-39, 285
ItemChanged event, 569
Items collection, 634

J
Jet (Microsoft), 109-10, 139, 609
   Connection object and, 50, 57, 81
   database engine, 50
   database queries and, 128
   security and, 57
join queries
   advantages of, 301
   alternatives to, 325
   defined, 300-301
   relational data access and, 300-301, 332-35
   splitting, 325, 334
   updates and, 459-60
joins, simulating, with DataRelation object, 347
JPEG (Joint Photographics Experts Group) files, 599-601

K
Kagera, 58
KeyInfo constant, 132-33

L
Label controls, 567
Leave event, 587
libraries, 8-9, 51, 58, 514, 526, 532-33
ListEvent event, 365
Load button, 37, 39
Load event, 122, 567, 579, 607
Load method, 534, 540, 542
LoadDataRow method, 236, 237, 273, 276
LoadDataSet procedure, 37
LoadEmployeePhotos application, 599
Locale property, 259, 261-62, 268, 270-71
locking
   pessimistic, 590-93, 603-4
   Web applications and, 609-10
log tables, 483-85
login
   anonymous, denying, 608-9
   authentication and, 74-75, 609
   impersonating users and, 608-9
   Web applications and, 607-10
lookups, 103-4, 583-85