vb.net qr code sample s USING TRANSACTIONS in Visual Basic .NET

Painting DataMatrix in Visual Basic .NET s USING TRANSACTIONS

CHAPTER 14 s USING TRANSACTIONS
DataMatrix Maker In VB.NET
Using Barcode encoder for VS .NET Control to generate, create Data Matrix ECC200 image in VS .NET applications.
www.OnBarcode.com
Recognizing ECC200 In Visual Basic .NET
Using Barcode recognizer for VS .NET Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
Coding Transactions in T-SQL
UCC.EAN - 128 Printer In VB.NET
Using Barcode generator for .NET framework Control to generate, create EAN 128 image in .NET framework applications.
www.OnBarcode.com
Make Code39 In VB.NET
Using Barcode creator for .NET framework Control to generate, create Code 39 image in .NET framework applications.
www.OnBarcode.com
We ll use a stored procedure to practice coding transactions in SQL. It s an intentionally artificial example but representative of transaction processing fundamentals. It keeps things simple so you can focus on the important issue of what can happen in a transaction. That s what you really need to understand, especially when you later code the same transaction in Visual Basic.
Barcode Drawer In VB.NET
Using Barcode encoder for .NET Control to generate, create Barcode image in Visual Studio .NET applications.
www.OnBarcode.com
Encode GS1 - 12 In VB.NET
Using Barcode drawer for .NET framework Control to generate, create UPC-A Supplement 5 image in VS .NET applications.
www.OnBarcode.com
s Warning Using ROLLBACK and COMMIT inside stored procedures typically requires careful consideration
Print Code 128C In Visual Basic .NET
Using Barcode creator for Visual Studio .NET Control to generate, create Code 128 image in .NET framework applications.
www.OnBarcode.com
Encoding UPC - 8 In VB.NET
Using Barcode maker for .NET framework Control to generate, create European Article Number 8 image in Visual Studio .NET applications.
www.OnBarcode.com
of what transactions may already be in progress and led to the stored procedure call. Our example runs by itself, so we re not concerned with this here, but you should always consider whether it s a potential issue.
DataMatrix Generation In Objective-C
Using Barcode encoder for iPad Control to generate, create Data Matrix 2d barcode image in iPad applications.
www.OnBarcode.com
ECC200 Encoder In None
Using Barcode generation for Software Control to generate, create ECC200 image in Software applications.
www.OnBarcode.com
Try It Out: Coding a Transaction in T-SQL
PDF 417 Generator In None
Using Barcode drawer for Software Control to generate, create PDF417 image in Software applications.
www.OnBarcode.com
Making USS-128 In Objective-C
Using Barcode generator for iPhone Control to generate, create EAN 128 image in iPhone applications.
www.OnBarcode.com
Let s code a transaction to both add a customer to and delete one from the Northwind Customers table. Customers has eleven columns, but only two, CustomerID and CompanyName, don t allow nulls, so we ll use just those columns for insertion. We ll also use arbitrary customer IDs to make it easy to find the rows we manipulate when viewing customers sorted by ID. 1. In SSMSE, create a stored procedure named sp_Trans_Test, using the code in Listing 14-1. Note that you re using several new SQL statements for stored procedure programming. We don t explain the ones not involved in transactions, but their meaning and usage should be obvious.
DataMatrix Generator In .NET Framework
Using Barcode creator for Reporting Service Control to generate, create ECC200 image in Reporting Service applications.
www.OnBarcode.com
UPC-A Maker In None
Using Barcode drawer for Software Control to generate, create UPC Symbol image in Software applications.
www.OnBarcode.com
Listing 14-1. sp_Trans_Test
Data Matrix ECC200 Encoder In C#
Using Barcode drawer for .NET Control to generate, create ECC200 image in .NET applications.
www.OnBarcode.com
QR Code 2d Barcode Creation In None
Using Barcode generation for Software Control to generate, create QR Code 2d barcode image in Software applications.
www.OnBarcode.com
create procedure sp_Trans_Test @newcustid nchar(5), @newconame nvarchar(40), @oldcustid nchar(5) as declare @inserr int declare @delerr int declare @maxerr int set @maxerr = 0 begin transaction
Draw Code 128 Code Set B In None
Using Barcode creation for Online Control to generate, create Code 128C image in Online applications.
www.OnBarcode.com
Barcode Drawer In Visual Studio .NET
Using Barcode creation for Visual Studio .NET Control to generate, create Barcode image in .NET applications.
www.OnBarcode.com
CHAPTER 14 s USING TRANSACTIONS
Drawing Code 128B In None
Using Barcode creation for Word Control to generate, create Code 128 Code Set B image in Word applications.
www.OnBarcode.com
Decoding Data Matrix 2d Barcode In Java
Using Barcode reader for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
-- Add a customer insert into customers ( customerid, companyname ) values(@newcustid, @newconame) -- Save error number set @inserr = @@error if @inserr > @maxerr set @maxerr = @inserr -- Delete a customer delete from customers where customerid = @oldcustid -- Save error number set @delerr = @@error if @delerr > @maxerr set @maxerr = @delerr -- If an error occurred, roll back if @maxerr <> 0 begin rollback print 'Transaction rolled back' end else begin commit print 'Transaction committed' end print 'INSERT error number:' + cast(@inserr as nvarchar(8)) print 'DELETE error number:' + cast(@delerr as nvarchar(8)) return @maxerr
CHAPTER 14 s USING TRANSACTIONS
2. Run the stored procedure from Object Explorer. When prompted, enter just a for both @newcustid and @newconame and z for @oldcustid. A new edit window will appear, displaying your input values. The Results window should show a Return Value of zero. Click on the Messages tab and you should see the same messages as in Figure 14-1.
Figure 14-1. Rows inserted (but not deleted) in a transaction
3. Look at the table with Object Explorer (right-click dbo.Customers and click Open Table) and you ll see that customer aaa has been inserted. (See Figure 14-2.)
Figure 14-2. Row inserted in a transaction
4. Add another customer, with aa for both @newcustid and @newconame and z for @oldcustid.
How It Works
We ll discuss the T-SQL first, then the results. You defined three input parameters:
create procedure sp_Trans_Test @newcustid nchar(5), @newconame nvarchar(40), @oldcustid nchar(5) as
CHAPTER 14 s USING TRANSACTIONS
and declared three local variables:
declare @inserr int declare @delerr int declare @maxerr int
that you used to instrument the stored procedure, so you can capture and display the error numbers from the INSERT and DELETE statements. You marked the beginning of the transaction with a BEGIN TRANSACTION statement and follow it with the INSERT and DELETE statements that are part of the transaction. After each statement, you save the return number for it.
begin transaction -- Add a customer insert into customers ( customerid, companyname ) values(@newcustid, @newconame) -- Save error number set @inserr = @@error if @inserr > @maxerr set @maxerr = @inserr -- Delete a customer delete from customers where customerid = @oldcustid -- Save error number set @delerr = @@error if @delerr > @maxerr set @maxerr = @delerr
Error handling is important at all times in SQL Server, and it s never more so than inside transactional code. When you execute any T-SQL statement, there s always the possibility that it may not succeed. The T-SQL @@ERROR function returns the error number for the last T-SQL statement executed. If no error occurred, @@ERROR returns zero.
Copyright © OnBarcode.com . All rights reserved.