ADO.NET Architecture

As the previous section mentioned, the .NET data providers offer four classes for handling data: Connection , Command , DataReader , and DataAdapter . This section reviews selected properties and methods for each of these classes. In addition, a few pieces of sample code illustrate the basics of using the properties and methods for these classes. The samples in Chapter 8 and Chapter 9 will illustrate more practical ways of using these classes that build on the foundation laid by this chapter s discussion and sample code. You will find all this chapter s forms and sample code in the OleDbObjectSamples project. A switchboard program in Form1 , the startup object, lets you navigate to each of the other forms.

OleDbConnection

The OleDbConnection class is the OLE DB .NET Data Provider class for implementing a connection with a database. You point an OleDbConnection object at a database. Other ADO.NET objects can use the contents of the database through an OleDbConnection object that points at it.

Selected OleDbConnection Properties and Methods

You can use the New method of the OleDbConnection class to create a fresh connection to a database. The New method takes an optional string argument that designates a connection string for the database to which you want to point an OleDbConnection instance. If you do not specify an argument for the New method, you can assign a connection string to an OleDbConnection object through its ConnectionString property. These two alternative formulations comprise the set of overloaded constructors for the OleDbConnection class. See the Overview of Concepts and Keywords and Derived Class Features sections in Chapter 4 for background material of the power that overloaded constructors bring to .NET Framework classes.

You can look at the contents of the ConnectionString property to verify the arguments for an OleDbConnection instance. The ConnectionString property reflects the connection properties that you set before you open the database connection. Use the Open method to enable a connection to a database and the Close method to free a connection to a database. You cannot assign a new ConnectionString property value for an OleDbConnection object while the object is open, but you can assign a new value to the property after invoking the Close method for the OleDbConnection instance.

Note  

To refer to System.Data.OleDb namespace members by their name without designating the namespace name as a prefix, the module instantiating a connection object must have an Imports statement, such as Imports System.Data.OleDb . Without this Imports statement, any reference in code to a namespace member must have the namespace as a prefix.

The details of a connection string are specific to the type of database that you are using and the way that you want to use it. To connect to an Access database, you need to specify a Provider clause and a Data Source clause. Assign a string value to the Provider argument that designates the name of the provider. This is the same argument that you used with ADO: Microsoft.Jet.OLEDB.4.0 . Follow the provider string with a semicolon. Then designate the Data Source clause. This is the path and filename to the Access database file. For example, for the Northwind database in Access 2002 at its default location, you can specify c:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb. You do not have to designate the full path when your database is in the bin subdirectory of the project folder. Instead, you can specify just the filename. Therefore, if you have a copy of the Northwind.mdb file in the bin subdirectory of the project folder, you can designate the Data Source argument as Northwind.mdb without any path information.

Sample Code

Figure 7-2 shows Form2 in the OleDbObjectSamples project. This form has two buttons , each with a click event procedure in the module behind the form. In addition, a connection object from the OLE DB .NET Data Provider appears below the form with the name OleDbConnection1 . I dragged the whole connection (not any particular database object) from Server Explorer to create the object instance. Because I dragged the connection instead of an object within the database, Visual Studio .NET did not generate a data adapter. In addition, Visual Studio .NET was smart enough to recognize that an Access database needs a connection object from the OLE DB .NET Data Provider. Therefore, the IDE created an instance of the OleDbConnection class as opposed to a connection class based on one of the other .NET data providers.

Figure 7-2: A form with a pair of buttons and a connection object created with Visual Studio .NET at design time

The module behind Form2 processes two connections. First, it works with a connection object instantiated via code at run time, which means you do not need a preexisting connection defined in Server Explorer. You can simply define the two minimum clauses for the connection object s connection string and call the Open method on the object to have a live connection to a database. Second, the code behind Form2 works with the OleDbConnection1 object that appears below the form in Figure 7-2. OleDbConnection1 is a design-time instance of an OleDbConnection object. By displaying the connection string for OleDbConnection1, you can see the special assignments that Windows Form Designer makes as you drag items from Server Explorer to a form.

Deploying a Project with a Connection

In general, you should deploy Windows application projects for running on another computer as the project folder. The Using the Start Page section in Chapter 2 briefly describes a couple of approaches for implementing this. For projects that use a database, the connection can point to a database in the project folder, in some standard location for the database on every computer, or in a single standard UNC specification on a LAN from which all workstations access the database.

Although you can sometimes run a project with a database connection residing on another computer, my recommendation is to always deploy the project folder to the computer running the project. This avoids potential problems from cross-computer security settings that can block the execution of the project. Access developers will note that this approach is similar to the requirement for .adp files that work with SQL Server databases or for a front-end/back-end solution for .mdb files. In the latter case, all users have their own front-end project (an .mdb file) that links to a common back-end project (another .mdb file shared by multiple users). Releasing a change to the front-end design involves distributing a new front-end file to each user. Similarly, with Visual Basic .NET projects connecting to an Access project, you should distribute a Visual Basic .NET project to each user .

I will discuss security issues for Access databases in Visual Basic .NET solutions in more detail in Chapter 13. For now, we will focus on the basics of instantiating ADO.NET objects in code and using those objects.

 

The listing for the code behind Form2 appears next . The code module for Form2 contains an Imports statement for the System.Data.OleDb namespace not shown in the listing.

The click event procedure for Button1 is the most interesting of the two event procedures. The Button1_Click procedure starts by designing a connection string that points at the Northwind database. You can readily adapt this approach to any other database simply by changing the string value in the second assignment to str1 . After designing the connection string, the procedure puts the string to work by creating an OleDbConnection object named cnn1 and opens the connection. At this point, you can tell whether your settings are correct and the database file is available. After connecting successfully, the procedure displays the Provider and DataSource properties for the cnn1 object. These two properties correspond to the two connection string clauses specified before instantiating the Connection object.

In production solutions, you will probably want to place your statements invoking the Open method in a Try...Catch Finally statement. The procedure illustrates the syntax for a simple Try...Catch Finally statement that catches an error from an attempt to invoke an invalid call to the Open method.

After making one connection with the cnn1 object, the procedure prepares to reuse the cnn1 object to connect to a new database. To revise the connection string, the procedure needs to call the Close method to close the cnn1 object. (Microsoft recommends using the Close method rather than the more generic Dispose method.) The new Connection object has an intentional error in its connection string. The string designates a connection to the Northwinds database, instead of the Northwind database. My test computers for this book have no Northwinds database, so the procedure fails. The attempt to open the connection for the cnn1 variable generates an error with a System.Data.OleDb.OleDbException object. To keep the code simple, the Try Catch Finally statement checks for a general system Exception object and displays a general message. Even this elementary statement design manages to recover gracefully from the error when it occurs. Notice in the Finally clause that the procedure closes the cnn1 object. The .NET Framework does not throw another exception in this case, even though the cnn1 object is already closed. Therefore, it is good practice to include a Close method for a connection in the Finally clause when you embed its Open method in the Try clause.

 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Designate string for connection to Northwind database. Dim str1 As String = _ "Provider = Microsoft.Jet.OLEDB.4.0;" str1 += _ "Data Source = c:\Program Files\" & _ "Microsoft Office\Office10\Samples\Northwind.mdb" Instantiate and open cnn1 instance of OleDbConnection class. Dim cnn1 As OleDbConnection = New OleDbConnection(str1) cnn1.Open() Display Provider and Data Source. MsgBox("Provider=" & cnn1.Provider & vbCr & _ "Data Source=" & cnn1.DataSource, _ MsgBoxStyle.Information, _ "After initial open") Close connection. cnn1.Close() Specify new erroneous connection string to the "Northwinds.mdb" Access database file. cnn1.ConnectionString = _ "Provider = Microsoft.Jet.OLEDB.4.0;" cnn1.ConnectionString += _ "Data Source = c:\Program Files\" & _ "Microsoft Office\Office10\Samples\" & _ "Northwinds.mdb" Display Provider and Data Source. MsgBox("Provider=" & cnn1.Provider & vbCr & _ "Data Source=" & cnn1.DataSource, _ MsgBoxStyle.Information, _ "After resetting connection string") Attempt to connect with invalid connection string. Try cnn1.Open() Catch exc1 As Exception MsgBox("Something went wrong.", _ MsgBoxStyle.Exclamation, _ "Wrong Connection String") Finally cnn1.Close Me.Close() End Try End Sub Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click Dim str1 As String = OleDbConnection1.ConnectionString Display full list of connection string clauses for a default connection. MsgBox(str1) Close the form. Me.Close() End Sub 

The second procedure in the preceding listing handles a click to Button2 . This procedure merely displays the connection string for the OleDbConnection1 object before closing the form. As mentioned, I created the OleDbConnection1 object at design time by dragging a connection from Server Explorer to Form2 . Therefore, Visual Studio .NET constructed the connection string for the application. Figure 7-3 shows the message box opened by a click to Button2 . Notice that it contains substantially more than the Provider and Data Source clauses designed in the click event procedure for Button1 . These additional clauses are specified by the Windows Form Designer. The items in the message box do not represent the total set of OleDbConnection object properties. In fact, many of these items are not connection properties but instead are optional settings for the Jet engine. The message box returns these clauses because the Windows Form Designer specifies them. These items suggest that you have some flexibility in creating your connection strings. For example, for database files with user-level security, you will need to specify connection strings with UserID and Password clauses.

Figure 7-3: A message box showing an expanded range of connection string clauses that are available for your use

OleDbCommand

An OleDbCommand object is a wrapper for a SQL statement. With this kind of ADO.NET object, your applications can program any of the three classic SQL language functions of data access, data manipulation, and data definition. The OleDbCommand object is capable by itself of returning a scalar value from a database, such as the count or the sum of the values in a column of a table. You can also use the OleDbCommand object with either an OleDbDataReader or an OleDbDataAdapter to process sets of rows from a data source. The SQL statement inside an OleDbCommand requires a database context that designates where to invoke that statement. You can assign the database context for the SQL statement by assigning an OleDbConnection instance to the Connection property for the OleDbCommand object.

Note  

To get the most from the OleDbCommand class, you will need valid Jet SQL statements that can accomplish the tasks you want to perform. Access developers can still build a query graphically within Access, then copy and paste the code from the query s SQL view into another location, such as a Visual Basic .NET procedure. The SQL Reserved Words Access Help topic under Microsoft Jet SQL Reference, Overview, documents Jet SQL statements, but the links from this page do not offer many code samples. My book Programming Microsoft Access Version 2002 (Microsoft Press, 2001) includes 20 pages that present Jet SQL code samples for most data access tasks and another 10 pages with Jet SQL samples for data definition tasks.

The CommandText and CommandType properties help you specify the contents of or the source for an OleDbCommand instance. By default, the CommandType property equals Text . With this default setting, you can assign a string with a SQL statement to the CommandText property. You can also specify a name for a query with parameters (a stored procedure) or even a table name. When you use one of these alternative specifications for the CommandText property, you must designate either StoredProcedure or TableDirect for the CommandType property of the OleDbCommand object.

Another special feature of commands is their ability to accept parameters. For example, if you have an OleDbCommand object to insert a new row in a table, you can use parameters to specify the column values for the new row. This enables the reuse of the same OleDbCommand object for the insertion of new rows with different column values (namely, the parameter value assignments).

After specifying the Connection and CommandText properties along with any other required properties for your implementation of an OleDbCommand object, you can execute the command. If you are using the command directly, choose either the ExecuteScalar method or the ExecuteNonQuery method. The ExecuteScalar method returns just the first column value from the first row in the result set for a query. You can assign the return value to a memory variable for use in your program. The ExecuteNonQuery method does not provide a result set. This method is suitable when the SQL statement does not return a result set, such as for a data definition statement that adds a new query to a database.

Note  

The constructor, which is implemented by the New method, for the OleDbCommand class is overloaded. Therefore, you can specify a basic instantiation of a new OleDbCommand object, or you can use a more elaborate form of the method that designates several property settings that you might need (such as the SQL statement and the connection) with your OleDbCommand instantiation statement.

Form3 in the OleDbObjectSamples project illustrates a couple of OleDbCommand object application issues. The form s design has the same basic layout as that of Form2 with two buttons. (See Figure 7-2 on page 323.) The code behind the form extends the coverage of OLE DB .NET Data Provider classes to include both connections and commands. The click event procedure for the first button demonstrates the use of the ExecuteScalar method after setting up the OleDbCommand object. The second button demonstrates a basic approach to managing the insertion and deletion of rows within a table in a database. You will find the OleDbDataAdapter object a more robust tool for data manipulation tasks such as this, but if you have simple application requirements (or very unusual requirements), you might find using a command can keep your overhead lower while still being highly flexible.

The code behind Form3 includes three procedures ”a click event procedure for each button and a Function procedure that instantiates and returns an OleDbConnection . Because both click event procedures work with the Northwind database, the Function procedure enables them to avoid repeating the same code to instantiate a connection to the Northwind database. The sample function, ConnectToNorthwind , connects to the Northwind database, but you could easily generalize the function by passing a connection string argument. The code behind Form3 does not include an Imports statement for the System.Data.OleDb namespace. Therefore, the code in the module must use the namespace name before each reference to a member in the namespace. This is a mixed blessing. On the one hand, it requires more typing. On the other hand, this code is self-documenting .

The click event procedure for Button1 starts by declaring and instantiating empty OleDbConnection and OleDbCommand instances; cnn1 represents the connection, and cmd1 denotes the command. Next, the Button1_Click procedure invokes the ConnectToNorthwind procedure to create a connection to the Northwind database. After assigning the returned object from the ConnectToNorthwind function to cnn1 , the procedure opens the connection. Then the click event procedure sets the Connection and CommandText property values for the cmd1 object. The SQL statement in the string variable for CommandText counts the number of CategoryID values in the Categories table. Because the CategoryID column is the primary key for the table, the count of CategoryID is the number of rows in the Categories table. The Button1_Click procedure invokes the ExecuteScalar method for cmd1 to return the count of categories and displays the result in a message box. The event procedure then closes the connection.

The Button2_Click procedure begins similarly to the preceding event procedure. The preliminary steps conclude by assigning the opened cnn1 object to the Connection property for cmd1 . The Button2_Click procedure toggles the insertion and deletion of a customer in the Customers table. On the first pass through the procedure, the code inserts a new row with CustomerID and CompanyName values of MYCOM and ProgrammingMSAccess.com . Because this row is not initially in the Customers table, the first attempt succeeds in the Try Catch Finally statement. Clicking Button2 a second time throws a System.Data.OleDb.OleDbException object. The Try Catch Finally statement catches the exception and deletes the row with a CustomerID column value of MYCOM . With just a little editing, you can readily extend the simple logic here to add more column values, automatically copy over a duplicate row or save the values in a duplicate row to a disk file.

 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Declare and instantiate Connection and Command objects. Dim cnn1 As New System.Data.OleDb.OleDbConnection() Dim cmd1 As New System.Data.OleDb.OleDbCommand() Open connection to Northwind database. cnn1 = ConnectToNorthwind() cnn1.Open() Assign Connection and CommandText property values for the cmd1 OleDbCommand object. cmd1.Connection = cnn1 cmd1.CommandText = _ "SELECT Count(CategoryID) FROM Categories" Save result set from command in int1 memory variable. Dim int1 As Integer = cmd1.ExecuteScalar Display result from query. MsgBox("The number of categories is " & _ int1.ToString & ".") Close connection. cnn1.Close() End Sub Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click Declare and instantiate Connection and Command objects. Dim cnn1 As New System.Data.OleDb.OleDbConnection() Dim cmd1 As New System.Data.OleDb.OleDbCommand() Open connection to Northwind database. cnn1 = ConnectToNorthwind() cnn1.Open() Assign connection. cmd1.Connection = cnn1 Attempt to insert MYCOM CustomerID; if record is already there, attempt to delete. Try cmd1.CommandText = _ "INSERT INTO Customers (CustomerID, CompanyName) " & _ "Values ( ˜MYCOM , ProgrammingMSAccess.com )" cmd1.ExecuteNonQuery() MsgBox("Inserted Customer with CustomerID of MYCOM", , _ "Inserted it") Catch exc1 As System.Data.OleDb.OleDbException cmd1.CommandText = _ "Delete * FROM Customers WHERE CustomerID = MYCOM " cmd1.ExecuteNonQuery() MsgBox("Deleted Customer with CustomerID of MYCOM", , _ "Deleted it") Finally cnn1.Close() End Try End Sub Function ConnectToNorthwind() As System.Data.OleDb.OleDbConnection Designate string for connection to Northwind database. Dim str1 As String = _ "Provider = Microsoft.Jet.OLEDB.4.0;" str1 += _ "Data Source = c:\Program Files\" & _ "Microsoft Office\Office10\Samples\Northwind.mdb" Instantiate and assign the connection string to the Connection object. Dim cnn1 As New System.Data.OleDb.OleDbConnection() cnn1.ConnectionString = str1 Return the Connection object. Return cnn1 End Function 

OleDbDataReader

As mentioned previously, an OleDbDataReader object enables a forward-only, read-only pass through the result set returned by an OleDbCommand object. Although you must declare a DataReader variable to programmatically manipulate one, the OleDbDataReader class does not offer a New method. The only way to instantiate a data reader is with the ExecuteReader method for a Command object. The OleDbDataReader object requires the exclusive use of the connection associated with its OleDbCommand object. When you are finished looping through the rows of a result set, you should close both the data reader and the connection.

After creating an instance of an OleDbDataReader class, your initial position within the result set will be just before the first row. To gain access to the first (and subsequent ) rows, you can invoke the Read method. The Read method advances your position one row in a result set each time you invoke it. The column positions within a row are zero-based . You can read the column values for the current row position with a collection of Get methods, such as GetInt32 and GetString . Each System data type has a Get method. (See Chapter 3 for a comparison of .NET Framework System data types with Visual Basic .NET data types.) Use the FieldCount property to determine the number of columns in a result set. The FieldCount property is not dependent on moving to a row with data so that you can invoke it before moving to the first row. After moving to the first row, you can iterate through the columns in the row and report the System data type for each column. This information is useful in determining which Get method to use to extract data from a column in the current row.

Form4 in the OleDbObjectSamples project illustrates design principles for applying OleDbDataReader instances. As with the preceding samples in this chapter, the form has two buttons. The click event for Button1 presents a couple of message boxes, one after the other. The first message box reports the number of columns in the Shippers table. The second message box reports the data type for each column in the table. You often will know this information when you are the architect of a database or you use the database often. However, database developers and administrators regularly inherit a database from another analyst or as part of an upgrading project from end users. Adaptations of a procedure such as this can be helpful in these cases. The click event procedure for Button2 iterates through the rows in the Shippers table and displays the column values for each row.

There are four procedures in the module behind Form4 ” one click event procedure each for Button1 and Button2 , plus a pair of functions for returning a connection to the Northwind database ( ConnectToNorthwind ) and a data reader with a result set containing the column values for all rows in the Shippers table ( ShippersDataReader ). The event procedures manage a task or two and invoke the Function procedures to help achieve their tasks. The ConnectToNorthwind function is identical to the earlier sample; this is a good example of code reuse based on procedures. The ShippersDataReader function accepts a connection argument passed to it and returns an OleDbDataReader object. The function starts by instantiating an OleDbCommand object, cmd1 . Next, the function defines the Connection and CommandText property values so that executing the SQL statement in cmd1 generates a result set with the column values for all rows in the Shippers table of the Northwind database.

You have seen this kind of code before. What s new is the invocation of the ExecuteReader method for the Command object. This method creates a data reader that can process the result set in cmd1 . No built-in OleDbCommand method exists for processing a result set with multiple rows. The ShippersDataReader function concludes by returning the data reader for its use in the routine calling it. In the current application, this function can be called by the click event procedure for Button1 or Button2 .

The Button1_Click procedure starts by instantiating and opening an OleDbConnection object named cnn1 . Then, the procedure passes cnn1 to the ShippersDataReader function to generate a DataReader object ( drd1 ) based on the Shippers table. Next, the procedure displays the number of columns in the Shippers table by displaying in a message box the FieldCount property for drd1 . After a user closes the message box, the procedure moves on to read the first row in the result set with the Read method for the OleDbDataReader object. In a For Next loop, the code iterates through the columns to develop a string that lists the field number, name, and data type for each successive field in the result set.

Notice the syntax distinction in how the procedure references the GetName method and the GetType method. For the GetName method, you pass the index for the column as an argument to the method. For the GetType method, you apply the method to an indexed column ( drd1(int1) ). The procedure s code delimits the metadata for each field with a carriage return. The procedure concludes by invoking the Close method for both the data reader and the connection. This is good Visual Basic .NET coding practice.

Note  

You re probably already familiar with the term metadata , but just to make sure, it means data about data. For example, the field number, name, and data type are metadata for a result set based on a table.

Figure 7-4 shows the two message boxes generated by the Button1_Click procedure. The Shippers table contains the standard number of rows (three) after it is installed. The second message box contains a sentence with the metadata for each column in the result set. The sentence for each column lists the field number and name along with the .NET Framework System data type. You can use this information to determine how to read values from the result set.

Figure 7-4: The top message box shows metadata about the number of fields in a result set that a data reader points at, and the bottom message box shows more detailed data about each field in the result set.

The Button2_Click procedure demonstrates how to iterate through the rows of a data reader and return the column values for each row. The procedure copies these values to a string, but you could just as easily use an array, a disk file, or another database, such as one in the project folder. This click event procedure starts the same way as the Button1 click procedure. After creating a data reader with the column values for the Shippers table, the code starts a Do Loop construct and successively invokes the Read method for the data reader. As long as the data reader contains another row, the loop continues to iterate. When no more rows remain unread, the Do Loop construct passes control to the statement immediately after the loop. Within the loop, the procedure creates a string with the three column values on each row. The code delimits the values for each row from one another by a carriage return. A message box after the loop displays the string constructed within the loop.

 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Open connection to Northwind database. Dim cnn1 As _ System.Data.OleDb.OleDbConnection = _ ConnectToNorthwind() cnn1.Open() Make available a data reader for the Shippers table. Dim drd1 As System.Data.OleDb.OleDbDataReader drd1 = ShippersDataReader(cnn1) Return the count of fields in the result set. MsgBox("Number of fields in result set = " & _ drd1.FieldCount.ToString & ".", , _ "Field count") Read the first row in the result set. drd1.Read() Report the data types of the fields in the row. Dim int1 As Integer Dim str1 As String For int1 = 0 To drd1.FieldCount - 1 str1 += "Field " & int1.ToString & _ " is named " & drd1.GetName(int1) & _ ", and its data type is " & _ drd1(int1).GetType.ToString() & "." & vbCr Next int1 MsgBox(str1, , "Field data types") Clean up. drd1.Close() cnn1.Close() End Sub Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click Open connection to Northwind database. Dim cnn1 As _ System.Data.OleDb.OleDbConnection = _ ConnectToNorthwind() cnn1.Open() Make available a data reader for the Shippers table. Dim drd1 As System.Data.OleDb.OleDbDataReader drd1 = ShippersDataReader(cnn1) Iterate through rows of the result set returned by cmd1 and display column values in a message box. Dim str1 As String Do While drd1.Read str1 += drd1.GetInt32(0).ToString & _ ", " & drd1.GetString(1) & _ ", " & drd1.GetString(2) & vbCr Loop MsgBox(str1) Clean up. drd1.Close() cnn1.Close() End Sub Function ShippersDataReader(ByVal cnn1 As _ System.Data.OleDb.OleDbConnection) As _ System.Data.OleDb.OleDbDataReader Declare and instantiate Command object. Dim cmd1 As New System.Data.OleDb.OleDbCommand() Assign Connection and CommandText property values for the cmd1 OleDbCommand object. cmd1.Connection = cnn1 cmd1.CommandText = _ "SELECT * FROM Shippers" Create an instance of a data reader for the result set returned by cmd1. Dim drd1 As System.Data.OleDb.OleDbDataReader drd1 = cmd1.ExecuteReader Return drd1 End Function Function ConnectToNorthwind() As _ System.Data.OleDb.OleDbConnection Designate string for connection to Northwind database. Dim str1 As String = _ "Provider = Microsoft.Jet.OLEDB.4.0;" str1 += _ "Data Source = c:\Program Files\" & _ "Microsoft Office\Office10\Samples\Northwind.mdb" Instantiate and assign the connection string to the Connection object. Dim cnn1 As New System.Data.OleDb.OleDbConnection() cnn1.ConnectionString = str1 Return the Connection object. Return cnn1 End Function 

OleDbDataAdapter

The OleDbDataAdapter class has two main roles. First, a class instance can fill a table within a local dataset with the contents of a database object, such as a table or query. Use the Fill method to implement this role. Second, a class instance can update the object in the database used to fill the table in the local dataset with any changes made to the local dataset. Apply the Update method to carry out this role. The update to the database object reflects any data manipulation tasks performed against the table in the local dataset, including the revision of a column value in an existing row, the deletion of an existing row, and the addition of a new row.

Properties and Methods

The OleDbDataAdapter object has four properties that moderate the operation of the Fill and Update methods. These properties designate the database object in a database to reference and indicate what kind of operation to perform. The four property names are SelectCommand , UpdateCommand , InsertCommand , and DeleteCommand . You can assign OleDbCommand objects to these properties. The CommandText property for the OleDbCommand object designates the result set or action associated with the property. The SelectCommand property is a specification for the result set from a database that a data adapter makes available to a local dataset. This result set is based on the CommandText property for the command object returned by the SelectCommand property. The UpdateCommand , InsertCommand , and DeleteCommand properties each designate classic Access action queries for updating an existing row, inserting a new row, or deleting an existing row. After specifying the SelectCommand property for a data adapter, you can automatically generate the UpdateCommand , InsertCommand , and DeleteCommand properties with the OleDbCommandBuilder class.

Use the OleDbDataAdapter object s Fill method to populate a table in a local dataset with the result set from the SelectCommand property for a data adapter. The Fill method will not create a dataset, but it can add a new table to an existing dataset if that table does not already exist in the dataset. Whether or not the method has to create a local table, the Fill method populates a local table on its initial use in a session. After the initial invocation of the Fill method in a session, subsequent invocations of the method append rows from the result set for the command designated by the SelectCommand property to the referenced table in the local dataset. If you want to replace rather than add to the existing data in the local table, you must clear the local table before invoking the Fill method. This clear-and-fill technique enables a table in a local dataset to reflect changes made by other users.

The Update method conveys changes made to a local table since the last time the local table was populated from a database source. In spite of its name ( Update ), this single method passes back inserts, updates, and deletes from a local table to its source in a database. The Connection object for the data adapter specifies the database, and the InsertCommand , UpdateCommand , and DeleteCommand properties designate the operation of the insert, update, and delete operations. If you have multiple users making changes to a database that might conflict with one another, your application must handle concurrency violations. Chapter 8 and Chapter 9 will demonstrate how to do this from different perspectives.

Sample Code

Form5 in the OleDbObjectSamples project contains a collection of sample code that demonstrates syntax for typical tasks, such as populating a DataGrid control and permitting updates from it. An OleDbDataAdapter object enables these functions, and the code behind the sample shows you the syntax for managing these kinds of tasks. Unlike earlier samples addressing this topic, these samples programmatically manage the creation of Connection , Command , DataAdapter , CommandBuilder , and DataSet objects. Knowing how to programmatically manage the creation of these objects enables your applications to create and customize ADO.NET objects as at run time.

Figure 7-5 shows Form5 in Design view and after it opens. The form contains a DataGrid control ( DataGrid1 ) below three buttons. The code behind this form demonstrates syntax conventions for the OleDbDataAdapter object. The form load event procedure and some module-level declarations initialize the ADO.NET objects that enable the populating of DataGrid1 . Users can double-click a grid cell to open the cell for editing. For example, double-clicking the second column of the first row lets you append an x to the name Speedy Express (so that the CompanyName column value appears as Speedy Expressx ). This updates the Shippers table in the dataset behind the form. However, just making the change locally does not force the Shippers table in the Northwind database to update accordingly . Therefore, clicking Button1 to populate the local Shippers table from the Shippers table in the Northwind database obliterates the update and restores the name to Speedy Express .

Figure 7-5: The Design view of Form5 in the OleDbObjectSamples project (on top) and the operational view after the form initially opens

Button2 and Button3 enable you to copy changes from the Shippers table in the local dataset to the Shippers table in the Northwind database. Button2 programmatically appends an x to the end of the value in the second column of the first row. In addition to changing the value locally, the code updates the value in the Northwind database. Therefore, the next time a user opens the form or clicks Button1 , the appended x remains visible. To make it simple for you to restore the Shippers table in the Northwind database, Button3 reverses the operation of appending an x in the second column of the first row. The code behind this button removes the last character from the column value before invoking the Update method to persist the change in the table within the dataset to the table with the same name in the Northwind database.

The code behind Form5 consists of a load event procedure, a separate click event procedure for each button, and a function to return an OleDbConnection object pointing at the Northwind database. The latter function is the familiar ConnectToNorthwind Function procedure.

The Form5_Load procedure starts by calling the ConnectToNorthwind function to return a connection ( cnn1 ) pointing at the Northwind database. Next, the procedure instantiates an OleDbCommand object with a result set containing all the column values for all the rows in the Shippers table of the Northwind database. Instead of instantiating an empty command object and assigning its Connection and CommandType property values, this sample uses an overloaded version of the OleDbCommand constructor to simultaneously instantiate the object and sets its property values.

After instantiating the Connection and Command objects, the procedure assigns a value for the SelectCommand property of the dap1 OleDbDataAdapter . The dap1 object is one of two objects declared at the module level because of its use in multiple procedures; the other object declared at the module level is das1 , the DataSet object that the data adapter populates. The code in the form load event procedure assigns the cmd1 object to the SelectCommand property for dap1 .

The procedure does not explicitly set the UpdateCommand , InsertCommand , and DeleteCommand properties of the dap1 OleDbDataAdapter . This is because the procedure designates a CommandBuilder object to manage the function of these properties. Whenever the code invokes the Update method for the dap1 object, the OleDbCommandBuilder object intercepts the request to perform an update and executes the revision to the database object on behalf of the data adapter.

After setting up the data adapter, the form load event procedure invokes the Fill method. This invocation of the Fill method adds the Shippers table to the das1 DataSet object as it populates the table. Notice that the procedure never explicitly opens the cnn1 connection object. The OleDbDataAdapter object is smart enough to open the cnn1 connection object only when it needs the connection to perform a task.

The form load event procedure concludes with a mix of cosmetic activities. For example, the code resizes DataGrid1 and Form5 to better show the data in the grid and make it easy to see edits. In addition, the setting of a PreferredColumnWidth property helps to make edits visible. Before closing the With End With statement that groups the assignments involving DataGrid1 , the procedure sets the DataSource property for the grid to the Shippers table in the das1 DataSet object. Some Text property assignments at the very end of the procedure make the purpose of the buttons more obvious than their default names, such as Button1 .

The Button1_Click procedure has just two lines. The first line clears the Shippers table in the das1 dataset. Then, the procedure invokes the Fill method for the dap1 OleDbDataAdapter object. This causes DataGrid1 to show any changes made by other users since the last time the application invoked the Fill method for the dap1 object.

The Button2_Click and Button3_Click procedures have a parallel design. The first line in the Button2_Click procedure makes a change to the CompanyName column value in the first row. Because row and column numbers are zero-based, the code designates the row as 0 and the column as 1. The &= operator simplifies appending an x to the end of the value in the Shippers table cell. Although the change is visible in the grid, it is still not fully committed or accepted. For example, you can uncommit the edit to the CompanyName column value by invoking the RejectChanges method for the DataTable object showing in DataGrid1 . (See the next section in the chapter for a review of the DataSet object model, including DataTable objects.)

The invocation of the Update method in the second line propagates the revision from the local Shippers table to the Shippers table in the Northwind database, and the method commits the change locally so that the update cannot be undone, except by a new edit to the local Shippers table. Similarly, the Button3_Click procedure makes a change in its first couple of lines and commits the change in its last line as it updates the Shippers table in the Northwind database. The change in this case is the removal of the last character from the second column value for the first row. The Button3_Click procedure can undo the edit implemented with a click to Button2 in both the local dataset and the Northwind database.

 Dim das1 As New System.Data.DataSet() Dim dap1 As New _ System.Data.OleDb.OleDbDataAdapter() Private Sub Form5_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load Instantiate the cnn1 Connection object based on the Northwind database. Dim cnn1 As _ System.Data.OleDb.OleDbConnection = _ ConnectToNorthwind() Instantiate the cmd1 OleDbCommand object and assign a SQL statement and connection in the constructor. Dim str1 As String = "SELECT * FROM Shippers" Dim cmd1 As _ New System.Data.OleDb.OleDbCommand( _ str1, cnn1) Assign a command to the SelectCommand property of the dap1 DataAdapter object. dap1.SelectCommand = cmd1 Automate the design of the UpdateCommand, InsertCommand, and DeleteCommand properties for the dap1 DataAdapter object. Dim cmb1 As New _ System.Data.OleDb.OleDbCommandBuilder() cmb1 = _ New System.Data.OleDb.OleDbCommandBuilder(dap1) Fill the Shippers table in the das1 dataset. dap1.Fill(das1, "Shippers") Resize datagrid and form and assign Shippers table from das1 to DataSource property for DataGrid1. With DataGrid1 .Size = New System.Drawing.Size(400, .Height) Me.Size = _ New System.Drawing. Size (.Width + 20, Me.Height) .PreferredColumnWidth = 100 .DataSource = das1.Tables("Shippers") End With Assign button Text property values. Button1.Text = "Populate" Button2.Text = "Modify" Button3.Text = "Restore" End Sub Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Clear dataset contents. das1.Tables("Shippers").Clear() Fill dataset with most recent data from database. dap1.Fill(das1, "Shippers") End Sub Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click Update second column of first row in Shippers table within das1 with trailing x. das1.Tables("Shippers").Rows(0)(1) &= "x" Convey change in das1 to Northwind database. dap1.Update(das1, "Shippers") End Sub Private Sub Button3_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button3.Click Remove last character from second column of first row in Shippers table within das1. Dim str1 As String = das1.Tables("Shippers").Rows(0)(1) das1.Tables("Shippers").Rows(0)(1) = _ Mid(str1, 1, Len(str1) - 1) Convey change in das1 to Northwind database. dap1.Update(das1, "Shippers") End Sub Function ConnectToNorthwind() As System.Data.OleDb.OleDbConnection Designate string for connection to Northwind database. Dim str1 As String = _ "Provider = Microsoft.Jet.OLEDB.4.0;" str1 += _ "Data Source = c:\Program Files\" & _ "Microsoft Office\Office10\Samples\Northwind.mdb" Instantiate and assign the connection string to the Connection object. Dim cnn1 As New System.Data.OleDb.OleDbConnection() cnn1.ConnectionString = str1 Return the Connection object. Return cnn1 End Function