Methods and Techniques for using ADO. NET

Methods and Techniques for using ADO. NET

The following code illustrates how to use the SqlDataAdapter object to generate a DataSet or able command. It retrieves a group of product categories from the SQL Server Northwind database.

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;

}

}

Use SqlAdapter to generate DataSet or DataTable

1.

Create a SqlCommand object to call the stored procedure and associate it with a SqlConnection object (displayed) or connection string (not displayed.

2.

Create a new SqlDataAdapter object and associate it with the SqlCommand object.

3.

Create a able (or DataSet) object. Use the constructor parameters to name the able.

4.

Call the Fill method of the SqlDataAdapter object and Fill the DataSet or able with the retrieved rows.

How to Use SqlDataReader to retrieve multiple rows

The following code snippet clarifies the SqlDataReader Method for retrieving multiple rows.

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 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

// CommandBehavior enum used when generating the reader

}

}

Use SqlDataReader to retrieve rows

1.

Create a SqlCommand object used to execute the stored procedure and associate it with a SqlConnection object.

2.

Open the connection.

3.

Create a SqlDataReader object by calling the ExecuteReader method of the SqlCommand object.

4.

To Read data from a stream, call the Read method of the SqlDataReader object to retrieve rows and use the typed accessors (such as the GetInt32 and GetString methods) to retrieve column values.

5.

Call the Close method after the reader is used.

How to Use XmlReader to retrieve multiple rows

You can use the SqlCommand object to generate an XmlReader object. The latter provides stream-based inbound access to XML data. A command (usually a stored procedure) must generate an XML-based result set. for SQL Server 2000, this result set usually contains a SELECT statement with a valid FOR XML clause. The following code snippet clarifies this method:

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

}

}

The above Code uses the following stored procedure:

Create procedure DATRetrieveProductsXML

AS

SELECT * FROM PRODUCTS

FOR XML AUTO

GO

Use XmlReader to retrieve XML data

1.

Create a SqlCommand object to call the stored procedure that can generate an XML result set (FOR example, use the for xml clause in a SELECT statement ). Associate the SqlCommand object with a connection.

2.

Call the ExecuteXmlReader method of the SqlCommand object and assign the result to the XmlTextReader object only. When you do not need to perform any XML-based verification on the returned data, this is the fastest type of XmlReader object to be used.

3.

Use the Read method of the XmlTextReader object to Read data.

How to use stored procedure output parameters to retrieve a single row

With the help of named output parameters, you can call the stored procedure to return the retrieved data items in a single row. The following code snippet uses a stored procedure to retrieve the product name and unit price of a specific product contained in the Products table of the Northwind database.

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;

}

}

Use Stored Procedure output parameters to retrieve a single row

1.

Create a SqlCommand object and associate it with a SqlConnection object.

2.

You can call the Add method of the Parameters set of SqlCommand to set the stored procedure Parameters. By default, all parameters are assumed as input parameters. Therefore, you must explicitly set the direction of any output parameter.

Note that a good practice is to explicitly set the direction of all parameters (including input parameters.

3.

Open the connection.

4.

Call the ExecuteNonQuery method of the SqlCommand object. This will fill in the output parameters (and may fill in the returned values ).

5.

You can use the Value attribute to retrieve output parameters from an appropriate SqlParameter object.

6.

Close the connection.

The preceding code snippet calls the following stored procedure.

Create procedure DATGetProductDetailsSPOutput

@ ProductID int,

@ ProductName nvarchar (40) OUTPUT,

@ UnitPrice money OUTPUT

AS

SELECT @ ProductName = ProductName,

@ UnitPrice = UnitPrice

FROM Products

WHERE ProductID = @ ProductID

GO

How to Use SqlDataReader to retrieve a single row

You can use the SqlDataReader object to retrieve a single row, especially the column values required from the returned data stream. The following code snippet describes this.

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 );

}

}

}

}

Use the SqlDataReader object to return a single row

1.

Create a SqlCommand object.

2.

Open the connection.

3.

Call the ExecuteReader method of the SqlDataReader object.

4.

Use the typed accessors (GetString and GetDecimal) of the SqlDataReader object to retrieve output parameters.

The preceding code snippet calls the following stored procedure.

Create procedure DATGetProductDetailsReader

@ ProductID int

AS

SELECT ProductName, UnitPrice FROM Products

WHERE ProductID = @ ProductID

GO

How to Use ExecuteScalar to retrieve a single item

The ExecuteScalar method is applicable to queries that only return a single value. If the query returns multiple columns and/or rows, ExecuteScalar returns only the first column of the first row.

The following code describes how to find the product name corresponding to a specific product ID:

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 ();

}

}

Use ExecuteScalar to retrieve a single item

1.

Create a SqlCommand object to call the stored procedure.

2.

Open the connection.

3.

Call the ExecuteScalar method. Note: This method returns an object type. It contains the value of the first column retrieved and must be converted to an appropriate type.

4.

Close the connection.

The above Code uses the following stored procedure:

Create procedure LookupProductNameScalar

@ ProductID int

AS

Select top 1 ProductName

FROM Products

WHERE ProductID = @ ProductID

GO

How to use stored procedure output or return parameters to retrieve a single item

You can use stored procedure output or return parameters to find a single value. The following code illustrates the usage of output parameters:

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 ();

}

}

Use Stored Procedure output parameters to retrieve a single value

1.

Create a SqlCommand object to call the stored procedure.

2.

Add SqlParameters to the Parameters set of SqlCommand to set any input Parameters and a single output parameter.

3.

Open the connection.

4.

Call the ExecuteNonQuery method of the SqlCommand object.

5.

Close the connection.

6.

Use the Value Attribute of the output SqlParameter to retrieve the output Value.

The above Code uses the following stored procedures.

Create procedure LookupProductNameSPOutput

@ ProductID int,

@ ProductName nvarchar (40) OUTPUT

AS

SELECT @ ProductName = ProductName

FROM Products

WHERE ProductID = @ ProductID

GO

The following code illustrates how to use the return value to specify whether a specific row exists. From the encoding point of view, this is similar to using Stored Procedure output parameters. The difference is that you must explicitly set SqlParameter to 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;

}

Check whether a specific row exists by using the Stored Procedure Return Value

1.

Create a SqlCommand object to call the stored procedure.

2.

Set an input parameter that contains the primary key value of the row to be accessed.

3.

Set a single return value parameter. Add a SqlParameter object to the Parameters set of SqlCommand and set its direction to ParameterDirection. ReturnValue.

4.

Open the connection.

5.

Call the ExecuteNonQuery method of the SqlCommand object.

6.

Close the connection.

7.

You can use the Value Attribute of the returned Value SqlParameter to retrieve the returned Value.

The above Code uses the following stored procedures.

Create procedure CheckProductSP

@ ProductID int

AS

If exists (SELECT ProductID

FROM Products

WHERE ProductID = @ ProductID)

Return 1

ELSE

Return 0

GO

How to Use SqlDataReader to retrieve a single item

You can use the SqlDataReader object and call the ExecuteReader method of the command object to obtain a single output value. This requires writing a little more code, because you must call the SqlDataReader Read method, and then retrieve the required value through one of the reader's accessors. The following code illustrates the usage of the SqlDataReader object.

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;

}

}

The above Code uses the following stored procedures.

Create procedure CheckProductExistsWithCount

@ ProductID int

AS

Select count (*) FROM Products

WHERE ProductID = @ ProductID

GO

How to compile ADO. NET manual transaction processing code

The following code fully utilizes the transaction processing support provided by the SQL Server. NET data provider to protect fund transfer operations. This operation transfers funds between two accounts in the same database.

Public void TransferMoney (string toAccount, string fromAccount, decimal amount)

{

Using (SqlConnection conn = new SqlConnection (

"Server = (local); Integrated Security = SSPI; database = SimpleBank "))

{

SqlCommand charge Credit = new SqlCommand ("Credit", conn );

Your credit. CommandType = CommandType. StoredProcedure;

Your credit. Parameters. Add (new SqlParameter ("@ AccountNo", toAccount ));

Your credit. Parameters. Add (new SqlParameter ("@ Amount", amount ));

SqlCommand includebit = new SqlCommand ("Debit", conn );

Includebit. CommandType = CommandType. StoredProcedure;

Includebit. Parameters. Add (new SqlParameter ("@ AccountNo", fromAccount ));

Includebit. 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

Export credit. Transaction = trans;

Includebit. Transaction = trans;

Try

{

Your credit. ExecuteNonQuery ();

Includebit. ExecuteNonQuery ();

// Both commands (credit and debit) were successful

Trans. Commit ();

}

Catch (Exception ex)

{

// Transaction failed

Trans. Rollback ();

// Log exception details...

Throw ex;

}

}

}

}

How to Use Transact-SQL to execute Transaction Processing

The following stored procedures demonstrate how to perform transactional fund transfers within the Transact-SQL stored procedure.

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

The stored procedure uses the begin transaction, commit transaction, and rollback transaction statements to manually control the TRANSACTION.

How to Write transactional. NET classes

The following sample code shows three service. NET managed classes that are configured to execute automatic transaction processing. Each class is annotated using the Transaction attribute. The value of this attribute determines whether a new Transaction stream should be started, or whether the object should share the Transaction stream of its direct caller. These components work together to perform the bank fund transfer task. The Transfer class is configured using the RequiresNew transaction attribute, while the Debit and Credit classes are configured using Required. As a result, all three objects share the same transaction at runtime.

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

}

}

}