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