ADO.NET在开发中的部分使用方法和技巧
这2天放假，在家没事做就整理一下自己平时用的在ADO.NET中常用的技巧
开始
如何使用 SqlDataAdapter 来检索多个行
以下代码阐明了如何使用 SqlDataAdapter 对象发出可生成 DataSet 或 DataTable 的命令。它从 SQL Server Northwind 数据库中检索一组产品类别。
1 using System.Data;
2 using System.Data.SqlClient;
3
4 public DataTable RetrieveRowsWithDataTable()
5 {
6 using ( SqlConnection conn = new SqlConnection(connectionString) )
7 {
8 conn.Open();
9 SqlCommand cmd = new SqlCommand("DATRetrieveProducts", conn);
10 cmd.CommandType = CommandType.StoredProcedure;
11 SqlDataAdapter adapter = new SqlDataAdapter( cmd );
12 DataTable dataTable = new DataTable("Products");
13 adapter .Fill(dataTable);
14 return dataTable;
15 }
16 }
使用 SqlAdapter 生成 DataSet 或 DataTable
1. 创建一个 SqlCommand 对象以调用该存储过程，并将其与一个 SqlConnection 对象（显示）或连接字符串（不显示）相关联。
2. 创建一个新的 SqlDataAdapter 对象并将其与 SqlCommand 对象相关联。
3. 创建一个 DataTable（也可以创建一个 DataSet）对象。使用构造函数参数来命名 DataTable。
4. 调用 SqlDataAdapter 对象的 Fill 方法，用检索到的行填充 DataSet 或 DataTable。
如何使用 SqlDataReader 来检索多个行
以下代码片段阐明了可检索多个行的 SqlDataReader 方法。
1 using System.IO;
2 using System.Data;
3 using System.Data.SqlClient;
4
5 public SqlDataReader RetrieveRowsWithDataReader()
6 {
7 SqlConnection conn = new SqlConnection(
8 "server=(local);Integrated Security=SSPI;database=northwind");
9 SqlCommand cmd = new SqlCommand("DATRetrieveProducts", conn );
10 cmd.CommandType = CommandType.StoredProcedure;
11 try
12 {
13 conn.Open();
14 // Generate the reader. CommandBehavior.CloseConnection causes the
15 // the connection to be closed when the reader object is closed
16 return( cmd.ExecuteReader( CommandBehavior.CloseConnection ) );
17 }
18 catch
19 {
20 conn.Close();
21 throw;
22 }
23 }
24
25 // Display the product list using the console
26 private void DisplayProducts()
27 {
28 SqlDataReader reader = RetrieveRowsWithDataReader();
29 try
30 {
31 while (reader.Read())
32 {
33 Console.WriteLine("{0} {1} {2}",
34 reader.GetInt32(0).ToString(),
35 reader.GetString(1) );
36 }
37 }
38 finally
39 {
40 reader.Close(); // Also closes the connection due to the
41 // CommandBehavior enum used when generating the reader
42 }
43 }
使用 SqlDataReader 检索行
1. 创建一个用来执行存储过程的 SqlCommand 对象，并将其与一个 SqlConnection 对象相关联。
2. 打开连接。
3. 通过调用 SqlCommand 对象的 ExecuteReader 方法创建一个 SqlDataReader 对象。
4. 要从流中读取数据，请调用 SqlDataReader 对象的 Read 方法来检索行，并使用类型化访问器方法（如 GetInt32 和 GetString 方法）来检索列值。
5. 使用完读取器后，请调用其 Close 方法。
如何使用 XmlReader 检索多个行
可以使用 SqlCommand 对象来生成 XmlReader 对象，后者可提供对 XML 数据的基于流的只进访问。命令（通常为存储过程）必须产生基于 XML 的结果集，对于 SQL Server 2000 而言，该结果集通常包含一个带有有效 FOR XML 子句的 SELECT 语句。以下代码片段阐明了该方法：
1 public void RetrieveAndDisplayRowsWithXmlReader()
2 {
3 using( SqlConnection conn = new SqlConnection(connectionString) )
4 {;
5 SqlCommand cmd = new SqlCommand("DATRetrieveProductsXML", conn );
6 cmd.CommandType = CommandType.StoredProcedure;
7 try
8 {
9 conn.Open();
10 XmlTextReader xreader = (XmlTextReader)cmd.ExecuteXmlReader();
11 while ( xreader.Read() )
12 {
13 if ( xreader.Name == "PRODUCTS" )
14 {
15 string strOutput = xreader.GetAttribute("ProductID");
16 strOutput += " ";
17 strOutput += xreader.GetAttribute("ProductName");
18 Console.WriteLine( strOutput );
19 }
20 }
21 xreader.Close(); // XmlTextReader does not support IDisposable so it can't be
22 // used within a using keyword
23 }
24 }
上述代码使用了以下存储过程：
CREATE PROCEDURE DATRetrieveProductsXML
AS
SELECT * FROM PRODUCTS
FOR XML AUTO
GO
使用 XmlReader 检索 XML 数据
1. 创建一个 SqlCommand 对象来调用可生成 XML 结果集的存储过程（例如，在 SELECT 语句中使用 FOR XML 子句）。将该 SqlCommand 对象与某个连接相关联。
2. 调用 SqlCommand 对象的 ExecuteXmlReader 方法，并且将结果分配给只进 XmlTextReader 对象。当您不需要对返回的数据进行任何基于 XML 的验证时，这是应该使用的最快类型的 XmlReader 对象。
3. 使用 XmlTextReader 对象的 Read 方法来读取数据。
如何使用存储过程输出参数来检索单个行
借助于命名的输出参数，可以调用在单个行内返回检索到的数据项的存储过程。以下代码片段使用存储过程来检索 Northwind 数据库的 Products 表中包含的特定产品的产品名称和单价。
1 void GetProductDetails( int ProductID,
2 out string ProductName, out decimal UnitPrice )
3 {
4 using( SqlConnection conn = new SqlConnection(
5 "server=(local);Integrated Security=SSPI;database=Northwind") )
6 {
7 // Set up the command object used to execute the stored proc
8 SqlCommand cmd = new SqlCommand( "DATGetProductDetailsSPOutput", conn )
9 cmd.CommandType = CommandType.StoredProcedure;
10 // Establish stored proc parameters.
11 // @ProductID int INPUT
12 // @ProductName nvarchar(40) OUTPUT
13 // @UnitPrice money OUTPUT
14
15 // Must explicitly set the direction of output parameters
16 SqlParameter paramProdID =
17 cmd.Parameters.Add( "@ProductID", ProductID );
18 paramProdID.Direction = ParameterDirection.Input;
19 SqlParameter paramProdName =
20 cmd.Parameters.Add( "@ProductName", SqlDbType.VarChar, 40 );
21 paramProdName.Direction = ParameterDirection.Output;
22 SqlParameter paramUnitPrice =
23 cmd.Parameters.Add( "@UnitPrice", SqlDbType.Money );
24 paramUnitPrice.Direction = ParameterDirection.Output;
25
26 conn.Open();
27 // Use ExecuteNonQuery to run the command.
28 // Although no rows are returned any mapped output parameters
29 // (and potentially return values) are populated
30 cmd.ExecuteNonQuery( );
31 // Return output parameters from stored proc
32 ProductName = paramProdName.Value.ToString();
33 UnitPrice = (decimal)paramUnitPrice.Value;
34 }
35 }
使用存储过程输出参数来检索单个行
1. 创建一个 SqlCommand 对象并将其与一个 SqlConnection 对象相关联。
2. 通过调用 SqlCommand 的 Parameters 集合的 Add 方法来设置存储过程参数。默认情况下，参数都被假设为输入参数，因此必须显式设置任何输出参数的方向。
注 一种良好的习惯做法是显式设置所有参数（包括输入参数）的方向。
3. 打开连接。
4. 调用 SqlCommand 对象的 ExecuteNonQuery 方法。这将填充输出参数（并可能填充返回值）。
5. 通过使用 Value 属性，从适当的 SqlParameter 对象中检索输出参数。
6. 关闭连接。
上述代码片段调用了以下存储过程。
1 CREATE PROCEDURE DATGetProductDetailsSPOutput
2 @ProductID int,
3 @ProductName nvarchar(40) OUTPUT,
4 @UnitPrice money OUTPUT
5 AS
6 SELECT @ProductName = ProductName,
7 @UnitPrice = UnitPrice
8 FROM Products
9 WHERE ProductID = @ProductID
10 GO
何使用 SqlDataReader 来检索单个行
可以使用 SqlDataReader 对象来检索单个行，尤其是可以从返回的数据流中检索需要的列值。以下代码片段对此进行了说明。
1 void GetProductDetailsUsingReader( int ProductID,
2 out string ProductName, out decimal UnitPrice )
3 {
4 using( SqlConnection conn = new SqlConnection(
5 "server=(local);Integrated Security=SSPI;database=Northwind") )
6 {
7 // Set up the command object used to execute the stored proc
8 SqlCommand cmd = new SqlCommand( "DATGetProductDetailsReader", conn );
9 cmd.CommandType = CommandType.StoredProcedure;
10 // Establish stored proc parameters.
11 // @ProductID int INPUT
12
13 SqlParameter paramProdID = cmd.Parameters.Add( "@ProductID", ProductID );
14 paramProdID.Direction = ParameterDirection.Input;
15 conn.Open();
16 using( SqlDataReader reader = cmd.ExecuteReader() )
17 {
18 if( reader.Read() ) // Advance to the one and only row
19 {
20 // Return output parameters from returned data stream
21 ProductName = reader.GetString(0);
22 UnitPrice = reader.GetDecimal(1);
23 }
24 }
25 }
26 }
使用 SqlDataReader 对象来返回单个行
1. 建立 SqlCommand 对象。
2. 打开连接。
3. 调用 SqlDataReader 对象的 ExecuteReader 方法。
4. 通过 SqlDataReader 对象的类型化访问器方法（在这里，为 GetString 和 GetDecimal）来检索输出参数。
上述代码片段调用了以下存储过程。
1 CREATE PROCEDURE DATGetProductDetailsReader
2 @ProductID int
3 AS
4 SELECT ProductName, UnitPrice FROM Products
5 WHERE ProductID = @ProductID
6 GO
如何使用 ExecuteScalar 来检索单个项
ExecuteScalar 方法专门适用于仅返回单个值的查询。如果查询返回多个列和/或行，ExecuteScalar 将只返回第一行的第一列。
以下代码说明了如何查找与特定产品 ID 相对应的产品名称：
1 void GetProductNameExecuteScalar( int ProductID, out string ProductName )
2 {
3 using( SqlConnection conn = new SqlConnection(
4 "server=(local);Integrated Security=SSPI;database=northwind") )
5 {
6 SqlCommand cmd = new SqlCommand("LookupProductNameScalar", conn );
7 cmd.CommandType = CommandType.StoredProcedure;
8
9 cmd.Parameters.Add("@ProductID", ProductID );
10 conn.Open();
11 ProductName = (string)cmd.ExecuteScalar();
12 }
13 }
使用 ExecuteScalar 来检索单个项
1.
建立一个 SqlCommand 对象来调用存储过程。
2.
打开连接。
3.
调用 ExecuteScalar 方法。注意，该方法返回一个对象类型。它包含检索到的第一列的值，并且必须转化为适当的类型。
4.
关闭连接。
上述代码使用了以下存储过程：
1 CREATE PROCEDURE LookupProductNameScalar
2 @ProductID int
3 AS
4 SELECT TOP 1 ProductName
5 FROM Products
6 WHERE ProductID = @ProductID
7 GO
如何使用存储过程输出或返回参数来检索单个项
可以使用存储过程输出或返回参数来查找单个值。以下代码阐明了输出参数的用法：
1 void GetProductNameUsingSPOutput( int ProductID, out string ProductName )
2 {
3 using( SqlConnection conn = new SqlConnection(
4 "server=(local);Integrated Security=SSPI;database=northwind") )
5 {
6 SqlCommand cmd = new SqlCommand("LookupProductNameSPOutput", conn );
7 cmd.CommandType = CommandType.StoredProcedure;
8
9 SqlParameter paramProdID = cmd.Parameters.Add("@ProductID", ProductID );
10 ParamProdID.Direction = ParameterDirection.Input;
11 SqlParameter paramPN =
12 cmd.Parameters.Add("@ProductName", SqlDbType.VarChar, 40 );
13 paramPN.Direction = ParameterDirection.Output;
14
15 conn.Open();
16 cmd.ExecuteNonQuery();
17 ProductName = paramPN.Value.ToString();
18 }
19 }
使用存储过程输出参数来检索单个值
1.
建立一个 SqlCommand 对象来调用存储过程。
2.
通过将 SqlParameters 添加到 SqlCommand 的 Parameters 集合中，设置任何输入参数和单个输出参数。
3.
打开连接。
4.
调用 SqlCommand 对象的 ExecuteNonQuery 方法。
5.
关闭连接。
6.
通过使用输出 SqlParameter 的 Value 属性来检索输出值。
上述代码使用了以下存储过程。
1 CREATE PROCEDURE LookupProductNameSPOutput
2 @ProductID int,
3 @ProductName nvarchar(40) OUTPUT
4 AS
5 SELECT @ProductName = ProductName
6 FROM Products
7 WHERE ProductID = @ProductID
8 GO
以下代码阐明了如何使用返回值来指明是否存在特定行。从编码角度来看，这类似于使用存储过程输出参数，不同之处在于必须将 SqlParameter 方向显式设置为 ParameterDirection.ReturnValue。
1 bool CheckProduct( int ProductID )
2 {
3 using( SqlConnection conn = new SqlConnection(
4 "server=(local);Integrated Security=SSPI;database=northwind") )
5 {
6 SqlCommand cmd = new SqlCommand("CheckProductSP", conn );
7 cmd.CommandType = CommandType.StoredProcedure;
8
9 cmd.Parameters.Add("@ProductID", ProductID );
10 SqlParameter paramRet =
11 cmd.Parameters.Add("@ProductExists", SqlDbType.Int );
12 paramRet.Direction = ParameterDirection.ReturnValue;
13 conn.Open();
14 cmd.ExecuteNonQuery();
15 }
16 return (int)paramRet.Value == 1;
17 }
通过使用存储过程返回值来检查是否存在特定行
1. 建立一个 SqlCommand 对象来调用存储过程。
2. 设置一个输入参数，该参数含有要访问的行的主键值。
3. 设置单个返回值参数。将一个 SqlParameter 对象添加到 SqlCommand 的 Parameters 集合中，并将其方向设置为 ParameterDirection.ReturnValue。
4. 打开连接。
5. 调用 SqlCommand 对象的 ExecuteNonQuery 方法。
6. 关闭连接。
7. 通过使用返回值 SqlParameter 的 Value 属性来检索返回值。
上述代码使用了以下存储过程。
1 CREATE PROCEDURE CheckProductSP
2 @ProductID int
3 AS
4 IF EXISTS( SELECT ProductID
5 FROM Products
6 WHERE ProductID = @ProductID )
7 return 1
8 ELSE
9 return 0
10 GO
如何使用 SqlDataReader 来检索单个项
可以使用 SqlDataReader 对象并通过调用命令对象的 ExecuteReader 方法来获取单个输出值。这要求编写稍微多一点的代码，因为必须调用 SqlDataReader Read 方法，然后通过该读取器的访问器方法之一来检索需要的值。以下代码阐明了 SqlDataReader 对象的用法。
1 bool CheckProductWithReader( int ProductID )
2 {
3 using( SqlConnection conn = new SqlConnection(
4 "server=(local);Integrated Security=SSPI;database=northwind") )
5 {
6 SqlCommand cmd = new SqlCommand("CheckProductExistsWithCount", conn );
7 cmd.CommandType = CommandType.StoredProcedure;
8
9 cmd.Parameters.Add("@ProductID", ProductID );
10 cmd.Parameters["@ProductID"].Direction = ParameterDirection.Input;
11 conn.Open();
12 using( SqlDataReader reader = cmd.ExecuteReader(
13 CommandBehavior.SingleResult ) )
14 {
15 if( reader.Read() )
16 {
17 return (reader.GetInt32(0) > 0);
18 }
19 return false;
20 }
21 }
上述代码采用了以下存储过程。
CREATE PROCEDURE CheckProductExistsWithCount
@ProductID int
AS
SELECT COUNT(*) FROM Products
WHERE ProductID = @ProductID
GO
如何编写 ADO.NET 手动事务处理代码
以下代码显示了如何充分利用 SQL Server .NET 数据提供程序所提供的事务处理支持，通过事务来保护资金转帐操作。该操作在同一数据库中的两个帐户之间转移资金。
1 public void TransferMoney( string toAccount, string fromAccount, decimal amount )
2 {
3 using ( SqlConnection conn = new SqlConnection(
4 "server=(local);Integrated Security=SSPI;database=SimpleBank" ) )
5 {
6 SqlCommand cmdCredit = new SqlCommand("Credit", conn );
7 cmdCredit.CommandType = CommandType.StoredProcedure;
8 cmdCredit.Parameters.Add( new SqlParameter("@AccountNo", toAccount) );
9 cmdCredit.Parameters.Add( new SqlParameter("@Amount", amount ));
10
11 SqlCommand cmdDebit = new SqlCommand("Debit", conn );
12 cmdDebit.CommandType = CommandType.StoredProcedure;
13 cmdDebit.Parameters.Add( new SqlParameter("@AccountNo", fromAccount) );
14 cmdDebit.Parameters.Add( new SqlParameter("@Amount", amount ));
15
16 conn.Open();
17 // Start a new transaction
18 using ( SqlTransaction trans = conn.BeginTransaction() )
19 {
20 // Associate the two command objects with the same transaction
21 cmdCredit.Transaction = trans;
22 cmdDebit.Transaction = trans;
23 try
24 {
25 cmdCredit.ExecuteNonQuery();
26 cmdDebit.ExecuteNonQuery();
27 // Both commands (credit and debit) were successful
28 trans.Commit();
29 }
30 catch( Exception ex )
31 {
32 // transaction failed
33 trans.Rollback();
34 // log exception details . . .
35 throw ex;
36 }
37 }
38 }
39 }
如何使用 Transact-SQL 执行事务处理
以下存储过程阐明了如何在 Transact-SQL 存储过程内部执行事务性资金转帐操作。
1 CREATE PROCEDURE MoneyTransfer
2 @FromAccount char(20),
3 @ToAccount char(20),
4 @Amount money
5 AS
6 BEGIN TRANSACTION
7 -- PERFORM DEBIT OPERATION
8 UPDATE Accounts
9 SET Balance = Balance - @Amount
10 WHERE AccountNumber = @FromAccount
11 IF @@RowCount = 0
12 BEGIN
13 RAISERROR('Invalid From Account Number', 11, 1)
14 GOTO ABORT
15 END
16 DECLARE @Balance money
17 SELECT @Balance = Balance FROM ACCOUNTS
18 WHERE AccountNumber = @FromAccount
19 IF @BALANCE < 0
20 BEGIN
21 RAISERROR('Insufficient funds', 11, 1)
22 GOTO ABORT
23 END
24 -- PERFORM CREDIT OPERATION
25 UPDATE Accounts
26 SET Balance = Balance + @Amount
27 WHERE AccountNumber = @ToAccount
28 IF @@RowCount = 0
29 BEGIN
30 RAISERROR('Invalid To Account Number', 11, 1)
31 GOTO ABORT
32 END
33 COMMIT TRANSACTION
34 RETURN 0
35 ABORT:
36 ROLLBACK TRANSACTION
37 GO
该存储过程使用 BEGIN TRANSACTION、COMMIT TRANSACTION 和 ROLLBACK TRANSACTION 语句来手动控制该事务。
如何编写事务性 .NET 类
以下示例代码显示了三个服务性 .NET 托管类，这些类经过配置以执行自动事务处理。每个类都使用 Transaction 属性进行了批注，该属性的值确定是否应该启动新的事务流，或者该对象是否应该共享其直接调用方的事务流。这些组件协同工作来执行银行资金转帐任务。Transfer 类被使用 RequiresNew 事务属性进行了配置，而 Debit 和 Credit 被使用 Required 进行了配置。结果，所有这三个对象在运行时都将共享同一事务。1 using System;
2 using System.EnterpriseServices;
3
4 [Transaction(TransactionOption.RequiresNew)]
5 public class Transfer : ServicedComponent
6 {
7 [AutoComplete]
8 public void Transfer( string toAccount,
9 string fromAccount, decimal amount )
10 {
11 try
12 {
13 // Perform the debit operation
14 Debit debit = new Debit();
15 debit.DebitAccount( fromAccount, amount );
16 // Perform the credit operation
17 Credit credit = new Credit();
18 credit.CreditAccount( toAccount, amount );
19 }
20 catch( SqlException sqlex )
21 {
22 // Handle and log exception details
23 // Wrap and propagate the exception
24 throw new TransferException( "Transfer Failure", sqlex );
25 }
26 }
27 }
28 [Transaction(TransactionOption.Required)]
29 public class Credit : ServicedComponent
30 {
31 [AutoComplete]
32 public void CreditAccount( string account, decimal amount )
33 {
34 try
35 {
36 using( SqlConnection conn = new SqlConnection(
37 "Server=(local); Integrated Security=SSPI"; database="SimpleBank") )
38 {
39 SqlCommand cmd = new SqlCommand("Credit", conn );
40 cmd.CommandType = CommandType.StoredProcedure;
41 cmd.Parameters.Add( new SqlParameter("@AccountNo", account) );
42 cmd.Parameters.Add( new SqlParameter("@Amount", amount ));
43 conn.Open();
44 cmd.ExecuteNonQuery();
45 }
46 }
47 }catch( SqlException sqlex ){
48 // Log exception details here
49 throw; // Propagate exception
50 }
51 }
52 [Transaction(TransactionOption.Required)]
53 public class Debit : ServicedComponent
54 {
55 public void DebitAccount( string account, decimal amount )
56 {
57 try
58 {
59 using( SqlConnection conn = new SqlConnection(
60 "Server=(local); Integrated Security=SSPI"; database="SimpleBank") )
61 {
62 SqlCommand cmd = new SqlCommand("Debit", conn );
63 cmd.CommandType = CommandType.StoredProcedure;
64 cmd.Parameters.Add( new SqlParameter("@AccountNo", account) );
65 cmd.Parameters.Add( new SqlParameter("@Amount", amount ));
66 conn.Open();
67 cmd.ExecuteNonQuery();
68 }
69 }
70 catch (SqlException sqlex)
71 {
72 // Log exception details here
73 throw; // Propagate exception back to caller
74 }
75 }
76 }
