- •Using Your Sybex Electronic Book
- •Acknowledgments
- •Contents at a Glance
- •Introduction
- •Who Should Read This Book?
- •How About the Advanced Topics?
- •The Structure of the Book
- •How to Reach the Author
- •The Integrated Development Environment
- •The Start Page
- •Project Types
- •Your First VB Application
- •Making the Application More Robust
- •Making the Application More User-Friendly
- •The IDE Components
- •The IDE Menu
- •The Toolbox Window
- •The Solution Explorer
- •The Properties Window
- •The Output Window
- •The Command Window
- •The Task List Window
- •Environment Options
- •A Few Common Properties
- •A Few Common Events
- •A Few Common Methods
- •Building a Console Application
- •Summary
- •Building a Loan Calculator
- •How the Loan Application Works
- •Designing the User Interface
- •Programming the Loan Application
- •Validating the Data
- •Building a Math Calculator
- •Designing the User Interface
- •Programming the MathCalculator App
- •Adding More Features
- •Exception Handling
- •Taking the LoanCalculator to the Web
- •Working with Multiple Forms
- •Working with Multiple Projects
- •Executable Files
- •Distributing an Application
- •VB.NET at Work: Creating a Windows Installer
- •Finishing the Windows Installer
- •Running the Windows Installer
- •Verifying the Installation
- •Summary
- •Variables
- •Declaring Variables
- •Types of Variables
- •Converting Variable Types
- •User-Defined Data Types
- •Examining Variable Types
- •Why Declare Variables?
- •A Variable’s Scope
- •The Lifetime of a Variable
- •Constants
- •Arrays
- •Declaring Arrays
- •Initializing Arrays
- •Array Limits
- •Multidimensional Arrays
- •Dynamic Arrays
- •Arrays of Arrays
- •Variables as Objects
- •So, What’s an Object?
- •Formatting Numbers
- •Formatting Dates
- •Flow-Control Statements
- •Test Structures
- •Loop Structures
- •Nested Control Structures
- •The Exit Statement
- •Summary
- •Modular Coding
- •Subroutines
- •Functions
- •Arguments
- •Argument-Passing Mechanisms
- •Event-Handler Arguments
- •Passing an Unknown Number of Arguments
- •Named Arguments
- •More Types of Function Return Values
- •Overloading Functions
- •Summary
- •The Appearance of Forms
- •Properties of the Form Control
- •Placing Controls on Forms
- •Setting the TabOrder
- •VB.NET at Work: The Contacts Project
- •Anchoring and Docking
- •Loading and Showing Forms
- •The Startup Form
- •Controlling One Form from within Another
- •Forms vs. Dialog Boxes
- •VB.NET at Work: The MultipleForms Project
- •Designing Menus
- •The Menu Editor
- •Manipulating Menus at Runtime
- •Building Dynamic Forms at Runtime
- •The Form.Controls Collection
- •VB.NET at Work: The DynamicForm Project
- •Creating Event Handlers at Runtime
- •Summary
- •The TextBox Control
- •Basic Properties
- •Text-Manipulation Properties
- •Text-Selection Properties
- •Text-Selection Methods
- •Undoing Edits
- •VB.NET at Work: The TextPad Project
- •Capturing Keystrokes
- •The ListBox, CheckedListBox, and ComboBox Controls
- •Basic Properties
- •The Items Collection
- •VB.NET at Work: The ListDemo Project
- •Searching
- •The ComboBox Control
- •The ScrollBar and TrackBar Controls
- •The ScrollBar Control
- •The TrackBar Control
- •Summary
- •The Common Dialog Controls
- •Using the Common Dialog Controls
- •The Color Dialog Box
- •The Font Dialog Box
- •The Open and Save As Dialog Boxes
- •The Print Dialog Box
- •The RichTextBox Control
- •The RTF Language
- •Methods
- •Advanced Editing Features
- •Cutting and Pasting
- •Searching in a RichTextBox Control
- •Formatting URLs
- •VB.NET at Work: The RTFPad Project
- •Summary
- •What Is a Class?
- •Building the Minimal Class
- •Adding Code to the Minimal Class
- •Property Procedures
- •Customizing Default Members
- •Custom Enumerations
- •Using the SimpleClass in Other Projects
- •Firing Events
- •Shared Properties
- •Parsing a Filename String
- •Reusing the StringTools Class
- •Encapsulation and Abstraction
- •Inheritance
- •Inheriting Existing Classes
- •Polymorphism
- •The Shape Class
- •Object Constructors and Destructors
- •Instance and Shared Methods
- •Who Can Inherit What?
- •Parent Class Keywords
- •Derived Class Keyword
- •Parent Class Member Keywords
- •Derived Class Member Keyword
- •MyBase and MyClass
- •Summary
- •On Designing Windows Controls
- •Enhancing Existing Controls
- •Building the FocusedTextBox Control
- •Building Compound Controls
- •VB.NET at Work: The ColorEdit Control
- •VB.NET at Work: The Label3D Control
- •Raising Events
- •Using the Custom Control in Other Projects
- •VB.NET at Work: The Alarm Control
- •Designing Irregularly Shaped Controls
- •Designing Owner-Drawn Menus
- •Designing Owner-Drawn ListBox Controls
- •Using ActiveX Controls
- •Summary
- •Programming Word
- •Objects That Represent Text
- •The Documents Collection and the Document Object
- •Spell-Checking Documents
- •Programming Excel
- •The Worksheets Collection and the Worksheet Object
- •The Range Object
- •Using Excel as a Math Parser
- •Programming Outlook
- •Retrieving Information
- •Recursive Scanning of the Contacts Folder
- •Summary
- •Advanced Array Topics
- •Sorting Arrays
- •Searching Arrays
- •Other Array Operations
- •Array Limitations
- •The ArrayList Collection
- •Creating an ArrayList
- •Adding and Removing Items
- •The HashTable Collection
- •VB.NET at Work: The WordFrequencies Project
- •The SortedList Class
- •The IEnumerator and IComparer Interfaces
- •Enumerating Collections
- •Custom Sorting
- •Custom Sorting of a SortedList
- •The Serialization Class
- •Serializing Individual Objects
- •Serializing a Collection
- •Deserializing Objects
- •Summary
- •Handling Strings and Characters
- •The Char Class
- •The String Class
- •The StringBuilder Class
- •VB.NET at Work: The StringReversal Project
- •VB.NET at Work: The CountWords Project
- •Handling Dates
- •The DateTime Class
- •The TimeSpan Class
- •VB.NET at Work: Timing Operations
- •Summary
- •Accessing Folders and Files
- •The Directory Class
- •The File Class
- •The DirectoryInfo Class
- •The FileInfo Class
- •The Path Class
- •VB.NET at Work: The CustomExplorer Project
- •Accessing Files
- •The FileStream Object
- •The StreamWriter Object
- •The StreamReader Object
- •Sending Data to a File
- •The BinaryWriter Object
- •The BinaryReader Object
- •VB.NET at Work: The RecordSave Project
- •The FileSystemWatcher Component
- •Properties
- •Events
- •VB.NET at Work: The FileSystemWatcher Project
- •Summary
- •Displaying Images
- •The Image Object
- •Exchanging Images through the Clipboard
- •Drawing with GDI+
- •The Basic Drawing Objects
- •Drawing Shapes
- •Drawing Methods
- •Gradients
- •Coordinate Transformations
- •Specifying Transformations
- •VB.NET at Work: Plotting Functions
- •Bitmaps
- •Specifying Colors
- •Defining Colors
- •Processing Bitmaps
- •Summary
- •The Printing Objects
- •PrintDocument
- •PrintDialog
- •PageSetupDialog
- •PrintPreviewDialog
- •PrintPreviewControl
- •Printer and Page Properties
- •Page Geometry
- •Printing Examples
- •Printing Tabular Data
- •Printing Plain Text
- •Printing Bitmaps
- •Using the PrintPreviewControl
- •Summary
- •Examining the Advanced Controls
- •How Tree Structures Work
- •The ImageList Control
- •The TreeView Control
- •Adding New Items at Design Time
- •Adding New Items at Runtime
- •Assigning Images to Nodes
- •Scanning the TreeView Control
- •The ListView Control
- •The Columns Collection
- •The ListItem Object
- •The Items Collection
- •The SubItems Collection
- •Summary
- •Types of Errors
- •Design-Time Errors
- •Runtime Errors
- •Logic Errors
- •Exceptions and Structured Exception Handling
- •Studying an Exception
- •Getting a Handle on this Exception
- •Finally (!)
- •Customizing Exception Handling
- •Throwing Your Own Exceptions
- •Debugging
- •Breakpoints
- •Stepping Through
- •The Local and Watch Windows
- •Summary
- •Basic Concepts
- •Recursion in Real Life
- •A Simple Example
- •Recursion by Mistake
- •Scanning Folders Recursively
- •Describing a Recursive Procedure
- •Translating the Description to Code
- •The Stack Mechanism
- •Stack Defined
- •Recursive Programming and the Stack
- •Passing Arguments through the Stack
- •Special Issues in Recursive Programming
- •Knowing When to Use Recursive Programming
- •Summary
- •MDI Applications: The Basics
- •Building an MDI Application
- •Built-In Capabilities of MDI Applications
- •Accessing Child Forms
- •Ending an MDI Application
- •A Scrollable PictureBox
- •Summary
- •What Is a Database?
- •Relational Databases
- •Exploring the Northwind Database
- •Exploring the Pubs Database
- •Understanding Relations
- •The Server Explorer
- •Working with Tables
- •Relationships, Indices, and Constraints
- •Structured Query Language
- •Executing SQL Statements
- •Selection Queries
- •Calculated Fields
- •SQL Joins
- •Action Queries
- •The Query Builder
- •The Query Builder Interface
- •SQL at Work: Calculating Sums
- •SQL at Work: Counting Rows
- •Limiting the Selection
- •Parameterized Queries
- •Calculated Columns
- •Specifying Left, Right, and Inner Joins
- •Stored Procedures
- •Summary
- •How About XML?
- •Creating a DataSet
- •The DataGrid Control
- •Data Binding
- •VB.NET at Work: The ViewEditCustomers Project
- •Binding Complex Controls
- •Programming the DataAdapter Object
- •The Command Objects
- •The Command and DataReader Objects
- •VB.NET at Work: The DataReader Project
- •VB.NET at Work: The StoredProcedure Project
- •Summary
- •The Structure of a DataSet
- •Navigating the Tables of a DataSet
- •Updating DataSets
- •The DataForm Wizard
- •Handling Identity Fields
- •Transactions
- •Performing Update Operations
- •Updating Tables Manually
- •Building and Using Custom DataSets
- •Summary
- •An HTML Primer
- •HTML Code Elements
- •Server-Client Interaction
- •The Structure of HTML Documents
- •URLs and Hyperlinks
- •The Basic HTML Tags
- •Inserting Graphics
- •Tables
- •Forms and Controls
- •Processing Requests on the Server
- •Building a Web Application
- •Interacting with a Web Application
- •Maintaining State
- •The Web Controls
- •The ASP.NET Objects
- •The Page Object
- •The Response Object
- •The Request Object
- •The Server Object
- •Using Cookies
- •Handling Multiple Forms in Web Applications
- •Summary
- •The Data-Bound Web Controls
- •Simple Data Binding
- •Binding to DataSets
- •Is It a Grid, or a Table?
- •Getting Orders on the Web
- •The Forms of the ProductSearch Application
- •Paging Large DataSets
- •Customizing the Appearance of the DataGrid Control
- •Programming the Select Button
- •Summary
- •How to Serve the Web
- •Building a Web Service
- •Consuming the Web Service
- •Maintaining State in Web Services
- •A Data-Driven Web Service
- •Consuming the Products Web Service in VB
- •Summary
978 Chapter 22 PROGRAMMING THE ADO.NET OBJECTS
Listing 22.7: Retrieving a New Row’s Identity Column
Dim myConnection As New SqlConnection() myConnection.ConnectionString = “data source=PowerToolkit; “ & _
“initial catalog=Northwind;integrated security=SSPI” myConnection.Open()
Dim myCommand As New SqlCommand() myCommand.Connection = myConnection
Try
myCommand.CommandText = “NewOrder” myCommand.CommandType = CommandType.StoredProcedure Dim p As New SqlParameter()
p.ParameterName = “@CustID” p.Direction = ParameterDirection.Input p.SqlDbType = SqlDbType.Char
p.Value = “BLAUS” myCommand.Parameters.Add(p) p = New SqlParameter() p.ParameterName = “RETURN”
p.Direction = ParameterDirection.ReturnValue p.SqlDbType = SqlDbType.Int myCommand.Parameters.Add(p) myCommand.ExecuteScalar()
Dim orderID As Integer = CType(myCommand.Parameters(“RETURN”).Value, Integer) Catch exc As Exception
MsgBox(exc.Message) End Try
And what might that further processing be? In most situations, it’s the use of the ID in adding new rows in related tables. To complete the insertion of an order to the database, we must also add a few rows to the Order Details table. These rows must refer to the order to which they belong, so their OrderID field must be set to the ID of the order we just inserted.
I will show you how to add the detail lines; it’s quite analogous to adding a row to the Orders table. However, there’s a complication. What if one of the detail lines can’t be added to its table? We should be able to cancel the entire order, not just a detail line. Performing multiple actions against a database is a transaction, and we must first discuss the topic of transactions. After you understand how transactions are handled, you’ll see the code for entering a new order to the Northwind database.
Transactions
The closing remark of the last section brings us to a very important topic in database programming, the topic of transactions. A transaction is a series of actions that must either succeed, or fail, as a whole. Should one of the actions fail, then the entire transaction fails and all the changes made to the database so far must be undone (“rolled back” in proper database terminology). If all actions succeed, then they can be finalized (“committed” in proper database terminology) and become part of the database. A transaction
Copyright ©2002 SYBEX, Inc., Alameda, CA |
www.sybex.com |
TRANSACTIONS 979
takes place while you transfer money from one account to another. The two actions are the withdrawal of an amount from one account and the deposit of the same amount to another account. If the bank charges you for the transaction, then a third action is involved in the transaction. If one of them fails, you want the accounts to be restored in their initial states, as if the transaction were never attempted. You don’t want the money to come out of your account and not appear in the other account, and you don’t want to be charged for an unsuccessful transaction. Even if you don’t care about an amount appearing magically in someone else’s account, the bank cares.
To implement a transaction, you mark the beginning of it. If the transaction fails, the database must be restored to the state it was in just prior to when the transaction was initiated. Then you insert the code for all the actions involved in the transaction, and at the end you commit the transaction. Once the transaction is committed, other users can see its effects. Until then, however, other users can’t see the effects of any of the steps. In other words, there are no partial transactions. If an error prevents the completion of the transaction, then you must roll it back—bring the tables to the state they were in just before the start of the transaction. The following pseudo-code is the skeleton of a transaction:
Begin Transaction
Try
{ statements to complete transaction }
Commit Transaction
Catch Exception
Rollback Transaction
End Try
Let’s say we want to place a new order. First, we must add a row to the Orders table. Then we must add some rows to the Order Details table—one row per item ordered. The rows in the Order Details table must reference the matching row in the Orders table—that is, they must use the ID of the order as foreign key.
To add rows to the Order Details table, we’ll do something similar. We’ll write a stored procedure that adds a new row to the Order Details table, the NewOrderLine stored procedure (Listing 22.8). The code we’ll discuss in this section can be found in the Transaction project on the CD. This procedure accepts as arguments the ID of the order to which the detail line belongs, the product ID, and the quantity ordered. To complete the row, it picks the price of the product from the Products table. I’ve ignored the discount in this example, but it can be added with an additional parameter.
Listing 22.8: The NewOrderLine Stored Procedure
CREATE PROCEDURE NewOrderLine
@OrderID integer, @ProductID integer, @quantity integer AS
DECLARE @ProductPrice money
SET @ProductPrice=(SELECT UnitPrice FROM Products WHERE ProductID=@ProductID) INSERT INTO [Order Details] (OrderID, ProductID, Quantity, UnitPrice)
VALUES (@OrderID, @ProductID, @Quantity, @ProductPrice) GO
Copyright ©2002 SYBEX, Inc., Alameda, CA |
www.sybex.com |
980 Chapter 22 PROGRAMMING THE ADO.NET OBJECTS
This stored procedure must be called once for each detail line. Let’s continue with the myCommand object of the previous example. First, we must set the Command object’s CommandText and CommandType properties:
myCommand.CommandText = “NewOrderLine” myCommand.CommandType = CommandType.StoredProcedure
Then we clear the Command object’s Parameters collection and add a new set of parameters, designed for the NewOrderLine stored procedure.
myCommand.Parameters.Clear() p = New SqlParameter() p.ParameterName = “@OrderID”
p.Direction = ParameterDirection.Input p.SqlDbType = SqlDbType.Int
p.Value = orderID myCommand.Parameters.Add(p)
p = New SqlParameter() p.ParameterName = “@ProductID” p.Direction = ParameterDirection.Input p.SqlDbType = SqlDbType.Int
p.Value = 15 myCommand.Parameters.Add(p)
p = New SqlParameter() p.ParameterName = “@Quantity” p.Direction = ParameterDirection.Input p.SqlDbType = SqlDbType.Int
p.Value = 1 myCommand.Parameters.Add(p) myCommand.ExecuteNonQuery()
Each parameter has a name (property ParameterName), type (property SqlDbType), direction (property Direction), and value (property Value). Once these properties are set, we add the parameter object to the Command object’s Parameters collection. After all the necessary parameters are set, we call the ExecuteNonQuery method of the Command object, which executes the stored procedure and returns the number of rows affected. The same process must be repeated for all the detail lines— only you need not re-create the Parameter object, you can simply set its properties and add it to the parameters collection. All other properties remain the same.
So, we have the code that adds a new row to the Orders table (Listing 22.6), the code that retrieves the new order’s ID (Listing 22.7), and the code that adds rows to the Order Details table (Listing 22.8). Let’s put all the actions together in a transaction. To create a transaction with ADO.NET, you must create a Transaction object. A Transaction object is created on the Connection object, by calling its BeginTransaction method. This statement marks the beginning of the
Copyright ©2002 SYBEX, Inc., Alameda, CA |
www.sybex.com |
TRANSACTIONS 981
transaction. Then, we set the Command object’s Transaction property to the Transaction object. This is how ADO knows which actions to undo when the transaction is rolled back:
Dim myTrans As SqlTransaction
myTrans = myConnection.BeginTransaction() myCommand.Transaction = myTrans
Then comes an exception handler to catch any error that may occur during the processing of the transaction. All the statements presented earlier must appear in the Try clause of the exception handler. If the Catch clause is entered, we roll back the transaction by calling the Transaction object’s RollBack method. If the transaction succeeds, we call the same object’s Commit method to commit the transaction. Listing 22.9 shows the code that creates a new order and adds three detail lines to it. The detail lines refer to the products with IDs of 15, 25, and 35, and the quantities are 1, 3, and 5 items of each product, respectively. You have seen most of the code already, but I’m repeating it for your convenience here.
Listing 22.9: Performing a Transaction with the Command Object
Dim myConnection As New SqlConnection() myConnection.ConnectionString = “dataSource=PowerToolkit;initial” & _
“catalog=Northwind;integrated security=SSPI” myConnection.Open()
Dim myCommand As New SqlCommand() myCommand.Connection = myConnection Dim myTrans As SqlTransaction
myTrans = myConnection.BeginTransaction() myCommand.Transaction = myTrans
Try
myCommand.CommandText = “NewOrder” myCommand.CommandType = CommandType.StoredProcedure Dim p As New SqlParameter()
p.ParameterName = “@CustID” p.Direction = ParameterDirection.Input p.SqlDbType = SqlDbType.Char
p.Value = “BLAUS” myCommand.Parameters.Add(p) p = New SqlParameter() p.ParameterName = “RETURN”
p.Direction = ParameterDirection.ReturnValue p.SqlDbType = SqlDbType.Int myCommand.Parameters.Add(p) myCommand.ExecuteScalar()
Dim orderID As Integer = CType(myCommand.Parameters(“RETURN”).Value, Integer)
‘Set up parameters collection and add first item myCommand.CommandText = “NewOrderLine” myCommand.CommandType = CommandType.StoredProcedure myCommand.Parameters.Clear()
Copyright ©2002 SYBEX, Inc., Alameda, CA |
www.sybex.com |
982 Chapter 22 PROGRAMMING THE ADO.NET OBJECTS
p = New SqlParameter() p.ParameterName = “@OrderID” p.Direction = ParameterDirection.Input p.SqlDbType = SqlDbType.Int
p.Value = orderID myCommand.Parameters.Add(p) p = New SqlParameter()
p.ParameterName = “@ProductID” p.Direction = ParameterDirection.Input p.SqlDbType = SqlDbType.Int
p.Value = 15 myCommand.Parameters.Add(p) p = New SqlParameter() p.ParameterName = “@Quantity”
p.Direction = ParameterDirection.Input p.SqlDbType = SqlDbType.Int
p.Value = 1 myCommand.Parameters.Add(p) myCommand.ExecuteNonQuery()
‘ Add second item
p = myCommand.Parameters(“@ProductID”) p.Value = 25
p = myCommand.Parameters(“@Quantity”) p.Value = 2 myCommand.ExecuteNonQuery()
‘ Add third item
p = myCommand.Parameters(“@ProductID”) p.Value = 35
p = myCommand.Parameters(“@Quantity”) p.Value = 3 myCommand.ExecuteNonQuery() myTrans.Commit()
Console.WriteLine(“Order written to database.”) Catch exc As Exception
myTrans.Rollback()
Console.WriteLine(exc.Message)
MsgBox(“Could not add order to database.”) Finally
myConnection.Close() End Try
The last statement in the Finally section of the error handler is very important. In ADO.NET you should never keep connections open longer than absolutely necessary. The code shown in Listing 22.9 will add a new order to the Northwind database by updating two tables in a transaction (you will find this code in the Transaction project on the CD).
Copyright ©2002 SYBEX, Inc., Alameda, CA |
www.sybex.com |