ADO.NET 3.5 Cookbook, 2nd Edition

3.14. Retrieving Schema and Constraints for a DataSet

Problem

You need to retrieve schema and constraint information from the data source into a DataSet or DataTable.

Solution

Use the FillSchema() method of the DataAdapter or set the MissingSchemaAction property of the DataAdapter to AddWithKey before calling the Fill() method of the DataAdapter.

The solution loads a DataSet with the first five records from the Person.Contact table from AdventureWorks and outputs the schema for the DataSet when calling Fill() without and with calling FillSchema().

The C# code in Program.cs in the project AddExistingConstraints is shown in Example 3-18.

Example 3-18. File: Program.cs for AddExistingConstraints solution

using System; using System.Data; using System.Data.SqlClient; namespace AddingExistingConstraints { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT TOP 5 ContactID," + "FirstName, LastName FROM Person.Contact"; // Create a data adapter SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); // Fill a DataSet without schema DataSet ds = new DataSet( ); da.Fill(ds, "Contact"); Console.WriteLine("---WITHOUT SCHEMA--", ds.Tables.Count); Console.WriteLine("GetXmlSchema( ) = {0}", ds.GetXmlSchema( )); // Fill a DataSet with schema using FillSchema( ) DataSet dsSchema = new DataSet( ); da.FillSchema(dsSchema, SchemaType.Source, "Person.Contact"); ...