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.
Westwind.Utilities.Data.DataAccessBase
public abstract class DataAccessBase : object, IDisposable
Class Members
Member | Description | |
---|---|---|
Constructor |
||
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) |
|
Dispose |
public sealed void Dispose() |
|
DoesTableExist |
Determines whether a table exists public virtual bool DoesTableExist(string tablename, string schema) |
|
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) |
|
ExecuteNonQueryAsync |
Executes a non-query command and returns the affected records public virtual Task public virtual Task |
|
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) |
|
ExecuteScalarAsync |
Executes a command and returns a scalar value from it public virtual Task public virtual Task |
|
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 |
Calls a stored procedure that returns a cursor results The result is returned as an IEnumerable public virtual IEnumerable |
|
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 |
Returns a single entity based on a keyfield and key value public virtual T Find public virtual T Find |
|
FindEx |
Returns an entity that is the first match from a sql statement string. public virtual T FindEx |
|
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 propertiesToSkip) public bool GetEntity(object entity, string sql, Object[] parameters) public virtual bool GetEntity(object entity, string table, string keyField, object keyValue, string propertiesToSkip) |
|
GetInsertEntityCommand |
Gets the DbCommand used to insert an object into the database based on its type information. The properties must match the database structure and you can skip over fields in the propertiesToSkip list. public DbCommand GetInsertEntityCommand(object entity, string table, string propertiesToSkip) |
|
GetUpdateEntityCommand |
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. Note this method will not save if the record doesn't already exist in the db. public virtual DbCommand GetUpdateEntityCommand(object entity, string table, string keyField, string propertiesToSkip) public virtual DbCommand GetUpdateEntityCommand(object entity, string table, string keyField, string propertiesToSkip, string fieldsToUpdate) |
|
InsertEntity |
Inserts an object into the database based on its type information. The properties must match the database structure and you can skip over fields in the propertiesToSkip list. public object InsertEntity(object entity, string table, string propertiesToSkip, bool returnIdentityKey) |
|
InsertEntityAsync |
Inserts an object into the database based on its type information. The properties must match the database structure and you can skip over fields in the propertiesToSkip list. public Task |
|
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 |
Executes a SQL statement and creates an object list using optimized Reflection. Not very efficient but provides an easy way to retrieve an object list from query. public virtual IEnumerable public virtual IEnumerable |
|
QueryList |
Returns list of objects from a query. public virtual List |
|
QueryListWithExclusions |
Returns list of objects from a query. public virtual List |
|
QueryWithExclusions |
Executes a SQL statement and creates an object list using Reflection. Not very efficient but provides an easy way to retrieve object lists from queries public virtual IEnumerable public virtual IEnumerable |
|
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. If ContinueOnError is false, operations are run inside of a transaction and changes are rolled back. If true commands are accepted even if failures occur and are not rolled back. 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 propertiesToSkip) |
|
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. Note this method will not save if the record doesn't already exist in the db. public virtual bool UpdateEntity(object entity, string table, string keyField, string propertiesToSkip) public virtual bool UpdateEntity(object entity, string table, string keyField, string propertiesToSkip, string fieldsToUpdate) |
|
Connection |
The SQL Connection object used for connections |
|
ConnectionString |
ConnectionString for the data access component |
|
ErrorException |
||
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 |
|
LastSql |
Holds the last SQL string executed |
|
LeftFieldBracket |
Character used for the left bracket on field names. Can be empty or null to use none |
|
ParameterPrefix |
The prefix used by the provider |
|
RightFieldBracket |
Character used for the right bracket on field names. Can be empty or null to use none |
|
ThrowExceptions |
||
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 |
|
UsePositionalParameters |
Determines whether parameters are positional or named. Positional parameters are added without adding the name using just the ParameterPrefix |
Requirements
Namespace: Westwind.Utilities.DataAssembly: westwind.utilities.dll
© West Wind Technologies, 1996-2023 • Updated: 07/15/20
Comment or report problem with topic