Chapter 22: Handling Databases in Code

Main Page

    The OleDbConnection Class
    The SqlConnection Class
    The OleDbCommand Class
    The SqlCommand Class
    The OleDbDataAdapter Class
    The SqlDataAdapter Class
    The DataSet Class
    The OleDbDataReader Class
    The SqlDataReader Class
    The DataTable Class
    The DataRow Class
    The DataColumn Class
    The DataRelation Class
    Immediate Solutions: Using the OleDbConnection Class
    Using the SqlConnection Class
    Using the OleDbCommand Class
    Using the SqlCommand Class
    Using the DataAdapter Class
    Using the DbDataAdapter Class
    Using the OleDbDataAdapter Class
    Using the SqlDataAdapter Class
    Using the DataSet Class
    Using the OleDbDataReader Class
    Using the SqlDataReader Class
    Using the DataTable Class
    Using the DataRow Class
    Using the DataColumn Class
    Using the DataRelation Class
    Creating a Dataset in Code
    Creating a Data Connection in Code
    Creating a Command Object in Code
    Creating a Data Adapter in Code
    Creating a Data Table in Code
    Creating Data Columns in Code
    Creating Data Rows in Code
    Accessing Individual Data Items
    Looping over all Tables in a Dataset
    Writing Datasets to XML and Reading Datasets from XML
    Using a Data Reader
    Creating a Data Relation Object in Code

In Depth

In this chapter, we're going to focus on working with databases in code. Generally speaking, Visual Basic offers two ways to work with databases—visually and in code. In the preceding two chapters, we've largely seen the visual way at work. Now we're going to focus on doing it all in code.

We're going to work with ADO.NET objects directly in Visual Basic code, creating connections, data adapters, data tables, datasets, and more directly. We'll see how to construct tables from scratch and how to place those tables in datasets. We'll see how to connect to a database by creating our own connection objects, how to place SQL statements in data adapters, and how to execute that SQL in the database. We'll see how to work with data readers, and how to set up data relations between datasets. Here are the objects we'll work with in this chapter:

• Connection objects

• Command objects

• Data adapter objects

• Dataset objects

• Data Reader objects

• Data Table objects

• Data Row objects

• Data Column objects

• Data Relation objects

• The OleDbConnection Class

In the In Depth section of this chapter, I'll take a look at each of these objects and how to work with them in code. And we'll put them all to work in the Immediate Solutions section of the chapter. I'll start with data connections; to work with a database in a data provider, you first need a connection to that database. You can use objects of the OleDbConnection class and SqlConnection class to create that connection.