| [ Team LiB ] |
|
29.4 Methods Reference
Adds new rows or refreshes rows with changed data from the data source to the DataSet. Parameters
ExampleThe following example demonstrates how to fill both a table within a DataSet object and a DataTable object using the Fill( ) method: // connection string and select statement
String connString = "Data Source=(local);Integrated security=SSPI;" +
"Initial Catalog=Northwind;";
String selectSql = "SELECT * FROM Orders";
// create the data adapter
SqlDataAdapter da = new SqlDataAdapter(selectSql, connString);
// create a new DataSet to receive the Orders data
DataSet ds = new DataSet();
// read all of the Orders data into a table named Orders in the DataSet
da.Fill(ds, "Orders");
// create a new DataTable to receive the Orders data
DataTable dt = new DataTable("Orders");
// read all of the Orders data into table
da.Fill(dt);
NotesThe Fill( ) method returns data from the data source using the SQL statement or stored procedure in the SelectCommand. The Connection object associated with the SelectCommand must be valid but doesn't need to be open. If it is open prior to calling Fill( ), it's left open. If it isn't open prior to calling fill, the DataAdapter opens it when required and closes it when the data has been retrieved. The Fill( ) method adds rows to the specified DataTable, creating the table if it doesn't exist. When creating a DataTable, column metadata for the table is created, and if the MissingSchemaAction property is set to AddWithKey, the primary key and constraints are created as well. If duplicate columns are encountered while populating a DataTable, column names ColumnName1, ColumnName2, and so on, are generated for the duplicate columns. If unnamed columns are returned, default column names Column1, Column2, and so on, are generated. Avoid using these default column names. If the SelectCommand doesn't return any rows, no tables are added to the DataSet, and no exceptions are raised. If multiple result sets are returned, a table is created in the DataSet for each result set. If a table name isn't specified, the DataAdapter generates the table names Table, Table1, Table2, and so on, for the tables and assign them to the result sets in the order in which they were returned. Avoid using these default table names. If a table name is specified, MyTable for example, the DataAdapter generates table names MyTable, MyTable1, MyTable2, and so on, and assigns them to the result sets in the order in which they were returned. The DataSet can contain DataTables whose names differ only by case. If the argument specifying the table name differs only by case from a table name in the DataSet, two outcomes are possible. If the DataSet contains only one table with a table name that differs only by case, the records are added to that table. If, on the other hand, the DataSet contains more than one table with the table name that differs only by case, the records are added to the table with the name that matches based on a case-sensitive comparison or to a newly created table, if no match is found. When executing a subsequent Fill( ) operation to refresh data, a primary key must exist on the table being refreshed. Rows from the data source are matched with existing rows in the DataSet based on this key and are used to update the existing rows. If an error is encountered during the Fill( ) operation, all records added up to the error remain in the DataSet, and the remainder of the operation is aborted. Also, if multiple result sets are returned, any result set after the error is skipped. In the case of the overload Fill( ) method accepting arguments specifying the starting record and the number of records to return, if zero is specified as the maximum number of records to return, all records after the starting record are returned. If the specified maximum number of records to return is greater than the number of remaining rows in the result set, all remaining rows are returned without raising an exception. If the SelectCommand returns multiple result sets, values specified for the start record and the maximum number of records to return are applied only to the first result set. It's important to understand that when the start record and the maximum number of records are specified, the underlying mechanism that retrieves the matching subset of records selects all records based on the SelectCommand query and then discards records outside of the specified range of records. This can result in poor performance and resource utilization when retrieving a small number of records from the result set of a SelectCommand query that retrieves a large number of records, such as in some paging scenarios.
Creates a schema in the DataSet based on the data source. The schema information retrieved is based on the query in the SelectCommand. Parameters
ExampleThe following example demonstrates how to get the schema for both a table within a DataSet object and a DataTable object using the DataAdapter FillSchema( ) method: // connection string and select statement
String connString = "Data Source=(local);Integrated security=SSPI;" +
"Initial Catalog=Northwind;";
String selectSql = "SELECT * FROM Orders";
// create the data adapter
SqlDataAdapter da = new SqlDataAdapter(selectSql, connString);
// create a new DataSet to receive the Orders data
DataSet ds = new DataSet();
// read Orders table schema into the Orders table in the DataSet
da.FillSchema(ds, SchemaType.Mapped, "Orders");
// create a new DataTable to receive the Orders data
DataTable dt = new DataTable("Orders");
// read the schema for the Orders table into the DataTable
da.FillSchema(dt, SchemaType.Mapped);
NotesThe FillSchema( ) method creates tables in the DataSet from the data source using the SQL statement or stored procedure in the SelectCommand. The Connection object associated with the SelectCommand must be valid but doesn't need to be open. If it is open prior to calling FillSchema( ), it is left open. If it isn't open prior to calling FillSchema( ), the DataAdapter opens it when required and closes it when the schema information has been retrieved. In addition to creating tables in the data source, the FillSchema method configures the columns within the tables. The AllowDBNull, AutoIncrement, Maxlength, ReadOnly, and Unique properties are set for each column as they exist at the data source. The AutoIncrementSeed and AutoIncrementStep properties must be set programmatically, as does the DefaultValue property. The primary key for the table is set to the primary key information from the data source, if it exists. If no primary key information exists in the data source, but unique columns are returned, those unique columns are used as the primary key if none of the unique columns allow null values. Unique constraints are set on columns based on the information returned from the data source. Other constraint types aren't added. If duplicate columns are encountered while creating a DataTable, column names ColumnName1, ColumnName2, ColumnName3, and so on, are generated for the duplicate columns. If unnamed columns are returned, default column names Column1, Column2, Column3, and so on, are generated. Avoid using these default column names. If multiple result sets are returned, a table is created in the DataSet for each result set. The DataAdapter generates the table names Table, Table1, Table2, and so on, for the tables and assigns them to the result sets in the order in which they were returned. Avoid using theses default table names. If a table name is specified, MyTable for example, the DataAdapter generates table names MyTable, MyTable1, MyTable2, and so on, and assigns them to the result sets in the order in which they were returned. The DataSet can contain DataTables whose names differ only by case. If the table-name argument specifies a table name that differs only by case from a table name in the DataSet, two outcomes are possible. If the DataSet contains only one table with a table name that differs only by case, that table is created or updated. If, on the other hand, the DataSet contains more than one table with a table name that differs only by case, the table with the matching name based on a case-sensitive comparison is updated or created, if no match is found.
Returns an array of parameter objects set by the user for the SelectCommand. ParametersNone. Example// connection string and select statement
String connString = "Data Source=(local);Integrated security=SSPI;" +
"Initial Catalog=Northwind;";
String selectSql = "SELECT * FROM Orders WHERE OrderID=@OrderID";
// create the data adapter
SqlDataAdapter da = new SqlDataAdapter(selectSql, connString);
da.SelectCommand.Parameters.Add("@OrderID", SqlDbType.Int);
da.GetFillParameters()[0].Value = 10248;
NoteAn array of IDataParameter objects is returned rather than the .NET data provider Parameter class that implements IDataParameter. Cast the IDataParameter interface to the provider-specific Parameter class to access provider-specific functionality.
Submits changed data in the DataSet to the data source. The method calls the respective DeleteCommand, InsertCommand, or UpdateCommand objects for each deleted, inserted, or modified DataRow to perform the reconciliation. Parameters
ExampleThe following example demonstrates how to use the Update( ) method of the DataAdapter to reconcile changes made to a DataSet with the underlying data source: // connection and select command strings
String connString = "Data Source=(local);Integrated security=SSPI;" +
"Initial Catalog=Northwind;";
String sqlSelect = "SELECT * FROM Orders";
// create a new DataSet to receive the data
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, connString);
// create the command builder
SqlCommandBuilder cb = new SqlCommandBuilder(da);
// load data from the Orders table into the Orders table in the DataSet
da.Fill(ds, "Orders");
// ... code to modify the data in the DataSet
// update the data in the Orders table in the DataSet to the data source
da.Update(ds, "Orders");
NotesThe Update( ) method submits changes to the DataSet back to the data source. The Update( ) method uses the DeleteCommand, InsertCommand, and UpdateCommand objects to attempt to update the data source with records that have been respectively deleted, inserted, or updated in the DataSet. The rows are updated one at a time and not as part of a batch process. The order that the rows are processed as part of the update is determined by the indexes on the DataTable and not by the type of update being performed. The UpdatedRowSource property of each Command object that submits updates to the data source determines how data is returned back to the DataSet. Table 29-3 lists the possible values for UpdatedRowSource and describes the effect of each on data returned back to the updated row in the DataSet. Parameter values for each update command are mapped to the current row through the SourceColumn and SourceVersion properties of the Parameter class. If the SourceColumn refers to a column that doesn't exist, the MissingMappingAction dictates what happens. The SourceColumn property also maps updated data-source values in the data source, back to the DataSet. An exception is raised if a nonexistent column is specified. If an error occurs when a row is being updated, any updates made to rows before the error occurred are kept. If the ContinueUpdateOnError property is true, the row in error is skipped, and processing continues with the next record. If ContinueUpdateOnError is set to false, an exception is raised, and the remaining records aren't processed.  |
| [ Team LiB ] |
|