174 |
Saturday Evening |
<%@ Page LANGUAGE=”VB”%>
<%@ Import Namespace=”System.Data” %>
<%@ Import Namespace=”System.Data.ODBC” %> <SCRIPT LANGUAGE=”VB” RUNAT=”server”>
Sub Page_Load(Sender As Object, E As EventArgs) Dim oConn As OdbcConnection
Dim sConnString As String sConnString = “DSN=Bands”
oConn = New OdbcConnection(sConnString) oConn.Open()
oConn.Close() End Sub </SCRIPT> <HTML>
<BODY>
Opening a Connection with ODBC! </BODY>
</HTML>
You’ll notice that the code is very similar to the previous OLEDB examples. Actually, the only difference is how you construct the connection string. Instead of providing all of the database information, such as location, Data Source, and name, Database, you simply supply a DSN name/value pair. You’ve also already provided the User ID or Password when you created the DSN.
Note
You’ll notice in all of the examples, we call a Close method. This method closes the connection that we have opened. Always close connections!!!!! We can’t emphasize this enough. When you close the connection, it is returned to the connection pool and can be used by another request.
Now that the connection is open, what can we do with it? Well, to be quite honest not too terribly much. With the ADO.NET connection objects you can’t directly execute SQL commands against a data source like you could with ADO. In order to create, update, delete, or retrieve data from our data source, you must utilize other ADO.NET objects such as the DataReader and DataAdapter objects, both of which we’ll discuss in Sessions 19 and 20, respectively. The connection does, however, provide several properties and methods to manage transactions.
Using Transactions
By definition, a transaction is an atomic unit of work that either fails or succeeds. There is no such thing as a partially completed transaction. Since a transaction is composed of many steps, each step in the transaction must succeed for the transaction to be successful. If any one part of transaction fails, the entire transaction fails. When the transaction fails, the system needs to return, or roll back, to its original state.
To demonstrate why you may want to use transactions, we will present a sample scenario. Imagine that you own your own bank. A customer wants to transfer $5,000 from checking to savings. So you get on your computer and begin the transaction. Behind the scenes, the following “units” of work need to be completed:
Session 17—Opening a Connection |
175 |
1.Subtract $5,000 from customer’s checking account.
2.Add $5,000 to customer’s saving’s account.
This is a very simple scenario, but we think it illustrates the need for transactions. If either of these steps fails, but the other succeeds, we have a major problem. If step 1 succeeds, but step 2 is not successful, you will have a very upset customer on your hands. If step 1 fails, but step 2 succeeds, you will have a very satisfied customer, but some upset shareholders. Either way, bad news! So you need to ensure that either both steps are successful or that the system returns to its state prior to beginning the transaction.
This is how ADO.NET supports transactions. The ADO.NET connection objects provide the BeginTransaction method, which, when called, returns a transaction object (either
SQLTransaction or OLEDBTransaction).
The BeginTransation method begins a transaction. When called, the BeginTransaction method returns a Transaction object (either an OleDbTransaction or SQLTransaction). It is through the Transaction objects that you can manage your transactions. The Transaction objects support numerous properties and methods (like any object). We will only discuss a few of them in this book. If you need more information, we suggest taking a quick glance at your .NET Framework documentation. The Transaction object’s Commit method commits the transaction, which means that all the steps in the transaction have completed successfully and the new state should be persisted. The transaction object’s RollBack method returns your data store to its pre-transaction state if one or more of the steps in the transaction fail.
So, the basic framework for a transactional ASP.NET page may look like this:
Dim oConn As OleDBConnection
Dim oTransaction As OleDbTransaction Dim sConnString As String sConnString = “DSN=Bands”
oConn = New OLEDBConnection(sConnString) oConn.Open()
oTransaction = oConn.BeginTransaction()
Try
[Transaction Steps] oTransaction.Commit()
Exit Try
Catch
oTransaction.RollBack()
End Try
We begin the transaction with the BeginTransaction method and then attempt to complete each of the steps of the transaction. Because the steps are within a try . . . catch structure, if a step fails, the transaction will be rolled back using the RollbackTransaction method. If all the steps are completed successfully, the transaction is committed with the CommitTransaction method. The try . . . catch structure is new to VB.NET and, as you can see, is very useful.
176 |
Saturday Evening |
Note
Transactions should only be used when writing to your data store. You could retrieve data from your data store using transactions, but this would really serve no purpose since you are not attempting to preserve the integrity of your data. Transactions do incur a bit of a performance penalty, so use them only when necessary.
REVIEW
With the ADO.NET connection objects, SQLConnection and OleDbConnection, you, as a developer, can easily connect to relational and non-relational data sources. The ADO connection objects were designed to be very lightweight — and thereby quickly created and destroyed. The connection objects also provide transaction management facilities through the transaction objects, which are vital for enterprise application development.
QUIZ YOURSELF
1.What is the main function of the connection objects? (See session introduction.)
2.What is the major difference between ODBC and OLEDB? (See “Opening a Connection.”)
3.What is a transaction and why are they important for enterprise application development? (See “Using Transactions.”)
S E S S I O N
18
Executing Commands
Session Checklist
Understanding the function of the Command objects in ADO.NET
Executing SQL commands against a data store
Appending parameters to a Command object
Filling a DataReader using the Command object’s Execute method
In Session 17, “Opening a Connection,” we introduced the ADO.NET Connection objects. In this session, we build upon our previous discussion of connections and introduce the OledbCommand objects. ADO.NET offers two flavors of command objects: OledbCommand
and SqlCommand. Just like the connection object, you can use either object to access a SQL Server database, but need to use the OleDbCommand object to access any other data source. However, using the SqlCommand object with SQL Server does provide some performance gain.
So, what is a command? A command is an instruction — in this case to create, retrieve, update, or delete data in your data store. Most importantly, the Command objects enable you to fill the DataReader objects with data.
In the telephone analogy we used earlier, you are the Command object. When the person on the other end answers the phone, you might say, “May I please speak with Tony?” You have issued a command or request. Or you might say, “Can I have directions to your restaurant?” in which case you are asking for information to be returned to you.
Note
For the remainder of this session, we will focus on the OleDb Managed Provider Command object, OleDbCommand. For the most part, the OleDbCommand and SqlCommand objects map one-to-one. So if you are using a SQL Server database and would like to improve performance, you can use the SqlCommand object. To utilize the SqlCommand object, you will need to import the
System.Data.SqlClient namespace.