Unlock Microsoft Access data by using Ado.net (ii)

access|ado| Data Data Form Wizard

The Visual Studio. NET Data Form Wizard allows you to quickly connect to a database and generate a simple user interface for displaying and interacting with data. To use the Data Form Wizard, follow these steps:
In the Windows application project, on the Project menu, click Add Windows Form (add Windows Forms).
In the Templates pane, click Wizard (Data Form Wizard), click Open, and then click Next.
In the Create a new dataset named box, type DataSet1, and then click Next.
Select an existing data connection or create a new data connection, and then click Next.
Select the items you want to populate with the DataSet1, and then click Next.
If you select multiple items, define the relationships between the items, and then click Next.
Define the tables and columns to be displayed in the form, and then click Next.
Select the display style option, and then click Finish.
Note: Make sure that the new data form is set to start the form as follows: On the Project menu, click Properties. Expand the Common Properties folder, click General, select the data form in the Startup object list, and then click OK.
Note: Be sure to bind the data in DataSet1 to a data form, which can be implemented by inserting the following code into the data form's Load event: Me.OleDbDataAdapter1.Fill (OBJDATASET1)
Run the application: On the Debug menu, click Run.
Extended Ado.net code example

To show you more Ado.net code, at the end of this month's column, I'll show you a few code examples of extensions created in Ado.net.
Use the DataReader object to access data in a forward-only, read-only format

Most of the time, you just want to simply browse the data without having to navigate back and forth between the data, or change the data (which we call "running data"). Ado.net's DataReader objects are designed specifically for this purpose. Here's a sample code I wrote to read all the selected data in the connected database one at a time:
' Visual Basic. NET code.
' Console application.
Reference
' System
' System.Data
' System.Xml

Imports System.Data.OleDb ' for OLE DB objects.
Imports Microsoft.VisualBasic.ControlChars ' for CrLf constants.

Module Module1

Sub Main ()

' Create and initialize OleDbConnection, OleDbCommand
' and OleDbDataReader objects.
Dim objconn as New _
OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User id=admin;" & _
"Data source=c:\program Files\Microsoft" & _
"Office\Office10\Samples\Northwind.mdb")

objConn.Open ()

' Executes the command and attaches the data reader to the
' The selected data.
Dim objcmd as New OleDbCommand ("SELECT * FROM Products", _
objconn)
Dim objreader as OleDbDataReader = Objcmd.executereader

' Read the data and list the values.
Call ReadData (objreader)

End Sub

Public Sub ReadData (ByVal objreader as OleDbDataReader)

' Purpose: Lists the data values for a given data reader.
' Accept: objreader-Data reader.

Dim Intfield the current field in the As Integer row.
Dim Intcolumn as Integer ' the name of the current column.
Dim blncolumns as Boolean = False ' column name is
Listed

With Objreader

' Read one row at a time until the end of the file.
Do While. Read = True

For Intfield = 0 to. FieldCount-1

' Lists the column names first.
If blncolumns = False Then

For intcolumn = 0 to. FieldCount-1

If Intcolumn =. FieldCount-1 Then
Console.Write (. GetName (Intcolumn) & _
CRLF)
Else
Console.Write (. GetName (Intcolumn) & _
", ")
End If

Next Intcolumn

' Lists the column names only once.
Blncolumns = True

End If

' Lists the values for each field in the current row.
If Intfield =. FieldCount-1 Then
Console.Write (. Item (Intfield) & CrLf)
Else
Console.Write (. Item (Intfield) & ",")
End If

Next Intfield

Loop

End With

' Pause so that the user can view the data in the console window.
Console.Write ("Press any key to continue ...")
Console.read ()

End Sub

End Module


The following is how the code works:
As the preceding code example shows, the Imports code (for example, the Imports System.Data.OleDb) helps reduce the amount of work required to access the objects or members of an object. Similarly, OleDbConnection, OleDbCommand, and OleDbDataReader objects are declared and initialized to represent database connections, data logging, and record cursors, respectively. The real core of this code is the ReadData subroutine.
The Do loop reads one row of data at a time using the Read method of the OleDbDataReader object until the method returns False (indicating that no other data is readable).
The FieldCount property of the OleDbDataReader object returns the number of data fields (columns) in the data row. If this is the first row of data, the GetName property of the OleDbDataReader object is called on the column to return the name of the column.
The Item property of the OleDbDataReader object, combined with the FieldCount property, returns the value of each data field in the data row using an index number.
Working with datasets using DataAdapter, Datasets, DataTable, DataRow, and DataColumn objects

The DataSet object for the Ado.net is designed for disconnected data. You can simulate the structure and data of an entire database in a dataset, including tables, rows, columns, fields, and even relationships. After you have processed the data, you can synchronize the data in the dataset with the data in the original database. Here's a sample code I wrote to read all the data in a disconnected dataset one at a time.
' Visual Basic. NET code.
' Console application.
Reference
' System
' System.Data
' System.Xml

Imports System.Data.OleDb ' for OLE DB objects.
Imports Microsoft.VisualBasic.ControlChars ' for CrLf constants.

Module Module1

Sub Main ()

' Create and initialize OleDbDataAdapter and DataSet objects.
Dim Objadapter as New OleDbDataAdapter _
("SELECT * FROM Products", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User id=admin;" & _
"Data source=c:\program Files\Microsoft" & _
"Office\Office10\Samples\Northwind.mdb")
Dim Objdataset as New DataSet ()

' Copy the selected data to the dataset.
Objadapter.fill (Objdataset)

' Lists the data values in the dataset.
Call Listdata (Objdataset)

End Sub

Public Sub Listdata (ByVal objdataset as DataSet)

' Purpose: To list the data values for a given dataset.
' Accept: Objdataset-DataSet.

Dim objtable as DataTable
Dim introw, Intcolumn as Integer

' A dataset may contain more than one table.
For each objtable in Objdataset.tables

With objtable

Console.Write ("Table" & _
. TableName Data "&": "& CrLf"

' Lists the column names first.
For intcolumn = 0 to. Columns.count-1

If Intcolumn =. Columns.count-1 Then
Console.Write (. Columns (Intcolumn). ColumnName _
& CrLf)
Else
Console.Write (. Columns (Intcolumn). ColumnName _
& ",")
End If

Next Intcolumn

' Press the row and column data ...
For introw = 0 to. Rows.count-1

' ... The data in each row is then listed by field.
For intcolumn = 0 to _
. Rows (introw). Itemarray.length-1

If Intcolumn = _
. Rows (introw). Itemarray.length-1 Then
Console.Write (. Rows (introw). _
ItemArray (Intcolumn) _
& CrLf)
Else
Console.Write (. Rows (introw). _
ItemArray (Intcolumn) & ",")
End If

Next Intcolumn

Next introw

End With

Next objtable

' Pause so that the user can view the data in the console window.
Console.Write ("Press any key to continue ...")
Console.read ()

End Sub

End Module


The following is how the code works:
Similarly, the Imports code (for example, the Imports System.Data.OleDb) helps reduce the amount of work required to access the objects or members of an object. Similarly, the OleDbDataAdapter and dataset objects are declared and initialized, representing the adapters and datasets between the database and the dataset respectively. The Filldata method of the OLEDBAdapter object copies the data from the database to the dataset. The core of this code is the Listdata subroutine.
By using the Tables property of the DataSet object, the outermost for ... Each loop traverses each table in the dataset and returns each table as a DataTable object.
The Columns property of a DataTable object returns a DataColumnCollection that represents all the columns in the table. A DataColumn object is returned by combining the Columns property with an index number (that is, a combination of the Count property of the DataColumnCollection collection, as shown in the code). The ColumnName property of the DataColumn object returns the name of the column.
Similarly, the Rows property of a DataTable object returns a DataRowCollection that represents all the rows in the table. A DataRow object is returned by combining the Rows property with an index number (that is, the Count property of the DataRowCollection collection, as shown in the code). The ItemArray property of the DataRow object returns an array of type Object that represents each value in the data row. A single data field value is returned by combining the ItemArray property with an index number (that is, a combination of the Count property of the DataColumnCollection collection, as shown in the code).