More Information on installing the. Net Framework click here. Download full Visual Studio C. NET Example from this Article. NET object model is rich, but at its heart it is a fairly straightforward set of classes. The most important of these is the DataSet. The DataSet represents a subset of the entire database, cached on your machine without a continuous connection to the database. Periodically, you'll reconnect the DataSet to its parent database, update the database with changes you've made to the DataSet, and update the DataSet with changes in the database made by other processes.
This is highly efficient, but to be effective the DataSet must be a robust subset of the database, capturing not just a few rows from a single table, but also a set of tables with all the metadata necessary to represent the relationships and constraints of the original database.
This is, not surprisingly, what ADO. These are accessed as properties of the DataSet object. DataTables and DataColumns The DataTable can be created programmatically or as a result of a query against the database.
The DataTable has a number of public properties, including the Columns collection, which returns the DataColumnCollection object, which in turn consists of DataColumn objects. Each DataColumn object represents a column in a table. Each DataRelation represents a relationship between two tables through DataColumn objects. The nature of the relationship is one-to-many, or parent-to-child.
For any given order, there will be exactly one customer, but any given customer might be represented in any number of orders. Rows DataTable's Rows collection returns a set of rows for any given table. Use this collection to examine the results of queries against the database, iterating through the rows to examine each record in turn.
NET, you do not iterate through the DataSet; instead, access the table you need, and then you can iterate through the Rows collection, typically with a foreach loop. You'll see this in the first example in this chapter. Data Adapter The DataSet is an abstraction of a relational database.
DataAdapter provides the Fill method to retrieve data from the database and populate the DataSet. The DBConnection object represents a connection to a data source. This connection can be shared among different command objects. Often these objects are implicitly created when you create your DataSet, but you can explicitly access these objects.
This decouples the DataSet from the database and allows a single DataSet to represent more than one database or other data source.
NET architecture, providing both high-performance data access as well as scalability. Datasets store data in a disconnected cache. The structure of a dataset is similar to that of a relational database; it exposes a hierarchical object model of tables, rows, and columns. In addition, it contains constraints and relationships defined for the dataset. Two Step Process Updating a data source via a dataset is a two-step process.
The first step is to update the dataset with new information — new records, changed records, or deleted records. If you are updating a data source such as a database , the second step is to send the changes from the dataset to the original data source. That is, the process of updating the dataset does not also write the changes through to an underlying data source; you must explicitly perform this second step.
After changes have been made in a dataset, you can transmit the changes to a data source. Most commonly, you do this by calling the Update method of a data adapter. The method loops through each record in a data table, determines what type of update is required update, insert, or delete , if any, and then executes the appropriate command. How an Update Is Transmitted to the Data Source As an illustration of how updates are made, suppose your application uses a dataset containing a single data table.
The application fetches two rows from the database. After the retrieval, the in-memory data table looks like this: As a result of this change, the value of the DataRow. RowState property for that row changes from Unchanged to Modified.
The value of the RowState property for the other row remains Unchanged. The data table now looks like this: The method inspects each row in turn. For the first row, the method transmits no SQL statement to the database, because that row has not changed since it was originally fetched from the database. In a typical implementation, you might work your way through the following steps: Display the data in various DataTable objects within your DataSet by either binding to a control or looping through the rows in the tables.
Change data in individual DataTable objects by adding, modifying, or deleting DataRow objects. Invoke the GetChanges method to create a second DataSet that features only the changes to the data.
Check for errors in the second newly created DataSet by examining the HasErrors property. If the table has errors, invoke the GetErrors method of the DataTable and get back an array of DataRow objects with errors. On each row you can examine the RowError property for specific information about the error, which you can then resolve. Merge the second Data Set with the first.
This process gives you very fine control over the update to your data as well as an opportunity to fix any data that would otherwise cause an error. In the following example, we will create a dialog box that displays the contents of the Customer table in Northwinds. The goal is to test updating a record, adding a new record, and deleting a record.
The code is as simple as possible, which means eliminating many of the error-checking and exception-handling routines you might expect in a production program. This form consists of a list box, a button for Update, an associated text box, and a Delete button. There is also a set of eight text fields that are used in conjunction with the New button.
These text fields represent eight of the fields in the Customers table in the Northwind database. There is also a label at the bottom that you can use for writing messages to the user. Right now, we have only one: The InitializeCommands method creates the remaining three: InitializeCommands uses the AddParms method to associate a column in each SQL command with the columns in the modified rows: