Now that you have configured the DAAB, you can build applications on top of this DAAB.
Establish the Database Connection
using
System;
using
System.Collections.Generic;
using
System.Text;
using
Microsoft.Practices.EnterpriseLibrary.Data;
using
System.Data;
using
System.Globalization;
namespace
DAAB_Test_App_1
{
class
Program
{
static
void
Main(
string
[] args)
{
Database database = DatabaseFactory.CreateDatabase
(
"MyOracle"
);
/*
* At this point, we have established the database connection
* configured above, and we can start making
calls into the DAAB
* using the following code samples adapted from Data Access
* Quick Start installed with Enterprise Library.
*/
}
}
}
Use a DataSet to Retrieve Multiple Rows
/*
* Stored Procedure DDL for sample below:
*
* CREATE Procedure GetProductsByCategory
* (
* @CategoryID int
* )
* AS
* SELECT ProductID, ProductName, CategoryID, UnitPrice,
* LastUpdate
* FROM Products
* WHERE CategoryID = @CategoryID
*
*/
string
sqlCommand =
"GetProductsByCategory"
;
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand,
"CategoryID"
, DbType.Int32, Category);
// DataSet that will hold the returned results.
DataSet productDataSet =
null
;
productDataSet = db.ExecuteDataSet(dbCommand);
Use a DbDataReader to Retrieve Multiple Rows of Data
string
sqlCommand = "Select CustomerID, Name, Address, City,
Country, PostalCode
" + "
From Customers";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
using
(IDataReader dataReader =db.ExecuteReader(dbCommand))
{ StringBuilder readerData =
new
StringBuilder();
while
(dataReader.Read())
{
// Get the value of Name column in the DbDataReader.
readerData.Append(dataReader[
"Name"
]);
readerData.Append(Environment.NewLine);
}
// Close the reader, which will cause the
connection to be closed
as
well.
if
(dataReader !=
null
)
dataReader.Close();
}
Execute a Command and Access Output Parameters
/*
* Stored Procedure DDL for sample below:
*
* CREATE PROCEDURE GetProductDetails
* @ProductID int,
* @ProductName nvarchar(40) OUTPUT,
* @UnitPrice money OUTPUT,
* AS
* SELECT @ProductName = ProductName,
* @UnitPrice = UnitPrice,
* FROM Products
* WHERE ProductID = @ProductID
*
*/
int
productID = 1;
string
sqlCommand =
"GetProductDetails"
;
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand,
"ProductID"
, DbType.Int32, productID);
// Output parameters specify the size of the return data.
db.AddOutParameter(dbCommand,
"ProductName"
, DbType.String, 50);
db.AddOutParameter(dbCommand,
"UnitPrice"
, DbType.Currency, 8);
db.ExecuteNonQuery(dbCommand);
string
results =
string
.Format(CultureInfo.CurrentCulture,
"{0}, {1}, {2:C} "
,
db.GetParameterValue(dbCommand,
"ProductID"
),
db.GetParameterValue(dbCommand,
"ProductName"
),
db.GetParameterValue(dbCommand,
"UnitPrice"
));
Execute a Command and Access a Single-Item Result
/*
* Stored Procedure DDL for sample below:
*
* CREATE PROCEDURE GetProductName
* @ProductID int
* AS
* SELECT ProductName
* FROM Products
* WHERE ProductID = @ProductID
*
*/
int
productID = 1;
string
sqlCommand =
"GetProductName"
;
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, productID);
string
productName = (
string
)db.ExecuteScalar(dbCommand);
Perform Multiple Updates within a Transaction
/*
* Stored Procedure DDL for sample below:
*
* CREATE PROCEDURE credit
* @AccountNo CHAR(20),
* @Amount SMALLMONEY
* AS
* INSERT Credits
* VALUES
* (@AccountNo, @Amount)
* GO
*
* CREATE PROCEDURE debit
* @AccountNo CHAR(20),
* @Amount SMALLMONEY
* AS
* INSERT Debits
* VALUES
* (@AccountNo, @Amount)
* GO
*/
int
sourceAccount = 0073735963;
int
destinationAccount = 15092;
int
transactionAmount = 485;
DbCommand creditCommand = db.GetStoredProcCommand(
"CreditAccount"
);
db.AddInParameter(creditCommand,
"AccountID"
, DbType.Int32,
sourceAccount);
db.AddInParameter(creditCommand,
"Amount"
, DbType.Int32,
transactionAmount);
DbCommand debitCommand = db.GetStoredProcCommand(
"DebitAccount"
);
db.AddInParameter(debitCommand,
"AccountID"
, DbType.Int32,
destinationAccount);
db.AddInParameter(debitCommand,
"Amount"
, DbType.Int32,
transactionAmount);
using
(DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
// Credit the first account.
db.ExecuteNonQuery(creditCommand, transaction);
// Debit the second account.
db.ExecuteNonQuery(debitCommand, transaction);
// Commit the transaction.
transaction.Commit();
}
catch
{
// Roll back the transaction.
transaction.Rollback();
}
connection.Close();
//Enter code to indicate transaction succeeded
}
Update a Database using a Dataset Fetched with Interoperable SQL Leveling Syntax from DataDirect
/*
* CREATE PROCEDURE AddProduct
* (
* @ProductName nvarchar(50),
* @CategoryID int,
* @UnitPrice money
* )
* AS
* INSERT INTO Products (ProductName, CategoryID, UnitPrice)
* VALUES (@ProductName, @CategoryID, @UnitPrice)
*
* SELECT ProductID, ProductName, CategoryID, UnitPrice
* FROM Products
* WHERE ProductID = SCOPE_IDENTITY()
* GO
*
* CREATE PROCEDURE DeleteProduct
* (
* @ProductID int
* )
* AS
*
* DELETE Products
* WHERE ProductID = @ProductID
* GO
*
* CREATE PROCEDURE UpdateProduct
* (
* @ProductID int,
* @ProductName nvarchar(50),
* @LastUpdate datetime
* )
* AS
*
* UPDATE Products
* SET ProductName = @ProductName
* WHERE ProductID = @ProductID AND LastUpdate = @LastUpdate
*
* IF @@ROWCOUNT > 0
* -- This statement is used to update the DataSet if
changes are done on the updated record
(identities, timestamps or triggers )
* SELECT ProductID, ProductName, CategoryID, UnitPrice
* FROM Products
* WHERE ProductID = @ProductID
* GO
*
*/
DataSet productsDataSet =
new
DataSet();
//Fetch records where LastUpdate is newer than March 3, 2008
using
interoperable SQL Leveling Syntax that works with
DAABs across multiple databases.
string
sqlCommand = "Select ProductID, ProductName, CategoryID,
UnitPrice, LastUpdate
" +"
From Products where
LastUpdate > {d
' 2008-03-03'
}";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
string
productsTable =
"Products"
;
// Retrieve the initial data.
db.LoadDataSet(dbCommand, productsDataSet, productsTable);
// Get the table that will be modified.
DataTable table = productsDataSet.Tables[productsTable];
// Do some work on the DataSet. In this example,
we'll add a
new
product to existing
DataSet and then update the first fetched row.
DataRow addedRow = table.Rows.Add(
new
object
[]
{ DBNull.Value,
"New product"
, 11, 25 });
table.Rows[0][
"ProductName"
] =
"Modified product"
;
DbCommand insertCommand = db.GetStoredProcCommand(
"AddProduct"
);
db.AddInParameter(insertCommand,
"ProductName"
, DbType.String,
"ProductName"
, DataRowVersion.Current);
db.AddInParameter(insertCommand,
"CategoryID"
, DbType.Int32,
"CategoryID"
, DataRowVersion.Current);
db.AddInParameter(insertCommand,
"UnitPrice"
, DbType.Currency,
"UnitPrice"
, DataRowVersion.Current);
DbCommand deleteCommand = db.GetStoredProcCommand(
"DeleteProduct"
);
db.AddInParameter(deleteCommand,
"ProductID"
, DbType.Int32,
"ProductID"
, DataRowVersion.Current);
DbCommand updateCommand = db.GetStoredProcCommand(
"UpdateProduct"
);
db.AddInParameter(updateCommand,
"ProductID"
, DbType.Int32,
"ProductID"
, DataRowVersion.Current);
db.AddInParameter(updateCommand,
"ProductName"
, DbType.String,
"ProductName"
, DataRowVersion.Current);
db.AddInParameter(updateCommand,
"LastUpdate"
, DbType.DateTime,
"LastUpdate"
, DataRowVersion.Current);
//Persist modified DataSet to table
int
rowsAffected = db.UpdateDataSet(productsDataSet,
"Products"
,
insertCommand, updateCommand, deleteCommand,
Microsoft.Practices.EnterpriseLibrary.Data.UpdateBehavior.Standard);