Unlock Microsoft Access data using ado.net (1)

access|ado| data using ado.net to unlock Microsoft Access data
Paul Cornell
Microsoft Corporation
December 6, 2001

Microsoft®office provides a variety of features and tools for storing and managing data, such as data Access Objects (DAO), ActiveX® Data Objects (ADO), Microsoft Word Mail Merge, Microsoft Excel Web queries, Microsoft Query, data access pages, Microsoft Access data projects, Office Data Connections, Office data links, and so on. The Microsoft. NET platform provides additional data access features and tools, such as ado.net,. NET data connections, and database projects.

In this month's column, I'll explain how to use Microsoft Visual Studio®.net, Microsoft Visual basic®.net, and especially how to use ado.net to unlock and manage Microsoft Access data.

Determine when Ado.net is best for use
Since office already provides a range of data access features and tools, why not use Office to manage its own data? Of course, in many cases, you might want to use the built-in Office features and tools that are appropriate for a particular data task. However, Ado.net provides a unified approach to adding data connections and writing data solution code, unlike multiple data entry points and data access libraries in Office.

You can still invoke the ADO functionality in Visual Studio. NET and visual Basic. NET, if you wish. However, Ado.net is a better data access solution in many ways than ADO, given the following reasons.

Ado.net allows multiple tables to be included in the dataset, including the relationships between those tables. ADO allows only one result table to be included (although this single result table may be the result of a JOIN operation performed on multiple related tables).
Ado.net provides disconnected data access. ADO can also provide disconnected data access, but ADO is primarily designed for connected data access.
Ado.net provides a record navigation paradigm that allows for unordered data access (unlike an ordered ADO Move method) and can access individual data tables using relationships between data tables.
Because Ado.net uses XML to transmit data, it can provide richer data types than ADO, gain better data access performance, and pass data through the firewall.
Ado.net is highly optimized to match the use of Microsoft SQL Server 7.0 and Microsoft SQL Server 2000 databases (reflected in Ado.net SQL objects) while providing additional OLE DB data sources such as Microsoft Access database (reflected in the Ado.net OLE DB object).
Of course, if you want to take advantage of the many new features in. NET, you should use. NET data access features and tools. For more information about the. NET platform, see the previous column introducing. NET to Office developers (English).

Getting Started with Ado.net
Figure 1 shows the main objects to use when using Ado.net.



Figure 1:ado.net The main object in the object model (source: Inside. NET Managed Providers [English])

These objects include:

OleDbConnection and SqlConnection objects, representing database connections, are similar to the Connection objects of ADO.
OleDbCommand and Sqldbcommand objects that represent the SQL text strings that will be sent directly to the database, similar to the Command object for ADO.
OleDbDataAdapter and SqlDataAdapter objects that hold the SELECT, INSERT, UPDATE, and DELETE commands for the database. These objects can act as intermediaries between the database and the DataSet object. You can use the Fill method to populate the DataSet object and disconnect from the database, then reconnect to the database using the Update method, and save the changes made in the DataSet object back to the database.
A DataSet object contains a copy of the actual data, similar to a disconnected ADO Recordset object. The DataSet object can contain both the DataRelation object and the Contraint object, which represent the relationship between the data tables and the constraints of the data in those tables, respectively. The DataSet object can also contain a DataTable object that can contain both DataColumn and DataRow objects. Using the DataTableMapping object and the DataColumnMapping object, the dataset can map the tables and columns in the database to the corresponding DataTable objects and DataColumn objects.
The SqlDataReader object and the Oledbreader object represent connected, forward-only, read-only data readers. This is useful if you can keep a persistent connection to a database and want to read only data without making changes. In this case, you do not need to create a DataSet object. A data reader is similar to a connected, forward-only, read-only ADO Recordset object.
As a reference, in Office Visual Basic for Applications (VBA), which uses ADO, you can open the Northwind sample Access database by copying the data from the Products table to the Recordset object and reporting Some data in the first data record in the Recordset:

' Office VBA and ADO code-Adocode.bas.
Public Sub adoexample ()

' first set up a reference to the ADO library.
Dim objconn as ADODB. Connection
Dim objRS as ADODB. Recordset

Set objconn = New ADODB. Connection

objconn.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User id=admin;" & _
"Data source=c:\program Files\Microsoft" & _
"Office\Office10\Samples\Northwind.mdb"
objConn.Open

Set objRS = objConn.Execute ("SELECT * FROM Products")

Objrs.movefirst

MsgBox prompt:=objrs.fields ("ProductName"). Value & "," & _
Objrs.fields ("UnitsInStock"). Value

Objrs.close
Objconn.close

End Sub

If you have previously encoded in ADO, this ADO code is very simple:

Declares ADO ' Connection objects and Recordset objects that refer to the connection of the database and the underlying data of the database, respectively.
The Connection object's ConnectionString property is set to a database-specific String value that represents the information that is required to connect to the database (in this case, the Northwind database).
The Connection object's Open method establishes the actual database connection.
The Execute method of the Connection object retrieves data from the database and places the retrieved data into the Recordset object.
The MoveFirst method of the Recordset object moves to the first record in the recordset.
The Fields property of the Recordset object refers to a specific column in the record, and the Value property of the Field object retrieves the actual data in the field.
As a comparison, here are some Visual Basic. NET sample codes for console applications, which perform roughly the same operations using ado.net, but instead of putting data into a recordset, a ado.net data reader maintains a connection to the data:

' Visual Basic. NET and Ado.net code-Adonetcode.vb.
' Use a connected ado.net data access.
' Console application.
Imports System.Data.OleDb

Module Module1

Sub Main ()

' first set a reference to the System.Data.dll.
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 ()

Dim objCMD as New OleDbCommand _
("SELECT * FROM Products", objconn)
Dim Objdatareader as OleDbDataReader = Objcmd.executereader

Objdatareader.read ()
Console.Write (Objdatareader.item ("ProductName") & "," & _
Objdatareader.item ("UnitsInStock"))

End Sub

End Module

The following is how the code works:

Code Imports SYSTEM.DATA.OLEDB helps reduce the amount of work required to access the objects or members of an object. For example, although you can write ADODB in ADO. The Recordset code, but usually does not do so because the ADODB library is already referenced. Similarly, although System.Data.OleDb.OleDbConnection code can be written in ado.net, if the namespace associated with the OleDbConnection object has already been imported System.Data.OleDb (assuming we've already set up a reference to System.Data.dll), there's no need to encode.
In ADO, you must first declare an object of type Connection and then use the ConnectionString property to define the connection information. In. NET, we use the concept of parameterized constructors and initializers to declare objects of type OleDbConnection and define connection information in just one line of code.
Like ADO, in ado.net, you use the Open method of the OleDbConnection object to establish the actual database connection.
Next, declare the Ado.net OleDbCommand object to store the commands that retrieve data from the database.
Then declare the Ado.net OleDbDataReader object and read the data from the database.
By using the ExecuteReader method of the OleDbCommand object, the code allows the data reader to access specific data in the database.
The Read method of the OleDbDataReader object reads the first record in the database.
The Console.Write method writes data from a specific column (using the Item property of the Oledbreader object) to the Command window.
The following is another section of Visual Basic. NET Sample code that performs the same operations as the previous Ado.net example. In this example, however, the data is copied to the Ado.net dataset and then disconnected from the database:

' Visual Basic. NET and Ado.net code-Adonetcode.vb.
' Use disconnected ado.net data access.
' Console application.
Imports System.Data.OleDb

Module Module1

Sub Main ()

' first set a reference to the System.Data.dll.
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 ()

Dim Objadapter as New OleDbDataAdapter _
("SELECT * FROM Products", objconn)
Dim Objdataset as New DataSet ()

Objadapter.fill (Objdataset)

With Objdataset.tables ("Table"). Rows (0)

Console.Write (. Item ("ProductName") & "," _
&. Item ("UnitsInStock"))

End With

End Sub

End Module

The following is the difference between this code and the previous Ado.net example:

Similar to the OleDbDataReader object, the OLEDBAdapter object accesses data from the database, but uses the Fill method of the OLEDBAdapter object to copy the data tables and their relationships from the database to the DataSet object.
The Tables property of the DataSet object accesses all the tables of the dataset, while the Rows property accesses a single row of the given table in the dataset.
Can I use ado.net in Office VBA?
Although you may want to use the new features of ado.net in Office VBA, you cannot do so at this time. This is because if you run the RegAsm.exe (the Assembly Registration tool in the. NET frameworks SDK) to register System.Data.dll as COM (Office VBA is based on this technology) and then try to reference the generated type library files from office, you do not Displays any members in the Object Browser of Office Visual Basic. This is because COM does not recognize the core private ado.net members and parameterized constructors. So, at least for now, you can only continue to use ADO in Office VBA.

Using the Visual Studio. NET Data Access Tool
Visual Studio. NET provides a number of convenient tools for accessing Office data, including creating reusable data connections, database projects, and data form wizards.

Data connection
Data connections allow you to define the details of a database connection at once, and then reuse that information in multiple solutions. The following example describes how to create a reusable data connection in Visual Studio. NET:

On the Tools menu, click Connect to Database. The Data Link Properties dialog box is displayed.
On the Provider (Provider) tab, click the appropriate data provider, and then click Next. For example, to connect to the Northwind sample database for Access, click Microsoft Jet 4.0 OLE DB Provider (Microsoft Jet 4.0 OLE DB Provider).
Fill in the information in the Connection (Connection) tab of the selected data provider. For example, for the Northwind sample database, click next to select or enter a database name (select or enter the DB name) box ... button, browse to Northwind.mdb, and then click Open.
Click Test Connection (Testing the connection). If the connection succeeds, click OK (OK). Otherwise, please review the information on the Connection (Connection) tab.
Click OK to close the Data Link Properties dialog box.
This allows for a reusable data connection that you can add to a Visual Studio. NET project.

The following steps provide an example of how to create a Ado.net object that can interoperate with an existing data connection in the Server Explorer window:

In the Designer view of the Windows application project, on the View menu, click Server Explorer, expand the data connection that you want, and then drag the table, view, or stored procedure to the design interface. Visual Studio. NET creates a Oledbconnection/sqlconnection object (depending on the type of database), a Oledbdataadapter/sqldataadapter object, and four Oledbcommand/sqlcommand object that is used to select, INSERT, UPDATE, and delete data.
Right-click the Oledbdataadapter/sqldataadapter object and clicking the Generate DataSet (Generate DataSet). In this example, select New (new), leave the default setting for DataSet1, select the table to be added to the dataset, select the Add this dataset to the designer (add the dataset to the designer), and then click OK. Visual Studio. NET creates a DataSet object.
Right-click the Oledbdataadapter/sqldataadapter object, clicking Preview data, click the Fill DataSet (fill the dataset), and then click Close.
Note: The Fill DataSet (fill DataSet) button is not displayed in Visual Studio. NET Beta 2 to generate the code that actually fills the DataSet object. You may need to manually add code to the Form_Load or Page_Load event, as follows: Me.OleDbDataAdapter1.Fill (DATASETNAME11).
The following example describes how to add a data grid to a Windows form and bind to an existing dataset that was built in the previous example:

In Designer view, click the Windows Forms (Windows Forms) tab on the toolbar, and then double-click the DataGrid.
In the Properties window for the DATAGRID1, select DataSet11 for the DataSource property, and then select the table for the DataMember property.
Run the application.
If the data is not displayed in DataGrid1, add code similar to the following in the Form_Load event: Me.OleDbDataAdapter1.Fill (DATASETNAME11).
Tip: To create a Ado.net object without an existing data connection in the Server Explorer window, perform the following steps:
In Design view, click the Data tab on the toolbar. Note that this is not valid in the Code view.
Drag the Ado.net object to the design interface.
Right-click the Ado.net object, and clicking Properties.
Fill in the appropriate properties for the selected Ado.net object.
Database project
The Visual Studio. NET database project allows you to quickly create a database-oriented solution. The following example describes how to create a database project in Visual Studio. NET:

On the File menu, point to New (new), and then click Project.
In the Project Types pane, expand Other Projects (another project), and then click Database Projects.
In the Templates pane, click Database Project.
Fill in the name (name) and Location (location) box, and then click OK.
Click the Add to Solution button to add this project to an existing solution, or click the Close Solution button to create a separate solution.
In the Add Database Reference dialog box, select an existing data connection, or click Add New Reference to add a new data connection. If you click Add New Reference (add a newer reference), follow the steps in the previous section to complete the Data Link Properties dialog box. If you use this method to add a new data connection, the data connection appears in the Server Explorer window.
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.