/// <summary>
/// Executes a database query and returns the result as a data set.
/// </summary>
public DataSet ReadData(string databaseQuery, CommandType commandType, params IDataParameter[] @params)
{
using (var command = CreateCommand(commandType, databaseQuery, @params))
{
var watch = StartWatch(databaseQuery);
try
{
var result = new DataSet();
var adapter = new TDataAdapter {
SelectCommand = command
};
adapter.Fill(result);
command?.Parameters?.Clear();
return(result);
}
catch (Exception ex)
{
throw new Exception("Error in running SQL Query.", ex).AddData("Command", command.CommandText)
.AddData("Parameters", @params.Get(l => l.Select(p => p.ParameterName + "=" + p.Value).ToString(" | ")))
.AddData("ConnectionString", command.Connection.ConnectionString);
}
finally
{
command.Parameters?.Clear();
CloseConnection(command.Connection);
if (watch != null)
{
DataAccessProfiler.Complete(watch);
}
}
}
}
/// <summary>
/// Executes the specified command text against the database connection of the context and returns the single value.
/// </summary>
public object ExecuteScalar(string command, CommandType commandType, params IDataParameter[] @params)
{
var watch = StartWatch(command);
var dbCommand = CreateCommand(commandType, command, @params);
try
{
var result = dbCommand.ExecuteScalar();
if (command.Contains("UPDATE ") || !command.ToLowerOrEmpty().StartsWith("select "))
{
DatabaseStateChangeCommand.Raise(command, commandType, @params);
}
return(result);
}
catch (Exception ex)
{
throw new Exception("Error in running Scalar SQL Command.", ex).AddData("Command", command)
.AddData("Parameters", @params.Get(l => l.Select(p => p.ParameterName + "=" + p.Value).ToString(" | ")))
.AddData("ConnectionString", dbCommand.Connection.ConnectionString);
}
finally
{
dbCommand.Parameters?.Clear();
CloseConnection(dbCommand.Connection);
if (watch != null)
{
DataAccessProfiler.Complete(watch);
}
}
}
/// <summary>
/// Executes the specified command text against the database connection of the context and builds an IDataReader.
/// Make sure you close the data reader after finishing the work.
/// </summary>
public IDataReader ExecuteReader(string command, CommandType commandType, params IDataParameter[] @params)
{
var watch = StartWatch(command);
var dbCommand = CreateCommand(commandType, command, @params);
try
{
if (DbTransactionScope.Root != null)
{
return(dbCommand.ExecuteReader());
}
else
{
return(dbCommand.ExecuteReader(CommandBehavior.CloseConnection));
}
}
catch (Exception ex)
{
throw new Exception("Error in running SQL Query.", ex).AddData("Command", command)
.AddData("Parameters", @params.Get(l => l.Select(p => p.ParameterName + "=" + p.Value).ToString(" | ")))
.AddData("ConnectionString", dbCommand.Connection.ConnectionString);
}
finally
{
dbCommand?.Parameters?.Clear();
if (watch != null)
{
DataAccessProfiler.Complete(watch);
}
}
}
/// <summary>
/// Executes the specified command text as nonquery.
/// </summary>
public int ExecuteNonQuery(string command, CommandType commandType, params IDataParameter[] @params)
{
var dbCommand = CreateCommand(commandType, command, @params);
var watch = StartWatch(command);
try
{
var result = dbCommand.ExecuteNonQuery();
DatabaseStateChangeCommand.Raise(command, commandType, @params);
return(result);
}
catch (Exception ex)
{
throw new Exception("Error in running Non-Query SQL command.", ex).AddData("Command", command)
.AddData("Parameters", @params.Get(l => l.Select(p => p.ParameterName + "=" + p.Value).ToString(" | ")))
.AddData("ConnectionString", dbCommand.Connection.ConnectionString);
}
finally
{
dbCommand.Parameters?.Clear();
CloseConnection(dbCommand.Connection);
if (watch != null)
{
DataAccessProfiler.Complete(watch);
}
}
}
/// <summary>
/// Executes the specified command text as nonquery.
/// </summary>
public int ExecuteNonQuery(CommandType commandType, List <KeyValuePair <string, IDataParameter[]> > commands)
{
var connection = CreateConnection();
var result = 0;
try
{
foreach (var c in commands)
{
var watch = StartWatch(c.Key);
IDbCommand dbCommand = null;
try
{
dbCommand = CreateCommand(commandType, c.Key, connection, c.Value);
result += dbCommand.ExecuteNonQuery();
DatabaseStateChangeCommand.Raise(c.Key, commandType, c.Value);
}
catch (Exception ex)
{
throw new Exception("Error in executing SQL command.", ex).AddData("Command", c.Key)
.AddData("Parameters", c.Value.Get(l => l.Select(p => p.ParameterName + "=" + p.Value).ToString(" | ")));
}
finally
{
dbCommand?.Parameters?.Clear();
if (watch != null)
{
DataAccessProfiler.Complete(watch);
}
}
}
return(result);
}
catch (Exception ex)
{
throw new Exception("Error in running Non-Query SQL commands.", ex).AddData("ConnectionString", connection.ConnectionString);
}
finally
{
CloseConnection(connection);
}
}