This data access layer is used in turn by other program modules to access and manipulate the data within the data store without having to deal with the complexities inherent in this access.
For example, instead of using commands such as insert, delete, and update to access a specific table in a database, a class and a few stored procedures could be created in the database. The procedures would be called from a method inside the class, which would return an object containing the requested values. Or, the insert, delete and update commands could be executed within simple functions like registeruser or loginuser stored within the data access layer.
Web.config:
<connectionStrings>
<add
name="ConnectionString"
connectionString="Data Source=server;InitialCatalog=TestDatabase;Persist Security Info=True;User ID=sa;Password=sa"
providerName="System.Data.SqlClient" />
</connectionStrings>
Step 1 : Create WebConfiguration.cs under App_Code with the following code:
using System;
using System.Configuration;
/// <summary>
/// Repository for configuration settings
/// </summary>
public static class WebConfiguration
{
// Caches the connection string
private readonly static string dbConnectionString;
// Caches the data provider name
private readonly static string dbProviderName;
// Initialize various properties in the constructor
static WebConfiguration()
{
dbConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
dbProviderName = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;
}
// Returns the connection string for the database
public static string DbConnectionString
{
get
{
return dbConnectionString;
}
}
// Returns the data provider name
public static string DbProviderName
{
get
{
return dbProviderName;
}
}
}
Step 2 : Create Class GenericDataAccess.cs with following code:
using System;
using System.Data;
using System.Data.Common;
using System.Configuration;
/// <summary>
/// Class contains generic data access functionality to be accessed from
/// the business tier
/// </summary>
public class GenericDataAccess
{
// static constructor
static GenericDataAccess()
{
}
// execute a command and return the results as a DataTable object
public DataTable ExecuteSelectCommand(DbCommand command)
{
// The DataTable to be returned
DataTable table;
// Execute the command making sure the connection gets closed in the end
try
{
// Open the data connection
command.Connection.Open();
// Execute the command and save the results in a DataTable
DbDataReader reader = command.ExecuteReader();
table = new DataTable();
table.Load(reader);
// Close the reader
reader.Close();
}
catch (Exception ex)
{
//Utilities.LogError(ex);
throw ex;
}
finally
{
// Close the connection
command.Connection.Close();
}
return table;
}
// execute an update, delete, or insert command
// and return the number of affected rows
public int ExecuteNonQuery(DbCommand command)
{
// The number of affected rows
int affectedRows = -1;
// Execute the command making sure the connection gets closed in the end
try
{
// Open the connection of the command
command.Connection.Open();
// Execute the command and get the number of affected rows
affectedRows = command.ExecuteNonQuery();
}
catch (Exception ex)
{
// Log eventual errors and rethrow them
//Utilities.LogError(ex);
throw ex;
}
finally
{
// Close the connection
command.Connection.Close();
}
// return the number of affected rows
return affectedRows;
}
// execute a select command and return a single result as a string
public string ExecuteScalar(DbCommand command)
{
// The value to be returned
string value = "";
// Execute the command making sure the connection gets closed in the end
try
{
// Open the connection of the command
command.Connection.Open();
// Execute the command and get the number of affected rows
value = command.ExecuteScalar().ToString();
}
catch (Exception ex)
{
//Utilities.LogError(ex);
//throw ex;
}
finally
{
// Close the connection
command.Connection.Close();
}
// return the result
return value;
}
// creates and prepares a new DbCommand object on a new connection
public DbCommand CreateCommand()
{
// Obtain the database provider name
string dataProviderName = WebConfiguration.DbProviderName;
// Obtain the database connection string
string connectionString = WebConfiguration.DbConnectionString;
// Create a new data provider factory
DbProviderFactory factory = DbProviderFactories.GetFactory(dataProviderName);
// Obtain a database specific connection object
DbConnection conn = factory.CreateConnection();
// Set the connection string
conn.ConnectionString = connectionString;
// Create a database specific command object
DbCommand comm = conn.CreateCommand();
// Set the command type to stored procedure
comm.CommandType = CommandType.StoredProcedure;
// Return the initialized command object
return comm;
}
//creates and prepares a new DbCommand object on a new connection(For QueryText)
public DbCommand CreateCommand4QueryText()
{
// Obtain the database provider name
string dataProviderName = WebConfiguration.DbProviderName;
// Obtain the database connection string
string connectionString = WebConfiguration.DbConnectionString;
// Create a new data provider factory
DbProviderFactory factory = DbProviderFactories.GetFactory(dataProviderName);
// Obtain a database specific connection object
DbConnection conn = factory.CreateConnection();
// Set the connection string
conn.ConnectionString = connectionString;
// Create a database specific command object
DbCommand comm = conn.CreateCommand();
// Set the command type to stored procedure
comm.CommandType = CommandType.Text;
// Return the initialized command object
return comm;
}
}
Step 3: Create BAL Folder under App_Code and start creating your BAL Classes & Use it on Presentation layer
using System;
using System.Data.Common;
using PropertyPortal.Common;
using System.Data;
public class BAL_AddAdImage
{
public BAL_AddAdImage()
{
}
public static DataTable getPropertyImageAll(int PropertyID)
{
// get a configured DbCommand object
GenericDataAccess gda = new GenericDataAccess();
DbCommand comm = gda.CreateCommand();
// set the stored procedure name
comm.CommandText = "PropertyDB_GetPropertyImageDetails_All";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@PropertyID";
param.Value = PropertyID;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// return the result table
DataTable table = gda.ExecuteSelectCommand(comm);
return table;
}
public static string GetImagePath(int ImageID)
{
// get a configured DbCommand object
GenericDataAccess gda = new GenericDataAccess();
DbCommand comm = gda.CreateCommand();
// set the stored procedure name
comm.CommandText = "PropertyDB_GetPropertyImagePath";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@ImageID";
param.Value = ImageID;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
string path = gda.ExecuteScalar(comm).ToString();
return path;
}
public static bool Delete(int ImageID)
{
// get a configured DbCommand object
GenericDataAccess gda = new GenericDataAccess();
DbCommand comm = gda.CreateCommand();
// set the stored procedure name
comm.CommandText = "PropertyDB_PropertyImageDetails_Delete";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@ImageID";
param.Value = ImageID;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
int retval = gda.ExecuteNonQuery(comm);
if (retval > 0)
return true;
else
return false;
}
}
