Programming Dynamic Data Manipulation

This section takes a case study approach to dynamic data manipulation by building a simple version of a typical application. The sample constructs an Access database file and populates the database with a couple of tables while putting some data in one table. This is not the typical part of the application. The two tables have the names Inventory and OrderDetails. The application processes any order for which inventory is on hand. If the inventory for the ordered item is not sufficient to fulfill the order, the order is not processed ”it is instead backordered. If another order arrives after a backorder has been made, the order will be processed if its quantity is less than or equal to the number of units in inventory.

The decreasing of inventory units is dynamic in the sense that the reduction depends on whether the number of items ordered is less than or equal to the number of units on hand. This updating of inventory units in response to orders is the typical part of the application. The sample for this section is interesting from another perspective. It demonstrates transaction processing ”that is, the marking of an order as processed and the decreasing of the units on hand are tied together in a single transaction. If the units on hand cannot decrease because the number ordered is too great, the order is not marked as processed. (Instead, it is marked as backordered.)

To create the sample database for this section, your project must have a reference to the ADOX library. See the COM Interop section in Chapter 3 for detailed instructions on adding a reference for a COM library, such as the ADOX library. The sample materials for this section depend on Form4 in the ADONETSamples project. The project shows a reference to ADOX in Solution Explorer. Because I selected the 2.7 version of the ADOX library, the Description property value for the ADOX reference equals Microsoft ADO Ext. 2.7 for DDL and Security; this is the long name for version 2.7 of the ADOX library. This version installs with Visual Studio .NET.

Form4 contains just three buttons with respective Text property values of Button1 , Button2 , and Button3 . No form load event procedure exists to assign new nondefault Text properties. The click event procedure for Button1 creates an Access database file. The Button2_Click procedure adds a couple of tables named Inventory and OrderDetails to the new database and populates the Inventory table with a row of column values. The click event procedure for Button3 performs the dynamic updating of the two tables in a transaction.

Creating an Access Database Programmatically

Your applications can programmatically create an Access database file with the Create method for the Catalog class in the ADOX library. An empty Microsoft Access database file is different from an empty text file. Specifically, an empty Microsoft Access database file contains a catalog for accepting the structure of a database.

The Create method for a Catalog object can take a connection string as an argument. The connection string is a standard one for an Access database file that consists of Provider and Data Source clauses. Not all providers support the Create method, but the Microsoft.Jet.OLEDB.4.0 provider is among those that do. In the listing for the Button1_Click procedure, str1 represents the path and filename for the new database file, which is the value designated in the Data Source clause of the connection string argument for the Create method. Because the path is the bin subdirectory of the current folder, the procedure doesn t need to include a full path and sets str1 equal to the filename, db1.mdb. Before attempting to create a new file named db1.mdb, the procedure checks whether one with that name already exists. The listing uses the Exists method of the File class in the System.IO namespace to confirm whether the file exists. If the file exists, the procedure invokes the Delete method of the File class to remove the previous version of the db1.mdb file from the bin subdirectory.

After ensuring that no database has the name the Create method uses to make a new database file, the procedure instantiates a new Catalog object, cat1 . Next , an expression formulates the connection string for the Create method based on the value of str1 . Because the procedure uses an expression, it is easy to substitute a different filename and designate a path without changing the core code for the sample. Invoking the Create method adds the db1.mdb file to the bin subdirectory of the ADONETSamples project folder.

The constructor for the Catalog class forms a sticky class instance. By sticky , I mean that merely setting the cat1 object to Nothing does not remove the object reference from memory after the procedure concludes. Therefore, you need to use a more aggressive means to remove the memory reference. Failing to remove the object reference can cause a second invocation of the procedure to fail. One technique that will succeed is to invoke the Collect method of the GC class. This class controls the .NET Framework garbage collector. Normally the garbage collector works in the background to remove unreferenced objects, but it does not routinely perform this function for the cat1 object reference. Invoking the Collect method after setting cat1 equal to Nothing forces the garbage collector to immediately remove the cat1 object from memory. The invocation of the WaitForPendingFinalizers method pauses the current thread until the Collect method completes.

 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Assign database file name to str1 Dim str1 = "db1.mdb" Delete file if it exists If System.IO.File.Exists(str1) Then System.IO.File.Delete(str1) End If Create cat1 in the file to which str1 points Dim cat1 As ADOX.Catalog cat1 = New ADOX.Catalog() str1 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & str1 cat1.Create(str1) Setting to nothing does not cause timely removal from memory Force removal of cat1 from memory by first setting to nothing and second collecting unreferenced objects with the Garbage Collector cat1 = Nothing GC.Collect() GC.WaitForPendingFinalizers() End Sub 

Figure 8-8 shows Solution Explorer after running the click event procedure for Button1 . Notice that db1.mdb appears in the bin subdirectory for the project. To display this directory, you normally have to click the Show All Files icon on Solution Explorer. This icon is located on the toolbar toward the top of Solution Explorer. I also expanded the References folder in Solution Explorer so that you can see the reference to the ADOX library.

Figure 8-8: The click event procedure for Button1 in Form4 of the ADONETSamples project adds a new db1.mdb file to the bin subdirectory of the project folder.

Adding Tables to a Database Programmatically

The preceding sample demonstrated how to create a new Access database file programmatically. However, we re only part of the way toward constructing our sample. The next step is to populate the database with tables. In addition, this step inserts a row of column values into one of the new tables.

The two table names are Inventory and OrderDetails. The design for each table illustrates some common features. The Inventory table has just two columns . One column (ProdID) uniquely identifies products; it serves as a primary key for the table. The primary key column has an Integer data type, but it is not an autonumber field. The second column (UnitsOnHand) also has an Integer data type. In addition, this column has a check constraint requiring its column value to be greater than or equal to 0.

The OrderDetails table is for orders that contain just one item. This isn t a problem because the table in this simple sample contains just one item to order. The column (OrderID) serving as the primary key for the OrderDetails table has an Integer data type that is also an autonumber field. The next two columns describe the order. The Quantity column value specifies the number of units ordered for the inventory item identified by the ProdID column value. The final column (BackOrder) can have either of two values. Jet SQL designates this kind of data type as a BIT data type; this data type corresponds to a Boolean data type in Visual Basic .NET and a Yes/No data type in Access.

The Button2_Click procedure listing illustrates one approach to creating the Inventory and OrderDetails tables in the db1.mdb file. The implementation uses the Connection object in the OLE DB .NET Data Provider to connect the procedure to the db1.mdb file in the bin subdirectory of the ADONETSamples folder. Then, the procedure uses the ExecuteNonQuery method for an OleDbCommand object to run Jet SQL statements for creating both tables and populating the Inventory table with a single row of column values. The Jet SQL statement operates within the cmd1 command object as a SQL string assigned to the command object s CommandText property.

You launch the specification of a table in a Jet SQL statement with the CREATE TABLE keywords followed by the name of the table. Designate individual columns within a table by specifying their column name, data type, and any special settings, such as settings for an autonumber field, primary key, or a check constraint. The ProdID column specification in the Inventory table illustrates the syntax for designating a primary key without an autonumber field, and the OrderID field specification in the OrderDetails table shows the syntax for specifying an autonumber field for a primary key. The IDENTITY keyword specifies an autonumber field. It takes an ordered pair of values as arguments ( autoincrement seed followed by autoincrement step ). The UnitsOnHand column declaration in the Inventory table demonstrates the syntax for a column check constraint requiring column values to be greater than or equal to 0.

Between the two invocations of the ExecuteNonQuery methods for creating the Inventory and OrderDetails tables is another invocation of the ExecuteNonQuery method. This application of the method illustrates the syntax for adding a row into a table with the INSERT INTO keywords. This is a very basic design. You will typically want to perform some error checks to handle attempts to add a row with a primary value that already exists in the table. For example, you could search for an existing column with the primary key value of the row that you seek to add to a table. In any event, the INSERT INTO statement adds an inventory item with a ProdID column value of 1 and a UnitsOnHand column value of 20.

 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 OleDb.OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=db1.mdb") Dim cmd1 As New OleDb.OleDbCommand() cnn1. Open () Create Inventory and OrderDetails table and initialize Inventory table with column values With cmd1 Create Inventory table .Connection = cnn1 .CommandText = _ "CREATE TABLE Inventory(" & _ "ProdID INTEGER PRIMARY KEY, " & _ "UnitsOnHand INTEGER, " & _ "CHECK(UnitsOnHand >=0))" .ExecuteNonQuery() Initialize Inventory table .CommandText = _ "INSERT INTO Inventory " & _ "(ProdID, UnitsOnHand) " & _ "VALUES(1, 20)" .ExecuteNonQuery() Create OrderDetails table .CommandText = _ "CREATE TABLE OrderDetails(" & _ "OrderID Integer IDENTITY(1,1) " & _ "PRIMARY KEY, " & _ "ProdID INTEGER, " & _ "Quantity INTEGER, " & _ "BackOrder BIT)" .ExecuteNonQuery() End With Close connection cnn1.Close() End Sub 

Dynamic Data Manipulation Within a Transaction

Data manipulation within a transaction is inherently dynamic because a transaction will not necessarily succeed. One common use for a transaction in an application is to wrap together a sequence of action queries. For example, the sample application for this section binds together an attempt to update the UnitsOnHand column value for a row with a specific ProdID column value in the Inventory table with an attempt to add a new row to the OrderDetails table. If the transaction is successful, the transaction reduces the UnitsOnHand for the ordered inventory item and adds a new row to the OrderDetails table with a BackOrder column value of 0 to indicate that the order is processed. If the transaction fails, the UnitsOnHand column value in the Inventory table remains unchanged. The application does enter a new row in the OrderDetails table, but this is a different new row ”not the one initially used in the failed transaction. The new row added to the OrderDetails table has a BackOrder column value of -1 to signify a value of Yes or True .

You can launch a transaction in Visual Basic .NET with two steps. First, you invoke the BeginTransaction method for a Connection object. This method returns a Transaction object. Second, you wrap a Command object in a transaction by assigning the returned object from the BeginTransaction method to the Transaction property of the Command object. Any action queries you invoke within the command operate as a single unit ”that is, none of the action queries succeed unless they all succeed. If a transaction fails, the .NET Framework throws a System.Data.OleDb.OleDbException object. Your applications can catch this object and respond appropriately with any actions suitable for a failed transaction. For example, the sample application in this section resubmits the same order with its BackOrder status set to True instead of False . In other words, a successful transaction inserts a row in the OrderDetails table and reduces the UnitsOnHand for the corresponding inventory item by the Quantity column value for the new order. When the transaction succeeds, the value of the BackOrder column for the order is 0.

You use two methods when coding any transaction: Commit and Rollback . Invoke the Commit method for a Transaction object after all the actions within a transaction succeed. These actions will normally be two or more data manipulation queries (inserts, updates, or deletes). Invoke the Commit method for the transaction immediately after the last data manipulation query. If all queries through the last one succeed, your procedure will invoke the Commit method and manipulate the data sources in accordance with all action queries within the transaction.

Use the Rollback method in the Catch clause of a Try Catch Finally statement. In a Catch clause declaration, search for an OleDbException object to trap an error with any action query in the transaction. The Rollback method can be the first statement in the Catch clause. Use this method to dismiss all pending changes to data sources in the transaction. Then, you can make any final commitments that might be necessary, such as saving an order for which you do not have the inventory in stock.

Note  

Transaction objects have an IsolationLevel property that governs the ability of users outside the transaction to interact with the data sources within a transaction. I successfully applied the ReadCommitted level, which is the default level for a transaction. This IsolationLevel property value locks the values during a read so that no other user can modify the data while a read is in process. However, other users can modify a data source before a transaction commits any changes to the sources that it reads. The RepeatableRead value for the IsolationLevel property restricts other users from updating the data sources in a transaction until the transaction completes ”successfully or otherwise .

The listing for the Button3_Click procedure demonstrates the syntax for coding transactions in Visual Basic .NET. Before clicking Button3 , start with a fresh database file and new Inventory and OrderDetails tables by clicking Button1 and Button2 . These two steps initialize the UnitsOnHand column value to 20 for the row with a column value of 1 in the Inventory table. The listing starts by instantiating an OleDbConnection object ( cnn1 ) that points at the db1.mdb file within the bin subdirectory of the current project s folder. Next, the procedure instantiates an OleDbCommand object ( cmd1 ).

After instantiating both the Connection and Command objects, the procedure moves on to wrap the Command object in the transaction. This begins with the declaration of a variable ( trn1 ) as a transaction. The Dim statement for trn1 does not instantiate a Transaction object ”it merely reserves a variable of the OleDbTransaction type. Before instantiation of the Transaction object for the cnn1 object, you need to open the connection. (The sample opens the cnn1 object.) Invoking the BeginTransaction method instantiates the Transaction object, and the sample assigns the trn1 variable to the method s return value. The next two lines assign the cnn1 object to the Connection property of the cmd1 object and assign the Transaction object pointed at by the trn1 variable to the Transaction property of the cmd1 object. This completes the wrapping of the cmd1 Command object in the trn1 Transaction object. At this point, all actions invoked within the transaction must succeed for any of them to succeed.

The Try Catch Finally statement in the Button3_Click procedure puts the transaction to a test. The Try clause includes two action queries. The first action query attempts to insert a row into the OrderDetails table. The new row represents an order with a ProdID column value of 1 in a Quantity of 7 units. This action query in the Try clause attempts to submit the order with a BackOrder column value of False (0). The value of 0 in the BackOrder column indicates there were enough units on hand to complete the order, and therefore the order was successful. Next, the procedure attempts to execute an update query that reduces the UnitsOnHand column value in the Inventory table for ProdID 1 by 7 units. The update query is the last one in the transaction, so the sample follows it with an invocation of the Commit method and finally a MsgBox function providing feedback about a successfully processed order. The transaction can fail at either of the statements in the Try clause invoking the ExecuteNonQuery method. If a failure occurs at either point, control passes to the Catch clause without invoking the Commit method or the MsgBox function.

Starting with fresh values in the Inventory table (UnitsOnHand column value of 20 and ProdID column value of 1), the procedure can process two orders successfully before the update query generates a failure for the third order by trying to compute a new column value for UnitsOnHand, which is less than 0. In response to a third click to Button3 , the sample application passes control to the Catch clause in the Button3_Click procedure. The first statement in this clause clears the transaction by invoking the Rollback method for the trn1 object. Once the transaction is cleared, the application is free to add a new row to the OrderDetails table. This row is nearly the same as the one in the Try clause, except that the BackOrder column value is -1 instead of 0. A value of - 1 in Access corresponds to a Yes or True setting for a Yes/No variable. The procedure uses an INSERT INTO Jet SQL statement to add the row to the OrderDetails table. The Catch clause concludes by invoking the MsgBox function to alert the user that the order is on backorder instead of being processed.

Another query can select from the OrderDetails table only those rows with a BackOrder column value of -1 to develop a list of orders awaiting processing. After refreshing the Inventory table with new shipments from suppliers, an application can fulfill rows with a BackOrder column value of -1 until the amount of unfilled orders exceeds the inventory.

 Private Sub Button3_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button3.Click Declare and instantiate Connection and Command objects Dim cnn1 As New OleDb.OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=db1.mdb") Dim cmd1 As New OleDb.OleDbCommand() Use BeginTransaction to instantiate an OleDbTransaction object for the OleDbConnection object Dim trn1 As OleDb.OleDbTransaction cnn1.Open() trn1 = cnn1.BeginTransaction cmd1.Connection = cnn1 cmd1.Transaction = trn1 Remove comment marker to confirm default IsolationLevel setting MsgBox(trn1.IsolationLevel.ToString) Insert process orders as long as inventory exists, otherwise backorder orders when insufficient units are available to process an order Try With cmd1 .CommandText = _ "INSERT INTO OrderDetails " & _ "(ProdID, Quantity, BackOrder) " & _ "VALUES(1, 7, 0)" .ExecuteNonQuery() .CommandText = _ "UPDATE Inventory " & _ "SET UnitsOnHand = UnitsOnHand - 7 " & _ "WHERE ProdID = 1" .ExecuteNonQuery() trn1.Commit() MsgBox("Order processed.", , "Order message") End With Catch exc1 As System.Data.OleDb.OleDbException trn1.Rollback() cmd1.CommandText = _ "INSERT INTO OrderDetails " & _ "(ProdID, Quantity, BackOrder) " & _ "VALUES(1, 7, -1)" cmd1.ExecuteNonQuery() MsgBox("Order backordered.", , "Backorder message") End Try Clean up when done cnn1.Close() End Sub 

Figure 8-9 displays the output from clicking Button3 three times after first clicking Button1 and Button2 . On the left, you see three message boxes. The first two message boxes report successfully processed orders. The bottom message box indicates that the order was saved for subsequent processing. The Window on the right side of Figure 8-9 shows three rows in the OrderDetails table from db1.mdb. The first two rows have BackOrder column values of 0, but the last row has a column value of -1, which indicates that the order represented by the row awaits processing. The OrderID column values are 1, 2, and 4. Notice that the row with an OrderID value of 3 is missing. This row corresponds to the unsuccessful attempt to process the third order. The attempt to process the third order threw an Exception object that made the initial version of the third order with an OrderID of 3 unavailable. The Catch clause released the system from the error associated with the exception, and the invocation of the Rollback method discarded the consequences of all actions in the third transaction. Then, a new insert query caused Access to issue a new autonumber value (4) for the second attempt to submit the third order with a BackOrder column value of -1.

Figure 8-9: Transactions can lead to dynamic data manipulation because their action queries can be rolled back if all actions do not succeed.