14.3 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.