Database Reference
In-Depth Information
How It Works
As before, you use a single data table in a data set.
// Get data table reference
DataTable dt = ds.Tables["Person.Address"];
Next, you can see an example of how you can change the schema information. You select the
FirstName column, whose
AllowNull
property is set to
false
in the database, and you change it—just for
the purposes of demonstration—to
true
.
// AddressLine2 column should be nullable
dt.Columns["AddressLine2"].AllowDBNull = true;
Note that you can use an ordinal index (for example,
dt.Columns[ l ]
) if you know what the index
for the column is, but using
*
to select all columns makes this less reliable since the position of a column
may change if the database table schema changes.
You can modify a row using the same technique. You simply select the appropriate row and set its
columns to whatever values you want, consistent with the column data types, of course. The following
line shows the City column of the first row of the data set being changed to Wilmington:
// Modify City in first row
dt.Rows[o]["city"] = "Wilmington";
Next you add a new row to the data table.
// Add a row
DataRow newRow = dt.NewRow();
newRow["PostalCode"] = "111111";
newRow["StateProvinceID"] = "80";
newRow["City"] = "Birmingham";
dt.Rows.Add(newRow);
The
NewRow
method creates a data row (a
System.Data.DataRow
instance). You use the data row's
indexer to assign values to its columns. Finally, you add the new row to the data table, calling the
Add
method on the data table's
Rows
property, which references the rows collection.
Updating data sources requires learning more about data adapter methods and properties. Let's
take a look at these now.
Propagating Changes to a Data Source
You've seen how a data adapter populates a data set's data tables. What you haven't looked at yet is how
a data adapter updates and synchronizes a data source with data from a data set. It has three properties
that support this (analogous to its
SelectCommand
property, which supports queries).
•
InsertCommand
•
UpdateCommandDeleteCommand
We'll describe
InsertCommand
briefly and then put that to work.