如何使用 SqlDataAdapter 來檢索多個(gè)行以下代碼闡明了如何使用 SqlDataAdapter 對(duì)象發(fā)出可生成 DataSet 或 DataTable 的命令。它從 SQL Server Northwind 數(shù)據(jù)庫中檢索一組產(chǎn)品類別。 using System.Data;
using System.Data.SqlClient;
public DataTable RetrieveRowsWithDataTable()
{
using ( SqlConnection conn = new SqlConnection(connectionString) )
{
conn.Open();
SqlCommand cmd = new SqlCommand("DATRetrieveProducts", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter( cmd );
DataTable dataTable = new DataTable("Products");
adapter .Fill(dataTable);
return dataTable;
}
}
使用 SqlAdapter 生成 DataSet 或 DataTable
如何使用 SqlDataReader 來檢索多個(gè)行以下代碼片段闡明了可檢索多個(gè)行的 SqlDataReader 方法。 using System.IO;
using System.Data;
using System.Data.SqlClient;
public SqlDataReader RetrieveRowsWithDataReader()
{
SqlConnection conn = new SqlConnection(
"server=(local);Integrated Security=SSPI;database=northwind");
SqlCommand cmd = new SqlCommand("DATRetrieveProducts", conn );
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
// Generate the reader. CommandBehavior.CloseConnection causes the
// the connection to be closed when the reader object is closed
return( cmd.ExecuteReader( CommandBehavior.CloseConnection ) );
}
catch
{
conn.Close();
throw;
}
}
// Display the product list using the console
private void DisplayProducts()
{
SqlDataReader reader = RetrieveRowsWithDataReader();
try
{
while (reader.Read())
{
Console.WriteLine("{0} {1} {2}",
reader.GetInt32(0).ToString(),
reader.GetString(1) );
}
}
finally
{
reader.Close(); // Also closes the connection due to the
// CommandBehavior enum used when generating the reader
}
}
使用 SqlDataReader 檢索行
如何使用 XmlReader 檢索多個(gè)行可以使用 SqlCommand 對(duì)象來生成 XmlReader 對(duì)象，后者可提供對(duì) XML 數(shù)據(jù)的基于流的只進(jìn)訪問。命令（通常為存儲(chǔ)過程）必須產(chǎn)生基于 XML 的結(jié)果集，對(duì)于 SQL Server 2000 而言，該結(jié)果集通常包含一個(gè)帶有有效 FOR XML 子句的 SELECT 語句。以下代碼片段闡明了該方法： public void RetrieveAndDisplayRowsWithXmlReader()
{
using( SqlConnection conn = new SqlConnection(connectionString) )
{;
SqlCommand cmd = new SqlCommand("DATRetrieveProductsXML", conn );
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
XmlTextReader xreader = (XmlTextReader)cmd.ExecuteXmlReader();
while ( xreader.Read() )
{
if ( xreader.Name == "PRODUCTS" )
{
string strOutput = xreader.GetAttribute("ProductID");
strOutput += " ";
strOutput += xreader.GetAttribute("ProductName");
Console.WriteLine( strOutput );
}
}
xreader.Close(); // XmlTextReader does not support IDisposable so it can't be
// used within a using keyword
}
}
上述代碼使用了以下存儲(chǔ)過程： CREATE PROCEDURE DATRetrieveProductsXML AS SELECT * FROM PRODUCTS FOR XML AUTO GO 使用 XmlReader 檢索 XML 數(shù)據(jù)
如何使用存儲(chǔ)過程輸出參數(shù)來檢索單個(gè)行借助于命名的輸出參數(shù)，可以調(diào)用在單個(gè)行內(nèi)返回檢索到的數(shù)據(jù)項(xiàng)的存儲(chǔ)過程。以下代碼片段使用存儲(chǔ)過程來檢索 Northwind 數(shù)據(jù)庫的 Products 表中包含的特定產(chǎn)品的產(chǎn)品名稱和單價(jià)。 void GetProductDetails( int ProductID,
out string ProductName, out decimal UnitPrice )
{
using( SqlConnection conn = new SqlConnection(
"server=(local);Integrated Security=SSPI;database=Northwind") )
{
// Set up the command object used to execute the stored proc
SqlCommand cmd = new SqlCommand( "DATGetProductDetailsSPOutput", conn )
cmd.CommandType = CommandType.StoredProcedure;
// Establish stored proc parameters.
// @ProductID int INPUT
// @ProductName nvarchar(40) OUTPUT
// @UnitPrice money OUTPUT
// Must explicitly set the direction of output parameters
SqlParameter paramProdID =
cmd.Parameters.Add( "@ProductID", ProductID );
paramProdID.Direction = ParameterDirection.Input;
SqlParameter paramProdName =
cmd.Parameters.Add( "@ProductName", SqlDbType.VarChar, 40 );
paramProdName.Direction = ParameterDirection.Output;
SqlParameter paramUnitPrice =
cmd.Parameters.Add( "@UnitPrice", SqlDbType.Money );
paramUnitPrice.Direction = ParameterDirection.Output;
conn.Open();
// Use ExecuteNonQuery to run the command.
// Although no rows are returned any mapped output parameters
// (and potentially return values) are populated
cmd.ExecuteNonQuery( );
// Return output parameters from stored proc
ProductName = paramProdName.Value.ToString();
UnitPrice = (decimal)paramUnitPrice.Value;
}
}
使用存儲(chǔ)過程輸出參數(shù)來檢索單個(gè)行
上述代碼片段調(diào)用了以下存儲(chǔ)過程。 CREATE PROCEDURE DATGetProductDetailsSPOutput @ProductID int, @ProductName nvarchar(40) OUTPUT, @UnitPrice money OUTPUT AS SELECT @ProductName = ProductName, @UnitPrice = UnitPrice FROM Products WHERE ProductID = @ProductID GO 如何使用 SqlDataReader 來檢索單個(gè)行可以使用 SqlDataReader 對(duì)象來檢索單個(gè)行，尤其是可以從返回的數(shù)據(jù)流中檢索需要的列值。以下代碼片段對(duì)此進(jìn)行了說明。 void GetProductDetailsUsingReader( int ProductID,
out string ProductName, out decimal UnitPrice )
{
using( SqlConnection conn = new SqlConnection(
"server=(local);Integrated Security=SSPI;database=Northwind") )
{
// Set up the command object used to execute the stored proc
SqlCommand cmd = new SqlCommand( "DATGetProductDetailsReader", conn );
cmd.CommandType = CommandType.StoredProcedure;
// Establish stored proc parameters.
// @ProductID int INPUT
SqlParameter paramProdID = cmd.Parameters.Add( "@ProductID", ProductID );
paramProdID.Direction = ParameterDirection.Input;
conn.Open();
using( SqlDataReader reader = cmd.ExecuteReader() )
{
if( reader.Read() ) // Advance to the one and only row
{
// Return output parameters from returned data stream
ProductName = reader.GetString(0);
UnitPrice = reader.GetDecimal(1);
}
}
}
}
使用 SqlDataReader 對(duì)象來返回單個(gè)行
上述代碼片段調(diào)用了以下存儲(chǔ)過程。 CREATE PROCEDURE DATGetProductDetailsReader @ProductID int AS SELECT ProductName, UnitPrice FROM Products WHERE ProductID = @ProductID GO 如何使用 ExecuteScalar 來檢索單個(gè)項(xiàng)ExecuteScalar 方法專門適用于僅返回單個(gè)值的查詢。如果查詢返回多個(gè)列和/或行，ExecuteScalar 將只返回第一行的第一列。 以下代碼說明了如何查找與特定產(chǎn)品 ID 相對(duì)應(yīng)的產(chǎn)品名稱： void GetProductNameExecuteScalar( int ProductID, out string ProductName )
{
using( SqlConnection conn = new SqlConnection(
"server=(local);Integrated Security=SSPI;database=northwind") )
{
SqlCommand cmd = new SqlCommand("LookupProductNameScalar", conn );
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ProductID", ProductID );
conn.Open();
ProductName = (string)cmd.ExecuteScalar();
}
}
使用 ExecuteScalar 來檢索單個(gè)項(xiàng)
上述代碼使用了以下存儲(chǔ)過程： CREATE PROCEDURE LookupProductNameScalar @ProductID int AS SELECT TOP 1 ProductName FROM Products WHERE ProductID = @ProductID GO 如何使用存儲(chǔ)過程輸出或返回參數(shù)來檢索單個(gè)項(xiàng)可以使用存儲(chǔ)過程輸出或返回參數(shù)來查找單個(gè)值。以下代碼闡明了輸出參數(shù)的用法： void GetProductNameUsingSPOutput( int ProductID, out string ProductName )
{
using( SqlConnection conn = new SqlConnection(
"server=(local);Integrated Security=SSPI;database=northwind") )
{
SqlCommand cmd = new SqlCommand("LookupProductNameSPOutput", conn );
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter paramProdID = cmd.Parameters.Add("@ProductID", ProductID );
ParamProdID.Direction = ParameterDirection.Input;
SqlParameter paramPN =
cmd.Parameters.Add("@ProductName", SqlDbType.VarChar, 40 );
paramPN.Direction = ParameterDirection.Output;
conn.Open();
cmd.ExecuteNonQuery();
ProductName = paramPN.Value.ToString();
}
}
使用存儲(chǔ)過程輸出參數(shù)來檢索單個(gè)值
上述代碼使用了以下存儲(chǔ)過程。 CREATE PROCEDURE LookupProductNameSPOutput @ProductID int, @ProductName nvarchar(40) OUTPUT AS SELECT @ProductName = ProductName FROM Products WHERE ProductID = @ProductID GO 以下代碼闡明了如何使用返回值來指明是否存在特定行。從編碼角度來看，這類似于使用存儲(chǔ)過程輸出參數(shù)，不同之處在于必須將 SqlParameter 方向顯式設(shè)置為 ParameterDirection.ReturnValue。 bool CheckProduct( int ProductID )
{
using( SqlConnection conn = new SqlConnection(
"server=(local);Integrated Security=SSPI;database=northwind") )
{
SqlCommand cmd = new SqlCommand("CheckProductSP", conn );
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ProductID", ProductID );
SqlParameter paramRet =
cmd.Parameters.Add("@ProductExists", SqlDbType.Int );
paramRet.Direction = ParameterDirection.ReturnValue;
conn.Open();
cmd.ExecuteNonQuery();
}
return (int)paramRet.Value == 1;
}
通過使用存儲(chǔ)過程返回值來檢查是否存在特定行
上述代碼使用了以下存儲(chǔ)過程。 CREATE PROCEDURE CheckProductSP @ProductID int AS IF EXISTS( SELECT ProductID FROM Products WHERE ProductID = @ProductID ) return 1 ELSE return 0 GO 如何使用 SqlDataReader 來檢索單個(gè)項(xiàng)可以使用 SqlDataReader 對(duì)象并通過調(diào)用命令對(duì)象的 ExecuteReader 方法來獲取單個(gè)輸出值。這要求編寫稍微多一點(diǎn)的代碼，因?yàn)楸仨氄{(diào)用 SqlDataReader Read 方法，然后通過該讀取器的訪問器方法之一來檢索需要的值。以下代碼闡明了 SqlDataReader 對(duì)象的用法。 bool CheckProductWithReader( int ProductID )
{
using( SqlConnection conn = new SqlConnection(
"server=(local);Integrated Security=SSPI;database=northwind") )
{
SqlCommand cmd = new SqlCommand("CheckProductExistsWithCount", conn );
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ProductID", ProductID );
cmd.Parameters["@ProductID"].Direction = ParameterDirection.Input;
conn.Open();
using( SqlDataReader reader = cmd.ExecuteReader(
CommandBehavior.SingleResult ) )
{
if( reader.Read() )
{
return (reader.GetInt32(0) > 0);
}
return false;
}
}
上述代碼采用了以下存儲(chǔ)過程。 CREATE PROCEDURE CheckProductExistsWithCount @ProductID int AS SELECT COUNT(*) FROM Products WHERE ProductID = @ProductID GO 如何編寫 ADO.NET 手動(dòng)事務(wù)處理代碼以下代碼顯示了如何充分利用 SQL Server .NET 數(shù)據(jù)提供程序所提供的事務(wù)處理支持，通過事務(wù)來保護(hù)資金轉(zhuǎn)帳操作。該操作在同一數(shù)據(jù)庫中的兩個(gè)帳戶之間轉(zhuǎn)移資金。 public void TransferMoney( string toAccount, string fromAccount, decimal amount )
{
using ( SqlConnection conn = new SqlConnection(
"server=(local);Integrated Security=SSPI;database=SimpleBank" ) )
{
SqlCommand cmdCredit = new SqlCommand("Credit", conn );
cmdCredit.CommandType = CommandType.StoredProcedure;
cmdCredit.Parameters.Add( new SqlParameter("@AccountNo", toAccount) );
cmdCredit.Parameters.Add( new SqlParameter("@Amount", amount ));
SqlCommand cmdDebit = new SqlCommand("Debit", conn );
cmdDebit.CommandType = CommandType.StoredProcedure;
cmdDebit.Parameters.Add( new SqlParameter("@AccountNo", fromAccount) );
cmdDebit.Parameters.Add( new SqlParameter("@Amount", amount ));
conn.Open();
// Start a new transaction
using ( SqlTransaction trans = conn.BeginTransaction() )
{
// Associate the two command objects with the same transaction
cmdCredit.Transaction = trans;
cmdDebit.Transaction = trans;
try
{
cmdCredit.ExecuteNonQuery();
cmdDebit.ExecuteNonQuery();
// Both commands (credit and debit) were successful
trans.Commit();
}
catch( Exception ex )
{
// transaction failed
trans.Rollback();
// log exception details . . .
throw ex;
}
}
}
}
如何使用 Transact-SQL 執(zhí)行事務(wù)處理以下存儲(chǔ)過程闡明了如何在 Transact-SQL 存儲(chǔ)過程內(nèi)部執(zhí)行事務(wù)性資金轉(zhuǎn)帳操作。 CREATE PROCEDURE MoneyTransfer
@FromAccount char(20),
@ToAccount char(20),
@Amount money
AS
BEGIN TRANSACTION
-- PERFORM DEBIT OPERATION
UPDATE Accounts
SET Balance = Balance - @Amount
WHERE AccountNumber = @FromAccount
IF @@RowCount = 0
BEGIN
RAISERROR('Invalid From Account Number', 11, 1)
GOTO ABORT
END
DECLARE @Balance money
SELECT @Balance = Balance FROM ACCOUNTS
WHERE AccountNumber = @FromAccount
IF @BALANCE < 0
BEGIN
RAISERROR('Insufficient funds', 11, 1)
GOTO ABORT
END
-- PERFORM CREDIT OPERATION
UPDATE Accounts
SET Balance = Balance + @Amount
WHERE AccountNumber = @ToAccount
IF @@RowCount = 0
BEGIN
RAISERROR('Invalid To Account Number', 11, 1)
GOTO ABORT
END
COMMIT TRANSACTION
RETURN 0
ABORT:
ROLLBACK TRANSACTION
GO
該存儲(chǔ)過程使用 BEGIN TRANSACTION、COMMIT TRANSACTION 和 ROLLBACK TRANSACTION 語句來手動(dòng)控制該事務(wù)。 如何編寫事務(wù)性 .NET 類以下示例代碼顯示了三個(gè)服務(wù)性 .NET 托管類，這些類經(jīng)過配置以執(zhí)行自動(dòng)事務(wù)處理。每個(gè)類都使用 Transaction 屬性進(jìn)行了批注，該屬性的值確定是否應(yīng)該啟動(dòng)新的事務(wù)流，或者該對(duì)象是否應(yīng)該共享其直接調(diào)用方的事務(wù)流。這些組件協(xié)同工作來執(zhí)行銀行資金轉(zhuǎn)帳任務(wù)。Transfer 類被使用 RequiresNew 事務(wù)屬性進(jìn)行了配置，而 Debit 和 Credit 被使用 Required 進(jìn)行了配置。結(jié)果，所有這三個(gè)對(duì)象在運(yùn)行時(shí)都將共享同一事務(wù)。 using System;
using System.EnterpriseServices;
[Transaction(TransactionOption.RequiresNew)]
public class Transfer : ServicedComponent
{
[AutoComplete]
public void Transfer( string toAccount,
string fromAccount, decimal amount )
{
try
{
// Perform the debit operation
Debit debit = new Debit();
debit.DebitAccount( fromAccount, amount );
// Perform the credit operation
Credit credit = new Credit();
credit.CreditAccount( toAccount, amount );
}
catch( SqlException sqlex )
{
// Handle and log exception details
// Wrap and propagate the exception
throw new TransferException( "Transfer Failure", sqlex );
}
}
}
[Transaction(TransactionOption.Required)]
public class Credit : ServicedComponent
{
[AutoComplete]
public void CreditAccount( string account, decimal amount )
{
try
{
using( SqlConnection conn = new SqlConnection(
"Server=(local); Integrated Security=SSPI"; database="SimpleBank") )
{
SqlCommand cmd = new SqlCommand("Credit", conn );
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( new SqlParameter("@AccountNo", account) );
cmd.Parameters.Add( new SqlParameter("@Amount", amount ));
conn.Open();
cmd.ExecuteNonQuery();
}
}
}catch( SqlException sqlex ){
// Log exception details here
throw; // Propagate exception
}
}
[Transaction(TransactionOption.Required)]
public class Debit : ServicedComponent
{
public void DebitAccount( string account, decimal amount )
{
try
{
using( SqlConnection conn = new SqlConnection(
"Server=(local); Integrated Security=SSPI"; database="SimpleBank") )
{
SqlCommand cmd = new SqlCommand("Debit", conn );
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( new SqlParameter("@AccountNo", account) );
cmd.Parameters.Add( new SqlParameter("@Amount", amount ));
conn.Open();
cmd.ExecuteNonQuery();
}
}
catch (SqlException sqlex)
{
// Log exception details here
throw; // Propagate exception back to caller
}
}
}
|
|
|
