In this, the final part of her two part series on ADO.NET, Vaijayantee talks about the purpose of ADO.NET, scalability, XML, the ADOCommand and SQLCommand objects and more. If you're just new to .NET then Vaijayantee's ADO.NET tutorial series will have you up and running with databases in a short time.
All You Need To Know About ADO.NET: Part 2/2 - DataRelation Objects and Constraints (Page 4 of 6 )
A DataRelation object is also used to create and enforce the following constraints:
A unique constraint, which guarantees that a column in the table contains no duplicates.
A foreign-key constraint, which can be used to maintain referential integrity.
A unique constraint is implemented either by simply setting the Unique property of a data column to true, or by adding an instance of the UniqueConstraint class to the DataRelation object's ParentKeyConstraint. As part of the foreign-key constraint, you can specify referential integrity rules that are applied at three points:
when a parent record is updated
when a parent record is deleted
when a change is accepted or rejected
Handling Updates Unless the data used by the application is read only, we typically perform some modifications to the data or add to the data before sending it back to the data source or to another process or application. In windows Forms, the data binding architecture takes care of sending changes in the data bound controls to the DataSet. But, when the controls are not data bound, we have to send the changes in the records to the DataSet manually. Each record in a DataSet is represented by a DataRow object. The DataRows collection can be used for updating DataSet. If we update, add or delete existing rows, we will be updating the DataSet.
You can manipulate the collection directly to delete records. However, if you want to send changes to the database or to another process, the DataSet has to maintain change information for deleted rows. Data tables therefore provide special methods for deleting records so this information can be retained. If the DataSet includes constraints, you can temporarily turn constraints off before making any updates, and then turn them back on afterwards.
Updating Records The following example shows how to update the data in the first two columns of the fifth record of the first table in dataset1, accessing the data values with the tables, rows, and items (columns) collection indices:
[Note] The table in the example is Customers from Northwind. Field number 0 is customer ID and field number 4 is Company Name [End Note]
dataset1.Tables(0).Rows(4).Item(0) = "ABCDE" dataset1.Tables(0).Rows(4).Item(1) = "My Company"
We can also replace the indices with column names if we like. This code makes the necessary changes in the table.
Inserting a record A blank record is inserted by using the NewRow method. The new row inherits all of the columns from the concerned table's DataColumnCollection collection:
Dim anyRow as DataRow = ExistingTable.NewRow
The code shown above is an example of adding a row. A row can be added using the add method of the DataRowCollection. After the new blank record is added, it is treated like an existing record and updated in the same way.
Deleting a record A record is deleted using the Delete method of the DataRow object:
Merging two Datasets You can merge two Datasets by using Merge method of the DataSet object.
Typed vs. Untyped Datasets A DataSet can be Typed or Untyped. The difference between the two lies in the fact that a Typed DataSet has a schema and an Untyped DataSet does not have one. It should be noted that the Typed Datasets have more support in Visual studio.
A typed dataset gives us easier access to the contents of the table through strongly typed programming that uses information from the underlying data schema. A typed DataSet has a reference to an XML schema file:
Dim s As String s = dsCustomersOrders1.Customers(0).CustomerID
In contrast, if we are working with an untyped DataSet, the equivalent code looks like this:
Dim s As String s = _ CType(dsCustomersOrders1.Tables("Customers").Rows(0).Item("CustomerID"), String)
As the syntax is much simpler and more practical, using typed Datasets is much more handy.
XML Integration Extensible Markup Language (XML) provides a format for describing data. This facilitates more precise declarations of content and more meaningful search results across multiple platforms. In addition, XML enables the separation of presentation from data, just like in HTML where we use tags to tell the browser how to format the data. In XML, we use tags only to describe the data, such as CustomerID, CompanyName, etc. XML separates the data from the presentation and the process, enabling us to display and process the data as we wish by applying different style sheets and applications to it.
XML is at the core of many features of Visual Studio .NET and the .NET Framework. XML plays a large role in the way data is handled in Visual Studio .NET. XML is used in the following ways:
Datasets can serialize their data as XML. The schema of a DataSet is defined using an XML Schema (.xsd file).
We can infer XML Schemas from existing Datasets. We can also create Datasets from existing XML Schemas.
Visual Studio makes working with XML files, XML Schemas, and ADO.NET Datasets easy by providing us with a visual tool, the XML Designer.
The XML Designer The XML designer provides a set of visual tools for working with XML Schemas, ADO.NET Datasets, and XML documents. It supports the XML Schema Definition (XSD) language defined by the World Wide Web Consortium (WC3).
To display the designer, add a DataSet, XML Schema, or XML file to your project or open any of the file types listed below.
[Note] You can not use undo while working with XML Designer. [End Note]
Schema: For visually creating and modifying XML Schemas and ADO.NET datasets.
Data: For visually modifying XML data files in a structured data grid.
XML: For editing XML; the source editor provides color-coding and IntelliSense, including Complete Word and List Members.
Designing Datasets A DataSet is derived from an XML schema. An XML schema can:
Describe 'shape' of the DataSet. In other words, it tells if the data is related or hierarchical.
Validate the data being imported.
Establish the relational structure of database tables.
Let's create a sample DataSet with the XML designer. We will assume that we are creating a DataSet for a new table called MyTable:
Open a windows project.
Select Add New Item from File menu.
Select a DataSet and give it some name and then click open.
Select the XML tools tab and drag an element on the designer.
In place of 'Element1' type the name of the table (MyTable).
Go to the next row in the element. Click the dropdown in the 1st column and select 'Attribute'.
In the second column type the name of the attribute and select attribute type from the third column.
You can add all the columns you need. You can also drag 'Key' from the toolbox and define a key for your table.
Save the schema.
Now, if you want another table, just follow the same steps over.