Writing ADO.NET Applications

It's now time to write your first ADO.NET application. In the following sections, we discuss the necessary steps required to write a database application using ADO.NET.

Choosing a .NET Data Provider

The first step of writing your first ADO.NET application is to decide what .NET data provider you'll use in your application. As discussed previously, there are many data providers that work with multiple data sources. Some of the available data providers are OleDb, Sql, Odbc, and Oracle. You use the OleDb data provider to access OLE-DB data sources. The Sql data provider works with SQL Server 7.0 or later databases. The Odbc data provider works with ODBC data sources, and the Oracle data provider works with Oracle databases. You can also use more than one data provider to access the same database. For example, you can use the OleDb and Odbc data providers to work with Microsoft Access databases.

Note

Some documentation and authors represent the Sql data provider as the SqlClient data provider. This may be because the Sql data provider is defined in the SqlClient namespace.

For this example, you'll learn how to build an application that uses OleDb data providers to access an Access 2000 database. In the "Using a DataSet to Read Data" section, you'll learn how to create an application that uses Sql data providers to access a SQL Server 2000 database.

Adding Namespace References

After you've chosen your data provider, you need to add references to the assembly and include the namespaces in your project. The System.Data.dll assembly defines ADO.NET namespaces. You can add references to the project using the Project Add Reference option. The .NET tab of the Add Reference dialog box lists all the available .NET assemblies (see Figure 1-14). Select the System.Data.dll assembly.

Figure 1-14: Adding a reference to the System.Data.dll assembly

After selecting the System.Data.dll assembly, click the Select button and then the OK button. This action adds the System.Data assembly and related namespace references to your project.

After adding a reference to the assembly, you need to include namespaces in your project by using the Imports directive. The Imports directive imports a namespace definition in a project. The following code shows how to import the System, System.Data, System.Data.Common, and System.Data.OleDb namespaces:

 Imports System Imports System.Data Imports System.Data.OleDb Imports System.Data.Common 

Note

If you are using the Sql data provider, you need to include the System.Data.Sql namespace. If you are using the Odbc data provider, you need to include the System.Data.Odbc namespace.

Establishing a Connection

Now, the next step is to create a Connection object. You create a Connection object using the data provider's Connection class. In the following code, the OleDbConnection class creates a connection with an Access 2000 database. The OleDbConnection constructor takes one parameter of a string type. This string is called connectionString, and it has two parts. First, it has a provider, and second, it has the path of the data source. As you can see, the following code uses the Microsoft.Jet.OLEDB.4.0 provider and the northwind.mdb data source:

 Dim connectionString As String 'Create connection and command objects connectionString = "Provider=Microsoft.JET.OLEDB.4.0;" & _ "data source=C:\\Northwind.mdb" Dim conn As OleDbConnection = New OleDbConnection(connectionString) 'Open connection conn.Open() 

Note

In this sample, the northwind.mdb database path is "C:\\northwind.mdb". You can use any database you want. You just need to change the path and name of the database and change the table names you are using in the SQL statements to access the data.

Creating a Command or DataAdapter Object

Next, you need to create a DataAdapter or Command object. You create a Command object by using the OleDbCommand class. (You'll see a DataAdapter object in the SQL Server example at the end of this chapter; see the "Using a DataSet to Read Data" section.)

The OleDbCommand constructor takes two parameters. The first is a SQL query, and the second is the Connection object. You can create a SELECT SQL query from the Customers table in the Northwind database. The following code shows how to create a Command object:

 Dim sql As String sql = "SELECT CustomerID, ContactName, ContactTitle FROM Customers" Dim cmd As OleDbCommand = New OleDbCommand(sql, conn) 

Filling Data to a DataSet or DataReader Object

The next step is to open the connection by calling the Open method of the Connection object and reading data from the Command object. The ExecuteReader method, OleDbCommand, returns data in an OleDbDataReader object. A DataReader object is used to read fast and forward only cached data. The following code shows this:

 Dim reader As OleDbDataReader = cmd.ExecuteReader() 

Displaying Data

The next step is to perform some operation on the data. In this example, you'll write data out to the console. The Read method of OleDbDataReader reads data. The DataReader class has a number of methods that return different types of data (for example, GetString and GetInteger). These methods take the index of the field from which you want to read data. The following code reads data from two fields of the Customers table, whose indexes are 0 and 1:

 While reader.Read() Console.Write(reader.GetString(0).ToString() + " ,") Console.Write(reader.GetString(1).ToString() + " ,") Console.WriteLine("") End While 

Closing the Connection

Finally, the last step is to close the reader and connection objects by calling their Close methods:

 reader.Close() conn.Close() 

Note

You must always close an open DataReader. We discuss DataReaders in more detail in the "The DataReader: An Easy Walk Through the Data" section of Chapter 4.