Class DataAccess

Namespace

JJMasterData.Commons.Data

Assembly

JJMasterData.Commons.dll

Classes that expose data access services and implements ADO methods.
Provides functionality to developers who write managed code similar to the functionality provided to native component object model (COM)

[PublicAPI]
public class DataAccess

Inheritance

object

DataAccess

Inherited Members

object.ToString()

object.Equals(object)

object.Equals(object, object)

object.ReferenceEquals(object, object)

object.GetHashCode()

object.GetType()

object.MemberwiseClone()

Examples

[!include[Example](../../../doc/Documentation/articles/usages/dataaccess.md)]

Constructors

DataAccess(IOptionsSnapshot<MasterDataCommonsOptions>)

[ActivatorUtilitiesConstructor]
public DataAccess(IOptionsSnapshot<MasterDataCommonsOptions> options)

Parameters

options IOptionsSnapshot<MasterDataCommonsOptions>

DataAccess(string, DataAccessProvider)

public DataAccess(string connectionString, DataAccessProvider dataAccessProvider)

Parameters

connectionString string

dataAccessProvider DataAccessProvider

DataAccess(string, string)

Initialize a with a connectionString and a specific providerName. See also DataAccessProvider.

public DataAccess(string connectionString, string connectionProviderType)

Parameters

connectionString string

Conections string with data source, user etc...

connectionProviderType string

Provider name. For avaliable providers see DataAccessProvider

Properties

TimeOut

Waiting time to execute a command on the database (seconds - default 240s)

[Obsolete("Please use DataAccessCommand.TimeoutSeconds instead.")]
public int TimeOut { get; set; }

Property Value

int

Methods

ExecuteBatch(string, int?)

Executes a database script.

public bool ExecuteBatch(string script, int? timeoutSeconds = null)

Parameters

script string

timeoutSeconds int?

Returns

bool

Returns true if the execution is successful.

Remarks

Lucio Pelinson 18-02-2013

ExecuteBatchAsync(string, CancellationToken)

public Task<bool> ExecuteBatchAsync(string script, CancellationToken cancellationToken = default)

Parameters

script string

cancellationToken CancellationToken

Returns

Task<bool>

GetDataSet(DataAccessCommand)

Returns a DataSet object populated by a DataAccessCommand.

public DataSet GetDataSet(DataAccessCommand command)

Parameters

command DataAccessCommand

Returns

DataSet

GetDataSet(string)

Returns a DataSet object populated by a SQL string. Use a DataAccessCommand if you need parameters.

public DataSet GetDataSet(string sql)

Parameters

sql string

Returns

DataSet

GetDataSetAsync(DataAccessCommand, CancellationToken)

Returns a DataSet object populated by a DataAccessCommand.

public Task<DataSet> GetDataSetAsync(DataAccessCommand command, CancellationToken cancellationToken = default)

Parameters

command DataAccessCommand

cancellationToken CancellationToken

Returns

Task<DataSet>

GetDataSetAsync(string, CancellationToken)

Returns a DataSet object populated by a SQL string. Use a DataAccessCommand if you need parameters.

public Task<DataSet> GetDataSetAsync(string sql, CancellationToken cancellationToken = default)

Parameters

sql string

cancellationToken CancellationToken

Returns

Task<DataSet>

GetDataTable(DataAccessCommand)

Returns a DataTable object populated by a DataAccessCommand.

public DataTable GetDataTable(DataAccessCommand command)

Parameters

command DataAccessCommand

Returns

DataTable

GetDataTable(ref DbConnection, string)

Returns a DataTable object populated from a sql.

[Obsolete("Inject your SQL connection instead.")]
public DataTable GetDataTable(ref DbConnection sqlConn, string sql)

Parameters

sqlConn DbConnection

Open Connection

sql string

Script sql, never use with concat parameters

Returns

DataTable

GetDataTable(string)

Returns a DataTable object populated by a SQL string. Use a DataAccessCommand if you need parameters.

public DataTable GetDataTable(string sql)

Parameters

sql string

Returns

DataTable

GetDataTableAsync(DataAccessCommand, CancellationToken)

Returns a DataTable object populated by a DataAccessCommand.

public Task<DataTable> GetDataTableAsync(DataAccessCommand command, CancellationToken cancellationToken = default)

Parameters

command DataAccessCommand

cancellationToken CancellationToken

Returns

Task<DataTable>

GetDataTableAsync(string, CancellationToken)

Returns a DataTable object populated by a SQL string. Use a DataAccessCommand if you need parameters.

public Task<DataTable> GetDataTableAsync(string sql, CancellationToken cancellationToken = default)

Parameters

sql string

cancellationToken CancellationToken

Returns

Task<DataTable>

GetDictionary(DataAccessCommand)

Retrieves the records of the sql statement in a Dictionary object. [key(database field), value(value stored in database)]

public Dictionary<string, object?>? GetDictionary(DataAccessCommand command)

Parameters

command DataAccessCommand

Command

Returns

Dictionary<string, object>

Return a Dictionary Object. If no record is found it returns null.

GetDictionary(string)

public Dictionary<string, object?>? GetDictionary(string sql)

Parameters

sql string

Returns

Dictionary<string, object>

GetDictionaryAsync(DataAccessCommand, CancellationToken)

public Task<Dictionary<string, object?>> GetDictionaryAsync(DataAccessCommand command, CancellationToken cancellationToken = default)

Parameters

command DataAccessCommand

cancellationToken CancellationToken

Returns

Task<Dictionary<string, object>>

GetDictionaryAsync(string, CancellationToken)

Retrieves the first record of the sql statement in a Hashtable object. [key(database field), value(value stored in database)]
Never concat string to SQL, please see DataAccessCommand

public Task<Dictionary<string, object?>> GetDictionaryAsync(string sql, CancellationToken cancellationToken = default)

Parameters

sql string

cancellationToken CancellationToken

Returns

Task<Dictionary<string, object>>

Return a Hashtable Object. If no record is found it returns null.

GetDictionaryList(DataAccessCommand)

public List<Dictionary<string, object?>> GetDictionaryList(DataAccessCommand command)

Parameters

command DataAccessCommand

Returns

List<Dictionary<string, object>>

GetDictionaryListAsync(DataAccessCommand, CancellationToken)

public Task<List<Dictionary<string, object?>>> GetDictionaryListAsync(DataAccessCommand command, CancellationToken cancellationToken = default)

Parameters

command DataAccessCommand

cancellationToken CancellationToken

Returns

Task<List<Dictionary<string, object>>>

GetHashtable(DataAccessCommand)

Retrieves the first record of the sql statement in a Hashtable object. [key(database field), value(value stored in database)]

public Hashtable? GetHashtable(DataAccessCommand command)

Parameters

command DataAccessCommand

Command

Returns

Hashtable

Return a Hashtable Object. If no record is found it returns null.

GetHashtable(string)

Retrieves the first record of the sql statement in a Hashtable object. [key(database field), value(value stored in database)]
Never concat string to SQL, please see DataAccessCommand

public Hashtable? GetHashtable(string sql)

Parameters

sql string

Returns

Hashtable

Return a Hashtable Object. If no record is found it returns null.

GetHashtableAsync(DataAccessCommand, CancellationToken)

public Task<Hashtable?> GetHashtableAsync(DataAccessCommand command, CancellationToken cancellationToken = default)

Parameters

command DataAccessCommand

cancellationToken CancellationToken

Returns

Task<Hashtable>

GetResult(DataAccessCommand)

ExecuteScalar command and returns the first column of the first row in the result set returned by the query. All other columns and rows are ignored.

public object? GetResult(DataAccessCommand command)

Parameters

command DataAccessCommand

Returns

object

GetResult(DataAccessCommand, ref DbConnection, ref DbTransaction)

ExecuteScalar command and returns the first column of the first row in the result set returned by the query. All other columns and rows are ignored.

public object? GetResult(DataAccessCommand command, ref DbConnection sqlConn, ref DbTransaction trans)

Parameters

command DataAccessCommand

Command

sqlConn DbConnection

Open Connection

trans DbTransaction

Transactions with Connection

Returns

object

Returns a DataTable object populated by a DataAccessCommand. This method uses a DbConnection by ref.

GetResult(string)

ExecuteScalar command and returns the first column of the first row in the result set returned by the query. All other columns and rows are ignored.

public object? GetResult(string sql)

Parameters

sql string

Returns

object

Remarks

To execute command with parameters and prevent SQL injection, please use the DataAccessCommand overload.

GetResultAsync(DataAccessCommand, CancellationToken)

ExecuteScalar command and returns the first column of the first row in the result set returned by the query. All other columns and rows are ignored.

public Task<object?> GetResultAsync(DataAccessCommand command, CancellationToken cancellationToken = default)

Parameters

command DataAccessCommand

cancellationToken CancellationToken

Returns

Task<object>

GetResultAsync(string, CancellationToken)

ExecuteScalar command and returns the first column of the first row in the result set returned by the query. All other columns and rows are ignored.

public Task<object?> GetResultAsync(string sql, CancellationToken cancellationToken = default)

Parameters

sql string

cancellationToken CancellationToken

Returns

Task<object>

Remarks

To execute command with parameters and prevent SQL injection, please use the DataAccessCommand overload.

SetCommand(DataAccessCommand)

ExecuteNonQuery command in the database and return the number of affected records.

public int SetCommand(DataAccessCommand command)

Parameters

command DataAccessCommand

Returns

int

SetCommand(DataAccessCommand, ref DbConnection, ref DbTransaction)

Execute the command in the database and return the number of affected records.

public int SetCommand(DataAccessCommand command, ref DbConnection sqlConn, ref DbTransaction trans)

Parameters

command DataAccessCommand

Command

sqlConn DbConnection

Open Connection

trans DbTransaction

Transactions with Connection

Returns

int

Returns a DataTable object populated by a DataAccessCommand. This method uses a DbConnection and a DbTransaction by ref.

SetCommand(IEnumerable<DataAccessCommand>)

Runs one or more commands on the database with transactions.

public int SetCommand(IEnumerable<DataAccessCommand> commands)

Parameters

commands IEnumerable<DataAccessCommand>

Returns

int

Returns the number of affected records.

Remarks

Author: Lucio Pelinson 14-04-2012

SetCommand(IEnumerable<string>)

Runs one or more commands on the database with transactions.

public int SetCommand(IEnumerable<string> sqlList)

Parameters

sqlList IEnumerable<string>

Returns

int

Returns the number of affected records.

Remarks

Author: Lucio Pelinson 14-04-2012

SetCommand(string)

Execute the command in the database and return the number of affected records.

public int SetCommand(string sql)

Parameters

sql string

Returns

int

SetCommandAsync(DataAccessCommand, CancellationToken)

ExecuteNonQuery command in the database and return the number of affected records.

public Task<int> SetCommandAsync(DataAccessCommand command, CancellationToken cancellationToken = default)

Parameters

command DataAccessCommand

cancellationToken CancellationToken

Returns

Task<int>

SetCommandAsync(IEnumerable<string>, CancellationToken)

Runs one or more commands on the database with transactions.

public Task<int> SetCommandAsync(IEnumerable<string> sqlList, CancellationToken cancellationToken = default)

Parameters

sqlList IEnumerable<string>

cancellationToken CancellationToken

Returns

Task<int>

Returns the number of affected records.

Remarks

Author: Lucio Pelinson 14-04-2012

SetCommandAsync(string, CancellationToken)

Execute the command in the database and return the number of affected records.

public Task<int> SetCommandAsync(string sql, CancellationToken cancellationToken = default)

Parameters

sql string

cancellationToken CancellationToken

Returns

Task<int>

SetCommandListAsync(IEnumerable<DataAccessCommand>, CancellationToken)

ExecuteNonQuery command in the database and return the number of affected records.

public Task<int> SetCommandListAsync(IEnumerable<DataAccessCommand> commands, CancellationToken cancellationToken = default)

Parameters

commands IEnumerable<DataAccessCommand>

cancellationToken CancellationToken

Returns

Task<int>

TryConnection(out string?)

Verify the database connection.

public bool TryConnection(out string? errorMessage)

Parameters

errorMessage string

Returns

bool

Remarks

Author: Lucio Pelinson 28-04-2014

TryConnectionAsync(CancellationToken)

Verify the database connection.

public Task<ConnectionResult> TryConnectionAsync(CancellationToken cancellationToken = default)

Parameters

cancellationToken CancellationToken

Returns

Task<ConnectionResult>

Remarks

Author: Lucio Pelinson 28-04-2014