Microsoft SQL Server 2005 Developer’s Guide- P11

Microsoft SQL Server 2005 Developer’s Guide- P11:This book is the successor to the SQL Server 2000 Developer’s Guide, whichwas extremely successful thanks to all of the supportive SQL Server developerswho bought that edition of the book. Our first thanks go to all of the peoplewho encouraged us to write another book about Microsoft’s incredible new relationaldatabase server: SQL Server 2005.
Nội dung trích xuất từ tài liệu:
Microsoft SQL Server 2005 Developer’s Guide- P11 Chapter 6: Developing Database Applications with ADO.NET 199functions would be considered part of the same logical transaction. From thedatabase standpoint, to ensure database integrity, both the withdrawal and the depositwould be grouped together as a single transaction. If the withdrawal operationsucceeded, but the deposit failed, the entire transaction could be rolled back, whichwould restore the database to the condition it had before the withdrawal operationwas attempted. Using transactions is an essential part of most production-leveldatabase applications. ADO.NET supports transactions using the Transaction classes. In order toincorporate transactions into your ADO.NET applications, you first need to create aninstance of the SqlTransaction object and then execute the BeginTransaction methodto mark the beginning of a transaction. Under the covers this will cause the databaseserver to begin a transaction. For instance, using the SqlTransaction object to issue aBeginTransaction statement will send a T-SQL BEGIN TRANSACTION commandto SQL Server. After the transaction has started, the database update operations areperformed and then the Commit method is used to actually write the updates to thetarget database. If an error occurs during the process, then the RollBack operationis used to undo the changes. The following SQLCommandTransaction subroutineshows how to start a transaction and then either commit the results of the transactionto the database or roll back the transaction in the event of an error:Private Sub SQLCommandTransaction(cn As SqlConnection) Dim cmd As New SqlCommand() Dim trans As SqlTransaction Start a local transaction trans = cn.BeginTransaction() cmd.Connection = cn cmd.Transaction = trans Try Insert a row transaction cmd.CommandText = _ INSERT INTO Department VALUES(100, Transaction 100) cmd.ExecuteNonQuery() This next insert will result in an error cmd.CommandText = _ INSERT INTO Department VALUES(100, Transaction 101) cmd.ExecuteNonQuery() trans.Commit() Catch e As Exception MsgBox(e.Message) trans.Rollback() End TryEnd Sub200 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e In the beginning of this subroutine, you can see where the SqlConnection object is passed in and a new instance of the SqlCommand object is created, followed by the definition of a SqlTransaction object named trans. Next, a local transaction is started by using the cn SqlConnection object’s BeginTransaction method to create a new instance of a SqlTransaction object. Note that the connection must be open before you execute the BeginTransaction method. Next, the cmd SqlCommand Connection property is assigned with the cn SqlConnection and the Transaction property is assigned with the trans SqlTransaction object. Within the Try-Catch block, two commands are issued that are within the local transaction scope. The first command is an INSERT statement that inserts two columns into the Department table that was created previously in this chapter. The first insert statement adds the DepartmentID of 100 along with a DepartmentName value of “Transaction 100.” The SqlCommand ExecuteNonQuery method is then used to execute the SQL statement. Next, the cmd object’s CommandText property is set to another SQL INSERT statement. However, this statement will cause an error because it is attempting to insert a duplicate primary key value. In this second case, the DepartmentID of 100 is attempted to be inserted along with the DepartmentName value of “Transaction 101.” This causes an error because the DepartmentID of 100 was just inserted by the previous INSERT statement. When the ExecuteNonQuery method is executed, the duplicate primary key error will be issued and the code in the Catch portion of the Try-Catch block will be executed. Displaying the exception message in a message box is the first action that happens within the Catch block. You can see an example of this message in Figure 6-3. After the message box is displayed, the trans SqlTransaction object’s RollBack method is used to roll back the attempted transaction. Note that because both insert statements were within the same transaction scope, both insert operations will be rolled back. The resulting department table will not contain either DepartmentName “Transaction 100” or DepartmentName “Transaction 101.” Figure 6-3 A duplicate primary key error prevents the Commit operation. Chapter 6: Developing Database Applications with ADO.NET 201Using the SqlDependency ObjectSQL Server 2005 and ADO.NET 2.0 now contain a signaling solution in the dataprovider and the database called Query Notifications. Query Notifications allowsyour application to request a notification from SQL Server when the results ofa query change. You can design applications that query the database only when thereis a change to information that the application has previously retrieved. Query Notifications are implemented through the SQL Server 2005 Query Engine,the SQL Server Service Broker, a system stored procedure (sp_DispatcherProc),the ADO.NET System.Data.Sql.SqlNotificationRequest class, the System.Data.SqlClient.SqlDependency class, and the ASP.NET System.Web.Caching.Cache class.The basic process is as follows: 1. The SqlCommand object contains a Notification property that is a request for notification. When the SqlCommand is executed and the Notification property is not null, a request of notification is appended to the com ...