C# Class Habanero.DB.DatabaseConnection

A super-class to manage a database connection and execute sql commands

Inheritance: System.MarshalByRefObject, IDatabaseConnection

Datei anzeigen Open project: Chillisoft/habanero Class Usage Examples

Protected Properties

Property	Type	Description
_sqlFormatter	Habanero.Base.SqlFormatter

Private Properties

Property	Type	Description
AppendOrderBy	void
CreateCommand	IDbCommand
CreateDataColumns	void
CreateDatabaseConnection	IDbConnection
ExecuteSqlInternal	int

Public Methods

Method	Description
BeginTransaction ( IDbConnection openConnection ) : IDbTransaction	Creates a transaction using the IsolationLevel set as the IsolationLevel. If this doesn't work, creates a transaction using the default IsolationLevel. Uses the connection passed in to create the transaction on - this should be open (so use GetOpenConnectionForReading to get the connection first). Override this method to do special transaction creation logic.
CreateParameterNameGenerator ( ) : IParameterNameGenerator	Creates an IParameterNameGenerator for this database connection. This is used to create names for parameters added to an ISqlStatement because each database uses a different naming convention for their parameters.
CreateSqlStatement ( ) : ISqlStatement	Creates a ISqlStatement initialised with this IDatabaseConnection
ErrorSafeConnectString ( ) : string	Returns a connection string with the password removed. This method serves as a secure way of displaying an error message in the case of a connection error, without compromising confidentiality.
ExecuteRawSql ( string sql ) : int	Executes a sql command as before, but with the full sql string provided, rather than with a sql statement object
ExecuteRawSql ( string sql, IDbTransaction transaction ) : int	Executes a sql command that returns no result set and takes no parameters, using the provided connection. This method can be used effectively where the database vendor supports the execution of several sql statements in one ExecuteNonQuery. However, for database vendors like Microsoft Access and MySql, the sql statements will need to be split up and executed as separate transactions.
ExecuteRawSqlScalar ( string sql ) : object	Executes a raw sql statement. BEWARE OF SQL INJECTION ATTACKS. Use for hardcoded sql only.
ExecuteSql ( IEnumerable statements ) : int	Executes a sql command that returns no result set and takes no parameters, using the provided connection
ExecuteSql ( IEnumerable statements, IDbTransaction transaction ) : int	Executes a collection of sql commands that returns no result set and takes no parameters, using the provided connection. This method can be used effectively where the database vendor supports the execution of several sql statements in one ExecuteNonQuery. However, for database vendors like Microsoft Access and MySql, the sql statements will need to be split up and executed as separate transactions.
ExecuteSql ( ISqlStatement sql ) : int	Executes a single sql statement object
ExecuteStoredProcNonQuery ( string procName, IEnumerable @params ) : int	Executes a stored proc with the params given.
ExecuteStoredProcNonQuery ( string procName, IEnumerable @params, int timeout ) : int	Executes a stored proc with the params given using the timeout length given.
GetConnection ( ) : IDbConnection	Returns the first closed connection available or returns a new connection object. Throws an exception and adds a message to the log if there is an error opening a connection.
GetDataTable ( IDataReader reader ) : DataTable	Returns the DataTable for the DataReader.
GetDataTable ( IDataReader reader, string dataTableName ) : DataTable	Returns a dataTable with the data from the reader and the columns names and field types set up.
GetLastAutoIncrementingID ( string tableName, IDbTransaction tran, IDbCommand command ) : long	Gets the value of the last auto-incrementing number. This called after doing an insert statement so that the inserted auto-number can be retrieved. The table name, current IDbTransaction and IDbCommand are passed in so that they can be used if necessary. Note_, this must be overridden in subclasses to include support for this feature in different databases - otherwise a NotImplementedException will be thrown.
LoadDataReader ( ISqlStatement selectSql ) : IDataReader	Loads a data reader
LoadDataReader ( ISqlStatement selectSql, string strOrderByCriteria ) : IDataReader	Loads a data reader and specifies an order-by clause
LoadDataReader ( string selectSql ) : IDataReader	Loads a data reader with the given raw sql select statement
LoadDataReader ( string selectSql, IDbTransaction transaction ) : IDataReader	Loads a data reader with the given raw sql select statement for the specified transaction
LoadDataTable ( ISqlStatement selectSql, string strSearchCriteria, string strOrderByCriteria ) : DataTable	Loads data from the database into a DataTable object, using the sql statement object provided
SetTimeoutPeriod ( int timeoutSeconds ) : void	Set the time-out period in seconds, after which the connection attempt will fail

Protected Methods

Method	Description
CreateCommand ( IDbConnection dbConnection ) : IDbCommand	Creates an IDbCommand using the provided IDbConnection. Sets the timout for the command based on what you have set using SetTimeoutPeriod
DatabaseConnection ( ) : System	Constructor that initialises a new set of null connections
DatabaseConnection ( string assemblyName, string className ) : System	Constructor that allows an assembly name and class name to be specified
DatabaseConnection ( string assemblyName, string className, string connectString ) : System	Constructor to initialise the connection with the assembly name, class name and connection string provided
DoAfterExecute ( SqlStatement statement, IDbCommand command, IDbTransaction transaction ) : void	Executes the necessary after execute logic for a particular SqlStatement.
GetOpenConnection ( IDbTransaction transaction = null ) : IDbConnection	Returns an open connection. If a IDbTransaction is provided then the connection for the transaction is used and opened if necessary.
GetOpenConnectionForReading ( ) : IDbConnection	Either finds a closed connection and opens and returns it, or creates a new connection and returns that. Throws an exception and adds a message to the log if there is an error opening a connection.
SetupCommand ( ISqlStatement statement, IDbCommand command, IDbTransaction transaction ) : void	Sets up an IDbCommand object with the provided statement.
SetupReadTransaction ( IDbCommand dbCommand ) : void	Setup the transaction on the connection to be used for read operations. This may be overridden for a specific provider (eg. sqlLite)

Private Methods

Method	Description
AppendOrderBy ( ISqlStatement statement, string orderByCriteria ) : void	Appends an order-by clause to the sql statement. " ORDER BY " is automatically prefixed by this method.
CreateCommand ( IDbConnection connection, IDbTransaction transaction ) : IDbCommand	Creates an IDbCommand using the provided IDbConnection. The created command is subscribed to the provided IDbTransaction.
CreateDataColumns ( IDataRecord reader, DataTable dt ) : void
CreateDatabaseConnection ( ) : IDbConnection	Creates a database connection using the assembly name and class name provided.
ExecuteSqlInternal ( IEnumerable statements, IDbConnection openConnection, IDbTransaction transaction ) : int

Method Details

BeginTransaction() public method

Creates a transaction using the IsolationLevel set as the IsolationLevel. If this doesn't work, creates a transaction using the default IsolationLevel. Uses the connection passed in to create the transaction on - this should be open (so use GetOpenConnectionForReading to get the connection first). Override this method to do special transaction creation logic.

public BeginTransaction ( IDbConnection openConnection ) : IDbTransaction
openConnection	IDbConnection
return	IDbTransaction

CreateCommand() protected method

Creates an IDbCommand using the provided IDbConnection. Sets the timout for the command based on what you have set using SetTimeoutPeriod

protected CreateCommand ( IDbConnection dbConnection ) : IDbCommand
dbConnection	IDbConnection	The connection to create the command with
return	IDbCommand

CreateParameterNameGenerator() public abstract method

Creates an IParameterNameGenerator for this database connection. This is used to create names for parameters added to an ISqlStatement because each database uses a different naming convention for their parameters.

public abstract CreateParameterNameGenerator ( ) : IParameterNameGenerator
return	IParameterNameGenerator

CreateSqlStatement() public method

Creates a ISqlStatement initialised with this IDatabaseConnection

public CreateSqlStatement ( ) : ISqlStatement
return	ISqlStatement

DatabaseConnection() protected method

Constructor that initialises a new set of null connections

protected DatabaseConnection ( ) : System
return	System

DatabaseConnection() protected method

Constructor that allows an assembly name and class name to be specified

protected DatabaseConnection ( string assemblyName, string className ) : System
assemblyName	string	The assembly name
className	string	The database class name
return	System

DatabaseConnection() protected method

Constructor to initialise the connection with the assembly name, class name and connection string provided

protected DatabaseConnection ( string assemblyName, string className, string connectString ) : System
assemblyName	string	The assembly name
className	string	The class name
connectString	string	The connection string. This can be /// generated by the various GetConnectionString() methods, tailored /// for the appropriate database vendors.
return	System

DoAfterExecute() protected method

Executes the necessary after execute logic for a particular SqlStatement.

protected DoAfterExecute ( SqlStatement statement, IDbCommand command, IDbTransaction transaction ) : void
statement	SqlStatement	The for which the after execute logic must be run.
command	IDbCommand	The that was used to execute the .
transaction	IDbTransaction	The under which the 's command was run.
return	void

ErrorSafeConnectString() public method

Returns a connection string with the password removed. This method serves as a secure way of displaying an error message in the case of a connection error, without compromising confidentiality.

public ErrorSafeConnectString ( ) : string
return	string

ExecuteRawSql() public method

Executes a sql command as before, but with the full sql string provided, rather than with a sql statement object

public ExecuteRawSql ( string sql ) : int
sql	string	The sql statement as a string
return	int

ExecuteRawSql() public method

Executes a sql command that returns no result set and takes no parameters, using the provided connection. This method can be used effectively where the database vendor supports the execution of several sql statements in one ExecuteNonQuery. However, for database vendors like Microsoft Access and MySql, the sql statements will need to be split up and executed as separate transactions.

Thrown if there is an /// error writing to the database. Also outputs error messages to the log. ///

public ExecuteRawSql ( string sql, IDbTransaction transaction ) : int
sql	string	A valid sql statement (typically "insert", /// "update" or "delete") as a string. Note_ that this assumes that the /// sqlCommand is not a stored procedure.
transaction	IDbTransaction	A valid transaction object in which the /// sql must be executed, or null
return	int

ExecuteRawSqlScalar() public method

Executes a raw sql statement. BEWARE OF SQL INJECTION ATTACKS. Use for hardcoded sql only.

public ExecuteRawSqlScalar ( string sql ) : object
sql	string	The sql statement to run as a scalar
return	object

ExecuteSql() public method

Executes a sql command that returns no result set and takes no parameters, using the provided connection

public ExecuteSql ( IEnumerable statements ) : int
statements	IEnumerable	A valid sql statement (typically "insert", /// "update" or "delete"). Note_ that this assumes that the /// sqlCommand is not a stored procedure.
return	int

ExecuteSql() public method

Executes a collection of sql commands that returns no result set and takes no parameters, using the provided connection. This method can be used effectively where the database vendor supports the execution of several sql statements in one ExecuteNonQuery. However, for database vendors like Microsoft Access and MySql, the sql statements will need to be split up and executed as separate transactions.

Thrown if there is an /// error writing to the database. Also outputs error messages to the log. ///

public ExecuteSql ( IEnumerable statements, IDbTransaction transaction ) : int
statements	IEnumerable	A valid sql statement object (typically "insert", /// "update" or "delete"). Note_ that this assumes that the /// sqlCommand is not a stored procedure.
transaction	IDbTransaction	A valid transaction object in which the /// sql must be executed, or null
return	int

ExecuteSql() public method

Executes a single sql statement object

public ExecuteSql ( ISqlStatement sql ) : int
sql	ISqlStatement	The sql statement object
return	int

ExecuteStoredProcNonQuery() public method

Executes a stored proc with the params given.

public ExecuteStoredProcNonQuery ( string procName, IEnumerable @params ) : int
procName	string	The stored proc name
@params	IEnumerable
return	int

ExecuteStoredProcNonQuery() public method

Executes a stored proc with the params given using the timeout length given.

public ExecuteStoredProcNonQuery ( string procName, IEnumerable @params, int timeout ) : int
procName	string	The stored proc name
@params	IEnumerable
timeout	int	The timeout in seconds
return	int

GetConnection() public method

Returns the first closed connection available or returns a new connection object. Throws an exception and adds a message to the log if there is an error opening a connection.

public GetConnection ( ) : IDbConnection
return	IDbConnection

GetDataTable() public method

Returns the DataTable for the DataReader.

public GetDataTable ( IDataReader reader ) : DataTable
reader	IDataReader
return	System.Data.DataTable

GetDataTable() public static method

Returns a dataTable with the data from the reader and the columns names and field types set up.

public static GetDataTable ( IDataReader reader, string dataTableName ) : DataTable
reader	IDataReader	the Reader that the dataTable will be made from
dataTableName	string	the name of the DataTable
return	System.Data.DataTable

GetLastAutoIncrementingID() public method

Gets the value of the last auto-incrementing number. This called after doing an insert statement so that the inserted auto-number can be retrieved. The table name, current IDbTransaction and IDbCommand are passed in so that they can be used if necessary. Note_, this must be overridden in subclasses to include support for this feature in different databases - otherwise a NotImplementedException will be thrown.

public GetLastAutoIncrementingID ( string tableName, IDbTransaction tran, IDbCommand command ) : long
tableName	string	The name of the table inserted into
tran	IDbTransaction	The current transaction, the one the insert was done in
command	IDbCommand	The Command the did the insert statement
return	long

GetOpenConnection() protected method

Returns an open connection. If a IDbTransaction is provided then the connection for the transaction is used and opened if necessary.

protected GetOpenConnection ( IDbTransaction transaction = null ) : IDbConnection
transaction	IDbTransaction	The to use for the connection.
return	IDbConnection

GetOpenConnectionForReading() protected method

Either finds a closed connection and opens and returns it, or creates a new connection and returns that. Throws an exception and adds a message to the log if there is an error opening a connection.

protected GetOpenConnectionForReading ( ) : IDbConnection
return	IDbConnection

LoadDataReader() public method

Loads a data reader

Thrown when an error /// occurred while setting up the data reader. Also sends error /// output to the log.

public LoadDataReader ( ISqlStatement selectSql ) : IDataReader
selectSql	ISqlStatement	The sql statement object
return	IDataReader

LoadDataReader() public method

Loads a data reader and specifies an order-by clause

public LoadDataReader ( ISqlStatement selectSql, string strOrderByCriteria ) : IDataReader
selectSql	ISqlStatement	The sql statement object
strOrderByCriteria	string	A sql order-by clause
return	IDataReader

LoadDataReader() public method

Loads a data reader with the given raw sql select statement

Thrown when an error /// occurred while setting up the data reader. Also sends error /// output to the log.

public LoadDataReader ( string selectSql ) : IDataReader
selectSql	string	The sql statement as a string
return	IDataReader

LoadDataReader() public method

Loads a data reader with the given raw sql select statement for the specified transaction

Thrown when an error /// occurred while setting up the data reader. Also sends error /// output to the log.

public LoadDataReader ( string selectSql, IDbTransaction transaction ) : IDataReader
selectSql	string	The sql statement as a string
transaction	IDbTransaction	Thransaction that gives the context within which the sql statement should be executed
return	IDataReader

LoadDataTable() public method

Loads data from the database into a DataTable object, using the sql statement object provided

Thrown if there is an /// error reading the database. Also outputs error messages to the log. ///

public LoadDataTable ( ISqlStatement selectSql, string strSearchCriteria, string strOrderByCriteria ) : DataTable
selectSql	ISqlStatement	The sql statement object
strSearchCriteria	string	The search criteria as a string /// to append
strOrderByCriteria	string	The order by criteria as a string /// to append
return	System.Data.DataTable

SetTimeoutPeriod() public method

Set the time-out period in seconds, after which the connection attempt will fail

public SetTimeoutPeriod ( int timeoutSeconds ) : void
timeoutSeconds	int	The time-out period in seconds
return	void

SetupCommand() protected method

Sets up an IDbCommand object with the provided statement.

protected SetupCommand ( ISqlStatement statement, IDbCommand command, IDbTransaction transaction ) : void
statement	ISqlStatement	The to setup the command with.
command	IDbCommand	The to set up.
transaction	IDbTransaction	The that the command runs under.
return	void

SetupReadTransaction() protected method

Setup the transaction on the connection to be used for read operations. This may be overridden for a specific provider (eg. sqlLite)

protected SetupReadTransaction ( IDbCommand dbCommand ) : void
dbCommand	IDbCommand	The connection to be set up with a transaction
return	void

Property Details

_sqlFormatter protected_oe property

The SqlFormatter that is used to format the Swl for the database type represented by this database connection

protected SqlFormatter,Habanero.Base _sqlFormatter
return	Habanero.Base.SqlFormatter