C# 資料庫程式設計
鄧姚文
http://www.ywdeng.idv.tw
1
課程簡介
.NET Framework Versions and the Green Bit and
Red Bit Assembly Model
Writing Database Queries
Manipulating Database Data
Using Stored Procedures
Using XML
Using Transactions
Using ADO.NET 3.5
Using LINQ
2
參考書
Vidya Vrat Agarwal, James Huddleston,
Ranga Raghuram, Syed Fahad Gilani, Jacob
Hammer Pedersen, and Jon Reid,
Beginning C# 2008 Databases: From Novice
to Professional,
Apress, 2008
3
範例資料庫
AdvantureWorks
http://msftdbprodsamples.codeplex.com/
使用 SQL 2005 者，安裝 AdventureWorksDB.msi
使用 SQL 2008 者，安裝
SQL2008.AdventureWorks_All_Databases.x86.msi
NorthWind
到 http://www.microsoft.com/downloads 搜尋
sample database
安裝 SQL2000SampleDb.msi
位於 C:\SQL Server 2000 Sample Databases
4
.NET Framework Versions
多種版本的 .NET Framework 可以安裝在同
一個作業系統之中
相容、共存的方式：
分目錄，一個版本一個目錄
一層蓋一層
5
.NET Framework versions installed in
Visual Studio 2008
Green Bit and Red Bit Assembly
Model
Green Bit Assemblies
Additional assemblies that can be installed
above other existing .NET Framework assemblies
without affecting them
Red Bit Assemblies
The assemblies that ship as either part of the
platform or part of a development tool.
7
.NET 3.5 green bit assemblies
8
Using Visual Studio 2008
9
資料儲存的等級
Spreadsheet 試算表
MS Excel
Desktop Database
MS Access
Server Database
MS SQL Server
10
Why Use a Database?
Compactness 精實: Databases help in maintaining
large amounts of data, and thus completely replace
voluminous paper files.
Speed 速度: Searches for a particular piece of data
or information in a database are much faster than
sorting through piles of paper.
Less drudgery 減少苦差事: It is a dull work to
maintain files by hand; using a database completely
eliminates such maintenance.
Currency 即時資訊: Database systems can easily be
updated and so provide accurate information all the
time and on demand.
11
Benefits of Using a Relational Database
Management System
Data integrity Redundancy
Data atomicity Inconsistency
Data security Access anomalies
Transaction processing
Recovery
Storage management
12
The Database Life Cycle
Requirement analysis 需求分析
Logical design 邏輯設計（ERD實體關係圖）
Physical design 實體設計（資料表與索引）
Database implementation 建立資料庫
（Data Definition Language, DDL）
Data modification 處理資料（Data
Manipulation Language, DML）
Database monitoring 監控
Monitoring, Redesign, Modification
13
Mapping Cardinalities
One-to-One (1:1)
Separate data by frequency of use
One-to-Many (1:M)
Reduce redundancy
Primary key -> Foreign Key
Parent Table -> Child table
Many-to-Many (M:M)
Junction table, 兩對 One-to-Many
14
Keys
Candidate Keys 候選鍵
Primary key 主鍵
Composite key 複合鍵
Surrogate key 代理鍵
Foreign key 外來鍵
15
Primary Keys
Each record of the entity must have a not-
null value.
The value must be unique for each record
entered into the entity.
The values must not change or become null
during the life of each entity instance.
There can be only one primary key defined
for an entity.
16
Data Integrity
Entity Integrity
Primary key value exists, can not be null
Unique primary key value
Referential Integrity (RI)
All foreign key values in a child table either
match PK values in a parent table or (if
permitted) be null
Foreign key constraint
17
SQL DDL and DML
Data definition language (DDL) statements
Used for creating tables, relationships, and other
structures.
Data manipulation language (DML)
statements.
Used for queries and data modification
18
Schemas（架構描述）
For SQL Server 2005 and later
Schemas are no longer equivalent to
database users
Each schema is now a distinct namespace
that exists independently of the database
user who created it
A schema is simply a container of objects
A schema can be owned by any user, and its
ownership is transferable.
19
20
Writing Database Queries
SELECT {ColumnName(s)}
FROM {TableName(s)}
WHERE {Conditions}
All SQL statements end with a semi-colon (;)
SELECT * FROM Sales.SalesReason;
SELECT * FROM Sales.SalesReason WHERE
ReasonType='Other';
SELECT * FROM HumanResources.Employee
ORDER BY BirthDate DESC;
21
22
The WHERE Clause
多個條件都必須成立時用 AND
眾多條件只需要一個成立即可時用 OR
SELECT *
FROM Purchasing.vVendor
WHERE City='Lynnwood' AND
[Name] like '%Bicycles'
23
Joins
Inner Joins
Returns only rows that satisfy the join
specification
Natural joins
Left Outer Join
Right Outer Join
Full Outer Join
Cross Join
Union Join
24
Inner Join
select
o.orderid,
o.customerid,
e.lastname
from
orders o inner join employees e
on
o.employeeid = e.employeeid
25
Pattern Matching in Search
Conditions
%
Matches any string of zero or more characters
_
Matches any single character
[]
Any single character within the specified range (for
example, [a-f]) or set (for example, [abcdef])
[^]
Any single character NOT within the specified range
(for example, [^a-f]) or set (for example, [^abcdef])
26
練習
HumanResources.Employee.ManagerID 是員
工上級主管的代碼
請寫一個 SQL 敘述列出 Marketing Manager
David Bradley 的下屬
27
DISTINCT 去除重複的項目
SELECT DISTINCT ProductID FROM
Production.ProductInventory
28
IN
SELECT *
FROM Purchasing.vVendor
WHERE City IN ('Lynnwood', 'New York')
ORDER BY City
也可以用 OR 實現
集合的概念
Subquery 的基礎
29
Ranges (BETWEEN and NOT
BETWEEN)
SELECT ProductID, Name
FROM AdventureWorks.Production.Product
WHERE ListPrice BETWEEN 100 AND 500
ORDER BY ListPrice
範圍起點（較小的值）必須在 AND 前面，
終點（較大的值）在 AND 後面。
30
Null values (IS NULL and IS NOT
NULL)
SELECT s.Name
FROM AdventureWorks.Sales.Customer c
JOIN AdventureWorks.Sales.Store s ON
c.CustomerID = S.CustomerID
WHERE c.SalesPersonID IS NOT NULL
ORDER BY s.Name
IS NOT NULL 不是空的，有值！
31
練習
Person.Contact 為聯絡人資料
請列出 First Name 第一個字
母為 A 且 Title 欄位有值的聯
絡人清單如右：
內容包括 Title, First Name,
Last Name
32
GROUP BY Clause
Organize output rows into groups
SELECT CardType, ExpYear, count(CardType) AS 'Total Cards'
FROM Sales.CreditCard
WHERE ExpYear in (2006,2007)
GROUP BY ExpYear,CardType
ORDER BY CardType,ExpYear
33
Aggregate Functions
select SalesOrderID,min(UnitPrice)as "Min",
max(UnitPrice) as "Max",Sum(UnitPrice) as "Sum",
Avg(UnitPrice)as "Avg"
from Sales.SalesOrderDetail
where SalesOrderID between 43659 and 43663
group by SalesOrderID
34
Choosing Rows with the HAVING
Clause
The WHERE clause is used to filter the rows
that result from the operations specified in
the FROM clause.
The GROUP BY clause is used to group the
output of the WHERE clause.
The HAVING clause is used to filter rows from
the grouped result.
35
SELECT ProductID, AVG(OrderQty) AS AverageQuantity,
SUM(LineTotal) AS Total 列出產品平均數量與小計
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $1000000.00
AND AVG(OrderQty) 5
ORDER BY ProductID ;
36
Subquery
SELECT Ord.SalesOrderID, Ord.OrderDate,
(SELECT MAX(OrdDet.UnitPrice)
FROM Sales.SalesOrderDetail AS OrdDet
WHERE Ord.SalesOrderID =
OrdDet.SalesOrderID) AS MaxUnitPrice
FROM Sales.SalesOrderHeader AS Ord
列出訂單最高單價
37
Subquery Rules
The select list of a subquery introduced with a
comparison operator can include only one expression
or column name (except that EXISTS and IN operate
on SELECT * or a list, respectively).
If the WHERE clause of an outer query includes a
column name, it must be join-compatible with the
column in the subquery select list.
The ntext, text, and image data types cannot be used
in the select list of subqueries.
Because they must return a single value, subqueries
introduced by an unmodified comparison operator
(one not followed by the keyword ANY or ALL) cannot
include GROUP BY and HAVING clauses.
38
Subquery Rules
The DISTINCT keyword cannot be used with subqueries
that include GROUP BY.
The COMPUTE and INTO clauses cannot be specified.
ORDER BY can only be specified when TOP is also
specified.
A view created by using a subquery cannot be updated.
The select list of a subquery introduced with EXISTS, by
convention, has an asterisk (*) instead of a single
column name. The rules for a subquery introduced with
EXISTS are the same as those for a standard select list,
because a subquery introduced with EXISTS creates an
existence test and returns TRUE or FALSE, instead of data.
39
大部分的 Subquery 可以用 JOIN 替
代
/* SELECT statement built using a subquery. */
SELECT Name
價錢和 Chainring Bolts 一樣
FROM Production.Product
WHERE ListPrice =
(SELECT ListPrice
FROM Production.Product
WHERE Name = 'Chainring Bolts' )
/* SELECT statement built using a join that returns the same result set. */
SELECT Prd1.Name
FROM Production.Product AS Prd1
JOIN Production.Product AS Prd2
ON (Prd1.ListPrice = Prd2.ListPrice)
WHERE Prd2.Name = 'Chainring Bolts'
40
Subquery Types
With aliases
With IN or NOT IN
In UPDATE, DELETE, and INSERT statements
With comparison operators
With ANY, SOME, or ALL
With EXISTS or NOT EXISTS
In place of an expression
41
Subquery with Aliases
SELECT EmployeeID, ManagerID
FROM HumanResources.Employee
WHERE ManagerID IN
(SELECT ManagerID
FROM HumanResources.Employee
WHERE EmployeeID = 12)
42
Subquery with NOT IN
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID NOT IN
(SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE Name = 'Mountain Bikes'
OR Name = 'Road Bikes'
OR Name = 'Touring Bikes')
43
Subquery in UPDATE, DELETE, and
INSERT Statements
UPDATE Production.Product
SET ListPrice = ListPrice * 2
WHERE ProductID IN
(SELECT ProductID
FROM Purchasing.ProductVendor
WHERE VendorID = 51);
44
Subqueries with Comparison
Operators
SELECT Name
FROM Production.Product
WHERE ListPrice >
SELECT CustomerID (SELECT MIN (ListPrice)
FROM Sales.Customer FROM Production.Product
GROUP BY ProductSubcategoryID
WHERE TerritoryID = HAVING ProductSubcategoryID = 14)
(SELECT TerritoryID
FROM Sales.SalesPerson
WHERE SalesPersonID = 276)
SELECT Name
FROM Production.Product
WHERE ListPrice >
(SELECT AVG (ListPrice)
45
FROM Production.Product)
Comparison Operators Modified by
ANY, SOME, or ALL
SOME is an ISO standard equivalent for ANY
>ALL means greater than every value
Greater than the maximum value
>ANY means greater than at least one value
Greater than the minimum
=ANY is equivalent to IN
ALL means the same as NOT IN
46
SELECT Name
FROM Production.Product
WHERE ListPrice >= ANY
(SELECT MAX (ListPrice)
FROM Production.Product
GROUP BY ProductSubcategoryID)
Finds the products whose list prices are greater than or equal to the maximum list
price of any product subcategory.
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID ANY
(SELECT TerritoryID
FROM Sales.SalesPerson)
Finds customers located in a territory not covered by any sales persons.
47
Subquery with EXISTS
SELECT Name
FROM Production.Product
WHERE EXISTS
(SELECT *
FROM Production.ProductSubcategory
WHERE ProductSubcategoryID =
Production.Product.ProductSubcategoryID
AND Name = 'Wheels')
Finds the names of all products that are in the Wheels subcategory
48
Subquery Used in Place of an
Expression
SELECT Name, ListPrice,
( SELECT AVG(ListPrice) FROM Production.Product
) AS Average,
ListPrice - (
SELECT AVG(ListPrice) FROM Production.Product
) AS Difference
FROM Production.Product
WHERE ProductSubcategoryID = 1
Finds the prices of all mountain bike products, their average price, and the
difference between the price of each mountain bike and the average price.
49
練習
Production.Product 為產品基本資料表
Production.ProductInventory 為庫存狀態表
請列出倉庫 Tool Crib 沒有的產品
50
Common Table Expressions
Common table expressions (CTEs) are new
to SQL Server 2005
A CTE is a named temporary result set that
will be used by the FROM clause of a SELECT
query
51
Creating a CTE
WITH TopSales
(SalesPersonID,TerritoryID,NumberOfSales)
AS
(
SELECT SalesPersonID,TerritoryID, Count(*)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID, TerritoryID
)
SELECT * FROM TopSales
WHERE SalesPersonID IS NOT NULL
ORDER BY NumberOfSales DESC
52
PIVOT Operator
To generate cross-tabulation reports to
summarize data
Can rotate rows to columns
53
SELECT DaysToManufacture, AVG(StandardCost) AS
AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
54
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;
55
56
57
ROW_NUMBER() Function
For ranking， 產生序號
Returns a unique, sequential number for
each row of the returned result set.
select SalesPersonID, Bonus,
ROW_NUMBER() over (order by Bonus DESC) as [#]
from Sales.SalesPerson
58
PARTITION BY Clause
PARTITION BY clause can be used to divide
the result set into partitions to which the
ROW_NUMBER() function is applied
select CustomerID, TerritoryID ,
Row_Number() over (Partition by TerritoryID
order by CustomerID) as [RowCount]
from Sales.Customer
Where TerritoryID in (1,2) AND
CustomerID Between 1 and 75
59
DATETIME Functions
select
current_timestamp'standard datetime',
getdate()'Transact-SQL datetime',
datepart(year, getdate())'datepart year',
year(getdate())'year function',
datepart(hour, getdate())'hour'
60
練習
HumanResources.Employee 為員工基本資
料表， Person.Contact 為聯絡人資料
（Person.Contact.ContactID=
HumanResources.Employee.EmployeeID）
請列出公司員工之中的『本月壽星』，列
出內容包括 EmployeeID, FirstName,
LastName, BirthDate
61
Manipulating Database Data
INSERT 插入新資料
UPDATE 更新舊資料
DELETE 刪除舊資料
62
Using SELECT INTO Statements
變數名稱前的 # 代表暫存資料表
select
orderid,employeeid,customerid,orderdate,shipcountry
into #myorder
from orders
Temporary tables will be deleted if you close
SQL Server Management Studio Express,
because the tempdb database gets rebuilt
every time you close and open SQL Server
Management Studio Express again
63
64
Using SELECT INTO to Copy Table
Structure
select
orderid,employeeid,customerid,orderdate,shipcountry
into #myemptyorder
from orders
where 0=1
65
Inserting Data
INSERT INTO
(, , ..., )
VALUES (, , ..., )
insert into shippers ( companyname, phone )
values ('GUIPundits', '+91 9820801756')
66
Updating Data
UPDATE
SET = , = , ...,
=
WHERE
update shippers
set companyname = 'PearlHRSolution'
where shipperid = 4
67
Deleting Data
DELETE FROM
WHERE
delete from shippers
where shipperid = 4
68
Using Stored Procedures
Stored procedures are SQL statements that
allow you to perform a task repeatedly
69
Working with a Stored Procedure in
SQL Server
create procedure sp_Select_All_Employees
as
select
Employeeid, firstname, lastname
from
employees
execute sp_Select_All_Employees
70
Creating a Stored Procedure with an
Input Parameter
create procedure sp_Orders_By_EmployeeId
@employeeid int
as
select orderid, customerid
from orders
where employeeid = @employeeid;
execute sp_Orders_By_EmployeeId 2
71
Creating a Stored Procedure with an
Output Parameter
create procedure sp_Orders_By_EmployeeId2
@employeeid int, @ordercount int = 0 output
as
select orderid,customerid
from orders
where employeeid = @employeeid;
select @ordercount = count(*)
from orders
where employeeid = @employeeid
return @ordercount
72
Creating a Stored Procedure with an
Output Parameter
Declare @return_value int, @ordercount int
Execute @return_value=sp_Orders_By_EmployeeId2
@employeeId=2, @ordercount=@ordercount output
Select @ordercount as '@ordercount'
Select 'Return value' =@return_value
73
Working with Stored Procedures in
C#
命令型態設定為 StoredProcedure
SqlCommand.CommandType =
CommandType.StoredProcedure;
命令文字為 StoredProcedure 的名稱
SqlCommand.CommandText =
"sp_select_employees_details";
74
try
{
// open connection
conn.Open();
// create command
SqlCommand cmd = conn.CreateCommand();
// specify stored procedure to execute
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_select_employees_details";
// execute command
SqlDataReader rdr = cmd.ExecuteReader();
// process the result set
while (rdr.Read())
{
Console.WriteLine("{0} {1} {2}",rdr[0].ToString().PadRight(5)
,rdr[1].ToString(),rdr[2].ToString());
}
rdr.Close();
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
conn.Close();
} 75
Executing a Stored Procedure with
Parameters
// create input parameter
SqlParameter inparm = cmd.Parameters.Add("@employeeid", SqlDbType.Int);
inparm.Direction = ParameterDirection.Input;
inparm.Value = 2;
// create output parameter
SqlParameter ouparm = cmd.Parameters.Add("@ordercount", SqlDbType.Int);
ouparm.Direction = ParameterDirection.Output;
// create return value parameter
SqlParameter retval = cmd.Parameters.Add("return_value", SqlDbType.Int);
retval.Direction = ParameterDirection.ReturnValue;
76
Executing a Stored Procedure with
Parameters
// display output parameter value
Console.WriteLine("The output parameter value is {0}",
cmd.Parameters["@ordercount"].Value);
// display return value
Console.WriteLine("The return value is {0}",
cmd.Parameters["return_value"].Value);
77
練習
AdventureWorks 資料庫的
dbo.uspGetEmployeeManagers 可追溯員工
的管理層級
請觀察下列指令執行結果
execute dbo.uspGetEmployeeManagers 15
寫一個 Console Application，呼叫
uspGetEmployeeManagers 印出相同的結果
78
Using XML
Benefits of Storing Data As XML
XML is self-describing, applications can
consume XML data without knowing the schema
or structure
XML maintains document ordering
XML Schema is used to define valid XML
document structure
XQuery and XPath are the query languages
designed to search XML data
Data stored as XML is extensible
79
Converting Relational Data to XML
FOR XML clause converts result sets from a
query into an XML structure
FOR XML RAW
FOR XML AUTO
FOR XML PATH
FOR XML EXPLICIT
80
Using FOR XML RAW (Attribute
Centric)
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID between 98 and 101
FOR XML RAW
FOR XML RAW does not provide a root node
Each row in the query result set into an XML element identified as row for each row
displayed in the result set.
Each column name in the SELECT statement is added as an attribute to the row
element while displaying the result set. 81
Using FOR XML RAW (Element
Centric)
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID between 98 and 101
FOR XML RAW,ELEMENTS
82
Renaming the row Element
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID between 98 and 101
FOR XML RAW ('ProductModelDetail'),ELEMENTS
83
Using FOR XML AUTO
SELECT Cust.CustomerID,OrderHeader.CustomerID,OrderHeader.SalesOrderID,
OrderHeader.Status,Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO
FOR XML AUTO does not provide a renaming mechanism
84
Creating a Table to Store XML
create table xmltest
(
xid int not null primary key,
xdoc xml not null
)
85
Storing and Retrieving XML
Documents
insert into xmltest insert into xmltest
Values(1,' values(2,'
CA
California
Berkeley
Los Angeles
Wilmington
DE
Delaware
Newark ')
Wilmington
')
86
練習
請觀察下列指令執行的結果
SELECT * FROM xmltest;
87
Understanding Transactions
A transaction is a set of operations
performed so all operations are guaranteed
to succeed or fail as one unit.
Commit: all done
Rollback: nothing done
88
When to Use Transactions?
In batch processing, where multiple rows must
be inserted, updated, or deleted as a single unit
Whenever a change to one table requires that
other tables be kept consistent
When modifying data in two or more databases
concurrently
In distributed transactions, where data is
manipulated in databases on different servers
89
ACID Properties
Atomicity: A transaction is regarded as a single
action rather than a collection of separate
operations
Consistency: leave the database in a consistent
state
Isolation: One transaction shouldn't affect other
transactions running at the same time.
Durability: Data modifications that occur within
a successful transaction are kept permanently
within the system regardless of what else
occurs
90
Transaction State
91
Specifying Transaction Boundaries
Transact-SQL statements: Use the BEGIN
TRANSACTION, COMMIT TRANSACTION,
COMMIT WORK, ROLLBACK TRANSACTION,
ROLLBACK WORK, and SET
IMPLICIT_TRANSACTIONS statements to
delineate transactions.
API functions and methods: Database APIs such
as ODBC, OLE DB, ADO, and the .NET
Framework SQLClient namespace contain
functions or methods used to delineate
transactions.
92
Local Transactions
Transactions that are restricted to only a
single resource or database are known as
local transactions
Local transactions can be in one of the
following four transaction modes:
Autocommit Transactions
Explicit Transactions
Implicit Transactions
Batch-Scoped Transactions
93
Autocommit Transactions
The default transaction management mode
Every T-SQL statement is committed or rolled
back when it is completed
If a statement completes successfully, it is
committed;
if it encounters any errors, it is bound to roll back
94
Explicit Transactions
Prior to SQL Server 2000, explicit
transactions were also called user-defined or
user-specified transactions
Use the BEGIN TRANSACTION, COMMIT
TRANSACTION, and ROLLBACK
TRANSACTION statements
Explicit transaction mode lasts only for the
duration of the transaction
95
Implicit Transactions
SET IMPLICIT_TRANSACTIONS ON|OFF
After SET IMPLICIT_TRANSACTIONS ON, SQL
Server automatically starts a transaction when
it first executes any one of:
ALTER TABLE, CREATE, DELETE, DROP, FETCH,
GRANT, INSERT, OPEN, REVOKE, SELECT, TRUNCATE
TABLE, or UPDATE.
The transaction remains in effect until a
COMMIT or ROLLBACK statement has been
explicitly issued
It auto rollback after disconnect
96
Batch-Scoped Transactions
Multiple Active Result Sets (MARS) enabled
Have multiple interleaved batches executing at
the same time
MARS does not let you have multiple
transactions on the same connection, it only
allows having Multiple Active Result Sets.
97
Distributed Transactions
Distributed transactions span two or more
servers
Microsoft Distributed Transaction
Coordinator (MS DTC)
Two phase commit:
Prepare phase
Commit phase
98
Two phase commit: Prepare phase
When the transaction manager receives a
commit request, it sends a prepare command
to all of the resource managers involved in the
transaction.
Each resource manager then does everything
required to make the transaction durable, and
all buffers holding any of the log images for
other transactions are flushed to disk.
As each resource manager completes the
prepare phase, it returns success or failure of
the prepare phase to the transaction manager.
99
Two phase commit: Commit phase
If the transaction manager receives successful
prepares from all of the resource managers, it
sends a COMMIT command to each resource
manager.
If all of the resource managers report a
successful commit, the transaction manager
sends notification of success to the application.
If any resource manager reports a failure to
prepare, the transaction manager sends a
ROLLBACK statement to each resource
manager and indicates the failure of the
commit to the application.
100
Guidelines to Code Efficient
Transactions
Do not require input from users during a
transaction
Do not open a transaction while browsing
through data, if at all possible
Keep the transaction as short as possible
Make intelligent use of lower cursor
concurrency options, such as optimistic
concurrency options
Access the least amount of data possible while
in a transaction
101
How to Code Transactions
BEGIN TRANSACTION: This marks the
beginning of a transaction.
COMMIT TRANSACTION: This marks the
successful end of a transaction. It signals
the database to save the work.
ROLLBACK TRANSACTION: This denotes that
a transaction hasn't been successful and
signals the database to roll back to the state
it was in prior to the transaction.
102
Coding a Transaction in T-SQL
Listing 8-1. sp_Trans_Test
exec sp_Trans_Test 'a ', 'a ', 'z '
@@ERROR is reset after every T-SQL
statement (even SET and IF) is executed
If @@ERROR returns any value other than 0,
an error has occurred
exec sp_Trans_Test 'a', 'a ', 'aa '
exec sp_Trans_Test 'aaa', 'aaa ', 'ALFKI'
103
Coding Transactions in ADO.NET
In ADO.NET, a transaction is an instance of a
class that implements the interface
System.Data.IDbTransaction.
104