Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

ADO.NET Is Fun (Part 2)

DZone's Guide to

ADO.NET Is Fun (Part 2)

Learn a variety of techniques to connect, read, and write data, including serialization, deserialization, and more.

· Database Zone
Free Resource

Traditional relational databases weren’t designed for today’s customers. Learn about the world’s first NoSQL Engagement Database purpose-built for the new era of customer experience.

In part 1, I introduced some classes, such as DataTable, DataColumnDataRow, and DataViewand I also explained how to use some methods and properties. In this article, I will introduce other aspects we'll be working with, such as the DataRelation and DataSet classes, constraints, serializing, and deserializing and more with through a simple application using the Visual Basic language. I'm also assuming you know a bit about relational databases.

My application has a form that looks like this:

Image title

The DATA button (in the above form) is used to load data from an XML file. When I click this button, my form will look like this:

Image title

DataSet and DataRelation Objects

Before introducing some other functions in my form, I will stop in here to explain a couple of things. Let’s look at the above form again. You can see that I have used two DataGridViews to display data, which I get from two tables — the Genre table and the Review table. The properties and the relationship between the Genre and the Review tables can be displayed in the below figure:

Image title

Creating the Genre table or the Review table is very easy (you can see how to do it in part 1), but before creating a relationship between them, you must put them in a same container or DataSet. We can think about a DataSet like a relational database, but simpler. When two tables are put in a DataSet, you can set a relationship between them with the DataRelation object. All of this can be displayed like this:

Image titleYou can look at the following code that I have used to create DataSet objects, tables (Genre and Review), and DataRelation objects in my application:

Creating a DataSet :

 Dim ds As New DataSet("myDataSet")


Creating the Genre table and putting it in the myDataSet:

Dim gen As DataTable = ds.Tables.Add("Genre")


Creating the Review table and putting it in the myDataSet:

Dim review As DataTable = ds.Tables.Add("Review")


Creating properties for the tables:

' Properties for the Genre table
gen.Columns.Add("Id", GetType(String))
gen.Columns.Add("Name", GetType(String))
gen.PrimaryKey = New DataColumn() {gen.Columns("Id")}
' Properties for the Review table
review.Columns.Add("Id", GetType(String))
review.Columns.Add("Title", GetType(String))
review.Columns.Add("Summary", GetType(String))
review.Columns.Add("genId", GetType(String))
review.PrimaryKey = New DataColumn() {review.Columns("Id")}


Setting a relationship between the Genre table and the Review table:

ds.Relations.Add("gen_review", gen.Columns("Id"), review.Columns("genId"))


OK. Now we have the DataSet, which contains two tables, and we have also connected the tables with the DataRelation object.

Constraints

When two tables are connected, they must accept some rules or constraints. We can see the table below describes some rules:

Rule

Description

Cascade

Default. Deletes or updates the child DataRow objects when the DataRow object is deleted or its unique key is changed. This is the default behavior.

None

Throws an InvalidConstraintException if the parent DataRow object is deleted or its unique key is changed.

SetDefault

Sets the foreign key column(s) value to the default value of the DataColumn object(s) if the parent DataRow object is deleted or its unique key is changed.

SetNull

Sets the foreign key column(s) value to DBNull if the parent DataRow object is deleted or its unique key is changed.


The following code and figures will demonstrate the rules in my application.

Let’s looks at the tables with Cascade rule.

Image title

When I click the Delete button to delete the row that has Id = "01" of the Genre table, the rows of Review table related to this row are also deleted

Image title

Code for Setting the Cascade Rule

Dim fk As ForeignKeyConstraint = review.Constraints("gen_review")
fk.DeleteRule = Rule.Cascade

The tables are connected with the None rule, and when I click the Delete button to delete the row that has Id = "01" of the Genre table, an exception appears:

Image title

Image title

Code for Setting the None Rule

Dim fk As ForeignKeyConstraint = review.Constraints("gen_review")
fk.DeleteRule = Rule.None

The tables are connected with SetDefault rule, and when I click the Delete button to delete the row that has Id = "01" of the Genre table, the rows of Review table related to this row are also changed at the genId column.

Image title

Image title

Code for Setting the SetDefault Rule

Dim fk As ForeignKeyConstraint = review.Constraints("gen_review")
fk.DeleteRule = Rule.SetDefault

The tables are connected with the SetNull rule, and when I click the Delete button to delete the row that has Id ="01" of the Genre table, the rows of Review table related to this row are also changed at the genId column.

Image title

Image title

Code for Setting the SetNull Rule

Dim fk As ForeignKeyConstraint = review.Constraints("gen_review")
fk.DeleteRule = Rule.SetNull

Code for the Delete Button of the Genre Table

Dim table As New DataTable("table")
table = ds.Tables("Genre")
Dim n = ds.Tables("Genre").Rows.Count
For i = 0 To n - 1
    If table.Rows(i)("Id") = txt_id.Text Then
         table.Rows(i).Delete()
         Exit Sub
    End If
Next
MsgBox("Sorry! Can not find this genre.")

Serializing and Deserializing

We can save the content of a data table (or a dataset) to an XML file or stream. This process is serializing, and we also get content from an XML file or stream for updating content of a data table (or a dataset). This process is deserializing. These processes (in my application) can be visualized like this:

Image title

With XML files, I used the WriteXML method for serializing and the ReadXML method for deserializing. Here's the code in my application:

' write to xml file (with schema) - serializing
gen.WriteXml(XMLName("Gen.xml"), XmlWriteMode.WriteSchema)
review.WriteXml(XMLName("Review.xml"), XmlWriteMode.WriteSchema)
' read xml file into table - deserializing
gen.ReadXml(XMLName("Gen.xml"))
review.ReadXml(XMLName("Review.xml"))
Private Function XMLName(ByVal fileName As String) As String
  ' method for creating local disk file names
   Return Path.Combine("I:\\", fileName)
End Function

Conclusion

In this article, I have introduced  DataSet,  DataRelation , and some techniques such as serializing and deserializing. You can download the full source code of my application here. I hope you find my article helpful!

Learn how the world’s first NoSQL Engagement Database delivers unparalleled performance at any scale for customer experience innovation that never ends.

Topics:
database ,ado.net ,serialization ,deserialization

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}