The chapter has five major sections.
All the samples throughout this chapter use the MyADODOTNETSamples solution. You can open the solution file (MyADODOTNETSamples.sln) in Visual Studio .NET. This chapter provides specific instructions for launching each sample from the solution. By following the instructions, you’ll gain familiarity with how to start a Windows application from an element within a solution other than Form1— the default startup object.
Imports System.Data.SqlClient
There are six basic ADO.NET object classes. These classes are the Connection class, the Command class, the DataReader class, the DataAdapter class, the DataSet class, and the DataView class. This section provides a brief orientation to each of these classes that focuses on selected properties and methods for each class that you are likely to find useful in your work. Much of this material specifically equips you to understand the samples that appear later in this chapter.
The SqlConnection object’s ConnectionString property lets you get or set the connection string for a Connection object. The DataSource property returns the SQL Server instance to which a SqlConnection object links, and the Database property denotes the specific database on the server to which the Connection object provides access. The ConnectionTimeout property allows you to fine-tune how long an application waits for a connection from a server before raising an exception because the server is unavailable.
Instantiate a SqlCommand object to represent a Command object with the SQL Server data provider. Three especially critical SqlCommand properties are CommandText, CommandType, and Parameters. The CommandText property holds a T- SQL statement, a stored procedure name, or a table name. By default, ADO.NET interprets the CommandText property as a T-SQL string. The CommandType property settings are Text, StoredProcedure, and TableDirect. Text is the default setting. Use a StoredProcedure setting for CommandType when the CommandText property designates a stored procedure. When you are selecting all the rows and columns from one or more tables, the TableDirect property setting is especially useful. With the TableDirect property setting, you can name one or more tables in the CommandText property. Delimit multiple table names from one another with a comma.
The Parameters property for a SqlCommand object returns the Parameters collection. Use parameters to dynamically set arguments for stored procedures, other database objects, and even T-SQL strings at run time. Parameters are especially useful when you’re performing database manipulation tasks, such as modifying records, inserting new records, and deleting rows from a SQL Server data source. In this context, you add the parameters to a Command property of a DataAdapter object. You can also use the Parameters collection to retrieve output parameters and return values from stored procedures.
Several methods facilitate the ability of a Command object to implement a T- SQL statement in its CommandText property. Use the ExecuteReader method to populate a DataReader object based on a SELECT statement. The ExecuteNonQuery method signals that a T-SQL statement doesn’t return values. This method is particularly appropriate for T-SQL statements that create and manipulate objects, such as those that add logins or manipulate permissions. The ExecuteScalar method returns a single value, instead of a set of rows, from a T-SQL statement. The value corresponds to the first column value in the first row of the result set from the T-SQL statement on the server.
Instantiate DataReader objects as instances of the SqlDataReader class for the SQL Server data provider. You populate SqlDataReader objects by invoking the ExecuteReader method for a SqlCommand object that returns a rowset. You can free the connection associated with a DataReader object by invoking the Close method for either the Connection or DataReader object. Although the RecordsAffected property reports the number of records that a DataReader selects, this property returns accurate results only after you close the DataReader. If a query fails to return rows, the FieldCount property of a DataReader will equal 0.
Immediately after a DataReader is populated, its position is just prior to the first row. You can access the first row by invoking the Read method. Repeatedly invoking the Read method allows an application to pass through successive rows in the result set for a DataReader. A return value of False from the Read method indicates that no more rows remain in a result set. Retrieve the column values within a row with any of a series of Get methods, such as GetInt32, GetString, or GetSqlDateTime. The methods allow you to represent column values with data type formats. Get methods with SQL in their names, such as GetSqlDateTime, return SQL Server data type values, such as those discussed in Chapter 2. Get methods without SQL in their names return values in .NET Framework value types, such as those discussed in Chapter 9. Reference individual column values within a row by ordinal number. For example, drd1.GetString(0) returns the first column value as a string for the current row in the drd1 DataReader.
Instantiate DataAdapter objects with the SQL Server data provider as instances of the SqlDataAdapter class. You must use a SqlDataAdapter object in concert with a SqlConnection object. The SqlConnection object that a SqlDataAdapter object references is the conduit the DataAdapter object uses to link a local data source, represented by a DataSet object, to a remote data source on a server. You can instantiate a SqlDataAdapter object with an embedded T-SQL statement or with a reference to a SqlCommand object. The T-SQL statement, whether it is embedded or available in a SqlCommand object, determines which remote database objects get tapped to populate a local data set.
Use the SqlDataAdapter Fill method to populate a local data set. Before you can invoke the method, the SqlDataAdapter must have an open connection to a remote data source. After initially invoking the Fill method, your application can close the connection and work with the local data set version.
The SqlDataAdapter class facilitates data manipulation for SQL Server objects through its Update method and its related UpdateCommand, InsertCommand, and DeleteCommand properties. By specifying these three properties, you can designate data manipulation instructions, such as T-SQL syntax for an update, an insert, or a delete task. You can specify the T-SQL property directly as part of the property specification or indirectly by referencing a stored procedure. In either case, you will map columns in the local data set to columns in a remote data source by adding parameters to the commands designated by the UpdateCommand, InsertCommand, and DeleteCommand DataAdapter properties. Invoke the Update method to transfer changes to a remote data source from a local data set. The Update method—in concert with the Command objects UpdateCommand, InsertCommand, and DeleteCommand—accommodates update, insert, and delete data manipulation tasks.
The SqlDataAdapter object supports optimistic concurrency between the remote data source and the local data set. This follows from the disconnected status of the local data set from the remote data source. In fact, ADO.NET doesn’t enable pessimistic concurrency. Keyset cursors and connected recordsets aren’t a part of ADO.NET as they are of ADO. While optimistic concurrency helps to enhance scalability, it requires an extra measure of care to match the changes in a local data set back to the remote server. For example, attempting to update a row in a remote data source from a local data set that changed since you last populated the data set raises an optimistic concurrency violation. This violation raises an exception.
ADO.NET provides two techniques for handling exceptions resulting from optimistic concurrency violations. First, you can create an event procedure for the RowUpdated event. ADO.NET raises this event after each attempt to change a remote data source based on a modified row from a local data set. With a RowUpdated event procedure, you can process exceptions as they occur for each row. Second, you can set the ContinueUpdateOnError property of the SqlDataAdapter object to True before invoking the Update method. This causes ADO.NET to complete all valid updates and write any error messages to the local data set so that you can respond to them after the Update method terminates.
Figure 10-1. The DataSet object model. (Image unavailable)
ADO.NET refers to each individual table within a data set as a DataTable object. The collection of all tables within a data set is the DataTableCollection class. The tables within a data set can relate hierarchically to one another. This makes it possible for you to represent the schema for the tables in the Northwind database within a data set. The Recordset object from classic ADO doesn’t directly represent hierarchical relationships. Instead, classic ADO flattens the relationships between tables into a single rowset.
DataTable objects consist of DataColumn and DataRow objects. The set of data columns within a DataTable object is the DataColumnCollection object. The DataColumnCollection class defines the schema for a DataTable object. For example, the individual DataColumn objects specify a data type that each column can contain. DataTable objects can have a PrimaryKey property. You can define this property with a DataColumn array that can contain one or more DataColumn objects. The DataRowCollection class represents all rows within a DataTable. Column values for a local table reside within the DataRow objects that make up the DataRowCollection object for a DataTable object. Invoke the NewRow method for a DataTable to create a new row. You can then assign column values to the row and add the new DataRow object to the DataRowCollection object for a DataTable object.
ADO.NET specifies the relationship between tables in a data set with a DataRelation object. The set of all DataRelation objects within a data set is a DataRelationCollection object. Additionally, you can specify constraints for tables with the Constraint class. With a Constraint object, you can specify unique or foreign key constraints. A DataRelation object between two tables denotes a parent- child relationship between the tables. When you create a DataRelation object between two tables, ADO.NET automatically creates a foreign key constraint in the child table and a unique constraint on the primary key in the parent table. A DataRelationCollection class for a DataSet object contains each of the DataRelation objects in a data set. You can access DataRelation object members through the ChildRelations and ParentRelations properties of individual DataTable objects.
There are two techniques for eliminating a DataRow object from the DataRowCollection of a DataTable object. The first of these techniques is the Delete method, which applies to individual DataRow objects within a DataTable. This technique assigns Deleted to the RowState property for a DataRow, but it doesn’t actually remove the row from the local table. (Your application can restore a deleted row with the RejectChanges method for a DataRow object.) Alternatively, you can commit the application of a Delete method to a row so that it isn’t recoverable by invoking the AcceptChanges method for the row.
The second technique for eliminating a row from a local table is to invoke the Remove method for the DataRowCollection object within a DataTable object. This method requires that you specify the index for the row that you want to eliminate. Index values start with 0 and progress by 1 for each DataRow object within a DataTable until 1 minus the count of rows within the DataTable. This second technique doesn’t allow you to restore a row.
The Sort property for a DataView object denotes a sort order for the rows of a DataView object. You designate the Sort property as a series of column names that are comma delimited if you specify more than one column for sorting a DataView object. By default, ADO.NET sorts in ascending order. However, you can explicitly specify an ascending sort order by following a column name with ASC. Follow a column name with DESC to designate a descending sort order for a column. The DataView object sorts its rows in the order of the columns listed for its Sort property.
Although you can return a single row or a subset of rows with the RowFilter property, doing so isn’t an efficient use of it. In general, you should try to set the RowFilter property just once to save on the cost of indexing a DataView object. If you need to find a single row or a subset of rows, invoke either the Find or FindRows method. For either of these methods to work, you must first assign a Sort property setting for the criteria that you use to find rows. The FindRows method returns an array of DataRowView objects. The Find method returns the row index for a row matching the Find argument. You can then use the row index value to display a row from the DataView or its underlying DataTable.
The sample runs from Module1 within the MyADODOTNETSamples solution. After opening the solution in Visual Studio .NET, right-click the solution’s name in Solution Explorer and choose Properties from the context menu. Select Module1 as the startup object. Then double-click Module1 in Solution Explorer to open it in the Code Editor with the tab label Module1.vb. Finally, remove the comment marker (') from IntegratedCnnToNorthwind() in the main sub procedure at the top of the module, and make sure all other procedure calls in the main procedure have comment markers in front of them. You can launch the procedure to run its lines in one step by pressing the F5 key. Step through the procedure one line at a time by pressing F8 once for each successive line. These steps actually start the main procedure. Then continue pressing F8 for each step through the main and IntegratedCnnToNorthwind procedures. I often use F8 to help debug procedures or even just to clarify the flow of control through a procedure.
The sample code for the IntegratedCnnToNorthwind procedure appears next. It begins with a declaration for cnn1 as a SqlConnection class instance. The statement both declares and instantiates the cnn1 object reference in a single statement. The New keyword instantiates a SqlConnection object. The argument for the Connection object specifies the connection string for the current Windows user to connect to the Northwind database on the local default instance of SQL Server on the current computer. When referring to the local default instance, you can designate the Data Source as either "(local)" or "localhost". You can also designate a server’s name instead of the local one. For example, you could enter cab2000 as the argument for Data Source if you could physically connect to a SQL Server instance named cab2000. The server need not have the .NET Framework installed—just the workstation running your .NET application. The Initial Catalog element of the ConnectionString argument specifies the database to which you want to open a connection. By setting the Integrated Security element of the ConnectionString argument to SSPI, you can designate a connection based on the current Windows account.
After declaring and instantiating cnn1, the sample invokes the Open method for the SqlConnection object. Provided the Northwind database is available on the local SQL Server instance and the current local user has permission to access the Northwind database, the Open method succeeds. If you are the administrator of the local server and you performed a standard installation of a regular SQL Server edition, the Open method will work. After making the connection, the sample echoes the connection string. The syntax for returning the connection string either to the Debug window in Visual Studio .NET or to a message box is in the sample. (The message box way is commented out.) Before terminating, the sample closes the cnn1 SqlConnection object. You should always explicitly close SqlConnection objects in your applications when you no longer need them.
Sub IntegratedCnnToNorthwind()
’Specify connection string for connection via user’s
’Windows login; make sure user’s Windows login has access
’to the Northwind database or the Northwind database has
’a guest user account.
Dim cnn1 As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Initial Catalog=northwind;Integrated Security=SSPI")
’Attempt to open Northwind database with user’s Windows login.
cnn1.Open()
’Echo connection string to either Debug window
’or a message box.
Debug.WriteLine(cnn1.ConnectionString)
’MsgBox(cnn1.ConnectionString)
’Close connection object to dispose of it.
cnn1.Close()
End Sub
Before reviewing the code for the procedure that illustrates the ConnectionString syntax for a SQL Server login, you need to make sure that a SQL Server login is available. If you have one available, feel free to replace our sample login. However, the following T-SQL script builds on the earlier work conducted in Chapter 7. It begins by dropping the vbdotnet1 SQL Server login. This is a two-step process. First you must drop user accounts associated with the login, and then you can drop the account. After dropping the account, the script re-creates it. However, the new version explicitly grants the vbdotnet1 login access to the Northwind database. By running this script in Query Analyzer, you ensure the availability of vbdotnet1 as a valid SQL Server login for the Northwind database with passvbdotnet1 as a password.
--vbdotnet1LoginScripts.sql
--Drop vbdotnet1 login, if it exists.
--Ignore message that login doesn’t exist
--or user doesn’t exist in current database.
USE Chapter07
EXEC sp_revokedbaccess ’vbdotnet1’
EXEC sp_droplogin ’vbdotnet1’
--Add vbdotnet1 login with database access
--for the Northwind database.
USE Northwind
EXEC sp_addlogin
@loginame = ’vbdotnet1’,
@passwd = ’passvbdotnet1’,
@defdb = ’Northwind’
EXEC sp_grantdbaccess ’vbdotnet1’
The connection sample for this section runs from the SqlServerCnnToNorthwind procedure in Module1 of the MyADODOTNETSamples solution. In the main procedure for Module1, comment out all procedure calls except the one for the SqlServerCnnToNorthwind procedure. Make sure that Module1 is the startup object for the solution (as described in the preceding section). Then press F5 to run the sample.
The SqlServerCnnToNorthwind procedure has the same general format as the one in the preceding section. The most important distinction between the two procedures is the syntax for the ConnectionString argument. The ConnectionString argument for this sample replaces the Integrated Security element from the preceding sample with userid and password elements. When connecting with a SQL Server login, you must specify a login name and a password if there is one. It’s common practice to refer to the login as a userid. The following sample uses the vbdotnet1 login and password created by the preceding T–SQL script. After you run the procedure in the following sample, the Debug window will display a line that echoes the ConnectionString argument.
Sub SQLServerCnnToNorthwind()
’Specify connection string for connection via vbdotnet1
’SQL Server login; make sure vbdotnet1 login has access
’to the Northwind database via its own account or guest account.
Dim str1 As String ="Data Source=(local);"& _
"Initial Catalog=northwind;"& _
"user id = vbdotnet1; password=passvbdotnet1"
Dim cnn1 As SqlConnection = _
New SqlConnection(str1)
’Attempt to open Northwind database with vbdotnet1 login.
cnn1.Open()
’Echo connection string.
Debug.WriteLine(cnn1.ConnectionString)
’Close connection object to dispose of it.
cnn1.Close()
End Sub
--Remove access to Northwind database by
--vbdotnet1 through own or guest account,
--then drop vbdotnet1 login.
EXEC sp_revokedbaccess ’vbdotnet1’
EXEC sp_revokedbaccess guest
EXEC sp_droplogin @loginame = ’vbdotnet1’
After you run the preceding T-SQL script, the SqlServerCnnToNorthwind procedure that ran successfully in the preceding section will fail. In fact, it ends abnormally with an exception dialog like the one in Figure 10-2. Interestingly, the additional information in the dialog about the exception is singularly uninformative—"System error." Choose Continue on the dialog to recover from the exception.
Figure 10-2. Default exception dialog from attempt to connect to the Northwind database with an invalid SQL Server login. (Image unavailable)
There is a single SqlClient exception for all the run-time errors that could happen. Happily, this exception automatically returns distinct messages for different kinds of errors. The CatchSQLClientException procedure shows an adaptation of the SQLServerCnnToNorthwind procedure. The adaptation is to place the Open and Close methods from the SQLServerCnnToNorthwind procedure in the Try clause of a Try…Catch…Finally statement; the sample omits the optional Finally clause. The Catch clause demonstrates the syntax for explicitly referencing the SqlClient exception and printing the associated message.
Sub CatchSQLClientException()
’Specify connection string for connection via vbdotnet1
’SQL Server login; make sure vbdotnet1 login doesn’t have
’access to the Northwind database if you want to test
’Try...Catch...Finally statement.
Dim str1 As String ="Data Source=(local);"& _
"Initial Catalog=northwind;"& _
"user id = vbdotnet1; password=passvbdotnet1"
Dim cnn1 As SqlConnection = _
New SqlConnection(str1)
’Start looking for exceptions.
Try
’Attempt to open Northwind database with vbdotnet1 login.
cnn1.Open()
’Echo connection string.
Debug.WriteLine(cnn1.ConnectionString)
’Close connection object to dispose of it.
cnn1.Close()
’Print default error message because it is
’so short and informative.
Catch er As System.Data.SqlClient.SqlException
MsgBox(er.Message)
End Try
End Sub
The CatchSQLClientException procedure resides in Module1. You can run it like the preceding samples. Namely, comment out the calls to other procedures and remove the comment marker for the CatchSQLClientException procedure in the main procedure within Module1. Then press F5. Figure 10-3 shows the resulting error message—an exception for an invalid vbdotnet1 login. Contrast this error message with the one that appears in Figure 10-2. Notice how much more informative the second message is compared with the first one. It pays to trap the SqlClient exception! In addition, if your server were down, the same CatchSQLClientException procedure would detect it and display a message that indicates this. There is no need to tweak the code. The procedure automatically traps the error and displays an appropriate message. By the way, the message for an unavailable server is, "SQL Server does not exist or access denied."
Figure 10-3. SqlClient exception dialog from attempt to connect to the Northwind database with an invalid SQL Server login. (Image unavailable)
The sample for this section is available as Form3 in the MyADODOTNETSamples solution. In Solution Explorer, right-click the solution’s name and choose Properties. From the Startup Object drop-down list, select Form3 and click OK. Then double-click Form3.vb in Solution Explorer. This opens Form3 in Design view. You can start the application by pressing F5.
Figure 10-4 shows the sample’s Windows form instance in Design view and at run time in either of two different configurations. The Design view appears on the left. It shows Form3 with three text boxes, each with matching label controls. The top text box is for the database name, and the next two text boxes are for the userid and password when a user decides to make a connection with a SQL Server login. I assigned * to the PasswordChar property for the bottom text box so that asterisks would mask characters typed in the box. The two radio buttons on the lower left portion of Form3 allow users to specify whether they want to make a connection based on their Windows login account or use a SQL Server login account to make the connection. Finally, the Click event procedure of the Login button (Button1) makes a connection to a SQL Server instance according to what the user specifies in the form’s other controls.
The top right form in Figure 10-4 shows Form3 when it initially opens. Notice that the default setting is for making a connection with a Windows login. The Checked property of the Windows NT radio button is set to True, and just one text box appears with the Login button below. All a user has to do is type the database name in the sole text box on the form. If the login attempt succeeds, the sample displays a message confirming that the connection was made. A failed attempt might result because the Windows login account isn’t valid for the SQL Server instance. For example, perhaps there is no corresponding SQL Server login for the Windows login. Alternatively, the Windows login might not have permission to access the database named in the top text box. In any event, the application returns the SqlClient exception message associated with the error that blocked the connection from succeeding.
Figure 10-4. Design view and run-time views of a form that accepts user input and makes a connection to a SQL Server database based on either a Windows login or a SQL Server login. (Image unavailable)
The bottom right of Figure 10-4 shows Form3 ready to accept SQL Server login credentials, including a SQL Server login and its password. A user can display the text boxes for the login and password by clicking the SQL Server radio button (RadioButton2). After the user clicks the Login button, the application attempts to make the connection to the database named in the top text box with the credentials specified in the second two text boxes.
Right-clicking Form3 in Design view and choosing View Code opens the module behind Form3. This module handles both form management issues, such as controlling the visibility of the second and third text boxes, and ADO.NET issues, such as handling the attempt to connect to a database.
Three event procedures and a regular sub procedure (ShowLabelsBoxes) control the form’s appearance. Users can invoke these procedures by running Form3 or by clicking controls on Form3. For example, the Form3_Load event procedure checks RadioButton1, the one labeled Windows NT, and calls the ShowLabelsBoxes procedure while passing it a value of False. This argument causes the procedure to make the second two text boxes and their corresponding labels invisible. This appearance for the form is consistent with the default Windows login offered by Form3.
Clicking the SQL Server radio button invokes the RadioButton2_CheckedChanged event procedure. This procedure makes the second and third text boxes and their labels visible by passing the argument True to ShowLabelsBoxes. As a result, a user can enter a SQL Server login and password so that the form can attempt to make a connection based on a SQL Server instead of a Windows login.
Finally, by clicking the Windows NT radio button, the user invokes the RadioButton1_CheckedChanged event procedure. This procedure makes the controls for SQL Server login credentials invisible if they are showing. When the user clicks this RadioButton1, it indicates he or she wants to make a connection with a Windows login. Therefore, Form3 doesn’t need to show the controls for a SQL Server login.
Private Sub Form3_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
’Set RadioButton1 to Checked for connection via
’Windows NT login.
RadioButton1.Checked = True
’Hide login and password controls because they
’aren’t necessary with Windows NT login.
ShowLabelsBoxes(False)
End Sub
Private Sub RadioButton1_CheckedChanged _
(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles RadioButton1.CheckedChanged
’Hide login and password controls because they
’aren’t necessary with Windows NT login.
ShowLabelsBoxes(False)
End Sub
Private Sub RadioButton2_CheckedChanged _
(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles RadioButton2.CheckedChanged
’Show login and password controls because they
’are necessary for a SQL Server login.
ShowLabelsBoxes(True)
End Sub
Sub ShowLabelsBoxes(ByVal bolShowEm As Boolean)
’Set the visibility of the second and third text
’boxes and their labels according to the value
’of bolShowEm.
Label2.Visible = bolShowEm
TextBox2.Visible = bolShowEm
Label3.Visible = bolShowEm
TextBox3.Visible = bolShowEm
End Sub
The following excerpt from the Form3 module shows the code devoted to making the connection based on the radio button selection and text box entries. The excerpt starts with a module-level declaration of the cnn1 object reference as a SqlConnection object. A module-level declaration isn’t strictly necessary in the context of this sample, but this type of declaration makes the SqlConnection object available to other procedures that could use it. In any event, notice that the declaration specifies the full name for the namespace containing the SqlConnection object reference. This is because the module doesn’t include an Imports statement for the System.Data.SqlClient namespace. By not using the Imports statement at the top of the Form3 module, the Catch clause in the excerpt must reference a System exception instead of the more specific SqlClient exception. In spite of this deviation from the sample in the "Catching SqlConnection Exceptions" section, SqlClient exceptions still percolate up through the more general System exception specification.
Aside from the declaration issues for cnn1, the balance of the code excerpt is a straightforward mixture of the code samples developed previously in this chapter. Based on whether RadioButton1 is checked, the Button1_Click event procedure composes a connection string for either a Windows or a SQL Server login. Then the procedure instantiates a connection based on the connection string. Within a Try…Catch…Finally statement, the procedure attempts to open the connection. If the attempt succeeds, the procedure displays a message confirming the attempt was successful and naming the database. Otherwise, control flows to the Catch clause, and the procedure displays the error message associated with the exception. Because SqlClient exceptions percolate up through the System exception, the message is likely to be specific and helpful for diagnosing any problems.
‘Using the full namespace name removes the need to
‘start module with Imports System.Data.SqlClient.
Dim cnn1 As System.Data.SqlClient.SqlConnection
Private Sub Button1_Click(ByVal sender As System.Object , _
ByVal e As System.EventArgs) Handles Button1.Click
’Make local variable declarations.
Dim strDBName As String = TextBox1.Text
Dim strConnect As String
’Compose a connection string for either a Windows
’or a SQL Server login.
If RadioButton1.Checked = True Then
strConnect = "Data Source=(local);" & _
"Initial Catalog=" & strDBName & _
";Integrated Security=SSPI"
Else
Dim strLogin As String = TextBox2.Text
Dim strPassword As String = TextBox3.Text
strConnect = "Data Source=(local);" & _
"Initial Catalog=" & strDBName & ";" & _
"user id=" & strLogin & _
"; password=" & strPassword
End If
’Instantiate a SqlConnection object based on the
’connection string.
cnn1 = _
New System.Data.SqlClient.SqlConnection(strConnect)
’Embed the attempt to open the cnn1 object inside a
’Try...Catch...Finally statement, and display a
’message for the exception if there is one.
Try
cnn1.Open()
MsgBox("Successfully connected to " & cnn1.Database & _
" database on local server.")
Catch er As System.Exception
MsgBox(er.Message)
End Try
End Sub
DataReader objects read the result set returned by Command objects. Use the ExecuteReader method on a Command object to convey its result set to a DataReader object. After the invocation of the ExecuteReader method, you can extract sequential rows from a result set with the Read method for the DataReader object. Use one of the DataReader Get methods to extract the value for a specific column into a data type designated by the Get method. Columns are designated with index numbers of 0 through 1 less than the number of columns in a result set.
The EnumerateCategories procedure, which appears next, demonstrates the application of these guidelines for using Command and DataReader objects. You can invoke this procedure from Module1 in the MyADODOTNETSamples solution by adapting the instructions for running other procedures from Module1. The procedure enumerates CategoryID and CategoryName values from the Categories table in the Northwind database. A compiler constant, bolOutputWindow, permits you to direct the contents of a DataReader object to either a message box or the Output window in the Visual Studio .NET design environment. The default value for bolOutputWindow directs the DataReader contents to a message box.
After assigning a value to the compiler constant, the EnumerateCategories listing begins by declaring and instantiating cnn1 as a Connection object before invoking the object’s Open method. Next the procedure declares cmd1 as a Command object and specifies cnn1 as its Connection property with the CreateCommand method for cnn1. The listing proceeds to assign a SQL string to the CommandText property for cmd1. With an ExecuteReader method in a declaration for the drd1 DataReader, the procedure generates a result set for drd1 based on the SQL string used to define cmd1.
After the conclusion of the ExecuteReader method, the DataReader object is ready to expose its contents to a Visual Basic .NET application. The balance of the procedure introduces you to two different strategies for achieving this. A compiler If…Then…Else statement based on a compiler constant adds one of two statements to the compiled version of the procedure. Either statement returns a row from the DataReader object, but they display the row in different ways. Although the listing shows both the Then and Else clauses, the compiled procedure contains only one or the other clause based on the compiler constant value for bolOutputWindow. Before encountering the compiler If…Then…Else statement, the procedure declares a string constant that can serve as a title for the enumerated values in a message box. The constant ends with a StrDup function that can duplicate a string constant any number of times. In this case, the function appends two carriage returns to the end of the text for the title. The intrinsic constant, vbCr, denotes the string equivalent of a carriage return.
Next the procedure starts a Do…While statement with the condition drd1.Read(). This condition will return the value True as long as there are remaining rows in the DataReader. After the Read method passes through all the rows from the drd1 object, the condition returns the value False, which causes control to pass to the first statement after the Loop statement for the Do…While statement. The compiler If…Then…Else statement compiles one of two possible statements depending on the value of bolOutputWindow. When bolOutputWindow equals its default value (False), the statement appends CategoryID and CategoryName values for the current row to a string value. The values for each row end with a carriage return (vbCr). If bolOutputWindow equals True, Visual Basic .NET compiles a different statement that simply echoes the row values to the Output window with the WriteLine method for a Console object. Notice that the two compiled statements use slightly different techniques for capturing the first column value for CategoryID. Both statements use a GetInt32 method because the SQL Server data type of int for CategoryID is consistent with the .NET value type of Int32, a 32-bit signed integer. However, the path for adding the values to a string for display in a message box invokes the ToString method to convert explicitly the Int32 number to a string. This kind of conversion is preferred because it saves the time for a run-time determination of how to finally represent a returned value.
Sub EnumerateCategories()
’Compiler constant directing output to Output window
’or a message box. Default value is False.
#Const bolOutputWindow = False
’Declare and open connection to Northwind.
Dim cnn1 As SqlConnection = New _
SqlConnection("Data Source=(local);" & _
"Integrated Security=SSPI;Initial Catalog=northwind")
cnn1.Open()
’Declare a command and assign a SQL string to it.
Dim cmd1 As SqlCommand = cnn1.CreateCommand()
cmd1.CommandText = _
"SELECT CategoryID, CategoryName FROM Categories"
’Declare a data reader and copy result set from SQL string
’for cmd1 to drd1.
Dim drd1 As SqlDataReader = cmd1.ExecuteReader()
’Loop through data reader and display in Output
’window or message box.
Dim str1 As String = _
"Summary of CategoryID and Category Names" _
& StrDup(2, vbCr)
Do While drd1.Read()
#If bolOutputWindow = True Then
Console.WriteLine("Category " & drd1.GetInt32(0) & _
" is " & drd1.GetString(1))
#Else
str1 = str1 & "Category " & _
drd1.GetInt32(0).ToString & _
" is " & drd1.GetString(1) & vbCr
#End If
Loop
’Conditionally display results in a message box.
#If bolOutputWindow = False Then
MsgBox(str1)
#End If
’Close data reader and connection object references.
drd1.Close()
cnn1.Close()
End Sub
After control passes from the Do…While statement, control can flow optionally to a MsgBox function statement for displaying the string computed in the loop. A compiler If…Then statement inserts the MsgBox function into the compiled procedure if bolOutputWindow equals False. Figure 10-5 shows the outcome from the procedure when the value of bolOutputWindow is False, and Figure 10-6 is an excerpt from the Output window generated when bolOutputWindow is True. No matter which path the procedure takes to generate results, it ends by closing the drd1 and cnn1 object references. You should always perform these tasks when you no longer need a DataReader object so that SQL Server can make the connection available for other requirements.
Figure 10-5. Return for the EnumerateCategories procedure when bolOutputWindow equals False. (Image unavailable)
Figure 10-6. An excerpt from the return for the EnumerateCategories procedure when bolOutputWindow equals True. (Image unavailable)
The EnumerateCustomerIDNames procedure allows a user to specify the number of rows to show in a message box. The procedure returns the CustomerID and CompanyName column values from the Customers table in the Northwind database. You can invoke the EnumerateCustomerIDNames procedure from the main procedure in Module1. Launching this procedure is slightly different than with preceding samples from Module1. In this case, you must pass along an argument value as you invoke the procedure. The argument is for the maximum number of rows to show in a text box. The result set from the Command object for a DataReader object may extend over several blocks and require multiple message boxes. Each message box, except the final one, must hold the maximum number of rows per block passed as an argument to the EnumerateCustomerIDNames procedure. The final message box will display from one row up to the maximum number of rows.
The EnumerateCustomerIDNames procedure starts in the same general fashion as the preceding one in that it makes a connection to the Northwind database and then populates a DataReader, drd1, with the results of a Command object, cmd1. The sole distinction in how the two procedures start is that this one has a different SQL string for the Command object that returns more rows than the one in the earlier sample. This larger number of rows in the DataReader for this sample calls for special treatment because a single message box cannot display all its rows.
The balance of the procedure demonstrates one solution for the problem of too many rows to display in a message box. Two code blocks facilitate the solution. The first block iterates through the rows in drd1 in blocks of intSize. The procedure obtains a value for intSize as a passed argument from the procedure that calls the EnumerateCustomerIDNames procedure. A user can specify a block size that does fit within a single message box no matter how many rows are in the DataReader. By clicking OK on each message box, the user can view successive blocks of rows from the DataReader. The second code block captures any remaining rows at the end of a DataReader object that don’t fill a complete block.
The first code block uses int1 as a variable to count the cumulative number of rows read from the drd1 DataReader. A string variable, str1, accumulates rows in successive blocks of size intSize. The first code block uses a Do…While statement with a condition of drd1.Read() to pass successively through all the rows in the drd1 DataReader. As the code block reads each new row, it recomputes str1 so that the new row appears at the bottom of the string variable. When the remainder of int1 divided by intSize equals 0, the procedure accumulates a new block of rows (of size intSize) to display in a message box. The expression int1 mod intSize returns the remainder of int1 divided by intSize. When the first code block detects the end of a block of rows, the string variable storing row values is passed to a MsgBox function as the message argument. After printing the message, the procedure resets the string variable str1 to start a new block of rows. Then the whole process starts over again.
When no more rows remain in the DataReader, the procedure passes control to the second code block. This second block starts by testing to see whether any rows remain that didn’t appear since the display of the last message box. Any remainder of int1 divided by intSize signals undisplayed rows. If there are any of these rows, the second code block passes the value of str1 to a MsgBox function as the message argument to show them. The procedure concludes in the standard way by closing the DataReader object and its Connection object.
Sub EnumerateCustomerIDNames(ByVal intSize As Integer)
’Declare and open connection to Northwind.
Dim cnn1 As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Integrated Security=SSPI;Initial Catalog=northwind")
cnn1.Open()
’Declare command and assign a SQL string to it, and then
’declare a data reader and copy result set from cmd1 to drd1.
Dim cmd1 As SqlCommand = cnn1.CreateCommand()
cmd1.CommandText = _
"SELECT CustomerID, CompanyName FROM Customers"
Dim drd1 As SqlDataReader = cmd1.ExecuteReader()
’Loop through data reader in blocks of intSize and sequentially
’display the contents of successive blocks.
Dim int1 As New Integer()
Dim str1 As String = _
"CustomerID and matching CompanyName column values" _
& StrDup(2, vbCr)
Do While drd1.Read()
str1 = str1 & drd1.GetString(0) & vbTab & _
drd1.GetString(1) & vbCrLf
int1 += 1
If (int1 Mod intSize) = 0 Then
str1 = str1 & StrDup(2, vbCr) & _
"Click OK for next " & _
intSize.ToString &"customers."
MsgBox(str1, ,"CustomerID and Customer Name")
str1 = _
"CustomerID and matching CompanyName " & _
"column values" & StrDup(2, vbCr)
End If
Loop
’If a partial block remains at end of data reader contents,
’display partial block.
If (int1 Mod intSize) > 0 Then
str1 = str1 & StrDup(2, vbCr) _
& "Click OK to close message box."
MsgBox(str1, , "CustomerID and Customer Name")
End If
’Close data reader and connection object references.
drd1.Close()
cnn1.Close()
End Sub
Figure 10-7 shows the first and last message boxes that result from running the EnumerateCustomerIDNames procedure with an intSize argument value of 25. The first message box contains 25 rows, as do all the intervening message boxes up until the last one. The last message box shows the rows remaining at the end that don’t fill an entire block of 25 rows.
Figure 10-7. The first and last message boxes displayed by the EnumerateCustomerIDNames procedure. (Image unavailable)
There are two approaches to setting parameter values for stored procedures. Many developers prefer to specify a SQL string that invokes the stored procedure and passes the value. Chapter 4 illustrates the syntax for accomplishing this, and we demonstrate the use of the technique in a .NET Framework application with the sample for this section. A second approach is to add parameters with the .NET Framework syntax. This approach allows you to explicitly specify the data type as you pass a parameter. I will demonstrate this second approach in the next section.
The sample for this section and the next one depends on the CustOrderHist stored procedure in the Northwind database. This procedure returns the quantity of each product ordered by a customer. The procedure takes a five-character string parameter to designate the CustomerID value. The result set contains a single row for each product ever ordered by a customer. Each row contains the product name and quantity ordered by the customer specified in the parameter when you invoke the stored procedure. For your convenience in understanding the logic of the CustOrderHist stored procedure, here’s the T–SQL code for the stored procedure:
CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND
O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName
Two sub procedures make up the solution for displaying the results from running the CustOrderHist stored procedure with a SQL string. The first sub procedure, RunCustOrderHistWithString, invokes the SQL string for the stored procedure and creates a DataReader object based on its result set. RunCustOrderHistWithString takes two arguments—one for the CustomerID value and a second for specifying the maximum number of rows to display as a block in a message box. This initial Visual Basic .NET procedure:
Because the sample uses a SQL string to invoke the stored procedure and pass a parameter, the process of running a stored procedure with a parameter is similar to just specifying a SQL string as the source for the Command object. This similarity is the chief advantage of using the SQL string to invoke the stored procedure. One disadvantage of the approach is that the server has to compile the T-SQL statement in the string to invoke the stored procedure. Another disadvantage is that you don’t get the benefit of explicit data typing for the parameter value at the client end of the solution. This explicit typing can allow you to catch inappropriate parameter values earlier in the solution and save server time devoted to detecting erroneous parameter values as well as passing back feedback on the error to the client.
The solution’s second sub procedure, drdToMessageBox, displays the rows in the DataReader created by RunCustOrderHistWithString. The drdToMessageBox procedure requires four arguments. The first two are passed by reference instead of in the normal Visual Basic .NET way of by value. These arguments are for the DataReader object and its associated Connection object. The second two arguments are passed by value. These are the CustomerID parameter value and the value for the maximum number of rows to display in a message box. The design of this second sub procedure is a direct extension of prior samples with specific adjustments, such as for the title within a message box. A specific benefit of dividing the solution across two sub procedures is that we will be able to reuse this second sub procedure in the next section’s sample.
Sub RunCustOrderHistWithString(ByVal CustomerID As Stri ng, _
ByVal intSize As Integer)
’Declare and open connection to Northwind.
Dim cnn1 As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Integrated Security=SSPI;Initial Catalog=northwind")
cnn1.Open()
’Declare command with T-SQL for a stored proc with a parameter.
Dim cmd1 As SqlCommand = _
New SqlCommand("EXEC CustOrderHist " & CustomerID, cnn1)
’Declare data reader and populate with result set
’from stored procedure.
Dim drd1 As SqlDataReader = cmd1.ExecuteReader()
’Display result set.
drdToMessageBox(drd1, cnn1, CustomerID, intSize)
End Sub
Sub drdToMessageBox(ByRef drd1 As SqlClient.SqlDataReader, _
ByRef cnn1 As SqlClient.SqlConnection, _
ByVal CustomerID As String, _
ByVal intSize As Integer)
’Declare header for report in message box and counter for rows
’showing within a message box.
Dim str1 As String = _
"Quantities for Products Ordered by " & _
CustomerID & StrDup(2, vbCr)
Dim int1 As Integer
’Loop through data reader in blocks of intSize and
’sequentially display the contents of successive bl ocks.
Do While drd1.Read()
str1 = str1 & drd1.GetInt32(1) & vbTab _
& drd1.GetString(0).ToString & vbCrLf
int1 += 1
If (int1 Mod intSize) = 0 Then
str1 = str1 & StrDup(2, vbCr) _
& "Click OK for next " & _
intSize.ToString & " customers."
MsgBox(str1, , "From CustOrderHist Stored Proc")
str1 = _
"Quantities for Products Ordered by " & _
CustomerID & StrDup(2, vbCr)
End If
Loop
’If a partial block remains at end of data reader contents,
’display partial block.
If (int1 Mod intSize) <> 0 Then
str1 = str1 & StrDup(2, vbCr) _
& "Click OK to close message box."
MsgBox(str1, , "From CustOrderHist Stored Proc")
End If
’Close data reader and connection object references.
drd1.Close()
cnn1.Close()
End Sub
You can run the sample defined by the preceding two sub procedures from Module1 in the MyADODOTNETSamples solution. The sample procedure call in the main procedure for invoking the first procedure follows. It passes two arguments to the RunCustOrderHistWithString procedure. The first argument is a CustomerID value, and the second argument designates the maximum number of rows to display in a message box. You can obtain a result set to display for any CustomerID in the Customers table that has orders associated with it. (Two CustomerID values don’t have any orders.) The solution automatically populates the argument list for the second sub procedure that prints the rows in the DataReader created by the RunCustOrderHistWithString procedure.
RunCustOrderHistWithString("TORTU", 10)
The solution to invoke a stored procedure without a SQL string requires you to assign the name of the stored procedure as the CommandText property for a Command object. You must also designate CommandType.StoredProcedure as the CommandType property setting for the Command object. If the stored procedure requires parameters, you can invoke the Add method for the Parameters collection of the Command object to declare the parameters. As with many Visual Basic .NET methods, the specification for the Add method of the Parameters collection has multiple overloaded specifications. The one used in the sample for this section uses @CustomerID to designate the parameter’s name. The second and third arguments for the Add method designate the @CustomerID parameter as a Unicode fixed length text field of 5 characters. The sample follows the parameter declaration with the syntax for assigning an actual value to the parameter. As you can see, you use the parameter’s Value property to perform this task.
Aside from the exceptions noted previously, the solution for running the CustOrderHist stored procedure with or without a SQL string is the same. You create the Connection object identically, and you pass the return set from the Command object to the DataReader object in the same way. Furthermore, this second-solution approach uses exactly the same second sub procedure, drdToMessageBox, to display the result set from the CustOrderHist stored procedure in a series of message boxes.
Sub RunCustOrderHistWithParameter(ByVal CustomerID As S tring, _
ByVal intSize As Integer)
’Declare and open connection to Northwind.
Dim cnn1 As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Integrated Security=SSPI;Initial Catalog=northwind")
cnn1.Open()
’Instantiate a command reference pointing at the
’CustOrderHist stored proc.
Dim cmd1 As SqlCommand = _
New SqlCommand("CustOrderHist", cnn1)
cmd1.CommandType = CommandType.StoredProcedure
’Declare the parameter with a SqlDbType to eliminate
’the need for conversion, then assign the parameter a value.
Dim prm1 As SqlParameter = _
cmd1.Parameters.Add("@CustomerID", SqlDbType.NChar, 5)
prm1.Value = CustomerID
’Declare data reader and populate with its result
’set from the stored proc.
Dim drd1 As SqlDataReader = cmd1.ExecuteReader()
’Display result set.
drdToMessageBox(drd1, cnn1, CustomerID, intSize)
End Sub
You can invoke the RunCustOrderHistWithParameter procedure from the main procedure in Module1 for the MyADODOTNETSamples solution. Simply remove its comment marker and ensure that all other procedure calls have a comment marker preceding them.
The user-defined function udfDaysDiffLessx in this sample computes the difference between two dates minus an offset. You can use the function to report how many days late an event occurred. For example, if the standard for shipping an order is within 3 days of the order date, you can use this user-defined function to report how many days after the standard an order ships.
The CreateAndInvokeUDF procedure in Module1 illustrates the Visual Basic .NET syntax for creating, using, and finally dropping a user-defined function like the one described. The CreateAndInvokeUDF procedure connects to the Northwind database. The procedure takes two optional arguments. (If the user doesn’t supply values for the arguments when calling the procedure, the procedure assigns default values to the arguments.) The intOrderNo argument denotes the OrderID value for the order about which you seek shipping information, and the strx argument is a string representing the offset in days between two datetime values.
While somewhat lengthy, the CreateAndInvokeUDF procedure design is straightforward. In actual practice, you are likely to extract the code for creating a user-defined function into a separate sub procedure. The procedure begins by making a connection to the Northwind database. Next the procedure defines a SQL string for dropping any prior version of the udfDaysDiffLessx user-defined function. The procedure runs this string from a Command object with the ExecuteNonQuery method. In the next code block, the procedure runs with the ExecuteNonQuery method a second SQL string to create a new version of the udfDaysDiffLessx user-defined function. Notice that the user-defined function includes a parameter to specify the offset for the difference between two dates.
After ensuring that the code for the user-defined function is the second SQL string, the procedure runs a third SQL string that invokes the user-defined function within a query statement. The design of the SQL string for the query uses the strx argument as a variable so that a procedure calling the CreateAndInvokeUDF procedure can dynamically set the offset between two dates. In addition, the intOrderNo argument is a variable in the SQL string so that a calling procedure can specify the order via an OrderID value on which to report. The procedure uses the ExecuteReader method to run the SQL string in a Command object and passes the result to a DataReader. After executing the Read method for the DataReader, a message box displays the shipping information for the order. The procedure concludes by performing various cleanup chores, including restoring the Northwind database so that the database no longer has a user-defined function named udfDaysDiffLessx. In practice, you may very well decide to keep a user-defined function after creating it, but the sample runs this step to restore your initial copy of the Northwind database.
Sub CreateAndInvokeUDF( _s
Optional ByVal intOrderNo As Integer = 10248, _
Optional ByVal strx As String = "1")
’Declare and open connection to Northwind.
Dim cnn1 As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Integrated Security=SSPI;Initial Catalog=northwind")
cnn1.Open()
’Define SQL string to drop prior version of user- defined
’function, then run the T- SQL batch with ExecuteNonQuery
’method for a command.
Dim str1 As String = _
"IF EXISTS " & _
"(SELECT * " & _
"FROM INFORMATION_SCHEMA.ROUTINES " & _
"WHERE ROUTINE_NAME = ’udfDaysDiffLessx’) " & _
"DROP FUNCTION udfDaysDiffLessx"
Dim cmd1 As SqlCommand = New SqlCommand(str1, cnn1)
cmd1.ExecuteNonQuery()
’Define SQL string to create a new user- defined function,
’then run the T-SQL batch with ExecuteNonQuery method
’for a command.
str1 = "CREATE FUNCTION udfDaysDiffLessx" & _
"(@date1 as datetime, @date2 as datetime, " & _
"@x as Integer) " & _
"RETURNS int " & _
"AS " & _
"BEGIN " & _
"Return(DATEDIFF(day,@date1,@date2)-@x) " & _
"END"
cmd1.CommandText = str1
cmd1.ExecuteNonQuery()
’Define a SQL string to use the preceding user-defined
’function and accept variables for SQL string
’(strx and intOrderNo), then assign SQL string to
’CommandText property of command(cmd1).
Dim strSQL As String
strSQL = "SELECT LEFT(OrderDate,11) AS ’Order Date’, " & _
"LEFT(ShippedDate,11) AS ’Shipped Date’, " & _
"dbo.udfDaysDiffLessx(OrderDate, ShippedDate, " & _
strx & ") AS ’Days Late’ " & _
"FROM Orders " & _
"WHERE OrderID = " & intOrderNo.ToString
cmd1.CommandText = strSQL
’Store result set from SQL string in a data reader and
’format its contents for display via a MsgBox function.
Dim drd1 As SqlDataReader = cmd1.ExecuteReader()
drd1.Read()
str1 = "For Order " & intOrderNo.ToString & vbCr & _
"OrderDate is " & drd1.GetString(0) & vbCr & _
"ShippedDate is " & drd1.GetString(1) & vbCr & _
"Days to ship after " & strx & " days is " _
& drd1.GetInt32(2).ToString
MsgBox(str1, , _
"SQL string with a scalar user-defined function")
’Restore the Northwind database by removing the udf.
str1 = _
"IF EXISTS " & _
"(SELECT * " & _
"FROM INFORMATION_SCHEMA.ROUTINES " & _
"WHERE ROUTINE_NAME = ’udfDaysDiffLessx’) " & _
"DROP FUNCTION udfDaysDiffLessx"
cmd1.CommandText = str1
’Close the data reader so the command can use it.
drd1.Close()
’Execute the SQL string to drop the user-defined function.
cmd1.Connection = cnn1
cmd1.ExecuteNonQuery()
’Finally, close the connection to the Northwind database.
cnn1.Close()
End Sub
The line in the main procedure of Module1 invoking the CreateAndInvokeUDF procedure specifies an OrderID of 10249 with intOrderNo and an offset of 3 days with strx. In response to invoking the CreateAndInvokeUDF procedure with this line, the procedure presents a message box like the one in Figure 10-8. If you were interested in tracking performance on a next-day delivery promise, you could replace the value 3 in the calling procedure with 1.
Figure 10-8. The message box displayed for running the CreateAndInvokeUDF procedure with the arguments specified for it in the main procedure of Module1. (Image unavailable)
The key to populating a data set behind a form with data from a SQL Server instance is to create a DataAdapter object that points to a data source on a SQL Server instance. You can represent the data source on the server with a SQL string, a table name, a view, or a stored procedure. As with the DataReader object, you can represent a SQL string for the DataAdapter object with a Command object. The DataAdapter object has two main roles. First, it can fill a data set behind a form. That’s the focus of this section. Second, you can use a DataAdapter to update a remote data source from the data set behind a form. That’s the focus of the last major section in this chapter.
Use the DataAdapter object’s SelectCommand property to reference the Command object specifying the remote data source for a DataAdapter. Recall that one important role for a DataAdapter is to copy to the data set behind a form. Make the remote data source available through the DataAdapter by opening the connection for the Command object. Copy the data from the remote data source to the data set by invoking the Fill method of the DataAdapter. In this type of application, the DataAdapter requires two arguments—one referencing the name of the data set behind the form and the other naming the table in the data set. You can designate the tables within a data set either by an index number indicating the order in which you added them to the data set or by the name that you specify as an argument to the Fill method.
The Populate procedure that follows illustrates the syntax for copying a remote data source to the data set behind a form. This procedure is in the module behind Form4, which I will discuss in more detail in the next sample discussion. For now, just understand that the Populate procedure is in a module behind a Windows form. I’ll be using several samples throughout the balance of this chapter that are variations of this procedure, so I decided to give the procedure a section of its own to help you focus on it.
It’s common to describe the DataAdapter as a bridge between a remote data source and the data set behind a form. Therefore, the Populate procedure starts by declaring a Connection object, cnn1. The cnn1 object reference points to the Northwind database on the local instance of SQL Server. Next the procedure declares and instantiates a Command object, cmd1. A SQL string specifies the CategoryID, CategoryName, and Description columns from the Categories tables to designate the result set from cmd1. The Command object cmd1 links to the Categories table through the Connection object cnn1. After indirectly specifying the CommandText property for a Command object, the procedure instantiates a DataAdapter object and uses the dap1 object reference to point to it.
In order for the dap1 DataAdapter to fill the data set behind the form, two conditions must hold. First, the DataAdapter needs a Command object assigned to its SelectCommand property. Assigning cmd1 to the SelectCommand property of dap1 satisfies this condition. Second, the DataAdapter requires an open connection to the Categories table in the Northwind database. Invoking the Open method for the cnn1 object meets this requirement. After meeting these two conditions, the procedure invokes the Fill method for dap1. The arguments for the method in the procedure designate Categories as the name of the DataTable object that holds the result set from cmd1 in the das1 data set. The module behind Form4 declares and instantiates das1 as a data set at the module level. This makes the das1 data set available for use in all the procedures behind a form. Of course, it also means that you cannot see the declaration in the listing for the Populate procedure. For your easy reference, I include the statement declaring and instantiating das1 just before the listing for the Populate procedure.
Notice that the Populate procedure concludes by closing the Connection object cnn1. In contrast to the DataReader object, the data set object operates while disconnected from a remote data source. Recall that this ability to operate while disconnected adds to the scalability of Visual Basic .NET applications for SQL Server.
’Module-level declaration of data set object.
Dim das1 As DataSet = New DataSet()
Sub Populate()
’Specify a connection for a data adapter that
’fills the data set used on the form.
Dim cnn1 As SqlConnection = _
New SqlConnection _
("Data Source=(local);" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=northwind")
’Specify the command and data adapter that serves
’as the source for the data set on the form.
Dim cmd1 As SqlCommand = _
New SqlCommand _
("SELECT CategoryID, CategoryName, Descript ion " & _
"FROM Categories", _
cnn1)
Dim dap1 As SqlDataAdapter = New SqlDataAdapter()
dap1.SelectCommand = cmd1
cnn1.Open()
’Fill the data set (das1) with the data adapter dap1;
’the Fill method populates the data set with a table
’named Categories.
dap1.Fill(das1, "Categories")
’Close the connection because a data set is a
’disconnected data source.
cnn1.Close()
End Sub
Figure 10-9 shows Form4. At the left is the form in Design view. At the top right of the figure is the form after it initially opens. The bottom right of the figure shows the form after I selected Confections from the combo box. Open Form4 in Design view by double-clicking Form4.vb in Solution Explorer for the MyADODOTNETSamples solution. Right-click the solution’s name in Solution Explorer, choose Properties, and select Form4 as the startup object to make the form easy to launch (for example, by pressing the F5 key).
The Design view of Form4 reveals that the form contains a combo box with a label, two text boxes with labels, and a button. As shown in Chapter 1, you can graphically bind controls at design time. However, Form4 programmatically sets the data binding for the combo box and the two text boxes. On the other hand, I set several control features at design time. For example, the Multiline property of TextBox2 is set to True, while the same property for TextBox1 has the default setting, False. The Multiline property setting facilitates TextBox2 showing Description column values that extend over more than one line.
Figure 10-9. A design-time view and two run-time views of Form4. The two text boxes are programmed to update their contents based on the selection from the combo box. (Image unavailable)
The initial view of Form4 shows that when it opens it displays the first category. Beverages appears in the combo box, and the two text boxes show 1 as the CategoryID and the description for the beverages product category. There is nothing mandatory about opening the form for the first category—any other category will work equally well. The form synchronizes the two text boxes with the combo box. For example, selecting Confections from the combo box revises the content displayed in the two text boxes to 3 and the description for the confections category.
To bind the form controls to data set columns and make the text boxes dependent on the combo box selection takes just a few lines of code. I used five lines of code to bind the controls to data set column values and set the category that appears when the form opens. This code appears in a form Load event procedure for Form4 that starts by calling Populate to create the das1 data set described in the preceding section.
The event procedure puts das1 to use by binding the Text property of TextBox1 to the CategoryID column in the Categories DataTable. You bind a column of values to a text box property by invoking the Add method for the DataBindings collection of a control. The Add method takes a Binding object as an argument. The arguments for the Binding object specify the TextBox property to bind (Text) and the column of values to bind to the property. This sample requires two arguments to specify the data source that binds to the text box property. First designate the data set name—das1. Second indicate the table name and column name within the data set that you want to bind to the property. Use a period delimiter to separate the two names, as in Categories.CategoryID. The Load event procedure uses the same syntax to bind the Description column in the Categories DataTable to the Text property of TextBox2. Both data bindings demonstrate the approach for simple data binding.
It takes a couple of lines to bind the combo box to the Categories DataTable. Actually, one line does the binding, but a second line specifies the values that the combo box displays for the user to make a selection. Assign a DataTable to the DataSource property of a combo box to bind the combo box to the DataTable. The syntax for specifying the Categories table used a named argument for denoting the table in the data set. I could also have indicated the Categories table by indicating its table index value, such as das1.Tables(0). This syntax depends on the table index values not changing. After setting the DataSource property for the combo box, the procedure assigns the CategoryName column from the Categories DataTable as the value for the combo box to display when the user clicks the control to make a selection.
The final line of the form Load event procedure designates the position in a column that the controls on Form4 bound to the first table in the das1 data set are to show when the form initially opens. Position 0 points to the first row in a DataTable (for example, the Categories DataTable in this sample). The Position property belongs to the BindingContext object associated with a form. The keyword Me denotes Form4 in the last line of the form Load event procedure.
Private Sub Form4_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
’Call the routine for creating the data set
’for the form.
Populate()
’Bind each text box to a different column in the
’Categories table of the data set (das1)on the form.
TextBox1.DataBindings.Add _
(New Binding("Text", das1, "Categories.CategoryID"))
TextBox2.DataBindings.Add _
(New Binding("Text", das1, "Categories.Description"))
’Bind combo box to Categories table in the
’data set (das1) on the form. Because the data set
’includes just one table, its index is 0.
ComboBox1.DataSource = das1.Tables("Categories")
ComboBox1.DisplayMember = "CategoryName"
Me.BindingContext(das1.Tables(0)).Position = 0
End Sub
The SelectedIndexChanged event procedure for the combo box takes just one line to synchronize the contents of the text boxes with the category name a user selects from the combo box. The index values for a combo box start at 0 for the first item in the list for a combo box. By setting the combo box’s SelectedIndex property to the Position property of the form’s BindingContext object, the line positions all controls on the form to the same row a user selected indirectly when picking a category name from the combo box.
Private Sub ComboBox1_SelectedIndexChanged _
(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles ComboBox1.SelectedIndexChanged
’Use selected combo item as basis for text boxes.
Me.BindingContext(das1, "Categories").Position _
= Me.ComboBox1.SelectedIndex
End Sub
As you can see from the following listing, the Click event for the button merely calls the PrintBindingMemberInfo procedure. However, the call also passes a reference to Form4 by using the keyword Me as an argument. The PrintBindingMemberInfo procedure in this sample is adapted from an example in the Visual Basic .NET Help file. While the adaptation is subtle, it substantially enhances the applicability of the procedure. First, the adaptation works for any form reference passed to it. The sample in the Help file had to be copied into the module for any form on which you sought a report. Second, you can run the adapted procedure even if you aren’t in the form for which you seek a report. The sample in the Help file works only from a form that a user has open with the focus.
The PrintBindingMemberInfo procedure accepts a form reference as an argument. For the referenced form, the procedure starts a loop to pass through all the controls on the form. Within the loop for the controls on a form, the procedure runs a second loop to report any data binding for the currently selected control in the loop through the controls. If there are no data bindings for a control, the inner loop merely returns control to the outer loop for the controls. When all the controls on a form are looped through, the PrintBindingMemberInfo procedure returns control to its calling procedure, which is the Click event for Button1 on Form4 in the following listing.
'From module for Form4.
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
'Display run-time binding settings specified in this module.
Module1.PrintBindingMemberInfo(Me)
End Sub
‘From Module1.
‘Adapted from Visual Basic .NET Help; the adaptation accommodates
‘any form as a passed argument and facilitates displaying run-time
‘bindings from outside a form.
Sub PrintBindingMemberInfo(ByRef MyForm As Form)
Dim thisControl As Control
For Each thisControl In MyForm.Controls
Dim thisBinding As Binding
For Each thisBinding In thisControl.DataBindings
’Print the control’s name and Binding information.
Console.WriteLine(ControlChars.Cr + thisControl.ToString())
Dim bInfo As BindingMemberInfo = thisBinding.BindingMemberInfo
Console.WriteLine("Binding Path " + ControlChars.Tab _
+ bInfo.BindingPath)
Console.WriteLine("Binding Field " + ControlChars.Tab _
+ bInfo.BindingField)
Console.WriteLine("Binding Member " + ControlChars.Tab _
+ bInfo.BindingMember)
Next thisBinding
Next thisControl
End Sub
Figure 10-10 shows an excerpt from the Output window showing the outcome generated by clicking the Show Bindings button in Form4 just after the form opens. The output shows feedback for the two text boxes. The text box reporting descriptions appears above the one that displays the CategoryID value. Recall that the form initially shows data for CategoryID 1 when it opens. The contents for each text box reflect the value for this category. You can also see that the procedure returns information about the DataTable name and the column within a table to which each text box on the form binds.
Figure 10-10. A report generated by clicking the Show Bindings button on Form4. (Image unavailable)
The PrintBindingMembe