Adding and Modifying Data

Introduction

This chapter focuses on issues related to inserting and updating data as well as using web services, remoting, and messaging to update data.

Web services allow distributed applications running on disparate platforms to communicate using open standards and ubiquitous protocols. Recipe 4.11 shows how to create a web service that lets a client update a database, and how to call the web service from a .NET application. Similarly, Recipe 4.12 shows how to create a.NET remoting component that lets a client update a database, and how to call the remote objects from a .NET application.

Messaging allows applications running on disparate platforms to communicate whether they are connected or disconnected. Recipe 4.13 shows how to use messaging to update a database.

In addition to these topics, this chapter also covers:

Identity and auto-increment columns

ADO.NET provides an auto-incrementing column type that generates a unique value for each new row. There is no mechanism to ensure that the values are unique from the values produced by other users. Recipe 4.1 shows how to use auto-incrementing columns to ensure that the values generated by different users do not conflict.

SQL Server has an identity column that is also an auto-incrementing column type. This value is used rather than the ADO.NET auto-increment column type when adding new records; there is no automatic way to keep these values synchronized after new rows in a DataTable have been inserted into a SQL Server table. Recipe 4.2 shows you how to synchronize the DataTable to the values in the database. Recipe 4.3 shows you how to synchronize these values with a Microsoft Access database.

Oracle does not support auto-increment columns but rather uses a sequence , that is, a procedure that generates a series of unique values. Recipe 4.4 shows how to synchronize auto-incrementing columns in a DataTable with Oracle sequence values after a row has been inserted into an Oracle database.

Primary keys and relationships

Recipe 4.5 shows how to add master-detail records to a DataSet where the primary key of the parent table is an auto-incrementing column.

A Globally Unique Identifier (GUID) is a 128-bit integer that is statistically unique. Recipe 4.6 shows how to add records to a DataSet containing master-detail records with both parent and child tables having a GUID primary key.

Changing the primary key value in a database is a little more complicated than changing it in a DataTable and updating it to the database. When the primary key is changed in the DataTable , the default behavior is for the update to look for a row matching the modified value for the primary key rather than the original. Recipe 4.8 demonstrates how to change the primary key in a database.

In a relational database, many-to-many relationships use a junction table to join two other tables. Recipe 4.10 shows how to update changes made to the tables and relationships between the rows without causing referential integrity errors.

DataSet twiddling

A DataSet keeps no connection or data source information about its data source. This allows a DataSet to be loaded with data from one data source and updated back to another data source, perhaps for auditing or logging purposes. Recipe 4.7 shows how this is done.

CommandBuilder

A CommandBuilder can quickly and easily generate update logic for a DataAdapter in small or test applications. A CommandBuilder cannot generate valid update logic if the table or column names contain special characters or spaces. Recipe 4.14 shows how to make the CommandBuilder delimit table and column names to overcome this problem.

Stored procedure parameters

Although of questionable usefulness in a production environment, ADO.NET allows you to retrieve stored procedure parameters information at runtime. SQL Server also lets you do the same thing using a system stored procedure. Recipe 4.9 shows you both techniques.