An introduction to ADO.NET

An overview of ADO.NET

ADO.NET (ActiveX Data Objects .NET) is the primary data access API for the .NET Framework. It provides the classes that you use as you develop database applications with Visual Basic .NET as well as other .NET languages. In the two topics that follow, you’ll learn about how ADO.NET uses these classes to provide access to the data in a database and the two ways you can create ADO.NET objects in your Visual Basic programs.

How ADO.NET works

To work with data using ADO.NET, you use a variety of ADO.NET objects. Figure 2-1 shows the primary objects you’ll use to develop Windows-based ADO.NET applications in Visual Basic.

To start, the data used by an application is stored in a dataset that contains one or more data tables. To load data into a data table, you use a data adapter. The main function of the data adapter is to manage the flow of data between a dataset and a database. To do that, it uses commands that define the SQL statements to be issued. The command for retrieving data, for example, typically defines a Select statement. Then, the command connects to the database using a connection and passes the Select statement to the database. After the Select statement is executed, the result set it produces is sent back to the data adapter, which stores the results in the data table.

To update the data in a database, the data adapter uses a command that defines an Insert, Update, or Delete statement for a data table. Then, the command connects to the database and performs the requested operation.

Although it’s not apparent in this figure, the data in a dataset is independent of the database that the data was retrieved from. In fact, the connection to the database is typically closed after the data is retrieved from the database. Then, the connection is opened again when it’s needed. Because of that, the application must work with the copy of the data that’s stored in the dataset. The architecture that’s used to implement this type of data processing is referred to as a disconnected data architecture. Although this is more complicated than a connected architecture, the advantages offset the complexity.

One of the advantages of using a disconnected data architecture is improved system performance due to the use of fewer system resources for maintaining connections. Another advantage is that it makes ADO.NET compatible with ASP.NET web applications, which are inherently disconnected. You’ll learn more about developing ASP.NET web applications that use ADO.NET in chapters 12 through 14 of this book.

The ADO.NET classes that are responsible for working directly with a database are provided by the .NET data providers. These data providers include the classes you use to create data adapters, commands, and connections. As you’ll learn later in this chapter, the .NET Framework currently includes two different data providers, but additional providers are available from Microsoft and other third-party vendors such as IBM and Oracle.

Basic ADO.NET objects

Figure 2-1.   How ADO.NET works

Description

• ADO.NET uses two types of objects to access the data in a database: datasets, which can contain one or more data tables, and .NET data provider objects, which include data adapters, commands, and connections.
  
• A dataset stores data from the database so that it can be accessed by the application. The .NET data provider objects retrieve data from and update data in the database.
  
• To retrieve data from a database and store it in a data table, a data adapter object issues a Select statement that’s stored in a command object. Next, the command object uses a connection object to connect to the database and retrieve the data. Then, the data is passed back to the data adapter, which stores the data in the dataset.
  
• To update the data in a database based on the data in a data table, the data adapter object issues an Insert, Update, or Delete statement that’s stored in a command object. Then, the command object uses a connection to connect to the database and update the data.
  
• The data provider remains connected to the database only long enough to retrieve or update the specified data. Then, it disconnects from the database and the application works with the data via the dataset object. This is referred to as a disconnected data architecture.
  
• All of the ADO.NET objects are implemented by classes in the System.Data namespace of the .NET Framework. However, the specific classes used to implement the connection, command, and data adapter objects depend on the .NET data provider you use.

Two ways to create ADO.NET objects

Figure 2-2 shows two basic techniques you can use to create the ADO.NET objects you need as you develop database applications. First, you can use the components in the Data tab of the Toolbox to create ADO.NET objects by dragging and dropping them onto a form. Notice that the names of most of the components in the Data tab are prefixed with either “OleDb” or “Sql.” As you’ll learn in the next figure, these prefixes identify the data provider that these components are associated with.

Before I go on, you should realize that when you drag one of the data adapter components onto a form, Visual Studio starts the Data Adapter Configuration Wizard. This wizard gathers information about the data you want to retrieve and then generates code to create the required ADO.NET objects. You’ll learn how to use the Data Adapter Configuration Wizard in the next chapter.

The Visual Basic project shown in this figure contains four ADO.NET objects: two data adapters named daVendors and daStates, a connection named conPayables, and a dataset named DsPayables1. Because these objects don’t have a visual interface like the controls that you add to a form, they don’t appear on the form itself. Instead, they appear in the Component Designer tray below the form. Then, when you select one of these objects, its properties appear in the Properties window and you can work with them from there.

The second technique for creating ADO.NET objects is to write the code yourself. The code shown in this figure, for example, creates three objects: a connection named conPayables, a data adapter named daVendors, and a dataset named dsPayables. It also uses the Fill method of the data adapter to retrieve data from the database identified by the connection and load it into the dataset. (Don’t worry if you don’t understand all of this code. You’ll learn more about coding these types of statements throughout this book.)

Although creating ADO.NET objects through code is more time-consuming than using the components and wizards, it can result in more compact and efficient code. In addition, because the components and wizards have limitations, there are times when you’ll need to write your own code. You’ll learn more about how you do that in chapter 6.

For now, you should realize that whether you create ADO.NET objects using the components in the Toolbox or using code, you need to be familiar with object-oriented programming techniques such as constructors and overloaded methods. For example, when you use the Fill method of a data adapter to retrieve data from a database and store it in a dataset, you’ll need to know which of the eight overloaded methods to use. And when you create ADO.NET objects like connections and data adapters through code, you’ll need to know which of the overloaded constructors to use. If you’re not familiar with these basic object-oriented programming techniques, we recommend that you review chapters 6 and 15 of our book, Murach’s Beginning Visual Basic .NET.

ADO.NET objects created using components in the Toolbox

Figure 2-2.  Two ways to create ADO.NET objects

ADO.NET objects created using code

  Dim sConnectionString As String = "data source=DOUG\VSdotNET;"& _
          "initial catalog=Payables;integrated security=SSPI;"& _
          "persist security info=False;workstation id=DOUG;packet size=4096"
  Dim conPayables As New SqlConnection(sConnectionString)
  Dim sVendorSelect = "Select * From Vendors"
  Dim daVendors As New SqlDataAdapter(sVendorSelect, conPayables)
  Dim dsPayables As New DataSet() 
  daVendors.Fill(dsPayables, "Vendors")

Description

• You can use the ADO.NET components in the Data tab of the Toolbox to add ADO.NET objects to a form. Then, you can set the properties of the objects using the Properties window.
  
• If you add a data adapter from the Toolbox, the Data Adapter Configuration Wizard is started. This wizard helps you create the data adapter and the related connection and command objects. See chapter 3 for details.
  
• To create ADO.NET objects in code, you use Dim statements that identify the class that each object is created from. This method requires more coding than using the components but is more flexible.

ADO.NET data providers

To access a database, you use an ADO.NET data provider. In the topics that follow, you’ll learn more about the classes that make up a data provider. First, you’ll learn about the two data providers that come with the .NET Framework. Then, you’ll learn what each of the core data provider classes does.

The SQL Server and OLE DB data providers

All .NET data providers must include core classes for creating the four types of objects listed in the first table in figure 2-3. You’ve already learned the basic functions of the connection, command, and data adapter objects. In addition to these, you can use a data reader object to access the data in a database in a read-only, forward-only manner.

The second table in this figure lists the two data providers that come with the .NET Framework. The SQL Server data provider is designed to provide efficient access to a Microsoft SQL Server database. The OLE DB data provider is a generic data provider that can access any database that supports the industry standard OLE DB interface, such as Oracle or MySQL. Although you can use the OLE DB data provider to access a SQL Server database, you shouldn’t do that unless you plan on migrating the data to another database since the SQL Server data provider is optimized for accessing SQL Server data.

In addition to the .NET data providers, you should also know that several database vendors have developed .NET data providers that are optimized for use with their databases. For example, .NET data providers are available for the popular MySQL database as well as for Oracle and SQL Anywhere. Before you develop an application using the OLE DB provider, then, you should check with your database vendor to see if a specialized .NET data provider is available.

The third table in this figure lists the names of the classes you use to create objects using the SQL Server and OLE DB providers. Notice that like the components you saw in the Toolbox in figure 2-2, all of the SQL Server classes are prefixed with “Sql” and all of the OLE DB classes are prefixed with “OleDb.” That way, it’s easy to tell which data provider you’re using in your applications.

When you develop a Visual Basic application that uses ADO.NET, you may want to add an Imports statement for the namespace that contains the data provider classes at the beginning of each source file that uses those classes. These namespaces are listed in the second table in this figure. If you include an Imports statement, you can then use the data provider classes without having to qualify them with the name of the namespace. The code shown in this figure illustrates how this works.

Now that you’re familiar with the core classes of the two .NET data providers, the next two topics will describe the classes of the SQL Server data provider in more detail. You should realize, though, that the information presented in these topics applies to the classes of the OLE DB data provider as well. In later chapters, you’ll learn some of the differences between these classes.

Figure 2-3.   The .NET data providers

.NET data provider core objects

Data providers included with the .NET framework

Class names for the SQL Server and OLE DB data providers

Code that uses qualification to identify the data provider namespace

  Dim conPayables As New SqlClient.SqlConnection()

Code that uses an Imports statement to identify the data provider namespace

  Imports System.Data.SqlClient
  .
  .
  Dim conPayables As New SqlConnection()

Description

• In addition to the core classes shown above, classes are provided for other functions such as passing parameters to commands or working with transactions.
  
• To use a .NET data provider in a program, you should add an Imports statement for the appropriate namespace at the beginning of the source file. Otherwise, you’ll have to qualify each class you refer to with the SqlClient or OleDb namespace since these namespaces aren’t included as references by default.
  
• Other .NET data providers are available to provide efficient access to non-Microsoft databases such as Oracle, MySQL, and SQL Anywhere.

   

The SqlConnection class

Before you can access the data in a database, you have to create a connection object that defines the connection to the database. To do that, you use the SqlConnection class presented in figure 2-4.

The most important property of the SqlConnection class is ConnectionString. A connection string is a text string that provides the information necessary to establish a connection to a database. That means it includes information such as the name of the database you want to access and the database server that contains it. It can also contain authentication information such as a user-id and password. You’ll learn more about coding connection strings in chapter 6.

The two methods of the SqlConnection class shown in this figure let you open and close the connection. In general, you should leave a connection open only while data is being retrieved or updated. That’s why when you use a data adapter, the connection is opened and closed for you. In that case, you don’t need to use the Open and Close methods.

The SqlCommand class

To execute a SQL statement against a SQL Server database, you create a SqlCommand object that contains the statement. Figure 2-4 presents the SqlCommand class you use to create this object. Notice that the Connection property of this class associates the command with a SqlConnection object, and the CommandText property contains the SQL statement to be executed.

The CommandType property indicates how the command object should interpret the value of the CommandText property. Instead of specifying a SQL statement for the CommandText property, for example, you can specify the name of a stored procedure, which consists of one or more SQL statements that have been compiled and stored with the database (see chapter 8 for details). Or you can specify the name of a table. If you specify a SQL statement, you set the value of the CommandType property to CommandType.Text. If you specify the name of a stored procedure, you set it to CommandType.StoredProcedure. And if you specify the name of a table, you set it to CommandType.TableDirect. Then, a Select * statement will be executed on the table.

Earlier in this chapter, you learned that you can use a data adapter to execute command objects. In addition, you can execute a command object directly using one of the three Execute methods shown in this figure. If the command contains a Select statement, for example, you can execute it using either ExecuteReader or ExecuteScalar. If you use ExecuteReader, the results are returned as a DataReader object. If you use ExecuteScalar, only the value in the first column and row of the query results is returned. You’re most likely to use this method with a Select statement that returns a single summary value.

If the command contains an Insert, Update, or Delete statement, you’ll use the ExecuteNonQuery method to execute it. This method returns an integer value that indicates the number of rows that were affected by the command. For example, if the command deletes a single row, the ExecuteNonQuery method returns 1.

Figure 2-4. The SqlConnection and SqlCommand classes

Common properties and methods of the SqlConnection class

Common properties and methods of the SqlCommand class

Description

• Each command object is associated with a connection object through the command’s Connection property. When a command is executed, the information in the ConnectionString property of the connection object is used to connect to the database.
  
• When you use a data adapter to work with a database, the connection is opened and closed automatically. If that’s not what you want, you can use the Open and Close methods of the connection object to open and close the connection.
  
• You can use the three Execute methods of a command object to execute the SQL statement it contains. You can also execute the SQL statement in a command object using methods of the data adapter. See figure 2-5 for more information.

   

blog comments powered by Disqus