/// <summary>
/// Execute a query exepcted to return a single result.
/// </summary>
/// <typeparam name="T"> the type of the result</typeparam>
/// <param name="query">the parsed query</param>
/// <param name="parameterSource">a parameter source that holds the parameter values for the query</param>
/// <returns>the result of the query</returns>
public T Query<T>(ParsedQuery query, IQueryParameterSource parameterSource = null)
{
using (var command = GetCommand(query.SubstitutedQuery))
{
SetParameters(command, query, parameterSource);
return Converter.Convert<T>(command.ExecuteScalar());
}
}
/// <summary>
/// Executes an insert or update query on a database.
/// </summary>
/// <param name="query">the parsed query</param>
/// <param name="parameterSource">a parameter source that holds the parameter values for the query</param>
/// <returns>the number of affected rows</returns>
public int Update(ParsedQuery query, IQueryParameterSource parameterSource = null)
{
using (var command = GetCommand(query.SubstitutedQuery))
{
SetParameters(command, query, parameterSource);
return command.ExecuteNonQuery();
}
}
/// <summary>
/// Executes a select query using a <see cref="DbDataAdapter"/> and returns a <see cref="DataSet"/>.
/// </summary>
/// <param name="query">the parsed query</param>
/// <param name="parameterSource">a parameter source that holds the parameter values for the query</param>
/// <returns>a data set filled using the query</returns>
public DataSet Select(ParsedQuery query, IQueryParameterSource parameterSource = null)
{
var dataSet = new DataSet();
using (var dataAdapter = ConnectionProvider.ProviderFactory.CreateDataAdapter())
using (var command = GetCommand(query.SubstitutedQuery))
{
dataAdapter.SelectCommand = command;
SetParameters(command, query, parameterSource);
dataAdapter.Fill(dataSet);
}
return dataSet;
}
/// <summary>
/// Executes a select query using a DataAdapter and returns data using the provided data reader extractor.
/// </summary>
/// <typeparam name="T"> the type of the data to return</typeparam>
/// <param name="query">the parsed query</param>
/// <param name="extractor">the data reader mapper used to extract data</param>
/// <param name="parameterSource">a parameter source that holds the parameter values for the query</param>
/// <returns>the extracted data</returns>
public T Select<T>(ParsedQuery query, DataReaderExtractor<T> extractor, IQueryParameterSource parameterSource = null)
{
var table = new DataTable();
using (var dataAdapter = ConnectionProvider.ProviderFactory.CreateDataAdapter())
using (var command = GetCommand(query.SubstitutedQuery))
{
dataAdapter.SelectCommand = command;
SetParameters(command, query, parameterSource);
dataAdapter.Fill(table);
}
return extractor(new DataTableReader(table));
}
private static void SetParameters(IDbCommand command, ParsedQuery query, IQueryParameterSource parameterSource)
{
Assert.IsTrue(query.ParameterNames.Count == 0 || parameterSource != null, "The query has parameters but no parameter source was provided.");
if (query.Named)
{
foreach (var name in query.ParameterNames.Distinct())
{
command.AddParameter(name, parameterSource[name]);
}
}
else
{
for (var i = 0; i < query.ParameterNames.Count; i++)
{
var name = query.ParameterNames[i];
command.AddParameter(i.ToString(), parameterSource[name]);
}
}
}
/// <summary>
/// Execute a query expected to return a single result.
/// </summary>
/// <typeparam name="T"> the type of the result</typeparam>
/// <param name="query">the SQL query</param>
/// <param name="parameterSource">a parameter source that holds the parameter values for the query</param>
/// <returns>the result of the query</returns>
public T Query<T>(string query, IQueryParameterSource parameterSource = null)
{
return Query<T>(new ParsedQuery(query, ConnectionProvider.PlaceholderGetter), parameterSource);
}
/// <summary>
/// Executes an insert or update query on a database.
/// </summary>
/// <param name="query">the query to execute</param>
/// <param name="parameterSource">a parameter source that holds the parameter values for the query</param>
/// <returns>the number of affected rows</returns>
public int Update(string query, IQueryParameterSource parameterSource = null)
{
var parsedQuery = new ParsedQuery(query, ConnectionProvider.PlaceholderGetter);
return Update(parsedQuery, parameterSource);
}
/// <summary>
/// Executes a select query using a <see cref="DbDataAdapter"/> and returns a <see cref="DataSet"/>.
/// </summary>
/// <param name="query">the query</param>
/// <param name="parameterSource">a parameter source that holds the parameter values for the query</param>
/// <returns>a data set filled using the query</returns>
public DataSet Select(string query, IQueryParameterSource parameterSource = null)
{
var parsedQuery = new ParsedQuery(query, ConnectionProvider.PlaceholderGetter);
return Select(parsedQuery, parameterSource);
}
/// <summary>
/// Executes a select query using a DataAdapter and returns data using the provided data reader extractor.
/// </summary>
/// <typeparam name="T"> the type of the data to return</typeparam>
/// <param name="query">the SQL query</param>
/// <param name="extractor">the data reader mapper used to extract data</param>
/// <param name="parameterSource">a parameter source that holds the parameter values for the query</param>
/// <returns>the extracted data</returns>
public T Select<T>(string query, DataReaderExtractor<T> extractor, IQueryParameterSource parameterSource = null)
{
var parsedQuery = new ParsedQuery(query, ConnectionProvider.PlaceholderGetter);
return Select(parsedQuery, extractor, parameterSource);
}
/// <summary>
/// Executes a select query using a DataAdapter and returns data using the provided data reader extractor.
/// </summary>
/// <typeparam name="T"> the type of the elements to return</typeparam>
/// <param name="query">the SQL query</param>
/// <param name="mapper">the row mapper responsible for creating the returned elements</param>
/// <param name="parameterSource">a parameter source that holds the parameter values for the query</param>
/// <returns>a list of T elements obtained by executing the query</returns>
public IList<T> Select<T>(string query, RowMapper<T> mapper, IQueryParameterSource parameterSource)
{
return Select(query, GetDataReaderExtractor(mapper), parameterSource);
}
