Class DataAccessBase

Base Data Access Layer (DAL) for ADO.NET SQL operations. Provides easy, single method operations to retrieve DataReader, DataTable, DataSet and Entities, perform non-query operations, call stored procedures.

This abstract class implements most data operations using a configured DbProvider. Subclasses implement specific database providers and override a few methods that might have provider specific SQL Syntax.

System.Object
  Westwind.Utilities.Data.DataAccessBase
public abstract class DataAccessBase : object, IDisposable

Class Members

MemberDescription

dbProvider

The internally used dbProvider

BeginTransaction

Starts a new transaction on this connection/instance

public virtual bool BeginTransaction()

CloseConnection

Closes a connection

public virtual void CloseConnection(DbCommand Command)

public virtual void CloseConnection()

CommitTransaction

Commits all changes to the database and ends the transaction

public virtual bool CommitTransaction()

CreateCommand

Creates a Command object and opens a connection

public virtual DbCommand CreateCommand(string sql,     CommandType commandType,     Object[] parameters)

public virtual DbCommand CreateCommand(string sql,     Object[] parameters)

CreatePagingCommand

Sql 2005 specific semi-generic paging routine

public virtual DbCommand CreatePagingCommand(string sql,     int pageSize,     int page,     string sortOrderFields,     Object[] Parameters)

CreateParameter

Used to create named parameters to pass to commands or the various methods of this class.

public virtual DbParameter CreateParameter(string parameterName,     object value)

public virtual DbParameter CreateParameter(string parameterName,     object value,     ParameterDirection parameterDirection)

public virtual DbParameter CreateParameter(string parameterName,     object value,     int size)

public virtual DbParameter CreateParameter(string parameterName,     object value,     DbType type)

public virtual DbParameter CreateParameter(string parameterName,     object value,     DbType type,     int size)

ExecuteDataSet

Returns a DataSet/DataTable from a Sql Command string passed in.

public virtual DataSet ExecuteDataSet(string Tablename,     DbCommand Command,     Object[] Parameters)

public virtual DataSet ExecuteDataSet(string tablename,     string sql,     Object[] parameters)

public virtual DataSet ExecuteDataSet(DataSet dataSet,     string tableName,     DbCommand command,     Object[] parameters)

public virtual DataSet ExecuteDataSet(DataSet dataSet,     string tablename,     string sql,     Object[] parameters)

ExecuteDynamicDataReader

Executes a Sql statement and returns a dynamic DataReader instance that exposes each field as a property

public virtual object ExecuteDynamicDataReader(string sql,     Object[] parameters)

ExecuteNonQuery

Executes a non-query command and returns the affected records

public virtual int ExecuteNonQuery(DbCommand Command)

public virtual int ExecuteNonQuery(string sql,     Object[] parameters)

ExecuteReader

Executes a SQL Command object and returns a SqlDataReader object

public virtual DbDataReader ExecuteReader(DbCommand command,     Object[] parameters)

public virtual DbDataReader ExecuteReader(string sql,     Object[] parameters)

ExecuteScalar

Executes a command and returns a scalar value from it

public virtual object ExecuteScalar(DbCommand command,     Object[] parameters)

public virtual object ExecuteScalar(string sql,     Object[] parameters)

ExecuteStoredProcedureNonQuery

Executes a stored procedure that doesn't return a result set.

public virtual int ExecuteStoredProcedureNonQuery(string storedProc,     Object[] parameters)

ExecuteStoredProcedureReader

Calls a stored procedure that returns a cursor results The result is returned as a DataReader

public virtual DbDataReader ExecuteStoredProcedureReader(string storedProc,     Object[] parameters)

ExecuteStoredProcedureReader<T>

public virtual IEnumerable ExecuteStoredProcedureReader(string storedProc,     Object[] parameters)

ExecuteTable

Returns a DataTable from a Sql Command string passed in.

public virtual DataTable ExecuteTable(string tablename,     DbCommand command,     Object[] parameters)

public virtual DataTable ExecuteTable(string Tablename,     string Sql,     Object[] Parameters)

Find<T>

Returns a single entity based on a keyfield and key value

public virtual T Find(object keyValue,     string tableName,     string keyField)

public virtual T Find(string sql,     Object[] parameters)

FindEx<T>

Returns an entity that is the first match from a sql statement string.

public virtual T FindEx(string sql,     string fieldsToSkip,     Object[] parameters)

GetConnectionInfo

Figures out the dbProvider and Connection string from a connectionString name in a config file or explicit ConnectionString and provider.

public void GetConnectionInfo(string connectionString,     string providerName)

GetEntity

Generic routine to retrieve an object from a database record The object properties must match the database fields.

public virtual bool GetEntity(object entity,     DbCommand command,     string fieldsToSkip)

public bool GetEntity(object entity,     string sql,     Object[] parameters)

public virtual bool GetEntity(object Entity,     string Table,     string KeyField,     object KeyValue,     string FieldsToSkip)

InsertEntity

public object InsertEntity(object entity,     string table,     string fieldsToSkip,     bool returnIdentityKey)

OpenConnection

Opens a Sql Connection based on the connection string. Called internally but externally accessible. Sets the internal _Connection property.

public virtual bool OpenConnection()

Query<T>

Executes a SQL statement and creates an object list using Reflection.

public virtual IEnumerable Query(string sql,     Object[] parameters)

public virtual IEnumerable Query(DbCommand command,     Object[] parameters)

QueryWithExclusions<T>

Executes a SQL statement and creates an object list using Reflection.

public virtual IEnumerable QueryWithExclusions(string sql,     string propertiesToExclude,     Object[] parameters)

public virtual IEnumerable QueryWithExclusions(DbCommand sqlCommand,     string propertiesToExclude,     Object[] parameters)

RollbackTransaction

Rolls back a transaction

public virtual bool RollbackTransaction()

RunSqlScript

Executes a long SQL script that contains batches (GO commands). This code breaks the script into individual commands and captures all execution errors.

public bool RunSqlScript(string script,     bool continueOnError,     bool scriptIsFile)

SaveEntity

Saves an enity into the database using insert or update as required. Requires a keyfield that exists on both the entity and the database.

public virtual bool SaveEntity(object entity,     string table,     string keyField,     string fieldsToSkip)

UpdateEntity

Updates an entity object that has matching fields in the database for each public property. Kind of a poor man's quick entity update mechanism.

public virtual bool UpdateEntity(object entity,     string table,     string keyField,     string fieldsToSkip)

public virtual bool UpdateEntity(object entity,     string table,     string keyField,     string fieldsToSkip,     string fieldsToUpdate)

Connection

The SQL Connection object used for connections

ConnectionString

ConnectionString for the data access component

ErrorMessage

An error message if a method fails

ErrorNumber

Optional error number returned by failed SQL commands

ExecuteWithSchema

Determines whether extended schema information is returned for queries from the server. Useful if schema needs to be returned as part of DataSet XML creation

ParameterPrefix

The prefix used by the provider

Timeout

The Sql Command execution Timeout in seconds. Set to -1 for whatever the system default is. Set to 0 to never timeout (not recommended).

Transaction

A SQL Transaction object that may be active. You can also set this object explcitly

Requirements

Namespace: Westwind.Utilities.Data
Assembly: westwind.utilities.dll

© West Wind Technologies, 1996-2016 • Updated: 12/12/15
Comment or report problem with topic