Class SqlStore

Namespace

Tripous.Data

Assembly

Tripous.Data.dll

Sql store. Used in executing SELECT, INSERT, UPDATE, DELETE, etc commands, using a DbConnection.

Many methods use a Params parameter. That Params can be:
a. either a comma separated list of parameters or
b. the Params[0] element, that is the first element in Params, may be

1. a DataRow
2. a generic IDictionary<string, object>
3. or an IList or Array

and in this second case no other Params elements are used

public class SqlStore

Inheritance

object

SqlStore

Inherited Members

object.Equals(object)

object.Equals(object, object)

object.GetHashCode()

object.GetType()

object.MemberwiseClone()

object.ReferenceEquals(object, object)

object.ToString()

Extension Methods

TypeExtensions.InheritsFrom(object, Type)

Constructors

SqlStore(DbConnectionInfo)

Constructor.

public SqlStore(DbConnectionInfo ConnectionInfo)

Parameters

ConnectionInfo DbConnectionInfo

Properties

ConnectionInfo

The connection info used by this instance.

public DbConnectionInfo ConnectionInfo { get; }

Property Value

DbConnectionInfo

Provider

The SqlProvider used by this instance.

public SqlProvider Provider { get; }

Property Value

SqlProvider

Methods

BeginTransactionContext()

Creates a transaction context that owns both the connection and the transaction.

public virtual SqlTransactionContext BeginTransactionContext()

Returns

SqlTransactionContext

CanConnect(bool)

Returns true if this connection info is valid and can connect to a database.

public bool CanConnect(bool ThrowIfNot = false)

Parameters

ThrowIfNot bool

Returns

bool

CreateTable(string)

Creates a new table in the database by executing CommandText. Returns true only if creates the table, false if the table already exists.

The method creates a table generator too, if the database supports generators/sequences.

CommandText should be a CREATE TABLE statement and can contain datatype placeholders. See ReplaceDataTypePlaceholders(string) for details.

public virtual bool CreateTable(string SqlText)

Parameters

SqlText string

Returns

bool

EnsureConnection(string)

Ensures that a connection can be done by opening and closing the connection.

public virtual void EnsureConnection(string ConnectionString)

Parameters

ConnectionString string

ExecSql(Action<DbTransaction>)

It can be used in executin multiple SQL operations inside a single transaction using a callback.

Example

Provider.ExecSql(ConnectionString,
    tr => Provider.ExecSql(tr, Sql1, Params1),
    tr => Provider.ExecSql(tr, Sql2, Params2),
    tr => Provider.ExecSql(tr, Sql3, Params3)
);

public void ExecSql(Action<DbTransaction> Action)

Parameters

Action Action<DbTransaction>

ExecSql(IEnumerable<string>)

Executes a list of executable statements inside a single transaction

public virtual void ExecSql(IEnumerable<string> SqlTextList)

Parameters

SqlTextList IEnumerable<string>

ExecSql(DbTransaction, string)

Executes a single SQL operation inside a transaction.

public int ExecSql(DbTransaction Transaction, string SqlText)

Parameters

Transaction DbTransaction

SqlText string

Returns

int

ExecSql(DbTransaction, string, params object[])

Executes a single SQL operation inside a transaction.

public int ExecSql(DbTransaction Transaction, string SqlText, params object[] Params)

Parameters

Transaction DbTransaction

SqlText string

Params object[]

Returns

int

ExecSql(string)

Executes a SQL statement (INSERT, UPDATE, DELETE, κλπ) and returns the number of rows affected.

public int ExecSql(string SqlText)

Parameters

SqlText string

Returns

int

ExecSql(string, params object[])

Executes a SQL statement (INSERT, UPDATE, DELETE, κλπ) and returns the number of rows affected.

public int ExecSql(string SqlText, params object[] Params)

Parameters

SqlText string

Params object[]

Returns

int

FieldExists(string, string)

Returns true if the FieldName exists in TableName table.

public bool FieldExists(string TableName, string FieldName)

Parameters

TableName string

FieldName string

Returns

bool

GetFieldNames(string)

Returns a string list with the field names of the TableName

public List<string> GetFieldNames(string TableName)

Parameters

TableName string

Returns

List<string>

GetIndexNames()

Returns a string list with the index names in the database

public List<string> GetIndexNames()

Returns

List<string>

GetNativeSchemaFromSelect(string, string)

Gets the native schema of the SqlText SELECT statement..

public DataTable GetNativeSchemaFromSelect(string StatementName, string SqlText)

Parameters

StatementName string

SqlText string

Returns

DataTable

GetNativeSchemaFromTableName(string, string)

Gets the native schema of the TableName table.

public DataTable GetNativeSchemaFromTableName(string StatementName, string TableName)

Parameters

StatementName string

TableName string

Returns

DataTable

GetSchema()

Returns schema information for the data source of this System.Data.Common.DbConnection.

public virtual DataTable GetSchema()

Returns

DataTable

GetSchema(string)

Returns schema information for the data source of this System.Data.Common.DbConnection using the specified string for the schema name.

public virtual DataTable GetSchema(string collectionName)

Parameters

collectionName string

Returns

DataTable

GetSchema(string, string[])

Returns schema information for the data source of this System.Data.Common.DbConnection using the specified string for the schema name and the specified string array for the restriction values.

public virtual DataTable GetSchema(string collectionName, string[] restrictionValues)

Parameters

collectionName string

restrictionValues string[]

Returns

DataTable

GetServerDateTime()

Gets the datetime value from the server.

public DateTime GetServerDateTime()

Returns

DateTime

GetTableNames()

Returns a string list with the table names in the database.

public List<string> GetTableNames()

Returns

List<string>

GetViewNames()

Returns a string list with the view names in the database.

public List<string> GetViewNames()

Returns

List<string>

IndexExists(string)

Returns true if an index with IndexName exists in the database.

public bool IndexExists(string IndexName)

Parameters

IndexName string

Returns

bool

IntegerResult(DbTransaction, string, int, params object[])

Ideal for executing SELECT statements of the type "select count(ID) as COUNT_ID from TABLE_NAME where ID = 1234"

public int IntegerResult(DbTransaction Transaction, string SqlText, int Default, params object[] Params)

Parameters

Transaction DbTransaction

SqlText string

Default int

Params object[]

Returns

int

IntegerResult(string, int)

Ideal for executing SELECT statements of the type "select count(ID) as COUNT_ID from TABLE_NAME where ID = 1234"

public int IntegerResult(string SqlText, int Default)

Parameters

SqlText string

Default int

Returns

int

IntegerResult(string, int, params object[])

Ideal for executing SELECT statements of the type "select count(ID) as COUNT_ID from TABLE_NAME where ID = 1234"

public int IntegerResult(string SqlText, int Default, params object[] Params)

Parameters

SqlText string

Default int

Params object[]

Returns

int

LastId(DbTransaction, string)

Returns the last id produced by an INSERT Sqlt statement.

It should be used only with databases that support identity (auto-increment) columns

public virtual int LastId(DbTransaction Transaction, string TableName)

Parameters

Transaction DbTransaction

TableName string

Returns

int

LastId(string)

Returns the last id produced by an INSERT Sqlt statement.

It should be used only with databases that support identity (auto-increment) columns

public virtual int LastId(string TableName)

Parameters

TableName string

Returns

int

NextId(DbTransaction, string)

Returns the next id value of a generator named after the TableName table.

It should be used only with databases that support generators or when a CustomOid object is used.

public virtual int NextId(DbTransaction Transaction, string TableName)

Parameters

Transaction DbTransaction

TableName string

Returns

int

NextId(string)

Returns the next id value of a generator named after the TableName table.

It should be used only with databases that support generators or when a CustomOid object is used.

public virtual int NextId(string TableName)

Parameters

TableName string

Returns

int

NextIdByGenerator(DbTransaction, string)

Returns the next value of the GeneratorName generator.

public virtual int NextIdByGenerator(DbTransaction Transaction, string GeneratorName)

Parameters

Transaction DbTransaction

GeneratorName string

Returns

int

NextIdByGenerator(string)

Returns the next value of the GeneratorName generator.

public virtual int NextIdByGenerator(string GeneratorName)

Parameters

GeneratorName string

Returns

int

OpenConnection()

Creates and opens a DbConnection

public DbConnection OpenConnection()

Returns

DbConnection

ResetTable(string)

Empties the TableName table in the database and initializes its generator/sequencer or identity column.

DANGEROUS

public void ResetTable(string TableName)

Parameters

TableName string

Select(DbTransaction, string, params object[])

Executes a SELECT statement inside a transaction and returns the result as a DataTable.

public MemTable Select(DbTransaction Transaction, string SqlText, params object[] Params)

Parameters

Transaction DbTransaction

SqlText string

Params object[]

Returns

MemTable

Select(string)

Executes a SELECT statement and returns the result as a DataTable.

public MemTable Select(string SqlText)

Parameters

SqlText string

Returns

MemTable

Select(string, params object[])

Executes a SELECT statement and returns the result as a DataTable.

public MemTable Select(string SqlText, params object[] Params)

Parameters

SqlText string

Params object[]

Returns

MemTable

SelectIncrementLocked(string, string, object, string)

Selects a row with update lock, increments an integer field, and returns the initial row.

Example

DataRow Row = SelectIncrementLocked("NumberSeries", "Code", "SALES_DOMESTIC_1", "NextNumber");

public virtual DataRow SelectIncrementLocked(string TableName, string KeyFieldName, object KeyValue, string ValueFieldName)

Parameters

TableName string

KeyFieldName string

KeyValue object

ValueFieldName string

Returns

DataRow

SelectResult(DbTransaction, string, object, params object[])

Ideal for executing SELECT statements of the type "select FIELD_NAME from TABLE_NAME"

public object SelectResult(DbTransaction Transaction, string SqlText, object Default, params object[] Params)

Parameters

Transaction DbTransaction

SqlText string

Default object

Params object[]

Returns

object

SelectResult(string)

Ideal for executing SELECT statements of the type "select FIELD_NAME from TABLE_NAME"

public object SelectResult(string SqlText)

Parameters

SqlText string

Returns

object

SelectResult(string, object)

Ideal for executing SELECT statements of the type "select FIELD_NAME from TABLE_NAME"

public object SelectResult(string SqlText, object Default)

Parameters

SqlText string

Default object

Returns

object

SelectResult(string, object, params object[])

Ideal for executing SELECT statements of the type "select FIELD_NAME from TABLE_NAME"

public object SelectResult(string SqlText, object Default, params object[] Params)

Parameters

SqlText string

Default object

Params object[]

Returns

object

SelectResults(DbTransaction, string, params object[])

Executes SqlText and returns the first DataRow of the result set.

WARNING: If SqlText returns no rows at all then this method returns null.

Params can be:

1. either a comma separated C# params list

2. or the Params[0] element, that is the first element in Params, may be a DataRow, generic IDictionary, IList or Array and in that case no other Params elements are used.

public DataRow SelectResults(DbTransaction Transaction, string SqlText, params object[] Params)

Parameters

Transaction DbTransaction

SqlText string

Params object[]

Returns

DataRow

SelectResults(string)

Executes SqlText and returns the first DataRow of the result set.

WARNING: If SqlText returns no rows at all then this method returns null.

public DataRow SelectResults(string SqlText)

Parameters

SqlText string

Returns

DataRow

SelectResults(string, params object[])

Executes SqlText and returns the first DataRow of the result set.

WARNING: If SqlText returns no rows at all then this method returns null.

Params can be:

1. either a comma separated C# params list

2. or the Params[0] element, that is the first element in Params, may be a DataRow, generic IDictionary, IList or Array and in that case no other Params elements are used.

public DataRow SelectResults(string SqlText, params object[] Params)

Parameters

SqlText string

Params object[]

Returns

DataRow

SelectTo(DbTransaction, MemTable, string, params object[])

Executes a SELECT statement and loads the result into the specified DataTable using a transaction.

public int SelectTo(DbTransaction Transaction, MemTable Table, string SqlText, params object[] Params)

Parameters

Transaction DbTransaction

Table MemTable

SqlText string

Params object[]

Returns

int

SelectTo(MemTable, string)

Executes a SELECT statement and loads the result into the specified DataTable.

public int SelectTo(MemTable Table, string SqlText)

Parameters

Table MemTable

SqlText string

Returns

int

SelectTo(MemTable, string, params object[])

Executes a SELECT statement and loads the result into the specified DataTable.

public int SelectTo(MemTable Table, string SqlText, params object[] Params)

Parameters

Table MemTable

SqlText string

Params object[]

Returns

int

TableExists(string, bool)

Returns true if a table with TableName exists in the database.

public bool TableExists(string TableName, bool UseSelect = false)

Parameters

TableName string

UseSelect bool

Returns

bool

TableIsEmpty(string)

Returns true if a table contains no rows in the database.

public bool TableIsEmpty(string TableName)

Parameters

TableName string

Returns

bool