Retrieving Schema Information from the Data Source

Retrieving Schema Information from the Data Source

Schema information can be retrieved from a data source using the FillSchema( ) method, which retrieves the schema information for the SQL statement in the SelectCommand. The method adds a DataTable to the DataSet and adds DataColumn objects to that table. Finally, it configures the AllowDBNull, AutoIncrement, MaxLength, ReadOnly, and Unique properties of the DataColumn, based on the data source. While it configures the AutoIncrement property, it doesn't set the AutoIncrementSeed and AutoIncrementStep properties. The FillSchema( ) method also configures the primary key and unique constraints for the DataTable. It doesn't configure the DefaultValue property.

In addition to an argument specifying the DataSet argument, the FillSchema( ) method takes an argument specifying whether the schema is transformed by the table mappings for the data adapter. Mapping tables and columns is discussed in more detail later in this chapter

If the FillSchema( ) method is used with a table that already has schema defined, the original schema isn't overwritten. Rather, new columns are added if they are part of the schema retrieved but don't exist in the table.

Finally, if a query returning multiple result sets is specified in the SelectCommand, only the schema from the first result set is used. To fill schemas based on queries with multiple result sets, use the Fill( ) method with the MissingSchemaAction set to AddWithKey.

The following example demonstrates the FillSchema method:

// connection and select command strings

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 table schema

DataSet ds = new DataSet();

// read the schema for the Orders table from the data source and

// create a table in the DataSet called "Orders" with the same schema

da.FillSchema(ds, SchemaType.Source, "Orders");

// create a new DataTable to receive the schema

DataTable dt = new DataTable("Orders");

da.FillSchema(dt, SchemaType.Source);

As with the Fill( ) method, the DataAdapter connection must be valid, but doesn't have to be open. If it is closed when FillSchema( ) is called, it is automatically opened

to retrieve the data and then closed. If it is open when FillSchema( ) is called, it is left open after the data is retrieved.

Updating the Data Source//imp

The Update( ) method can submit DataSet changes back to the data source. It uses the statements in the DeleteCommand, InsertCommand, and UpdateCommand objects to attempt to update the data source with records that have been deleted, inserted, or updated in the DataSet. Each row is updated individually and not as part of a batch process. Furthermore, the order in which the rows are processed is determined by the indexes on the DataTable and not by the update type. Figure below illustrates how the DataAdapter is used both to reconcile changed data in the DataSet with the data source using the Update() method and to retrieve data from the data source using the Fill() method.

Figure. Retrieving and updating data using the DataAdapter

The delete, insert, and update statements can be automatically generated using the CommandBuilder object, but this is probably not the best approach for production systems. Alternatively, custom update logic can be used where the DeleteCommand, InsertCommand, and UpdateCommand are each defined. Compared with using the CommandBuilder, custom logic can significantly improve performance and can implement solutions to complex updating and conflict-resolution scenarios.

The following example demonstrates the Update( ) method. For simplicity, a CommandBuilder generates the update logic.

// connection and select command strings

String connString = "Data Source=(local);Integrated security=SSPI;" + 

    "Initial Catalog=Northwind;";

String selectSql="SELECT * FROM Orders";

// create a new DataSet to receive the data

DataSet ds = new DataSet();

SqlDataAdapter da = new SqlDataAdapter(selectSql, connString);

// create the command builder

// this creates SQL statements for the DeleteCommand, InsertCommand,

// and UpdateCommand properties for the data adapter based on the

// select command that the data adapter was initialized with

SqlCommandBuilder cb = new SqlCommandBuilder(da);

// read all of the data from the orders table and load it 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");

As with the Fill( ) and FillSchema( ) methods, opening and closing the connection are performed by the data adapter, if necessary.