Home

Data Binding

 

.NET Framework Data Binding Objects

 

Introduction

Consider the following table named StoreItems and that was created in a database named Exercise:

Store Items

 

Store Items

To present data to the user, we can use some familiar objects such as the data grid view, the text box, or the combo box. Although the data grid view is the most complex, one of the most complete, and one of the most aesthetic controls of the .NET Framework, it cannot suit every possible scenario. Besides, the data grid view is a .NET object, not a Win32 control. As you may know already from your familiarity with Microsoft Windows, the operating system provides many more controls than that. Most users are more familiar with those controls and they would appreciate if the database was presented to them through these controls. The Windows controls in the .NET Framework were created to suit various types of controls, not just databases. Because they were made as broad as possible, the Windows controls are not readily made to display data. They need an intermediary object that can transmit data to them.

Using a Data Reader

In Lesson 23, we saw that you could use a data reader to read the value of a table. Once the values have been read, to access these values, you first call the Read() method of the class. The values read by the data adapter are stored in an indexed property of the class. When accessing each value, you must remember the order in which they are listed in the table.

As you read and access each value by its index, you can retrieve it and do what you want with it. For example you can assign it to a Windows control to display to the user. Here is an example:

Imports System.Data.SqlClient

Public Class Exercise

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        Using Connect As SqlConnection = _
 		New SqlConnection("Data Source=(local);" & _
     				  "Database='Exercise';" & _
     				  "Integrated Security=SSPI;")

            Dim strItems As String = _
                "SELECT [Item Name] FROM StoreItems WHERE [Item Number] = '209457';"
            Dim Command As SqlCommand = _
  		New SqlCommand(strItems, Connect)

            Connect.Open()

            Dim rdr As SqlDataReader = Command.ExecuteReader()

            While rdr.Read()
                TxtMerchandiseDescription.Text = rdr(0)
            End While
        End Using
    End Sub

End Class

When reading the records of a table, as mentioned already, the data reader reads one record at a time and moves to the next. Before moving to the next record, you can access the values stored in the current record. To help with this, the columns of the table being read are stored in a collection and each column can be referred to with a numeric index. The first column has an index of 1. The second column has an index of 2, and so on. To retrieve the actual data stored in a column, you may need to know the type of information the column is holding so you can read it accurately.

Depending on the data type that a column was created with, you can access it as follows:

If the column holds the following data type Use the following method System.Data.SqlTypes Equivalent
bit GetBoolean()   GetSqlBoolean()
char, nchar GetChar()   GetSqlChar()
varchar, nvarchar GetString()   GetSqlString()
text, ntext GetString()   GetSqlString()
binary, varbinary GetBinary()   GetSqlBinary()
decimal GetDecimal() GetDouble() GetSqlDecimal()
float GetFloat()   GetSqlSingle()
int GetInt32()   GetSqlInt32()
money, smallmoney GetDecimal()   GetSqlDecimal()
bigint GetInt64()   GetSqlInt64()
datetime, smalldatetime GetDateTime()   GetSqlDateTime()
smallint, tinyint GetInt16() GetByte() GetSqlInt16()
 

When using one of the Get... or GetSql... methods, the compiler does not perform any conversion. This means that, before sending the data, you must convert the value read to the appropriate (and probably exact) format. For example, if you read a natural number from a column created with the tinyint data type, the compiler you use for your application would not perform or assume the conversion. For example, the value of a column created with tinyint must be read with GetByte() or GetSqlByte() and trying to use GetInt32() or GetSqlInt32() would throw an error. 

Using a Data Adapter

In Lesson 34, we saw that you could obtain data from a table and use it in your database. One way you could do this consists of using a data adapter. This is possible because a data adapter uses a command performed on a database. The command could perform the action of selecting data from a table. The data adapter then gets this data and fills a data set with it.

After a data set has been filled, the records are available. Through the features of a data set, you can access the table(s), its(their) column(s), and its(their) record(s). You can either create a new record, check the existence of a record, delete an existing record, or edit/update a record.

Using Visual Database Objects

 

Using a Binding Source

After specifying the data source, you can use the binding source because it then holds a list. If you want a singly list-based control such as a list box, a combo box or a checked list box to use the list, these controls are equipped with a property named DataSource. This allows you to apply the list of a binding source directly to the control. To do this, assign the binding source to the DataSource property of the control. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Dim bsNames As BindingSource = New BindingSource
    Dim strNames As List(Of String) = New List(Of String)

    strNames.Add("Vicky Bisso")
    strNames.Add("Amy Warren")
    strNames.Add("Chrissie Childs")
    strNames.Add("Martial Otto")

    bsNames.DataSource = strNames
    lbxNames.DataSource = bsNames
End Sub

After this assignment, the control can display the list.

The Display Member of a List-Based Control

In some cases, the list is not single. For example, you may already know that a data set can contain one or more tables and a table can contain one or more columns. In this case, you must specify the sub-list or column that holds the values that you want the control to display. To support this scenario, the singly list-based controls are equipped with a property named DisplayMember.

Databases and Collection Classes

 

Introduction

In our introductory lessons to databases, we studied collections to get acquainted with lists. As you may imagine, a database is primarily a list of objects. As such, you can use a collection class in your database or even use a formal database in your collection-based application. To do this, we saw that you start by creating a connection to a database:

Connection

After establishing a connection to a database, you can use a command to specify the type of operation you want to perform, such as selecting records from the columns of a table, optionally using a condition. To get the data produced by the command, we saw that you could either use a data reader or a data adapter. If you use a data adapter, you can retrieve the values from the data adapter and store them in a data set object:

Data Connection to a Data Set

This is where, once again, a data set object becomes (very) valuable. Remember that a data set object contains one or more tables. Once you can identify a table from a data set, you can create a class that shares its structure, then use the data from that table and temporarily store it in a collection-based class:

Once the data is available in a collection class, you can manipulate it using a regular class you would have created. This allows you to use your knowledge of collections to explore a database.

Using a Collection-Based Class

As reviewed above, before using a regular class to manipulate a database, you should first create a class. To do this, you must know the structure of the table that will be represented by the class. This means that you should be familiar with the tables in the data set object. After creating the class, to get the values from a table, you can declare a variable from a collection-based class. Then, you can use the properties and methods of the collection class, just as we saw in previous lessons.

 

Home Copyright © 2008-2012 FunctionX