public static void ExecuteReader(IDbCommand command, DataReaderDelegate fn)
{
using (IDataReader dataRow = command.ExecuteReader())
{
fn(dataRow);
}
}
public override T GetSingleRow <T>(string sql, DataReaderDelegate <T> dataLoader, List <IDbDataParameter> parameters = null)
{
try
{
parameters = parameters ?? new List <IDbDataParameter>();
using (var conn = new SqlConnection(ConnectionString))
{
using (var cmd = new SqlCommand(sql, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
foreach (var p in parameters)
{
cmd.Parameters.AddWithValue(p.ParameterName, p.Value);
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
return(dataLoader(reader));
}
}
return(default(T));
}
}
}
catch (Exception ex)
{
throw new Exception(string.Format("ERROR SQL Query: {0}", sql, ex));
}
}
protected void ExecuteQuery(string query, DataReaderDelegate reader_func)
{
dbcon.Open ();
IDbCommand dbcmd = dbcon.CreateCommand ();
dbcmd.CommandText = query;
IDataReader reader = dbcmd.ExecuteReader ();
reader_func (reader);
reader.Close ();
reader = null;
dbcmd.Dispose ();
dbcmd = null;
dbcon.Close ();
}
public static DataTable ExecuteReader(String connection, String sql, List <SqlParameter> parameterList, CommandType cmdType = CommandType.Text, DataReaderDelegate fn = null)
{
using (SqlConnection conn = new SqlConnection(connection))
{
conn.Open();
return(ExecuteReader(conn, sql, parameterList, cmdType, fn));
}
}
/// <exclude/>
public override void ExecuteQuery(ITransaction transaction, ClassMapping mapping, IDaQuery query,
DataReaderDelegate invokeMe, Hashtable parameters)
{
if (query == null)
{
throw new ArgumentNullException("query", "Cannot execute a null query.");
}
if (!(query is SqlDaQuery))
{
throw new ArgumentException("Cannot execute a query not created by me.");
}
SqlDaQuery sqlQuery = (SqlDaQuery)query;
SqlConnectionUtilities.XSafeQuery(_connDesc, (SqlTransaction)transaction, sqlQuery.Sql.ToString(),
sqlQuery.Params, invokeMe, parameters);
}
public static DataTable ExecuteReader <T>(IDbConnection connection, string sql, List <T> parameterList, CommandType cmdType, DataReaderDelegate fn = null) where T : IDbDataParameter
{
DataTable result = null;
if (fn == null)
{
result = new DataTable();
fn = delegate(IDataReader dataReader)
{
result.Load(dataReader);
};
}
using (IDbCommand command = connection.CreateCommand())
{
command.Connection = connection;
command.CommandType = cmdType;
command.CommandTimeout = CommandTimeout;
command.CommandText = sql;
parameterList.ForEach(p => { command.Parameters.Add(p); });
ExecuteReader(command, fn);
}
return(result);
}
/// <summary>
/// Executes a query and invokes a method with a DataReader of results.
/// </summary>
/// <param name="transaction">Should be null, transactions are not supported.</param>
/// <param name="mapping">Class mapping for the table we're querying against. Optional,
/// but not all columns may be properly typed if it is null.</param>
/// <param name="query">The query to execute, should have come from CreateQuery.</param>
/// <param name="invokeMe">The method to invoke with the IDataReader results.</param>
/// <param name="parameters">A hashtable containing any values that need to be persisted through invoked method.
/// The list of objects from the query will be placed here.</param>
public override void ExecuteQuery(ITransaction transaction, ClassMapping mapping, IDaQuery query, DataReaderDelegate invokeMe, Hashtable parameters)
{
switch (_connDesc.Type)
{
case CsvConnectionType.Directory:
case CsvConnectionType.FileName:
case CsvConnectionType.Reader:
// These are OK.
break;
default:
throw new LoggingException("Connection does not support querying: " + _connDesc);
}
CsvDataReader reader = new CsvDataReader(this, mapping, ((UnqueryableQuery)query).Criteria);
try
{
invokeMe.Invoke(parameters, reader);
}
finally
{
reader.Close();
}
}
protected void ExecuteDataReader(string connectionString, string commandText, IEnumerable <DbParameter> parameters, DataReaderDelegate del)
{
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = commandText;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = CMD_TIMEOUT;
if (parameters != null)
{
foreach (var param in parameters)
{
cmd.Parameters.Add(param.CreateSqlParameter());
}
}
SqlDataReader reader = cmd.ExecuteReader();
try
{
del(reader);
}
finally
{
reader.Close();
}
}
}
}
/// <summary> /// Executes a query and invokes a method with a DataReader of results. /// </summary> /// <param name="transaction">The transaction to do this as part of.</param> /// <param name="mapping">Class mapping for the table we're querying against. Optional, /// but not all columns may be properly typed if it is null.</param> /// <param name="query">The query to execute, should have come from CreateQuery.</param> /// <param name="invokeMe">The method to invoke with the IDataReader results.</param> /// <param name="parameters">A hashtable containing any values that need to be persisted through invoked method. /// The list of objects from the query will be placed here.</param> public abstract void ExecuteQuery(ITransaction transaction, ClassMapping mapping, IDaQuery query, DataReaderDelegate invokeMe, Hashtable parameters);
public abstract T GetSingleRow <T>(string sql, DataReaderDelegate <T> dataLoader, List <IDbDataParameter> parameters = null);
public abstract List <T> GetTypedList <T>(string sql, DataReaderDelegate <T> dataLoader, List <IDbDataParameter> parameters = null);
/// <summary>
/// This provides a way to query the DB and do something with the results without
/// having to copy the "try, open, try, execute, finally, close, finally, close"
/// type logic in a bunch of places. This method correctly closes the objects
/// used in the DB access in the event of an exception.
/// </summary>
/// <param name="connDesc">The database connection descriptor. This is used both as
/// a key for caching connections/commands as well as for
/// getting the actual database connection the first time.</param>
/// <param name="transaction">The transaction to do this as part of.</param>
/// <param name="sql">The SQL statement to execute.</param>
/// <param name="sqlParams">A list of objects to use as parameters
/// to the SQL statement. The list may be
/// null if there are no parameters.</param>
/// <param name="invokeMe">The method to delegate to. If null, nothing is
/// done with the data reader and it is just closed.</param>
/// <param name="parameters">The other parameters to the delegate, in whatever
/// form makes sense for that delegate method.</param>
public static void XSafeQuery(AbstractSqlConnectionDescriptor connDesc,
SqlTransaction transaction, string sql,
IEnumerable sqlParams, DataReaderDelegate invokeMe, Hashtable parameters)
{
IDbConnection conn = transaction != null
? transaction.Connection
: DbCaches.Connections.Get(connDesc);
try
{
IDbCommand cmd = DbCaches.Commands.Get(sql, conn);
if (transaction != null)
{
cmd.Transaction = transaction.Transaction;
}
try
{
SetSQLOnCommand(connDesc, cmd, sql, sqlParams);
IDataReader reader;
try
{
reader = cmd.ExecuteReader();
}
catch (Exception e)
{
throw new UnableToRunSqlException(connDesc, sql, sqlParams, e);
}
try
{
if (invokeMe != null)
{
invokeMe(parameters, reader);
}
}
catch (ExceptionWithConnectionInfo)
{
// The delegate may have thrown an exception that came from another
// database utilities call, in which case we don't want to confuse
// the issue by wrapping with a different SQL command.
throw;
}
catch (Exception e)
{
throw new UnableToProcessSqlResultsException(connDesc, sql, sqlParams, e);
}
finally
{
try
{
reader.Close();
}
catch (Exception e)
{
// This exception is not rethrown because we don't want to mask any
// previous exception that might be being thrown out of "invokeMe".
_log.Warn("Caught an exception while trying to close the data reader.", e);
}
}
}
finally
{
DbCaches.Commands.Return(sql, conn, cmd);
}
}
finally
{
if (transaction == null)
{
DbCaches.Connections.Return(connDesc, conn);
}
}
}
/// <summary>
/// This provides a way to query the DB and do something with the results without
/// having to copy the "try, open, try, execute, finally, close, finally, close"
/// type logic in a bunch of places. This method correctly closes the objects
/// used in the DB access in the event of an exception.
/// </summary>
/// <param name="connDesc">The database connection descriptor. This is used both as
/// a key for caching connections/commands as well as for
/// getting the actual database connection the first time.</param>
/// <param name="sql">The SQL statement to execute.</param>
/// <param name="sqlParams">A list of objects to use as parameters
/// to the SQL statement. The list may be
/// null if there are no parameters.</param>
/// <param name="invokeMe">The method to delegate to. If null, nothing is
/// done with the data reader and it is just closed.</param>
/// <param name="parameters">The other parameters to the delegate, in whatever
/// form makes sense for that delegate method.</param>
public static void XSafeQuery(AbstractSqlConnectionDescriptor connDesc, string sql,
IEnumerable sqlParams, DataReaderDelegate invokeMe, Hashtable parameters)
{
XSafeQuery(connDesc, null, sql, sqlParams, invokeMe, parameters);
}
/// <summary>
/// Executes a query and invokes a method with a DataReader of results.
/// </summary>
/// <param name="transaction">The transaction to do this as part of.</param>
/// <param name="mapping">Class mapping for the table we're querying against. Optional,
/// but not all columns may be properly typed if it is null.</param>
/// <param name="query">The query to execute, should have come from CreateQuery.</param>
/// <param name="invokeMe">The method to invoke with the IDataReader results.</param>
/// <param name="parameters">A hashtable containing any values that need to be persisted through invoked method.
/// The list of objects from the query will be placed here.</param>
public override void ExecuteQuery(ITransaction transaction, ClassMapping mapping, IDaQuery query, DataReaderDelegate invokeMe, Hashtable parameters)
{
// Make a copy of the table and iterate over that, that way reading doesn't block writing (or
// more reading).
IDictionary<string, MemoryObject> tempTable;
IDictionary<string, MemoryObject> table = GetTable(mapping);
lock (table)
{
tempTable = new CheckedDictionary<string, MemoryObject>(table);
}
MemoryDataReader reader = new MemoryDataReader(this, mapping, ((UnqueryableQuery)query).Criteria,
tempTable.Values.GetEnumerator());
try
{
invokeMe.Invoke(parameters, reader);
}
finally
{
reader.Close();
}
}