Adding Restrictions to DataTable and DataColumn Objects

Adding Restrictions to DataTable and DataColumn Objects

As you know, a DataSet object is used to store a copy of a subset of the database. For example, you can store a copy of the rows from database tables into a DataSet, with each table represented by a DataTable object. A DataTable stores columns in DataColumn objects.

In addition to storing rows retrieved from a database table, you can also add restrictions to a DataTable and its DataColumn objects. This allows you to model the same restrictions placed on the database tables and columns in your DataTable and DataColumn objects. For example, you can add the following constraints to a DataTable:

  • Unique

  • Primary key

  • Foreign key

In addition, you can add the following restrictions to a DataColumn:

  • Whether the column can accept a null value-which you store in the AllowDBNull property of the DataColumn.

  • Any auto-increment information-which you store in the AutoIncrement, AutoIncrementSeed, and AutoIncrementStep properties of the DataColumn. You set these properties when adding rows to a DataTable with a corresponding database table that contains an identity column. The ProductID column of the Products table is an example of an identity column.

    Note 

    ADO.NET will not automatically generate values for identity columns in a new row. Only the database can do that. You must read the generated identity value for the column from the database. You'll see how to do that later in the sections "Retrieving New Identity Column Values" and "Using Stored Procedures to Add, Modify, and Remove Rows from the Database." Also, if your database table contains columns that are assigned a default value, you should read that value from the database. This is better than setting the DefaultValue property of a DataColumn because if the default value set in the database table definition changes, you can pick up the new value from the database rather than having to change your code.

  • The maximum length of a string or character column value-which you store in the MaxLength property of the DataColumn.

  • Whether the column is read-only-which you store in the ReadOnly property of the DataColumn.

  • Whether the column is unique-which you store in the Unique property of the DataColumn.

By adding these restrictions up front, you prevent bad data from being added to your DataSet to begin with. This helps reduce the errors when attempting to push changes in your DataSet to the database. If a user of your program attempts to add data that violates a restriction, they'll cause an exception to be thrown. You can then catch the exception in your program and display a message with the details. The user can then change the data they were trying to add and fix the problem.

You also need to define a primary key before you can find, filter, and sort DataRow objects in a DataTable. You'll learn how to do that later in the section "Finding, Filtering, and Sorting Rows in a DataTable."

Tip 

Adding constraints causes a performance degradation when you call the Fill() method of a DataAdapter. This is because the retrieved rows are checked against your constraints before they are added to your DataSet. You should therefore set the EnforceConstraints property of your DataSet to false before calling the Fill() method. You then set EnforceConstraints back to the default of true after the call to Fill().

You can use one of following ways to add restrictions to DataTable and DataColumn objects:

  • Add the restrictions yourself by setting the properties of your DataTable and DataColumn objects. This results in the fastest executing code.

  • Call the FillSchema() method of your DataAdapter to copy the schema information from the database to your DataSet. This populates the properties of the DataTable objects and their DataColumn objects automatically. Although simple to call, the FillSchema() method takes a relatively long time to read the schema information from the database and you should avoid using it.

You'll learn the details of both these techniques in the following sections.

Adding the Restrictions Yourself

You can add restrictions to your DataTable and DataColumn objects yourself using the properties of the DataTable and DataColumn objects.

For example, assume you have a DataSet object named myDataSet that contains three DataTable objects named Products, Orders, and Order Details that have been populated using the following code:

 SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText =   "SELECT ProductID, ProductName " +   "FROM Products;" +   "SELECT OrderID " +   "FROM Orders;" +   "SELECT OrderID, ProductID, UnitPrice " +   "FROM [Order Details];"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); mySqlDataAdapter.Fill(myDataSet); mySqlConnection.Close(); myDataSet.Tables["Table"].TableName = "Products"; myDataSet.Tables["Table1"].TableName = "Orders"; myDataSet.Tables["Table2"].TableName = "Order Details"; 

The primary key for the Products table is the ProductID column; the primary key for the Orders table is the OrderID column; and the primary key for the Order Details table is made up of both the OrderID and ProductID columns.

Note 

You must include all the columns of a database table's primary key in your query if you want to define a primary key on those columns in your DataTable.

In the following sections, you'll see how to

  • Add constraints to the Products, Orders, and Order Details DataTable objects.

  • Restrict the values placed in the DataColumn objects of the Products DataTable.

Adding Constraints to DataTable Objects

In this section, you'll see how to add constraints to DataTable objects. Specifically, you'll see how to add primary key constraints to the Products, Orders, and Order Details DataTable objects. A primary key constraint is actually implemented as a unique constraint. You'll also see how to add foreign key constraints from the Order Details to the Products and Orders DataTable objects.

Constraints are stored in a ConstraintCollection object that stores Constraint objects. You access the ConstraintCollection using the DataTable object's Constraints property. To add a new Constraint object to ConstraintCollection, you call the Add() method through the Constraints property. The Add() method allows you to add unique constraints and foreign key constraints to a DataTable. Since a primary key constraint is implemented as a unique constraint, you can also use the Add() method to add a primary constraint to a DataTable. You'll see how to use the Add() method shortly.

You can also add a primary key constraint to a DataTable object by setting its PrimaryKey property, which you set to an array of DataColumn objects that make up the primary key. An array is required because the primary key of a database table can be made up of multiple columns. As you'll see in the examples, this is simpler than using the Add() method to add a primary key constraint.

start sidebar
CALLING THE Fill() METHOD OF A DataAdapter MORE THAN ONCE

The Fill() method retrieves all of the rows from the database table, as specified in your DataAdapter object's SelectCommand property. If you add a primary key to your DataTable, then calling the Fill() method more than once will put the retrieved rows in your DataTable and throw away any existing rows with matching primary key column values already in your DataTable.

If you don't add a primary key to your DataTable, then calling the Fill() method more than once will simply add all the retrieved rows to your DataTable again, duplicating the rows already there.

This is another reason for adding a primary key constraint to your DataTable because you don't want duplicate rows.

end sidebar

Adding a Primary Key to the Products DataTable

Let's take a look at adding a primary key to the Products DataTable. First, the following example creates a DataTable object named productsDataTable and sets it to the Products DataTable retrieved from myDataSet:

 DataTable productsDataTable = myDataSet.Tables["Products"]; 

Now, the primary key for the Products database table is the ProductID column; therefore, you need to set the PrimaryKey property of productsDataTable to an array containing the ProductID DataColumn object. The following example shows how you do this. It creates an array of DataColumn objects named productsPrimaryKey and initializes it to the ProductID column of productsDataTable, then sets the PrimaryKey property of productsDataTable to the array:

 DataColumn[] productsPrimaryKey =   new DataColumn[]   {     productsDataTable.Columns["ProductID"]   }; productsDataTable.PrimaryKey = productsPrimaryKey; 

When you set the PrimaryKey property of a DataTable, the AllowDBNull and Unique properties of the DataColumn object are automatically changed as follows:

  • The AllowDBNull property is changed to false and indicates that the DataColumn cannot accept a null value.

  • The Unique property is changed to true and indicates that the DataColumn value in each DataRow must be unique.

In the previous example, therefore, the AllowDBNull and Unique properties of the ProductID DataColumn are automatically changed to false and true, respectively.

Adding a Primary Key to the Orders DataTable

The following example sets the PrimaryKey property of the Orders DataTable to the OrderID DataColumn:

 myDataSet.Tables["Orders"].PrimaryKey =   new DataColumn[]   {     myDataSet.Tables["Orders"].Columns["OrderID"]   }; 

Notice I've used just one statement in this example to make it more concise than the previous example.

You can also use the Add() method to add a unique, primary key, or foreign key constraint to a DataTable. The Add() method is overloaded as follows:

 void Add(Constraint myConstraint)  // adds any constraint void Add(string constraintName, DataColumn myDataColumn,  bool isPrimaryKey)  // adds a primary key or unique constraint void Add(string constraintName, DataColumn parentColumn,  DataColumn childColumn)  // adds a foreign key constraint void Add(string constraintName, DataColumn[] myDataColumn,  bool isPrimaryKey)  // adds a primary key or unique constraint void Add(string cosntraintName, DataColumn[] parentColumns,  DataColumn[] childColumns)  // adds a foreign key constraint 

where

  • constraintName is the name you want to assign to your constraint.

  • isPrimaryKey indicates whether the constraint is a primary key constraint or just a regular unique constraint.

The following example uses the Add() method to add a primary key constraint to the Products DataTable:

 myDataSet.Tables["Orders"].Constraints.Add(   "Primary key constraint",   myDataSet.Tables["Orders"].Columns["OrderID"],   true ); 

This example does the same thing as the previous example that added the primary key constraint using the PrimaryKey property. Notice the last parameter to the Add() method is set to true, which indicates the constraint is for a primary key.

Just as an aside, if you have a column that isn't a primary key but is unique, you can add a UniqueConstraint object to the ConstraintsCollection. For example:

 UniqueConstraint myUC =   new UniqueConstraint(myDataTable.Columns["myColumn"]); myDataTable.Constraints.Add(myUC); 

Adding a Primary Key to the OrderDetails DataTable

Let's consider an example of setting the PrimaryKey property for the Order Details DataTable. The primary for the Order Details table is made up of the OrderID and ProductID columns, and the following example sets the PrimaryKey property of the Order Details DataTable to these two columns:

 myDataSet.Tables["Order Details"].PrimaryKey =   new DataColumn[]   {     myDataSet.Tables["Order Details"].Columns["OrderID"],     myDataSet.Tables["Order Details"].Columns["ProductID"]   }; 

The following example uses the Add() method to do the same thing:

 myDataSet.Tables["Order Details"].Constraints.Add(   "Primary key constraint",   new DataColumn[]   {     myDataSet.Tables["Order Details"].Columns["OrderID"],     myDataSet.Tables["Order Details"].Columns["ProductID"]   },   true ); 

One thing to keep in mind when adding constraints to a DataTable is that it knows only about the rows you store in it; it doesn't know about any other rows stored in the actual database table. To see why this is an issue, consider the following scenario that involves primary keys:

  1. You add a primary key constraint to a DataTable.

  2. You retrieve a subset of the rows from a database table and store them in your DataTable.

  3. You add a new DataRow to your DataTable with a primary key value not used in the subset of rows retrieved into your DataTable in the previous step-but that primary key value is already used in a row in the database table. Your new DataRow is added without any problem to the DataTable even though you added a primary key constraint to your DataTable in step 1. Your new DataRow is added successfully because the DataTable knows only about the rows stored in it, not the other rows stored in the database table that were not retrieved in step 2.

  4. You attempt to push the new DataRow to the database, but you get a SqlException that states you've violated the primary key constraint in the database table. This is because a row in the database table already uses the primary key value.

You need to keep this issue in mind when adding rows to a DataTable, which you'll see how to do shortly.

That wraps up adding the primary key constraints to the DataTable objects. Next, you'll see how to add foreign key constraints.

Adding Foreign Key Constraints to the Order Details DataTable

In this section, you'll see how to add a foreign key constraint to the Order Details DataTable. To do this, you use the Add() method through the Constraints property of the DataTable.

The following example adds a foreign key constraint from the OrderID DataColumn of the Order Details DataTable to the OrderID DataColumn of the Orders DataTable:

 ForeignKeyConstraint myFKC = new ForeignKeyConstraint(   myDataSet.Tables["Orders"].Columns["OrderID"],   myDataSet.Tables["Order Details"].Columns["OrderID"] ); myDataSet.Tables["Order Details"].Constraints.Add(myFKC); 

Note 

Notice that the parent DataColumn (OrderID of Orders) is specified before the child DataColumn (OrderID of Order Details).

The next example adds a foreign key constraint from the ProductID DataColumn of the Order Details DataTable to the ProductID DataColumn of the Products DataTable:

 myDataSet.Tables["Order Details"].Constraints.Add(   "Foreign key constraint to ProductID DataColumn of the " +     "Products DataTable",   myDataSet.Tables["Order Details"].Columns["ProductID"],   myDataSet.Tables["Products"].Columns["ProductID"] ); 

That wraps up adding constraints to the DataTable objects. Next, you'll see how to add restrictions to DataColumn objects.

Adding Restrictions to DataColumn Objects

In this section, you'll see how to add restrictions to the DataColumn objects stored in a DataTable. Specifically, you'll see how to set the AllowDBNull, AutoIncrement, AutoIncrementSeed, AutoIncrementStep, ReadOnly, and Unique properties of the ProductID DataColumn of the Products DataTable. You'll also see how to set the MaxLength property of the ProductName DataColumn of the Products DataTable.

The ProductID column of the Products database table is an identity column. The seed is the initial value and the step is the increment added to the last number and they are both set to 1 for ProductID. The ProductID identity values are therefore 1, 2, 3, and so on.

Tip 

When you set the AutoIncrementSeed and AutoIncrementStep properties for a DataColumn that corresponds to a database identity column, you should always set them both to -1. That way, when you call the Fill() method, ADO.NET will automatically figure out what values to set the AutoIncrementSeed and AutoIncrementStep to, based on the values retrieved from the database, and you don't have to figure out these values yourself.

The following code sets the properties of the ProductID DataColumn:

 DataColumn productIDDataColumn =   myDataSet.Tables["Products"].Columns["ProductID"]; productIDDataColumn.AllowDBNull = false; productIDDataColumn.AutoIncrement = true; productIDDataColumn.AutoIncrementSeed = -1; productIDDataColumn.AutoIncrementStep = -1; productIDDataColumn.ReadOnly = true; productIDDataColumn.Unique = true; 

The next example sets the MaxLength property of the ProductName DataColumn to 40. This stops you from setting the column value for ProductName to a string greater than 40 characters in length:

 myDataSet.Tables["Products"].Columns["ProductName"].MaxLength = 40; 

Listing 11.1 uses the code examples shown in this section and the previous one. Notice this program also displays the ColumnName and DataType properties of the DataColumn objects in each DataTable. The ColumnName property contains the name of the DataColumn, and the DataType contains the .NET data type used to represent the column value stored in the DataColumn.

Listing 11.1: ADDRESTRICTIONS.CS

start example
 /*   AddRestrictions.cs illustrates how to add constraints to   DataTable objects and add restrictions to DataColumn objects */ using System; using System.Data; using System.Data.SqlClient; class AddRestrictions {   public static void Main()   {     SqlConnection mySqlConnection =       new SqlConnection(         "server=localhost;database=Northwind;uid=sa;pwd=sa"       );     SqlCommand mySqlCommand = mySqlConnection.CreateCommand();     mySqlCommand.CommandText =       "SELECT ProductID, ProductName " +       "FROM Products;" +       "SELECT OrderID " +       "FROM Orders;" +       "SELECT OrderID, ProductID, UnitPrice " +       "FROM [Order Details];";     SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();     mySqlDataAdapter.SelectCommand = mySqlCommand;     DataSet myDataSet = new DataSet();     mySqlConnection.Open();     mySqlDataAdapter.Fill(myDataSet);     mySqlConnection.Close();     myDataSet.Tables["Table"].TableName = "Products";     myDataSet.Tables["Table1"].TableName = "Orders";     myDataSet.Tables["Table2"].TableName = "Order Details";     // set the PrimaryKey property for the Products DataTable     // to the ProductID column     DataTable productsDataTable = myDataSet.Tables["Products"];     DataColumn[] productsPrimaryKey =       new DataColumn[]       {         productsDataTable.Columns["ProductID"]       };     productsDataTable.PrimaryKey = productsPrimaryKey;     // set the PrimaryKey property for the Orders DataTable     // to the OrderID column     myDataSet.Tables["Orders"].PrimaryKey =       new DataColumn[]       {         myDataSet.Tables["Orders"].Columns["OrderID"]       };     // set the PrimaryKey property for the Order Details DataTable     // to the OrderID and ProductID columns     myDataSet.Tables["Order Details"].Constraints.Add(       "Primary key constraint on the OrderID and ProductID columns",       new DataColumn[]       {         myDataSet.Tables["Order Details"].Columns["OrderID"],         myDataSet.Tables["Order Details"].Columns["ProductID"]       },       true     );     // add a foreign key constraint on the OrderID column     // of Order Details to the OrderID column of Orders     ForeignKeyConstraint myFKC = new ForeignKeyConstraint(       myDataSet.Tables["Orders"].Columns["OrderID"],       myDataSet.Tables["Order Details"].Columns["OrderID"]     );     myDataSet.Tables["Order Details"].Constraints.Add(myFKC);     // add a foreign key constraint on the ProductID column     // of Order Details to the ProductID column of Products     myDataSet.Tables["Order Details"].Constraints.Add(       "Foreign key constraint to ProductID DataColumn of the " +       "Products DataTable",       myDataSet.Tables["Products"].Columns["ProductID"],       myDataSet.Tables["Order Details"].Columns["ProductID"]     );     // set the AllowDBNull, AutoIncrement, AutoIncrementSeed,     // AutoIncrementStep, ReadOnly, and Unique properties for     // the ProductID DataColumn of the Products DataTable     DataColumn productIDDataColumn =       myDataSet.Tables["Products"].Columns["ProductID"];     productIDDataColumn.AllowDBNull = false;     productIDDataColumn.AutoIncrement = true;     productIDDataColumn.AutoIncrementSeed = -1;     productIDDataColumn.AutoIncrementStep = -1;     productIDDataColumn.ReadOnly = true;     productIDDataColumn.Unique = true;     // set the MaxLength property for the ProductName DataColumn     // of the Products DataTable     myDataSet.Tables["Products"].Columns["ProductName"].MaxLength = 40;     // display the details of the DataColumn objects for     // the DataTable objects     foreach (DataTable myDataTable in myDataSet.Tables)     {       Console.WriteLine("\n\nReading from the " +         myDataTable + "DataTable:\n");       // display the primary key       foreach (DataColumn myPrimaryKey in myDataTable.PrimaryKey)       {         Console.WriteLine("myPrimaryKey = " + myPrimaryKey);       }       // display some of the details for each column       foreach (DataColumn myDataColumn in myDataTable.Columns)       {         Console.WriteLine("\nmyDataColumn.ColumnName = " +           myDataColumn.ColumnName);         Console.WriteLine("myDataColumn.DataType = " +           myDataColumn.DataType);         Console.WriteLine("myDataColumn.AllowDBNull = " +           myDataColumn.AllowDBNull);         Console.WriteLine("myDataColumn.AutoIncrement = " +           myDataColumn.AutoIncrement);         Console.WriteLine("myDataColumn.AutoIncrementSeed = " +           myDataColumn.AutoIncrementSeed);         Console.WriteLine("myDataColumn.AutoIncrementStep = " +           myDataColumn.AutoIncrementStep);         Console.WriteLine("myDataColumn.MaxLength = " +           myDataColumn.MaxLength);         Console.WriteLine("myDataColumn.ReadOnly = " +           myDataColumn.ReadOnly);         Console.WriteLine("myDataColumn.Unique = " +           myDataColumn.Unique);       }     }   } } 
end example

The output from this program is as follows:

 Reading from the Products DataTable: myPrimaryKey = ProductID myDataColumn.ColumnName = ProductID myDataColumn.DataType = System.Int32 myDataColumn.AllowDBNull = False myDataColumn.AutoIncrement = True myDataColumn.AutoIncrementSeed = -1 myDataColumn.AutoIncrementStep = -1 myDataColumn.MaxLength = -1 myDataColumn.ReadOnly = True myDataColumn.Unique = True myDataColumn.ColumnName = ProductName myDataColumn.DataType = System.String myDataColumn.AllowDBNull = True myDataColumn.AutoIncrement = False myDataColumn.AutoIncrementSeed = 0 myDataColumn.AutoIncrementStep = 1 myDataColumn.MaxLength = 40 myDataColumn.ReadOnly = False myDataColumn.Unique = False Reading from the Orders DataTable: myPrimaryKey = OrderID myDataColumn.ColumnName = OrderID myDataColumn.DataType = System.Int32 myDataColumn.AllowDBNull = False myDataColumn.AutoIncrement = False myDataColumn.AutoIncrementSeed = 0 myDataColumn.AutoIncrementStep = 1 myDataColumn.MaxLength = -1 myDataColumn.ReadOnly = False myDataColumn.Unique = True Reading from the Order Details DataTable: myPrimaryKey = OrderID myPrimaryKey = ProductID myDataColumn.ColumnName = OrderID myDataColumn.DataType = System.Int32 myDataColumn.AllowDBNull = False myDataColumn.AutoIncrement = False myDataColumn.AutoIncrementSeed = 0 myDataColumn.AutoIncrementStep = 1 myDataColumn.MaxLength = -1 myDataColumn.ReadOnly = False myDataColumn.Unique = False myDataColumn.ColumnName = ProductID myDataColumn.DataType = System.Int32 myDataColumn.AllowDBNull = False myDataColumn.AutoIncrement = False myDataColumn.AutoIncrementSeed = 0 myDataColumn.AutoIncrementStep = 1 myDataColumn.MaxLength = -1 myDataColumn.ReadOnly = False myDataColumn.Unique = False myDataColumn.ColumnName = UnitPrice myDataColumn.DataType = System.Decimal myDataColumn.AllowDBNull = True myDataColumn.AutoIncrement = False myDataColumn.AutoIncrementSeed = 0 myDataColumn.AutoIncrementStep = 1 myDataColumn.MaxLength = -1 myDataColumn.ReadOnly = False myDataColumn.Unique = False 

Adding Restrictions by Calling the DataAdapter Object's FillSchema() Method

Instead of adding restrictions yourself, you can add them by calling the FillSchema() method of your DataAdapter. The FillSchema() method does the following:

  • Copies the schema information from the database.

  • Creates DataTable objects in your DataSet if they don't already exist.

  • Adds the constraints to the DataTable objects.

  • Sets the properties of the DataColumn objects appropriately.

The properties of the DataColumn objects set by FillSchema() include the following:

  • The DataColumn name-which is stored in the ColumnName property.

  • The DataColumn .NET data type-which is stored in the DataType property.

  • The maximum length of a variable length data type-which is stored in the MaxLength property.

  • Whether the DataColumn can accept a null value-which is stored in the AllowDBNull property.

  • Whether the DataColumn value must be unique-which is stored in the Unique property.

  • Any auto-increment information-which is stored in the AutoIncrement, AutoIncrementSeed, and AutoIncrementStep properties.

The FillSchema() method will also determine whether the DataColumn is part of a primary key and store that information in the PrimaryKey property of the DataTable.

Warning 

FillSchema() does not automatically add ForeignKeyConstraint objects to the DataTable objects. Neither does it retrieve the actual rows from the database; it retrieves only the schema information.

The FillSchema() method is overloaded, with the most commonly used version of this method being the following:

 DataTable[] FillSchema(DataSet myDataSet, SchemaType mySchemaType) 

where mySchemaType specifies how you want to handle any existing schema mappings.

You set mySchemaType to one of the constants defined in the System.Data.SchemaType enumeration. Table 11.7 shows the constants defined in the SchemaType enumeration.

Table 11.7: SchemaType ENUMERATION MEMBERS

CONSTANT

DESCRIPTION

Mapped

Apply any existing table mappings to the incoming schema and configure the DataSet with the transformed schema. This is the constant you should typically use.

Source

Ignore any table mappings and configure the DataSet without any transformations.

Let's take a look at an example that contains a call to the FillSchema() method. Notice the call uses the SchemaType.Mapped constant to apply any existing table mappings:

 SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText =   "SELECT ProductID, ProductName " +   "FROM Products;" +   "SELECT OrderID " +   "FROM Orders;" +   "SELECT OrderID, ProductID, UnitPrice " +   "FROM [Order Details];"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); mySqlDataAdapter.FillSchema(myDataSet, SchemaType.Mapped); mySqlConnection.Close(); myDataSet.Tables["Table"].TableName = "Products"; myDataSet.Tables["Table1"].TableName = "Orders"; myDataSet.Tables["Table2"].TableName = "Order Details"; 

The call to FillSchema() copies the schema information from the Products, Orders, and Order Details tables to myDataSet, setting the PrimaryKey property of each DataTable and the properties of the DataColumn objects appropriately.

Listing 11.2 shows the use of the FillSchema() method.

Listing 11.2: FILLSCHEMA.CS

start example
 /*   FillSchema.cs illustrates how to read schema information   using the FillSchema() method of a DataAdapter object */ using System; using System.Data; using System.Data.SqlClient; class FillSchema {   public static void Main()   {     SqlConnection mySqlConnection =       new SqlConnection(         "server=localhost;database=Northwind;uid=sa;pwd=sa"       );     SqlCommand mySqlCommand = mySqlConnection.CreateCommand();     mySqlCommand.CommandText =       "SELECT ProductID, ProductName " +       "FROM Products;" +       "SELECT OrderID " +       "FROM Orders;" +       "SELECT OrderID, ProductID, UnitPrice " +       "FROM [Order Details];";     SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();     mySqlDataAdapter.SelectCommand = mySqlCommand;     DataSet myDataSet = new DataSet();     mySqlConnection.Open();     mySqlDataAdapter.FillSchema(myDataSet, SchemaType.Mapped);     mySqlConnection.Close();     myDataSet.Tables["Table"].TableName = "Products";     myDataSet.Tables["Table1"].TableName = "Orders";     myDataSet.Tables["Table2"].TableName = "Order Details";     // display the details of the DataColumn objects for     // the DataTable objects     foreach (DataTable myDataTable in myDataSet.Tables)     {       Console.WriteLine("\n\nReading from the " +         myDataTable + "DataTable:\n");       // display the primary key       foreach (DataColumn myPrimaryKey in myDataTable.PrimaryKey)       {         Console.WriteLine("myPrimaryKey = " + myPrimaryKey);       }       // display the constraints       foreach (Constraint myConstraint in myDataTable.Constraints)       {         Console.WriteLine("myConstraint.IsPrimaryKey = " + ((UniqueConstraint) myConstraint).IsPrimaryKey);         foreach (DataColumn myDataColumn in ((UniqueConstraint) myConstraint).Columns)         {           Console.WriteLine("myDataColumn.ColumnName = " + myDataColumn.ColumnName);         }       }       // display some of the details for each column       foreach (DataColumn myDataColumn in myDataTable.Columns)       {         Console.WriteLine("\nmyDataColumn.ColumnName = " +           myDataColumn.ColumnName);         Console.WriteLine("myDataColumn.DataType = " +           myDataColumn.DataType);         Console.WriteLine("myDataColumn.AllowDBNull = " +           myDataColumn.AllowDBNull);         Console.WriteLine("myDataColumn.AutoIncrement = " +           myDataColumn.AutoIncrement);         Console.WriteLine("myDataColumn.AutoIncrementSeed = " +           myDataColumn.AutoIncrementSeed);         Console.WriteLine("myDataColumn.AutoIncrementStep = " +           myDataColumn.AutoIncrementStep);         Console.WriteLine("myDataColumn.MaxLength = " +           myDataColumn.MaxLength);         Console.WriteLine("myDataColumn.ReadOnly = " +           myDataColumn.ReadOnly);         Console.WriteLine("myDataColumn.Unique = " +           myDataColumn.Unique);       }     }   } } 
end example

The output from this program is as follows:

 Reading from the Products DataTable: myPrimaryKey = ProductID myConstraint.IsPrimaryKey = True myDataColumn.ColumnName = ProductID myDataColumn.ColumnName = ProductID myDataColumn.DataType = System.Int32 myDataColumn.AllowDBNull = False myDataColumn.AutoIncrement = True myDataColumn.AutoIncrementSeed = 0 myDataColumn.AutoIncrementStep = 1 myDataColumn.MaxLength = -1 myDataColumn.ReadOnly = True myDataColumn.Unique = True myDataColumn.ColumnName = ProductName myDataColumn.DataType = System.String myDataColumn.AllowDBNull = False myDataColumn.AutoIncrement = False myDataColumn.AutoIncrementSeed = 0 myDataColumn.AutoIncrementStep = 1 myDataColumn.MaxLength = 40 myDataColumn.ReadOnly = False myDataColumn.Unique = False Reading from the Orders DataTable: myPrimaryKey = OrderID myConstraint.IsPrimaryKey = True myDataColumn.ColumnName = OrderID myDataColumn.ColumnName = OrderID myDataColumn.DataType = System.Int32 myDataColumn.AllowDBNull = False myDataColumn.AutoIncrement = True myDataColumn.AutoIncrementSeed = 0 myDataColumn.AutoIncrementStep = 1 myDataColumn.MaxLength = -1 myDataColumn.ReadOnly = True myDataColumn.Unique = True Reading from the Order Details DataTable: myPrimaryKey = OrderID myPrimaryKey = ProductID myConstraint.IsPrimaryKey = True myDataColumn.ColumnName = OrderID myDataColumn.ColumnName = ProductID myDataColumn.ColumnName = OrderID myDataColumn.DataType = System.Int32 myDataColumn.AllowDBNull = False myDataColumn.AutoIncrement = False myDataColumn.AutoIncrementSeed = 0 myDataColumn.AutoIncrementStep = 1 myDataColumn.MaxLength = -1 myDataColumn.ReadOnly = False myDataColumn.Unique = False myDataColumn.ColumnName = ProductID myDataColumn.DataType = System.Int32 myDataColumn.AllowDBNull = False myDataColumn.AutoIncrement = False myDataColumn.AutoIncrementSeed = 0 myDataColumn.AutoIncrementStep = 1 myDataColumn.MaxLength = -1 myDataColumn.ReadOnly = False myDataColumn.Unique = False myDataColumn.ColumnName = UnitPrice myDataColumn.DataType = System.Decimal myDataColumn.AllowDBNull = False myDataColumn.AutoIncrement = False myDataColumn.AutoIncrementSeed = 0 myDataColumn.AutoIncrementStep = 1 myDataColumn.MaxLength = -1 myDataColumn.ReadOnly = False myDataColumn.Unique = False 




Mastering C# Database Programming
Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver
ISBN: 0764596373
EAN: 2147483647
Year: 2003
Pages: 181

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net