Code Snippet Details

<- Back to List


Language
C#.Net
Description
AccessDataProvider
Code
using System.Data; using System.Data.OleDb; /// <summary> /// Data Provider for Access databases. /// </summary> namespace Alphabet.Framework.DAL { public class AccessDataProvider : IDataProvider { #region Constants private const int CommandTimeout = 120; #endregion #region Member variables private string _ConnectionString; private OleDbConnection _Connection; private OleDbTransaction _Transaction; #endregion #region Constructors public AccessDataProvider(string connectionString) { try { _ConnectionString = connectionString; } catch { throw; } } #endregion #region Public Methods public void OpenConnection() { if (_Connection == null) { _Connection = new OleDbConnection(_ConnectionString); } if (_Connection.State == ConnectionState.Closed) { _Connection.Open(); } } public void CloseConnection() { if (_Connection.State == ConnectionState.Open) { _Connection.Close(); } } public DataTable GetDataTable(string sql) { return GetDataTableUsingDataReader(GetCommand(sql, _Connection)); } /// <summary> /// Call a stored procedure and return a data table. /// </summary> /// <returns>True if successful, false on error.</returns> /// <param name="genericCommand">Generic command to run.</param> /// <remarks></remarks> public DataTable GetDataTable(GenericCommand genericCommand) { return GetDataTableUsingDataReader(GetCommand(genericCommand, _Connection)); } /// <summary> /// Get a single value from a query. /// </summary> /// <param name="genericCommand">Generic command to run.</param> /// <returns>An object of the relevant type for the value.</returns> /// <remarks>Use to get the id of last record inserted in a table or a maximum field value.</remarks> public object GetScalarValue(GenericCommand genericCommand) { object returnValue = null; OleDbCommand command = GetCommand(genericCommand, _Connection); returnValue = command.ExecuteScalar(); return returnValue; } /// <summary> /// Get a single value from a query. /// </summary> /// <param name="sql">SQL command to run.</param> /// <returns>An object of the relevant type for the value.</returns> /// <remarks>Use to get the id of last record inserted in a table or a maximum field value.</remarks> public object GetScalarValue(string sql) { object returnValue = null; OleDbCommand command = GetCommand(sql, _Connection); returnValue = command.ExecuteScalar(); return returnValue; } /// <summary> /// Run a stored procedure. /// </summary> /// <param name="genericCommand">Generic command to run.</param> /// <returns>The number of rows affected</returns> public int ExecuteStoredProcedure(ref GenericCommand genericCommand) { int rowsAffected = 0; OleDbCommand command = GetCommand(genericCommand, _Connection); rowsAffected = command.ExecuteNonQuery(); return rowsAffected; } /// <summary> /// Run a stored procedure. /// </summary> /// <param name="sql">SQL command to run.</param> /// <returns>The number of rows affected</returns> public int ExecuteStoredProcedure(string sql) { int rowsAffected = 0; OleDbCommand command = GetCommand(sql, _Connection); rowsAffected = command.ExecuteNonQuery(); return rowsAffected; } #endregion #region Private methods private OleDbCommand GetCommand(string sql, OleDbConnection _Connection) { try { OleDbCommand command = new OleDbCommand(sql); command.Connection = _Connection; command.CommandType = CommandType.Text; command.CommandTimeout = CommandTimeout; if (_Transaction != null) { command.Transaction = _Transaction; } return command; } catch { throw; } } private OleDbCommand GetCommand(GenericCommand genericCommand, OleDbConnection _Connection) { OleDbCommand command = new OleDbCommand(genericCommand.CommandText); command.Connection = _Connection; command.CommandType = CommandType.StoredProcedure; command.CommandTimeout = CommandTimeout; if (_Transaction != null) { command.Transaction = _Transaction; } foreach (GenericParameter genericParameter in genericCommand.GenericParameterValues()) { OleDbParameter parameter = new OleDbParameter(); parameter.ParameterName = genericParameter.Name; parameter.Value = genericParameter.Value; parameter.Direction = genericParameter.Direction; command.Parameters.Add(parameter); } return command; } /// <remarks>This is the fastest way to get a dataTable.</remarks> private DataTable GetDataTableUsingDataReader(OleDbCommand command) { DataTable dataTable = new DataTable(); using (OleDbDataReader reader = command.ExecuteReader()) { dataTable.Load(reader, LoadOption.OverwriteChanges); } return dataTable; } /// <remarks>This is another way to get a dataTable.</remarks> private DataTable GetDataTableUsingDataAdapter(OleDbCommand command) { DataTable dataTable = new DataTable(); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(); dataAdapter.SelectCommand = command; dataAdapter.Fill(dataTable); return dataTable; } #endregion #region Transaction Procedures public void BeginTransaction() { _Transaction = _Connection.BeginTransaction(IsolationLevel.ReadCommitted); } public void RollbackTransaction() { _Transaction.Rollback(); } public void CommitTransaction() { _Transaction.Commit(); } #endregion } }