.NET Tricks
.NET Tricks
How to establish a relationship between 2 tables within a DataSet
Because the DataSet can be considered its own personal little database, it's important for the DataSet to support a key concept behind relational databases: Table relationships. The DataSet can support multiple DataTable objects, and in some cases, you may need to relate two or more of those DataTable objects together. This is where the DataRelation class comes in. This class manages a DataTable's relationship with another DataTable by creating that relationship between two DataColumn objects. The following code shows a simple example of how to establish a relationship between two tables within a DataSet.
For C#:
System.Data.DataColumn oParentColumn; System.Data.DataColumn oChildColumn; oParentColumn = oDS.Tables["Customers"].Columns["CustomerID"]; oChildColumn = oDS.Tables["Orders"].Columns["CustomerID"]; System.Data.DataRelation oRelation = [ic:ccc]new System.Data.DataRelation("CustomerOrders", [ic:ccc]oParentColumn, oChildColumn); oDS.Relations.Add(oRelation);
For VB.NET:
Dim oParentColumn As System.Data.DataColumn Dim oChildColumn As System.Data.DataColumn oParentColumn = oDS.Tables["Customers"].Columns["CustomerID"]; oChildColumn = oDS.Tables["Orders"].Columns["CustomerID"]; Dim oRelation As System.Data.DataRelation = [ic:ccc]new System.Data.DataRelation("CustomerOrders", [ic:ccc]oParentColumn, oChildColumn) oDS.Relations.Add(oRelation)
How to create a custom view of the data within the Customers DataTable
The DataView is provided within the ADO.NET framework to allow filtering and sorting for a DataTable. This provides an interface for WebForms and WindowsForms for which controls may be bound. For instance, you could have a DataTable that contained an entire set of data and a DataView that provided a small subset of that data. This prevents having to make another round-trip to the server to get that information. The following example provides a simple method to create a custom view of the data within the Customers DataTable for all customers within the city of Berlin.
For C#:
System.Data.DataTable oTable = oDS.Tables["Customers"]; System.Data.DataView oView = new System.Data.DataView(oTable); oView.RowFilter = "City='Berlin'";
For VB.NET:
Dim oTable As System.Data.DataTable = oDS.Tables["Customers"] Dim oView As System.Data.DataView = new System.Data.DataView(oTable) oView.RowFilter = "City='Berlin'"
Loading an XML Document into a DataSet
Since XML is the key technology that enables DataSets to function, it's really a rather trivial task to load an XML document into a DataSet. Why would you ever need to do this? Sometimes it may be easier to access an XML document in a relational manner, rather than a hierarchical one. For these cases, Microsoft has included the ability to load XML documents directly into DataSets by calling a ReadXml() method. This method will accept two parameters, the first being the XML source and a second, optional parameter indicating how the XML document should be loaded into the DataSet. The following code demonstrates a simple example of loading an XML file, called Customers1.xml, into a DataSet.
For C#:
System.Data.DataSet oDS = new System.Data.DataSet(); oDS.ReadXml("http://localhost/sql2000/template/customers1.xml");
For VB.NET:
Dim oDS As System.Data.DataSet = new System.Data.DataSet(); oDS.ReadXml("http://localhost/sql2000/template/customers1.xml");
Notice that we elected not to use the second parameter in the above example. By not explicitly passing the second parameter, we leave it up to .NET to decide the best way to create the DataSet from the given XML document. Also, the first parameter itself can be an XML stream, XML document, or an XmlReader object as the ReadXml() method is overloaded. This gives the developer added flexibility in application design by allowing him to load XML into a DataSet from multiple sources.