Using Data Access Application Block DAAB in SQL Server Part 2 of 2

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
namespace DAL_UsingDAAB
{
    class Program
    {
        static void Main(string[] args)
        {
            DatasetDemo();
        }
        private static void DatasetDemo()
        {
            try
            {
                /*DatabaseFactory.CreateDatabase creates an instance of the database;
                 *first of the two overloaded methods takes no parameter and creates
                 *database instance taking default settings from the confifuration whereas
                 *second one takes configuration key and creates instance on that basis */
                Database _database = DatabaseFactory.CreateDatabase("AdventureWorksConnectionString");
                string sqlCommand = "SELECT TOP 5 FirstName, LastName, JobTitle FROM HumanResources.vEmployee";
                DbCommand dbCommand = _database.GetSqlStringCommand(sqlCommand);
                DataSet _dataSet = _database.ExecuteDataSet(dbCommand);
                /* Iterate through all the rows of the resultset (datatable)*/
                foreach (DataRow _dataRow in _dataSet.Tables[0].Rows)
                {
                    /* Iterate through all the columns of each row*/
                    foreach (DataColumn _dataColumn in _dataSet.Tables[0].Columns)
                    {
                        Console.Write(_dataRow[_dataColumn.ColumnName] + ", ");
                    }
                    Console.WriteLine("");
                }
            }
            catch (Exception exp)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine(exp.Message);
            }
        }
        private static void CallingStoredProcedureDemo()
        {
            try
            {
                Database _database = DatabaseFactory.CreateDatabase();
                DbCommand _command = _database.GetStoredProcCommand("GetEmployeeDetail");
                /* Define the input parameter for SP with AddInParameter method */
                _database.AddInParameter(_command, "@EmployeeID", DbType.Int32, 10);
                /* Define the output parameters for SP with AddOutParameter method */
                _database.AddOutParameter(_command, "@FirstName", DbType.String, 50);
                _database.AddOutParameter(_command, "@MiddleName", DbType.String, 50);
                _database.AddOutParameter(_command, "@LastName", DbType.String, 50);
                /* Define the SP return value with AddParameter method & ParameterDirection.ReturnValue*/
                _database.AddParameter(_command, "@ReturnValue", DbType.Int32,
                    ParameterDirection.ReturnValue, "@ReturnValue", DataRowVersion.Default, null);
                /* Alternatively you can use these lines of code to get return value  */
                //IDbDataParameter myParam = _command.CreateParameter(); 
                //myParam.DbType = DbType.Int32; 
                //myParam.ParameterName = "@RETURN_VALUE"; 
                //myParam.Direction = ParameterDirection.ReturnValue; 
                //_command.Parameters.Add(myParam); 
                _database.ExecuteNonQuery(_command);
                //int retvalue = (int)myParam.Value;
                /* Getting return value after command execution*/
                int retvalue = (int)_database.GetParameterValue(_command, "@ReturnValue");
                if (retvalue == 0)
                {
                    Console.WriteLine("Employe found:");
                    /* Getting values of output parameters after command execution*/
                    Console.WriteLine(String.Format("{0}, {1}, {2}", _database.GetParameterValue(_command, "@FirstName"),
                                _database.GetParameterValue(_command, "MiddleName"), _database.GetParameterValue(_command, "@LastName")));
                }
                else
                {
                    Console.Write("Employe not found.");
                }
            }
            catch (Exception exp)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine(exp.Message);
            }
        }
        private static void LeveragingTransactionDemo()
        {
            Database _database = DatabaseFactory.CreateDatabase();
            DbCommand _cmdAcceptOrder =
                _database.GetSqlStringCommand("Specify your command to insert or update order details");
            DbCommand _cmdConfirmOrder =
                _database.GetSqlStringCommand("Command to send a confirmation to Inventory as well as to customer");
            using (DbConnection _dbConnection = _database.CreateConnection())
            {
                _dbConnection.Open();
                /*Start a transaction by calling BeginTransaction method on the
                 * connection object created above*/
                DbTransaction _dbTransaction = _dbConnection.BeginTransaction();
                try
                {
                    _database.ExecuteNonQuery(_cmdAcceptOrder, _dbTransaction);
                    /*Once order is accepted successfully, send a confirmation 
                     *to Inventory department as well as to customer */
                    _database.ExecuteNonQuery(_cmdConfirmOrder, _dbTransaction);
                    /*Complete the transaction by commiting it*/
                    _dbTransaction.Commit();
                }
                catch (Exception exp)
                {
                    /*Rollback the transaction in case of any exception*/
                    _dbTransaction.Rollback();
                    Console.ForegroundColor = ConsoleColor.Red;
                    Console.WriteLine(exp.Message);
                }
                _dbConnection.Close();
            }
        }
    }
}