Microsoft Data Access Application Block Quick Code Snippet

I was just testing through the Microsoft Data Access Application Block 5.0 which is part of Enterprise Library 5.0.

Just thought of sharing the code.

Libraries Needed (Add reference to the following libraries from Enterprise Library 5.0 or 4.x)

Microsoft.Practices.EnterpriseLibrary.Common.dll
Microsoft.Practices.EnterpriseLibrary.Data.dll

Here’s the connection string to be declared

Connection String
[code lang=”xml”]

<add name="NorthwindConnectionString" connectionString="server=(local)sqlexpress;database=Northwind;User ID=nw_user;Password=developer;Application Name=Northwind" providerName="System.Data.SqlClient"/>

[/code]

C# Source Code
[code lang=”c-sharp”]

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

/* Microsoft Data Accesss Application Block Namespace */
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace WebAppPro1.EntLib
{
/// <summary>
///
/// </summary>
public class DataAccessBlockDemo
{
const string SqlQueryEmployeeGetDetails = "SELECT Distinct FirstName, LastName, JobTitle FROM Employee ";

private const string SqlQueryEmployeeUpdate =
"Update Employee set JobTitle=’Sr.Manager’ where EmployeeID = @EmployeeID ";

private const string SqlQueryEmployeeSalaryUpdate =
"Update [Employee_Salary] set Grade = ‘L3’, SalPkg = ‘25000.00’ where EmployeeID = @EmployeeID";

private const string ConnStringName = "NorthwindConnectionString";

/// <summary>
/// Employees the get details data set.
/// </summary>
/// <returns></returns>
public DataSet EmployeeGetDetailsDataSet()
{
DataSet returnSet = new DataSet();
Database database;

try
{

//Calls the DatabaseFactory.CreateDatabase with the connection string name for the DataBase
//connection specified in the *.config file.

database = DatabaseFactory.CreateDatabase(ConnStringName);

DbCommand dbCommand = database.GetSqlStringCommand(SqlQueryEmployeeGetDetails);

returnSet = database.ExecuteDataSet(dbCommand);

}
catch (Exception exp)
{
return null; //either return null to indicate a null dataset or rethrow exception.
//throw;
}
finally
{
database = null; //This is not necessary actually. Because DAAB manages the connection destroying and establishing etc.
}

return returnSet;
}

/// <summary>
/// Employees the get details sp.
/// </summary>
/// <returns></returns>
public DataSet EmployeeGetDetailsSp()
{
return EmployeeGetDetailsSp(0);
}

/// <summary>
/// Employees the get details sp.
/// </summary>
/// <param name="empId">The emp id.</param>
/// <returns></returns>
public DataSet EmployeeGetDetailsSp(int empId)
{
DataSet returnSet = new DataSet();

try
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand command = db.GetStoredProcCommand("GetEmployeeDetail");

// Define the input parameter for SP with AddInParameter method
db.AddInParameter(command, "@EmployeeID", DbType.Int32, empId); //if empid <= 0, returns all employee details.

// You can Specify output Paramameters like below.
//db.AddOutParameter(_command, "@TotalLeaves", DbType.String, 50);

/* If the stored procedure return any value(single valued), with AddParameter method & ParameterDirection.ReturnValue
*
db.AddParameter(_command, "@ReturnValue", DbType.Int32,
ParameterDirection.ReturnValue, "@ReturnValue", DataRowVersion.Default, null);
*
*/

returnSet = db.ExecuteDataSet(command);

/* will be null if no match found */

//return returnSet;

}
catch (Exception exp)
{
returnSet = null;
}

return returnSet;
}

/// <summary>
/// Updates the employee details transacted.
/// </summary>
public void UpdateEmployeeDetailsTransacted()
{
Database database = DatabaseFactory.CreateDatabase();

//comm
DbCommand cmdUpdateEmployee = database.GetSqlStringCommand(SqlQueryEmployeeUpdate);

DbCommand cmdUpdateEmployeeSalary = database.GetSqlStringCommand(SqlQueryEmployeeSalaryUpdate);

using (DbConnection dataConnection = database.CreateConnection())
{
dataConnection.Open();

/* Start a transaction by calling BeginTransaction method on the
* connection object created above*/

DbTransaction dbTransaction = dataConnection.BeginTransaction();

try
{
//Calling Updating Employee Designation. If designation update is successful, we will update his salary
database.ExecuteNonQuery(cmdUpdateEmployee, dbTransaction);

//Calling Updating Employee Salary Package
database.ExecuteNonQuery(cmdUpdateEmployeeSalary, dbTransaction);

//both update was successful so committing the transaction.
dbTransaction.Commit();
}
catch (Exception exp)
{
//Oohh!! Some exception happened lets roll back….
dbTransaction.Rollback();
}

//Hey close me — ( Not necessary some times. "Using" will do it.)
dataConnection.Close();
}
}
}
}

[/code]

Can i tell one benefit of using this code. You can just simply change to a new data source (stored procedure supprt i am not sure, cross database does it supports? need to test). But normal text query i have tested in MS ACCESS and Oracle Client setting like below connection strings.

For MS ACCESS DB
[code lang=”xml”]

<add name="NorthwindConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0; User ID=myuser; Data Source=|DataDirectory|northwind.mdb; Password=" providerName="System.Data.OleDb"/>
[/code]

For ORACLE DB
[code lang=”xml”]
<add name="NorthwindConnectionString" connectionString="Data Source=Oracle8i; Integrated Security=SSPI" providerName="System.Data.OracleClient"/>
[/code]

Since Micrsoft DAAB uses the ADO.NET DatabaseFactory classes and we uses DAAB defined classes, it would be easy for us to Migrating to different Data Source.

That’s all for now. I hope this information helps.

Happy Coding !!! Enjoy the Day!!!