Chapter 08 : ADO.NET Coding Techniques

This chapter builds on the introduction to ADO.NET presented in Chapter 7. This chapter s goal is to move beyond architectural and conceptual issues and focus on the typical tasks that you are likely to perform with Microsoft Visual Basic .NET solutions for Microsoft Access databases. At the same time, the chapter focuses on fundamental ADO.NET building blocks, such as the Connection , Command , DataAdapter , and DataSet objects. Another major theme of this chapter is enabling dynamic behavior. To that end, the chapter introduces the Parameters collection. With this collection, you can dynamically modify the behavior of Command objects based on either SQL strings or Access parameter queries. This chapter s primary focus is on data manipulation, which we ll cover from several perspectives, including running action queries in Visual Basic .NET transactions; performing inserts , deletes, and updates via data adapters from a local dataset; and handling concurrency violations.

The chapter has four major sections. The first of these dwells on programming database Connection objects. This section carefully reviews all properties and selected methods for the OleDbConnection object. The section demonstrates using connection strings for Access and Microsoft SQL Server databases. The second section illustrates dynamic data access techniques via Command objects. You will learn how to make a Command object dynamic by using SQL strings with variables or parameters. The third section is a case study for performing data manipulation within transactions. You will learn what transactions are and how to use them with Visual Basic .NET and Access databases. This section literally builds the sample. You will also discover how to programmatically create an Access database file and add tables to a database with Visual Basic .NET. The closing section discusses performing data manipulation via the DataAdapter and DataSet objects. This is a highly scalable approach to enabling data manipulation with Access databases via Visual Basic .NET. You will learn how to configure datasets programmatically and graphically. Sample code illustrates the fundamentals of inserting, deleting, and updating rows in an Access database based on changes to a local dataset. You will also learn how to handle conflicts when a data adapter tries to update an Access database that has changed since the last data access. This last scenario simulates a multiuser application.

Programming Database Connections

Programming database connections is largely a matter of getting the connection string right and using the proper .NET data provider. The connection string is determined by the demands of the database and the particular way in which your application uses the database. However, the properties for a database connection can indicate settings made in a connection string or help you manage the state of a connection (whether it is open or closed). Chapter 7 already examined roles for the Provider and DataSource properties. You can also use the Database property to report the name of the database for a SQL Server instance and other server-based database managers. Access does not use the Database property because it is exclusively used for server-based systems, as opposed to file-based ones. This section introduces you to programming database connections for use with Access and SQL Server databases.

All the samples for this section reside on Form2 of the ADONETSamples project included with this book s sample files. This project is a Microsoft Windows application with multiple forms. Form1 is a switchboard menu built with LinkLabel controls for selecting the code samples in the module behind Form2 and other form modules mentioned throughout this chapter. You can either use Form1 or make another form the startup object for the project to work with the code samples.

Figure 8-1 shows Form2 in Design view and immediately after it opens. The form contains three buttons with click event procedures that illustrate different Visual Basic .NET coding techniques for managing connections. Button1 launches a connection to the Access Northwind database. Its main role is to demonstrate how to monitor the full set of Connection properties. Button2 focuses on making a connection to a SQL Server database. The click event procedure interacts with the RadioButton controls in the GroupBox control to enable the demonstration of connection strings for logins based on either Windows or SQL Server security. Button3 shows one approach to handling a classic deployment issue dynamically: the developer creates a solution with an Access database in one path, but the deployment of the solution requires a different path to the database file.

Figure 8-1: A Design view and an initial operational view of Form2 in the ADONETSamples project

Note  

The load event procedure for Form2 includes code for editing the controls to manage their appearance when the form opens. This is true for the other Windows Forms samples in the ADONETSamples project. To keep this chapter s focus on ADO.NET coding techniques, I have not included the control management code in any listings. You can view this code in the ADONETSamples project.

Reporting Connection Properties for Microsoft Access

The first piece of sample code in this section is the click event procedure behind Button1 in Form2 . The sample code has a couple of objectives, including demonstrating the syntax for all the OleDbConnection properties and demonstrating how to use one of the properties to monitor the state of a connection. This sample makes a connection to an Access database. You can contrast its output with that for the second sample, which makes a connection to a SQL Server database. This will clarify the roles of the properties.

This Button1_Click procedure has three segments. In the initial segment, the procedure uses the OleDbConnection constructor that accepts a connection string. This constructor points an OleDbConnection object at an Access database file as it instantiates the object. You can also use a constructor that has no connection string at the time that you instantiate an OleDbConnection object and then assign the object s ConnectionString property later. You can reuse an existing OleDbConnection object for more than one database by closing the connection to a database, making a new ConnectionString property assignment that points at another database, and then reopening the Connection object.

The second segment of the Button1_Click procedure illustrates the syntax for designating each of the OleDbConnection object properties. This segment begins with the opening of the cnn1 object specified in the first segment. Then, it constructs a string variable for a MsgBox function that returns the initial value for each cnn1 object property. Most of the Connection object properties are read-only. You can change their setting through the connection string. The only property that is an exception to this general rule is, of course, ConnectionString . You can use the ConnectionTimeout property to determine how long the application will wait for the availability of a database server before returning an error. This feature will come into play when you connect to a non-Access database. The property applies to connections for classic Access ODBC data sources, such as SQL Server and Oracle. Its default value is 15 seconds. If you elect to change it, use a positive integer to designate the number of seconds to wait. Avoid negative values that generate Exception objects and values of 0 that can cause indefinite waits for a connection.

The Provider and DataSource properties are also typical of many Connection object properties; you can get their value, but you set them through the connection string. These are particularly important properties for a connection to an Access database because they can completely define the connection. Another potentially interesting Connection object property is the State property. This property indicates whether a connection is open or closed. The Database property applies exclusively to classic Access ODBC data sources. Therefore, this property returns an empty string for connections to Access databases. The ServerVersion property returns information about the major and minor version number as well as the build number of a database server product providing a data source through an OleDbConnection object. For a connection to an Access database, the ServerVersion property returns information about the OLE DB .NET Data Provider version as opposed to the Access database version.

The final code block in the procedure displays the State property value after the Button1_Click procedure closes the cnn1 object. Notice that you can apply the ToString method to represent the return value from the State property in a MsgBox function. The State property returns a value of Open or Closed through the ToString method to indicate the status of a Connection object. This property can prove useful when you allow a user to close a connection either directly or indirectly, or when a connection can be disabled by environmental conditions such as a temporarily disabled network connection.

 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Embed a connection string in a constructor Dim cnn1 As New OleDb.OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\Program Files\" & _ "Microsoft Office\Office10\Samples\Northwind.mdb") After opening, display OleDbConnection property values; Database property not supported for Access databases cnn1.Open() Dim str1 As String str1 = "ConnectionString = " & _ cnn1.ConnectionString & vbCr & _ "ConnectionTimeout = " & _ cnn1.ConnectionTimeout & vbCr & _ "Database = " & cnn1.Database() & vbCr & _ "DataSource = " & cnn1.DataSource & vbCr & _ "Provider = " & cnn1.Provider & vbCr & _ "ServerVersion = " & cnn1.ServerVersion & vbCr & _ "State = " & cnn1.State.ToString() MsgBox(str1, , "After Open") Display just state property after closing the OleDbConnection property cnn1.Close() str1 = "State = " & cnn1.State.ToString() MsgBox(str1, , "After Close") End Sub 

As a point of reference, Figure 8-2 shows the output from both message boxes generated by the Button1_Click procedure. The top message box initially displays the cnn1 properties after a user clicks Button1 . Because the code connects to an Access database (Northwind), the Database property returns an empty string. The State property shows a value of Open through the ToString method. Clicking the OK button in the top message box advances the procedure to the message box shown at the bottom of Figure 8-2. This second message box displays the value of the State property after invoking the Close method for the cnn1 object.

Figure 8-2: Message boxes showing the initial default values for properties after a connection to the Access Northwind database as well as a change in value for the State property

Connecting to a SQL Server Database

Some Access developers connect occasionally or often to SQL Server databases too. However, the connection string settings are different for a SQL Server database than for an Access database. In addition, SQL Server databases require that you login via one of two secure routes: SQL Server security or Windows security. From a client application perspective, the main difference between these two routes is whether you designate your Windows server login or use special login settings to gain access to a SQL Server database. Database administrators can generally configure a SQL Server instance to support either or both of these routes. However, a SQL Server instance running on a computer running Windows 98 supports the SQL Server login route only.

The Button2_Click procedure interacts with the status of the RadioButton controls to the right of Button2 in Form2 . Clicking Button2 connects a user to the SQL Server version of the Northwind database that installs by default with SQL Server version 7 and later. Form2 initially opens with the Windows Login radio button selected; the form load event procedure makes this selection by invoking the Select method for RadioButton1 . If a user wants to log in with SQL Server login credentials, she can click the SQL Server Login radio button. Normally, an application such as this will show a pair of text boxes that ask the user to designate a user ID and password. The section Validating a Password with a TextBox Control in Chapter 5 presents a sample application that accepts a user ID and a password with an Access database as a back end. You can adapt the user interface from that sample for use with SQL Server.

The sample application for Button2 in Form2 of the ADONETSamples project uses a default SQL Server login to connect to the database. If you attempt to run the sample application as is, you will need to update your SQL Server logins so that they include a login with a user ID of BookAuthor that has a password of Rick_Dobson with access to the SQL Server Northwind database. Alternatively, you can replace the BookAuthor user ID setting with any other SQL Server login to which the database administrator has granted access to the Northwind database.

The Button2_Click procedure uses the OleDbConnection object to connect to a SQL Server database. You can, of course, use the SqlConnection class to connect with a SQL Server version 7 or later database. However, if you are an Access developer who uses SQL Server databases only occasionally for basic data access tasks, you might find it convenient to use the OLE DB .NET Data Provider for both Access and SQL Server databases. The click event procedure for Button2 allows you to contrast the default settings for a connection to a SQL Server database with those for an Access database by displaying the Connection properties immediately after connecting to the Northwind database. (Compare these property values with those from the Button1_Click procedure.)

The sample code starts by instantiating an OleDbConnection object without specifying a connection string. The procedure then sets the OleDbConnection object s ConnectionString property to one of two settings. If RadioButton1 is still checked as it was in the form load event procedure, the procedure specifies an integrated security setting that uses the Windows login for identifying the user to the SQL Server instance. If the user clicked the SQL Server Login radio button, RadioButton1 will no longer be checked, which transfers control to the Else clause of the If Then Else statement for the Checked property of RadioButton1 . This Else clause assigns a value to the ConnectionString property for the OleDbConnection object that shows the syntax for a SQL Server login.

The two connection string settings have the same Provider , Data Source , and Initial Catalog clauses. The Provider clause value of SQLOLEDB designates the type of database server and driver. The Data Source clause points at a particular instance of SQL Server. In an environment running multiple instances of SQL Server, you can develop an application against a test server and change the Data Source setting to a second server at deployment time. SQL Server names often correspond to the network name for a computer running SQL Server. The designation of localhost points to the default instance of SQL Server running on the current computer. Change this setting if you are using a different instance of SQL Server. The Initial Catalog clause specifies the name of the database to which you want to connect.

The clauses that are different in the Then and Else clauses apply to either Windows or SQL Server security. When using the Windows login to connect to a SQL Server instance, include an Integrated Security clause in the connection string. Set this clause equal to SSPI . For a SQL Server login, replace the Integrated Security clause with User ID and Password clauses. Each SQL Server login has an ID or name and an associated password. Use the name for the User ID clause and the password for the Password clause.

After the ConnectionString property for the OleDbConnection object is set in one of two ways, the Button2_Click procedure is identical to the Button1_Click procedure. Therefore, both procedures output the same array of property values in two message boxes. By contrasting the first message box for the Button2_Click procedure (shown in Figure 8-3) with the first one for the Button1_Click procedure (shown earlier in Figure 8-2), you can discern differences in the property values associated with Connection objects for Access databases from those associated with Connection objects for SQL Server databases. As you can see, the Connection object s string property values largely duplicate the settings for a connection string. The property corresponding to the Initial Catalog clause is the Database property. The value for this property shown in Figure 8-3 points at the SQL Server version of the Northwind database. The ConnectionTimeout property value is 15, which means the application will wait 15 seconds for a connection attempt to a server to succeed. Although neither connection string setting in the Button2_Click procedure explicitly sets this value, 15 seconds is the default assignment for the property.

Figure 8-3: Contrast this After Open message box for a connection to a SQL Server database with the After Open message box for an Access database shown in Figure 8-2.

 Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click Dim cnn1 As New System.Data.OleDb.OleDbConnection() If RadioButton1.Checked = True Then Assign Windows login to connection string for a SQL Server database cnn1.ConnectionString = _ "Provider=SQLOLEDB;Data Source=localhost;" & _ "Initial Catalog=Northwind;" & _ "Integrated Security=SSPI;" Else Assign SQL Server login to connection string for a SQL Server database; use valid User ID and Password arguments instead of those here cnn1.ConnectionString = _ "Provider=SQLOLEDB;Data Source=localhost;" & _ "Initial Catalog=Northwind;" & _ "User ID=BookAuthor;Password=Rick_Dobson" End If cnn1.Open() Dim str1 As String str1 = "ConnectionString = " & _ cnn1.ConnectionString & vbCr & _ "ConnectionTimeout = " & _ cnn1.ConnectionTimeout & vbCr & _ "Database = " & cnn1.Database() & vbCr & _ "DataSource = " & cnn1.DataSource & vbCr & _ "Provider = " & cnn1.Provider & vbCr & _ "ServerVersion = " & cnn1.ServerVersion & vbCr & _ "State = " & cnn1.State.ToString() MsgBox(str1, , "After Open") Display just state property after closing the OleDbConnection property cnn1.Close() str1 = "State = " & cnn1.State.ToString() MsgBox(str1, , "After Close") End Sub 

Connecting to a Database at Your Place or Their Place

One traditional Access database development issue is determining where to connect to the database file from an application file. Often, the developer will build a solution with a test database in one path. Then, deployment of the application will take place with the database in a different path. The path issue specifically addresses where to connect to an Access database file. A couple of approaches exist for dynamically altering the location or path where an application looks for an Access database file. The approach demonstrated in the following listing, which applies to the Button3_Click procedure of Form2 , tries to connect to a database at one location. If that attempt fails, the control passes to a second attempt to connect to a database at a different location. With this strategy, the application will always work at both the developer s test environment and the deployment environment, as long as only one copy of the database file is at the test and deployment environments. You can extend this strategy for use with classic Access ODBC databases because the development and deployment versions of a database will typically have distinct Data Source clauses in the connection strings.

The main feature that the Button3_Click procedure demonstrates is how to connect with a database in one of two locations. The procedure embeds one ConnectionString property assignment and an invocation of the Open method in a Try clause and embeds a contrasting ConnectionString property assignment with an invocation of the Open method in a Catch clause. If the connection string in the Try clause fails, this approach automatically tries to make a connection based on the connection string in the immediately following Catch clause for an OleDbException object. Place the connection string for the most typical database location in the Try clause. Assign the secondary location for the database in the Catch location. If the Access database file is not at either location or does not make a connection for some other reason, control passes to a second nested Catch clause, which once again traps an OleDbException object and gracefully returns control to the application. In the Finally clause for the error-trap statement, the Button3_Click procedure returns via a message box the state of the connection, which will be open or closed. You can easily amend the sample code to reveal the DataSource property or any other property for a Connection object.

The closing segment of the Button3_Click procedure demonstrates how to conditionally close the cnn1 object. With a condition expression for an If Then Else statement based on the State property for the cnn1 object, the sample invokes the Close method only if the Connection object is open. Because you can invoke the Close method for a Connection object that is already closed without generating an error, this precise application of the State property is not strictly necessary. Nevertheless, your applications might need to conditionally perform some tasks depending on whether a connection is open or closed. The syntax of the If Then Else statement at the close of the Button3_Click procedure demonstrates the syntax for accomplishing that task.

 Private Sub Button3_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button3.Click Instantiate an empty Connection object Dim cnn1 As New OleDb.OleDbConnection() Dim str1 As String Try Try to connect with Northwind in the default Access 2000 location cnn1.ConnectionString = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\Program Files\" & _ "Microsoft Office\Office\Samples\Northwind.mdb" cnn1.Open() Catch exc1 As System.Data.OleDb.OleDbException Try Try to connect with Northwind in the default Access 2002 location cnn1.ConnectionString = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\Program Files\" & _ "Microsoft Office\Office10\Samples\Northwind.mdb" cnn1.Open() Catch exc2 As System.Data.OleDb.OleDbException If you cannot connect at either Data Source, let user know MsgBox("Wasn t able to connect. Make sure the " & _ "database file is available and you have a " & _ "connection string pointing at it.", , _ "From nested Catch") str1 = "State = " & cnn1.State.ToString() MsgBox(str1, , "After Close") End Try Finally Display connection state whether connected or not str1 = "State = " & cnn1.State.ToString() MsgBox(str1, , "From Finally") End Try Cleanup with conditional close; IntelliSense offers more options than are available; you can only use Open and Close states If cnn1.State = ConnectionState.Open Then cnn1.Close() Else MsgBox("Not able to close at this time.") End If End Sub