Adding Restrictions to DataTable and DataColumn Objects phần 1

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
Nội dung trích xuất từ tài liệu:
Adding Restrictions to DataTable and DataColumn Objects phần 1Adding Restrictions to DataTable and DataColumn ObjectsAs you know, a DataSet object is used to store a copy of a subset of the database. Forexample, you can store a copy of the rows from database tables into a DataSet, with eachtable represented by a DataTable object. A DataTable stores columns in DataColumnobjects.In addition to storing rows retrieved from a database table, you can also add restrictionsto a DataTable and its DataColumn objects. This allows you to model the samerestrictions placed on the database tables and columns in your DataTable andDataColumn objects. For example, you can add the following constraints to a DataTable: • Unique • Primary key • Foreign keyIn 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. Youll 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 yourDataSet to begin with. This helps reduce the errors when attempting to push changes inyour DataSet to the database. If a user of your program attempts to add data that violatesa restriction, theyll cause an exception to be thrown. You can then catch the exception inyour program and display a message with the details. The user can then change the datathey were trying to add and fix the problem.You also need to define a primary key before you can find, filter, and sort DataRowobjects in a DataTable. Youll 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 DataColumnobjects: • 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.Youll learn the details of both these techniques in the following sections.Adding the Restrictions YourselfYou can add restrictions to your DataTable and DataColumn objects yourself using theproperties of the DataTable and DataColumn objects.For example, assume you have a DataSet object named myDataSet that contains threeDataTable objects named Products, Orders, and Order Details that have been populatedusing the following code:SqlCommand mySqlCommand = mySqlConnection.CreateCommand();mySqlCommand.CommandText = SELECT ProductID, ProductName + FROM Products; + SELECT OrderID + FROM Orders; + SELECT OrderID, ProductID ...