Basic ADO.NET Features

This chapter begins with a quick look at the basics of ADO.NET and then provides an overview of ADO.NET capabilities, namespaces, and classes. It also reviews how to work with the Connection, Command, DataAdapter, DataSet, and DataReader classes. Later chapters will cover some of the more recently added ADO.NET features.

Common ADO.NET Tasks

Before jumping into the depths of ADO.NET, step back and make sure that you understand some of the common tasks you might perform programmatically within ADO.NET. This section looks at the process of selecting, inserting, updating, and deleting data.

In addition, stored procedure SQL files required for the sample code can be found with the code download for this chapter.

Selecting Data

After the connection to the data source is open and ready to use, you probably want to read the data from it. If you do not want to manipulate the data but simply read it or transfer it from one spot to another, use the DataReader class (or one of the classes that inherit from DataReader for each database type).

The following example retrieves a list of vendor names from the database (code file: DataReaderExample). (You may need to update the connection string to match the location of the AdventureWorks database on your computer.)

Imports System.Data.SqlClient
        
Module Main
  Sub Main()
    Dim connection As SqlConnection
    Dim command As SqlCommand

    Console.WriteLine("Loading records...")

    'update to match the location of AdventureWorks on your computer
    Dim cmdString As String = "Select Name from [Purchasing].[Vendor]"
    connection = New SqlConnection("Data Source=.SQLEXPRESS;" +
                                   "Initial Catalog=AdventureWorks;" +
                                   "Integrated Security=True")
    command = New SqlCommand(cmdString, connection)
    connection.Open()

    Dim reader As SqlDataReader
    reader = command.ExecuteReader(CommandBehavior.CloseConnection)

    While reader.Read()
      Console.WriteLine(reader("Name").ToString())
    End While

    Console.WriteLine("Press ENTER to exit")
    Console.ReadLine()

  End Sub
End Module

In this example, you create an instance of both the SqlConnection and the SqlCommand classes. Then, before you open the connection, you simply pass the SqlCommand class an SQL statement selecting specific data from the database. After your connection is opened (based upon the commands passed in), you create a DataReader. To read the data from the database, you iterate through the data with the DataReader by using the reader.Read method. Each time you call the Read method, the current position of the reader is set to point to the next line returned by the SQL statement. Once the position moves to the end, the Read method returns false, exiting the loop. In the sample application, this data is displayed in the console window.

Inserting Data

When working with data, you often insert the data into the data source, in this case an SQL Server database. The following shows you how to do this (code file: DataInsertExample) :

Imports System.Data.SqlClient
        
Module Main
  Sub Main()
    Dim connection As SqlConnection
    Dim command As SqlCommand

    Console.WriteLine("Inserting data...")

    connection = New SqlConnection("Data Source=.SQLEXPRESS;" +
                                   "Initial Catalog=AdventureWorks;" +
                                   "Integrated Security=True")

    Dim insert As String = "Insert [Purchasing].[Vendor]([AccountNumber]," +
                   "[Name],[CreditRating],[PreferredVendorStatus]," +
                   "[ActiveFlag],[PurchasingWebServiceURL],[ModifiedDate])" +
                   "Values ('ACME10000', 'Acme Supply', 1, 1, 1, " +
                   "'http://acmesupply.com', '10/10/2012')"

    command = New SqlCommand(insert, connection)
    connection.Open()
    command.ExecuteNonQuery()

    Console.WriteLine("Loading data...")

    'Confirm we have it inserted
    Dim query As String = "SELECT [Name] FROM [Purchasing].[Vendor] " +
                            "WHERE Name='Acme Supply'''
    command = New SqlCommand(query, connection)

    Dim reader As SqlDataReader
    reader = command.ExecuteReader(CommandBehavior.CloseConnection)

    While reader.Read()
      Console.WriteLine(reader("Name").ToString())
    End While

    Console.WriteLine("Press ENTER to exit")
    Console.ReadLine()
  End Sub 
End Module

Inserting data into SQL is pretty straightforward and simple. Using the SQL command string, you insert specific values for specific columns. The actual insertion is initiated using the ExecuteNonQuery command. This executes a command on the data when you don't want anything in return. If you were expecting data back from the insert, you could use ExecuteScalar (if a single value—such as the inserted record ID—is returned) or ExecuteReader (if data—such as the complete inserted record—is returned).

Updating Data

In addition to inserting new records into a database, you frequently need to update existing rows of data in a table. The following changes revision numbers of documents (code file: DataUpdateExample):

Imports System.Data.SqlClient
        
Module Main
  Sub Main()
    Dim records As Integer
    records = ChangeDocumentRevision(1, 2)
    Console.WriteLine("{0} records affected", records)
    Console.WriteLine("Press ENTER to exit.")
    Console.ReadLine()
  End Sub

  Public Function ChangeDocumentRevision(ByVal oldRevison As Integer,
                                       ByVal newRevision As Integer) As Integer
    Dim command As SqlCommand
    Dim connection As SqlConnection
    Dim result As Integer

    Dim updateQuery As String = 
                      String.Format("UPDATE [Production].[Document] SET " +
                                    "[Revision]={0} where [Revision]={1}",
                                     newRevision, oldRevison)

    connection = New SqlConnection("Data Source=.SQLEXPRESS;" +
                                   "Initial Catalog=AdventureWorks;" +
                                   "Integrated Security=True")
    connection.Open()

    'Display the record before updating
    DisplayData(connection, "before")

    Console.WriteLine("Updating data...")

    command = New SqlCommand(updateQuery, connection)
    result = command.ExecuteNonQuery()

    'Display the record after updating
    DisplayData(connection, "after")

    Return result
  End Function

  Private Sub DisplayData(ByVal connection As SqlConnection,
                          ByVal direction As String)
    Dim query As String = "SELECT [Title], [Revision] FROM " +
                            "[Production].[Document] ORDER BY [Title]"
    Dim command As New SqlCommand(query, connection)

    Console.WriteLine("Displaying data ({0})", direction)
    Dim reader As SqlDataReader = command.ExecuteReader

    While reader.Read
      Console.WriteLine("Document: {0}  Rev. {1}",
                        reader.GetString(0),
                        reader.GetString(1))

    End While

    reader.Close()
  End Sub
End Module

This update function changes the revision number for the Production.Document from 1 to 2. This is done with the SQL command string. The great thing about these update capabilities is that you can capture the number of records that were updated by assigning the result of the ExecuteNonQuery command to the records variable. The total number of affected records is then returned by the function.

Deleting Data

Along with reading, inserting, and updating data, you sometimes need to delete data from the data source. Deleting data is a simple process of using the SQL command string and then the ExecuteNonQuery command as you did in the update example. The following bit of code illustrates this (code file: DataDeleteExample):

Imports System.Data.SqlClient
        
Module Main
  Sub Main()
    Dim deletes As Integer
    deletes = ClearDatabaseLog(DateTime.Now())
    Console.WriteLine("{0} records deleted", deletes)
    Console.WriteLine("Press ENTER to exit.")
    Console.ReadLine()
  End Sub

  Public Function ClearDatabaseLog(ByVal queryDate As DateTime) As Integer
    Dim result As Integer
    Dim command As SqlCommand
    Dim connection As SqlConnection
    Dim deleteQuery As String = String.Format("DELETE [DatabaseLog] " +
                                              "WHERE [PostTime] < '{0}''' 
                                               queryDate)

    connection = New SqlConnection("Data Source=.SQLEXPRESS;" +
                                   "Initial Catalog=AdventureWorks;" +
                                   "Integrated Security=True")
    connection.Open()

    command = New SqlCommand(deleteQuery, connection)

    DisplayData(connection, "before")

    Console.WriteLine("Deleting data...")
    result = command.ExecuteNonQuery()

    DisplayData(connection, "after")

    connection.Close()

    Return result
  End Function

  Private Sub DisplayData(ByVal conn As SqlConnection,
                          ByVal direction As String)
    Dim query As String = "SELECT count(*) FROM [DatabaseLog]"
    Dim command As New SqlCommand(query, conn)

    Dim count As Integer = CType(command.ExecuteScalar(), Integer)
    Console.WriteLine("Number of log records {0}: {1}", direction, count)
  End Sub
End Module

You can assign the ExecuteNonQuery command to an Integer variable (just as you did for the update function) to return the number of records deleted in order to verify that the records are deleted.

Basic ADO.NET Namespaces and Classes

The core ADO.NET namespaces are shown in Table 9.1. In addition to these namespaces, each new data provider will have its own namespace. For example, the Oracle .NET data provider adds a namespace of System.Data.OracleClient (for the Microsoft-built Oracle data provider).

Table 9.1 Core ADO.NET Namespaces

ADO.NET has three distinct types of classes commonly referred to as:

A data provider contains Connection, Command, DataAdapter, and DataReader objects. Typically, in programming ADO.NET, you first create the Connection object and provide it with the necessary information, such as the connection string. You then create a Command object and provide it with the details of the SQL command that is to be executed. This command can be an inline SQL text command, a stored procedure, or direct table access. You can also provide parameters to these commands if needed.

After you create the Connection and the Command objects, you must decide whether the command returns a result set. If the command doesn't return a result set, then you can simply execute the command by calling one of its several Execute methods. Conversely, if the command returns a result set, you must decide whether you want to retain the result set for future use without maintaining the connection to the database. If you want to retain the result set but not the connection, then you must create a DataAdapter object and use it to fill a DataSet or a DataTable object. These objects are capable of maintaining their information in a disconnected mode. However, if you don't want to retain the result set, but rather simply process the command in a swift fashion, then you can use the Command object to create a DataReader object. The DataReader object needs a live connection to the database, and it works as a forward-only, read-only cursor.

ADO.NET Components

To better support the disconnected model as defined above, the ADO.NET components separate data access from data manipulation. This is accomplished via two main components: the DataSet and the .NET data provider. Figure 9.1 illustrates the concept of separating data access from data manipulation.

Figure 9.1 ADO.NET Data Provider & Dataset

The DataSet is the core component of the disconnected architecture of ADO.NET. It is explicitly designed for data access independent of any data source. As a result, it can be used with multiple and differing data sources, with XML data, or even to manage data local to an application such as an in-memory data cache. The DataSet contains a collection of one or more DataTable objects made up of rows and columns of data, as well as primary key, foreign key, constraint, and relation information about the data in the DataTable objects. It is basically an in-memory database, but what sets it apart is that it doesn't care whether its data is obtained from a database, an XML file, a combination of the two, or somewhere else. You can apply inserts, updates, and deletes to the DataSet and then push the changes back to the data source, no matter where the data source lives! This chapter offers an in-depth look at the DataSet object family.

The other core element of the ADO.NET architecture is the .NET data provider, whose components are designed for data manipulation (as opposed to data access with the DataSet). These components are listed in Table 9.2.

Table 9.2 .NET Data Provider Components

The DataAdapter uses Command objects to execute SQL commands at the data source, both to load the DataSet with data and to reconcile changes made to the data in the DataSet with the data source. You will take a closer look at this later in the detailed discussion of the DataAdapter object.

The .NET Framework 4.5 ships with a number of .NET data providers, including ones for accessing SQL Server and Oracle databases, as well as more generic data providers, such as the ODBC and OLE DB data providers. Other data providers are available for just about every other database out there, for example, a MySQL database.

The rule of thumb when deciding which data provider to use is to first use a .NET Relational Database Management System (RDBMS)–specific data provider if it is available, and to use the .NET OLE DB provider when connecting to any other data source. (Most RDBMS vendors are now producing their own .NET data providers in order to encourage .NET developers to use their databases.) Finally, if no OLE DB provider is available, try ODBC access using the .NET ODBC data provider.

For example, if you were writing an application that uses SQL Server, then you would want to use the SQL Server .NET data provider. The .NET OLE DB provider is used to access any data source exposed through OLE DB, such as Microsoft Access. You will be taking a closer look at these later.