The DataSet is the central component in disconnected data access architecture. The ADO.NET data architecture uses DataSet objects for the in-memory representation of data. DataSet objects can be filled by DataAdapter objects, or you can create new DataSet objects by reading data from XML or a flat file. You can also create new DataSet objects programmatically by adding new rows.
The data held in DataSet objects can be bound to and displayed in the user interface, updated, and used to update a database through a DataAdapter. In this lesson, you will learn how to create DataSet objects programmatically, how to add DataRelation objects to your DataSet objects, and how to update databases from a DataSet.
A DataSet is an in-memory representation of data. It is, in essence, a copy of part or all of a database, stored locally on the client machine. Accessing the data contained in a DataSet does not require interaction with the database, and changes to the data stored in the DataSet have no effect on the database until such time as the database is updated.
In the previous lesson, you learned how to fill a DataSet from a DataAdapter. Although this is the primary way to create DataSet objects, you might need to access data that is stored in some other way, such as in an array of values or in a comma-delimited text file. In this section, you will learn alternative ways of creating and filling DataSet objects.
To create a DataSet programmatically
Declare and instantiate the DataSet.
Declare and instantiate one or more DataTable objects and add them to the Tables collection of the DataSet.
In each DataTable, declare and instantiate DataColumn objects to represent the columns of data in the table and add them to the Columns collection of the DataSet.
Create a new row of data using the DataTable.NewRow method.
Fill the new row with data.
Add the new row to the Rows collection of the DataTable.
Repeat Steps 4-6 until all of the data has been added.
Accessing Flat Files
In many legacy applications, data is stored in text files. It might be necessary to read data from these files into an ADO.NET DataSet. Using the general scheme described in the previous section, you can read data from text files into a DataSet at run time. The System.IO namespace facilitates file access, and the methods exposed by the System.String class enable parsing of the data contained in text files.
When reading data from a text file, you must first determine how the data is stored. Data stored in text files is usually separated by a common character called a delimiter. A delimiter can be a comma (,), a colon (:), a semicolon (;), or any other character. Rows of data are often stored as lines of text with a carriage return signifying the beginning of a new row. The number of entries in each row should correspond to the number of columns in the table.
Once the basic structure of the stored data has been determined, you can begin to construct your DataSet around it. You can use the techniques described in the previous section to programmatically construct your DataSet. You must create a single DataTable for each table of data you want to represent.
Next, you should add the appropriate number of columns to your DataSet. Sometimes, the data in the first row of the file is used as the names of the columns. If this is the case, you should read the first line of the text file and name the columns programmatically.
A text file can be read with the System.IO.StreamReader class. This class can open a file and return the characters represented within it. Once the file has been opened, you can use the method of the System.String class to separate the data entries and add them to new data rows.
Because every flat file could conceivably have a different format, you must determine the correct procedure for accessing the data on an individual basis. The following example demonstrates how to access data from a simple, common scenario: data stored in a text file where the rows are represented by lines and the entries are delimited by commas.
DataRelation Objects
A DataRelation object represents a relationship between two columns of data in different tables. For example, you might have a Customers table and an Orders table, each of which contain a CustomerID column.
Each customer would only be listed once in the Customers table, but might be listed multiple times in the Orders table. The CustomerID in the Orders table specifies which customer in the Customers table placed the order. Thus, the CustomerID field of the Orders table directly refers to a particular row of data in the Customers field and can be used as a key to retrieve that row.
Likewise, the CustomerID column in the Customers table can be used to retrieve any relevant rows in the Orders table. This is an example of a one-to-many relationship, where a unique identifier in one table might indicate multiple rows in another table. It is the most common type of data relationship.
You can use a DataRelation object to create this kind of relationship between two tables in a DataSet. The DataRelation objects of a particular DataSet are contained in the Relations property of the DataSet. A DataRelation is created by specifying the name of the DataRelation, the parent column, and the child column. In a typed DataSet, both columns must contain the same type.
To create a new DataRelation
Declare and instantiate a new DataRelation object that specifies a parent column and a child column within the same DataSet.
If you are using a typed DataSet, the two columns must contain the same type.
Add the new DataRelation to the Relations collection of the DataSet.
Retrieving Related Records
You can use a DataRelation to retrieve parent and child rows. Related rows are retrieved by calling the GetChildRows or GetParentRow methods of a DataRow. These methods require a valid DataRelation object as a parameter. The GetChildRows method returns an array of rows that have a child relationship as defined by the DataRelation object.
The GetParentRow method is similar, but returns only a single row that has a parent relationship as defined by the DataRelation object. The following example demonstrates how to call these methods.
It assumes two tables called Customers and Orders that are related through a DataRelation named CustomersOrders. Both tables are contained within a DataSet called myDataSet.
Constraints
DataRelation objects work closely with constraints. Constraints define the rules by which data is added to and manipulated in DataTables. There are two kinds of constraints: the UniqueConstraint and the ForeignKeyConstraint.
The UniqueConstraint specifies that a column or columns should have no duplicate entries. It is usually used to define a primary key for a table. The ForeignKeyConstraint defines the rules used to update child rows when a parent row is edited.
Constraints are created in the following general manner: a constraint of the appropriate type is instantiated and then added to the Constraints collection of the table that contains the constrained column.
Constraints are enforced only when the EnforceConstraints property of the DataSet is set to true.
Editing and Updating Data
Data contained within a DataSet can be manipulated and edited in the client. Values in DataRow objects can be changed, new DataRow objects can be added to a DataSet, or DataRow objects can be deleted from a DataSet. No changes, however, are reflected in the database until the database is updated through the DataAdapter.
Every DataSet maintains two versions of itself. The current version, which holds the client copy of the DataSet and any changes that have occurred, and the original version, which holds the state the data was in when the DataSet was first filled. When the Update method of the DataAdapter is called, the original values are used to generate the UPDATE, INSERT, and DELETE commands that are used to perform the database update.
Editing Data
Data contained in a DataSet can be edited two ways: through data-bound controls in the user interface or programmatically. Changes made through data-bound controls are automatically reflected in the relevant row.
Transactions
At times, you might want to execute several updates in such a way that either all succeed or none succeed. A common example would be a banking program. Consider a method that debits an account in one table and credits an account in a second table. If the debit operation was successful, but the credit operation failed, the results would be disastrous. Problems of this nature can be solved by employing transactions. A transaction is a set of related operations that execute as a unit. Either they are all successful or none are.
You initiate a transaction by calling the BeginTransaction method of any open Connection object. This method returns a reference to the transaction. You must then assign this transaction to the Transaction property of each command involved in the transaction. Next, you must execute each command in the transaction.
If the result of each command is satisfactory, you can call the Transaction.Commit to commit the changes to the database. If an error occurs, you can call Transaction.Rollback to roll back the changes and address the error.
To execute multiple commands transactionally
Open the database connection.
Obtain a reference to a transaction by calling the Connection.BeginTransaction method.
Assign this transaction to the Transaction property of each command you want to execute in the transaction.
Execute each transaction.
Call the Transaction.Commit method to commit the transaction.
If you are using DataAdapter objects to facilitate interaction with the database, you can still use transactions, although it is somewhat more complicated. You must create a transaction with the BeginTransaction method of an open connection.
You must then assign that transaction to the Transaction property of the InsertCommand, UpdateCommand, and DeleteCommand of each DataAdapter that will be involved in the database update. You can then call the Update method of each DataAdapter and call the Commit or Rollback method of the transaction as necessary.
Handling Update Errors
When updating data, errors can occur from a variety of causes. For example, attempting to duplicate a primary key causes an error, as does attempting to update a database that has been modified by another user. Regardless of the source of the error, you must anticipate and plan for possible errors when updating rows.
Both SqlDataAdapters and OledbDataAdapters provide a RowUpdated event that fires after a row update has been attempted but before any exception has been thrown. You can write code in this event to handle any update errors that might occur without going through resource-intensive exception handling blocks.
The RowUpdated event provides an instance of SqlRowUpdatedEventArgs or OleDbRowUpdatedEventArgs, depending on the type of DataAdapter you are using. These event arguments provide information that can be used to determine the error that occurred and how to proceed.
In the RowUpdated event handler, you can determine if an error occurred by examining the Status property of the event arguments. The Status property has four possible settings:
Continue.
Means that the DataAdapter is to continue processing rows. If no errors have occurred, the Status property will have this setting.
ErrorsOccurred. Indicates that one or more errors occurred while attempting to update this row.
SkipAllRemainingRows.
Indicates that updates for the current row and any remaining rows should be skipped.
SkipCurrentRow. Indicates that the update for the current row should be skipped, but the rest of the updates should proceed normally.
If an error has occurred, the Status property will have a value of ErrorsOccurred. You can choose to handle the error in the RowUpdated event handler by setting the Status property to another value. If the Status property is set to SkipCurrentRow, the DataAdapter will skip the update for the row that caused the error and proceed normally with the rest of the updates.
If the Status property is set to SkipAllRemainingRows, the DataAdapter will abort the rest of the update. If the Status property is set to Continue, the DataAdapter will ignore the error and continue. Note that this can cause unpredictable results depending on the data source. If the Status property remains ErrorsOccurred, the exception will be thrown and forwarded to the application's exception handling procedure.
You can obtain information about the error that occurred with the Errors property. The Errors property returns the exception that represents the error that occurred.
How you choose to handle database update errors depends largely upon the individual circumstances of your application. In some cases, you might want exceptions to move up to the application exception handling code, and in others, you might want to handle errors in the RowUpdated event handler.
related post
DAY 11 OOPS INTRODUCTION
DAY 12 POLYMORPHISM
DAY 13 INHERITANCE AND POLYMORPHISM
DAY 14 EBUGGING TOOLS IN DOT NET
DAY 15 DEBUG AND TRACE IN CLASSES
DAY 16 UNIT TEST PLAN
DAY 17 EXCEPTIONS IN VISUAL STUDIO
DAY 19 ADO.NET INTRODUCTION
DAY 20 DATA ACCESSING IN DOT NET
DAY 21 DATA BASE OBJECTS
The data held in DataSet objects can be bound to and displayed in the user interface, updated, and used to update a database through a DataAdapter. In this lesson, you will learn how to create DataSet objects programmatically, how to add DataRelation objects to your DataSet objects, and how to update databases from a DataSet.
A DataSet is an in-memory representation of data. It is, in essence, a copy of part or all of a database, stored locally on the client machine. Accessing the data contained in a DataSet does not require interaction with the database, and changes to the data stored in the DataSet have no effect on the database until such time as the database is updated.
In the previous lesson, you learned how to fill a DataSet from a DataAdapter. Although this is the primary way to create DataSet objects, you might need to access data that is stored in some other way, such as in an array of values or in a comma-delimited text file. In this section, you will learn alternative ways of creating and filling DataSet objects.
To create a DataSet programmatically
Declare and instantiate the DataSet.
Declare and instantiate one or more DataTable objects and add them to the Tables collection of the DataSet.
In each DataTable, declare and instantiate DataColumn objects to represent the columns of data in the table and add them to the Columns collection of the DataSet.
Create a new row of data using the DataTable.NewRow method.
Fill the new row with data.
Add the new row to the Rows collection of the DataTable.
Repeat Steps 4-6 until all of the data has been added.
Accessing Flat Files
In many legacy applications, data is stored in text files. It might be necessary to read data from these files into an ADO.NET DataSet. Using the general scheme described in the previous section, you can read data from text files into a DataSet at run time. The System.IO namespace facilitates file access, and the methods exposed by the System.String class enable parsing of the data contained in text files.
When reading data from a text file, you must first determine how the data is stored. Data stored in text files is usually separated by a common character called a delimiter. A delimiter can be a comma (,), a colon (:), a semicolon (;), or any other character. Rows of data are often stored as lines of text with a carriage return signifying the beginning of a new row. The number of entries in each row should correspond to the number of columns in the table.
Once the basic structure of the stored data has been determined, you can begin to construct your DataSet around it. You can use the techniques described in the previous section to programmatically construct your DataSet. You must create a single DataTable for each table of data you want to represent.
Next, you should add the appropriate number of columns to your DataSet. Sometimes, the data in the first row of the file is used as the names of the columns. If this is the case, you should read the first line of the text file and name the columns programmatically.
A text file can be read with the System.IO.StreamReader class. This class can open a file and return the characters represented within it. Once the file has been opened, you can use the method of the System.String class to separate the data entries and add them to new data rows.
Because every flat file could conceivably have a different format, you must determine the correct procedure for accessing the data on an individual basis. The following example demonstrates how to access data from a simple, common scenario: data stored in a text file where the rows are represented by lines and the entries are delimited by commas.
DataRelation Objects
A DataRelation object represents a relationship between two columns of data in different tables. For example, you might have a Customers table and an Orders table, each of which contain a CustomerID column.
Each customer would only be listed once in the Customers table, but might be listed multiple times in the Orders table. The CustomerID in the Orders table specifies which customer in the Customers table placed the order. Thus, the CustomerID field of the Orders table directly refers to a particular row of data in the Customers field and can be used as a key to retrieve that row.
Likewise, the CustomerID column in the Customers table can be used to retrieve any relevant rows in the Orders table. This is an example of a one-to-many relationship, where a unique identifier in one table might indicate multiple rows in another table. It is the most common type of data relationship.
You can use a DataRelation object to create this kind of relationship between two tables in a DataSet. The DataRelation objects of a particular DataSet are contained in the Relations property of the DataSet. A DataRelation is created by specifying the name of the DataRelation, the parent column, and the child column. In a typed DataSet, both columns must contain the same type.
To create a new DataRelation
Declare and instantiate a new DataRelation object that specifies a parent column and a child column within the same DataSet.
If you are using a typed DataSet, the two columns must contain the same type.
Add the new DataRelation to the Relations collection of the DataSet.
Retrieving Related Records
You can use a DataRelation to retrieve parent and child rows. Related rows are retrieved by calling the GetChildRows or GetParentRow methods of a DataRow. These methods require a valid DataRelation object as a parameter. The GetChildRows method returns an array of rows that have a child relationship as defined by the DataRelation object.
The GetParentRow method is similar, but returns only a single row that has a parent relationship as defined by the DataRelation object. The following example demonstrates how to call these methods.
It assumes two tables called Customers and Orders that are related through a DataRelation named CustomersOrders. Both tables are contained within a DataSet called myDataSet.
Constraints
DataRelation objects work closely with constraints. Constraints define the rules by which data is added to and manipulated in DataTables. There are two kinds of constraints: the UniqueConstraint and the ForeignKeyConstraint.
The UniqueConstraint specifies that a column or columns should have no duplicate entries. It is usually used to define a primary key for a table. The ForeignKeyConstraint defines the rules used to update child rows when a parent row is edited.
Constraints are created in the following general manner: a constraint of the appropriate type is instantiated and then added to the Constraints collection of the table that contains the constrained column.
Constraints are enforced only when the EnforceConstraints property of the DataSet is set to true.
Editing and Updating Data
Data contained within a DataSet can be manipulated and edited in the client. Values in DataRow objects can be changed, new DataRow objects can be added to a DataSet, or DataRow objects can be deleted from a DataSet. No changes, however, are reflected in the database until the database is updated through the DataAdapter.
Every DataSet maintains two versions of itself. The current version, which holds the client copy of the DataSet and any changes that have occurred, and the original version, which holds the state the data was in when the DataSet was first filled. When the Update method of the DataAdapter is called, the original values are used to generate the UPDATE, INSERT, and DELETE commands that are used to perform the database update.
Editing Data
Data contained in a DataSet can be edited two ways: through data-bound controls in the user interface or programmatically. Changes made through data-bound controls are automatically reflected in the relevant row.
Transactions
At times, you might want to execute several updates in such a way that either all succeed or none succeed. A common example would be a banking program. Consider a method that debits an account in one table and credits an account in a second table. If the debit operation was successful, but the credit operation failed, the results would be disastrous. Problems of this nature can be solved by employing transactions. A transaction is a set of related operations that execute as a unit. Either they are all successful or none are.
You initiate a transaction by calling the BeginTransaction method of any open Connection object. This method returns a reference to the transaction. You must then assign this transaction to the Transaction property of each command involved in the transaction. Next, you must execute each command in the transaction.
If the result of each command is satisfactory, you can call the Transaction.Commit to commit the changes to the database. If an error occurs, you can call Transaction.Rollback to roll back the changes and address the error.
To execute multiple commands transactionally
Open the database connection.
Obtain a reference to a transaction by calling the Connection.BeginTransaction method.
Assign this transaction to the Transaction property of each command you want to execute in the transaction.
Execute each transaction.
Call the Transaction.Commit method to commit the transaction.
If you are using DataAdapter objects to facilitate interaction with the database, you can still use transactions, although it is somewhat more complicated. You must create a transaction with the BeginTransaction method of an open connection.
You must then assign that transaction to the Transaction property of the InsertCommand, UpdateCommand, and DeleteCommand of each DataAdapter that will be involved in the database update. You can then call the Update method of each DataAdapter and call the Commit or Rollback method of the transaction as necessary.
Handling Update Errors
When updating data, errors can occur from a variety of causes. For example, attempting to duplicate a primary key causes an error, as does attempting to update a database that has been modified by another user. Regardless of the source of the error, you must anticipate and plan for possible errors when updating rows.
Both SqlDataAdapters and OledbDataAdapters provide a RowUpdated event that fires after a row update has been attempted but before any exception has been thrown. You can write code in this event to handle any update errors that might occur without going through resource-intensive exception handling blocks.
The RowUpdated event provides an instance of SqlRowUpdatedEventArgs or OleDbRowUpdatedEventArgs, depending on the type of DataAdapter you are using. These event arguments provide information that can be used to determine the error that occurred and how to proceed.
In the RowUpdated event handler, you can determine if an error occurred by examining the Status property of the event arguments. The Status property has four possible settings:
Continue.
Means that the DataAdapter is to continue processing rows. If no errors have occurred, the Status property will have this setting.
ErrorsOccurred. Indicates that one or more errors occurred while attempting to update this row.
SkipAllRemainingRows.
Indicates that updates for the current row and any remaining rows should be skipped.
SkipCurrentRow. Indicates that the update for the current row should be skipped, but the rest of the updates should proceed normally.
If an error has occurred, the Status property will have a value of ErrorsOccurred. You can choose to handle the error in the RowUpdated event handler by setting the Status property to another value. If the Status property is set to SkipCurrentRow, the DataAdapter will skip the update for the row that caused the error and proceed normally with the rest of the updates.
If the Status property is set to SkipAllRemainingRows, the DataAdapter will abort the rest of the update. If the Status property is set to Continue, the DataAdapter will ignore the error and continue. Note that this can cause unpredictable results depending on the data source. If the Status property remains ErrorsOccurred, the exception will be thrown and forwarded to the application's exception handling procedure.
You can obtain information about the error that occurred with the Errors property. The Errors property returns the exception that represents the error that occurred.
How you choose to handle database update errors depends largely upon the individual circumstances of your application. In some cases, you might want exceptions to move up to the application exception handling code, and in others, you might want to handle errors in the RowUpdated event handler.
related post
DAY 11 OOPS INTRODUCTION
DAY 12 POLYMORPHISM
DAY 13 INHERITANCE AND POLYMORPHISM
DAY 14 EBUGGING TOOLS IN DOT NET
DAY 15 DEBUG AND TRACE IN CLASSES
DAY 16 UNIT TEST PLAN
DAY 17 EXCEPTIONS IN VISUAL STUDIO
DAY 19 ADO.NET INTRODUCTION
DAY 20 DATA ACCESSING IN DOT NET
DAY 21 DATA BASE OBJECTS
No comments:
Post a Comment