Adding restrictions to datatable and datacolumn objects phần 2

Chia sẻ: Tuan Nghia | Ngày: | Loại File: PDF | Số trang:8

Thêm vào BST

Báo xấu

85
lượt xem 7
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

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.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Adding restrictions to datatable and datacolumn objects phần 2

• 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 /* 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); } } } } 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: