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)

public class DataAccess

Inheritance

object

DataAccess

Inherited Members

object.Equals(object)

object.Equals(object, object)

object.GetHashCode()

object.GetType()

object.MemberwiseClone()

object.ReferenceEquals(object, object)

object.ToString()

Examples

By default DataAccess recover connection string from appsettings.json with name ConnectionString, but you can pass a custom string connection in the constructor.

How use?

Example to return a DataTable from a DataBase:

var dataAccess = new DataAccess();
var cmd = new DataAccessCommand();
cmd.Sql = "select id, name from table1 where group = @group";
cmd.Paramneters.Add(new DataAccessCommand("@group", "G1"));
 
DataTable dt = await dataAccess.GetDataTableAsync(cmd);

How to execute a sequence of commands?

This example shows how to execute a sequence of commands:

   class TestClass  
   { 
       var dataAccess = new DataAccess())
        try
        {
            var commands = new List<DataAccessCommand>
            {
                new("update table1 set ..."),
                new("update table2 set ..."),
            };
            await dataAccess.SetCommandAsync(commands);
        }
        catch (Exception ex)
        {
            //Handle your exception
        }
   }

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

ConnectionProvider

Database Connection Provider;

public DataAccessProvider ConnectionProvider { get; set; }

Property Value

DataAccessProvider

Provider Name

Remarks

Author: Lucio Pelinson 14-04-2012

ConnectionString

Database connection string; Default value configured in app.config as "ConnectionString";

public string ConnectionString { get; set; }

Property Value

string

Connection string

Remarks

Author: Lucio Pelinson 14-04-2012

Factory

public DbProviderFactory Factory { get; }

Property Value

DbProviderFactory

TimeOut

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

public int TimeOut { get; set; }

Property Value

int

Methods

ColumnExistsAsync(string, string, CancellationToken)

public Task<bool> ColumnExistsAsync(string tableName, string columnName, CancellationToken cancellationToken = default)

Parameters

tableName string

columnName string

cancellationToken CancellationToken

Returns

Task<bool>

ExecuteBatch(string)

Executes a database script.

public bool ExecuteBatch(string script)

Parameters

script string

Returns

bool

Returns true if the execution is successful.

Remarks

Lucio Pelinson 18-02-2013

ExecuteBatchAsync(string, CancellationToken)

Executes a database script.

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

Parameters

script string

cancellationToken CancellationToken

Returns

Task<bool>

Returns true if the execution is successful.

Remarks

Lucio Pelinson 18-02-2013

GetConnection()

[MustDisposeResource]
public DbConnection GetConnection()

Returns

DbConnection

GetConnectionAsync(CancellationToken)

[MustDisposeResource]
public Task<DbConnection> GetConnectionAsync(CancellationToken cancellationToken = default)

Parameters

cancellationToken CancellationToken

Returns

Task<DbConnection>

GetDataSet(DataAccessCommand)

Returns a DataSet object populated by a DataAccessCommand.

public DataSet GetDataSet(DataAccessCommand cmd)

Parameters

cmd 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)

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

public Task<DataSet> GetDataSetAsync(string sql)

Parameters

sql string

Returns

Task<DataSet>

GetDataTable(DataAccessCommand)

Returns a DataTable object populated by a DataAccessCommand.

public DataTable GetDataTable(DataAccessCommand cmd)

Parameters

cmd DataAccessCommand

Returns

DataTable

GetDataTable(ref DbConnection, string)

Returns a DataTable object populated from a sql.

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 cmd)

Parameters

cmd 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)

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(DataAccessCommand command, CancellationToken cancellationToken = default)

Parameters

command DataAccessCommand

cancellationToken CancellationToken

Returns

Task<Dictionary<string, object>>

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

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 cmd)

Parameters

cmd DataAccessCommand

Returns

List<Dictionary<string, object>>

GetDictionaryListAsync(DataAccessCommand, CancellationToken)

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

Parameters

cmd 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 cmd)

Parameters

cmd 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.

GetModelAsyncEnumerable<T>(DataAccessCommand, JsonSerializerSettings?, CancellationToken)

public IAsyncEnumerable<T>? GetModelAsyncEnumerable<T>(DataAccessCommand cmd, JsonSerializerSettings? serializerSettings = null, CancellationToken cancellationToken = default)

Parameters

cmd DataAccessCommand

serializerSettings JsonSerializerSettings

cancellationToken CancellationToken

Returns

IAsyncEnumerable<T>

Type Parameters

T

GetModelAsync<T>(DataAccessCommand, JsonSerializerSettings?, CancellationToken)

public Task<T?> GetModelAsync<T>(DataAccessCommand cmd, JsonSerializerSettings? serializerSettings = null, CancellationToken cancellationToken = default)

Parameters

cmd DataAccessCommand

serializerSettings JsonSerializerSettings

cancellationToken CancellationToken

Returns

Task<T>

Type Parameters

T

GetModelList<T>(DataAccessCommand, JsonSerializerSettings?)

public IList<T>? GetModelList<T>(DataAccessCommand cmd, JsonSerializerSettings? serializerSettings = null)

Parameters

cmd DataAccessCommand

serializerSettings JsonSerializerSettings

Returns

IList<T>

Type Parameters

T

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 cmd)

Parameters

cmd 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 cmd, ref DbConnection sqlConn, ref DbTransaction trans)

Parameters

cmd 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 cmd, CancellationToken cancellationToken = default)

Parameters

cmd 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 cmd)

Parameters

cmd 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 cmd, ref DbConnection sqlConn, ref DbTransaction trans)

Parameters

cmd 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 cmd, CancellationToken cancellationToken = default)

Parameters

cmd 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>

TableExists(string)

Check if table exists in the database

public bool TableExists(string tableName)

Parameters

tableName string

Returns

bool

TableExistsAsync(string, CancellationToken)

Check if table exists in the database

public Task<bool> TableExistsAsync(string tableName, CancellationToken cancellationToken = default)

Parameters

tableName string

cancellationToken CancellationToken

Returns

Task<bool>

TryConnection(out string?)

Verify the database connection

public bool TryConnection(out string? errorMessage)

Parameters

errorMessage string

Returns

bool

True if the connection is successful.

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>

True if the connection is successful.

Remarks

Author: Lucio Pelinson 28-04-2014