/// <summary> /// Executes a query, returning the data typed as <typeparamref name="T"/>. /// </summary> /// <typeparam name="T">The type of results to return.</typeparam> /// <param name="db">The DB to query on.</param> /// <param name="command">The command used to query on this connection.</param> /// <returns> /// A single instance or null of the supplied type; if a basic type (int, string, etc) is queried then the data from the first column in assumed, otherwise an instance is /// created per row, and a direct column-name===member-name mapping is assumed (case insensitive). /// </returns> public static T QueryFirst <T>(this Db db, CommandDefinition command) => QueryRowImpl <T>(db.Connection, Row.First, ref command, typeof(T));
/// <summary> /// Executes a query, returning the data typed as <typeparamref name="T"/>. /// </summary> /// <typeparam name="T">The type of results to return.</typeparam> /// <param name="db">The DB to query on.</param> /// <param name="command">The command used to query on this connection.</param> /// <returns> /// A single instance of the supplied type; if a basic type (int, string, etc) is queried then the data from the first column in assumed, otherwise an instance is /// created per row, and a direct column-name===member-name mapping is assumed (case insensitive). /// </returns> public static T QuerySingleOrDefault <T>(this Db db, CommandDefinition command) => QueryRowImpl <T>(db.Connection, Row.SingleOrDefault, ref command, typeof(T));
/// <summary> /// Execute parameterized SQL. /// </summary> /// <param name="db">The DB to execute on.</param> /// <param name="command">The command to execute on this connection.</param> /// <returns>The number of rows affected.</returns> public static int Execute(this Db db, CommandDefinition command) => SqlMapper.ExecuteImpl(db.Connection, ref command);
/// <summary>
/// Executes a query, returning the data typed as <typeparamref name="T"/>.
/// </summary>
/// <typeparam name="T">The type of results to return.</typeparam>
/// <param name="db">The DB to query on.</param>
/// <param name="command">The command used to query on this connection.</param>
/// <returns>
/// A sequence of data of <typeparamref name="T"/>; if a basic type (int, string, etc) is queried then the data from the first column in assumed, otherwise an instance is
/// created per row, and a direct column-name===member-name mapping is assumed (case insensitive).
/// </returns>
public static IEnumerable <T> Query <T>(this Db db, CommandDefinition command)
{
var data = SqlMapper.QueryImpl <T>(db.Connection, command, typeof(T));
return(command.Buffered ? data.ToList() : data);
}
/// <summary>
/// Executes a single-row query, returning the data typed as <typeparamref name="T"/>.
/// </summary>
/// <typeparam name="T">The type of result to return.</typeparam>
/// <param name="db">The DB to query on.</param>
/// <param name="sql">The SQL to execute for the query.</param>
/// <param name="param">The parameters to pass, if any.</param>
/// <param name="commandTimeout">The command timeout (in seconds).</param>
/// <param name="commandType">The type of command to execute.</param>
/// <returns>
/// A sequence of data of the supplied type; if a basic type (int, string, etc) is queried then the data from the first column in assumed, otherwise an instance is
/// created per row, and a direct column-name===member-name mapping is assumed (case insensitive).
/// </returns>
public static T QuerySingleOrDefault <T>(this Db db, string sql, object param = null, int?commandTimeout = null, CommandType?commandType = null)
{
var command = new CommandDefinition(sql, param, db.Transaction, commandTimeout, commandType, CommandFlags.None);
return(QueryRowImpl <T>(db.Connection, Row.SingleOrDefault, ref command, typeof(T)));
}
/// <summary>
/// Execute parameterized SQL and return an <see cref="IDataReader"/>.
/// </summary>
/// <param name="db">The DB to execute on.</param>
/// <param name="command">The command to execute.</param>
/// <param name="commandBehavior">The <see cref="CommandBehavior"/> flags for this reader.</param>
/// <returns>An <see cref="IDataReader"/> that can be used to iterate over the results of the SQL query.</returns>
/// <remarks>
/// This is typically used when the results of a query are not processed by Dapper, for example, used to fill a <see cref="DataTable"/>
/// or <see cref="T:DataSet"/>.
/// </remarks>
public static IDataReader ExecuteReader(this Db db, CommandDefinition command, CommandBehavior commandBehavior)
{
var reader = ExecuteReaderImpl(db.Connection, ref command, commandBehavior, out IDbCommand dbcmd);
return(new WrappedReader(dbcmd, reader));
}
/// <summary> /// Executes a query, returning the data typed as <typeparamref name="T"/>. /// </summary> /// <typeparam name="T">The type of results to return.</typeparam> /// <param name="db">The DB to query on.</param> /// <param name="command">The command used to query on this connection.</param> /// <returns> /// A single instance of the supplied type; if a basic type (int, string, etc) is queried then the data from the first column in assumed, otherwise an instance is /// created per row, and a direct column-name===member-name mapping is assumed (case insensitive). /// </returns> public static T QuerySingle <T>(this Database db, CommandDefinition command) => QueryRowImpl <T>(db.Connection, Row.Single, ref command, typeof(T));
/// <summary>
/// Execute parameterized SQL.
/// </summary>
/// <param name="db">The DB to execute on.</param>
/// <param name="sql">The SQL to execute for this query.</param>
/// <param name="param">The parameters to use for this query.</param>
/// <param name="commandTimeout">Number of seconds before command execution timeout.</param>
/// <param name="commandType">Is it a stored proc or a batch?</param>
/// <returns>The number of rows affected.</returns>
public static int Execute(this Database db, string sql, object param = null, int?commandTimeout = null, CommandType?commandType = null)
{
var command = new CommandDefinition(sql, param, db.Transaction, commandTimeout, commandType, CommandFlags.Buffered);
return(SqlMapper.ExecuteImpl(db.Connection, ref command));
}
internal static async Task <IEnumerable <T> > QueryAsync <T>(this IDbConnection cnn, Type effectiveType, CommandDefinition command)
{
object param = command.Parameters;
var identity = new Identity(command.CommandText, command.CommandType, cnn, effectiveType, param?.GetType(), null);
var info = GetCacheInfo(identity, param, command.AddToCache);
bool wasClosed = cnn.State == ConnectionState.Closed;
var cancel = command.CancellationToken;
using (var cmd = command.TrySetupAsyncCommand(cnn, info.ParamReader))
{
DbDataReader reader = null;
try
{
if (wasClosed)
{
await cnn.TryOpenAsync(cancel).ConfigureAwait(false);
}
reader = await ExecuteReaderWithFlagsFallbackAsync(cmd, wasClosed, CommandBehavior.SequentialAccess | CommandBehavior.SingleResult, cancel).ConfigureAwait(false);
var tuple = info.Deserializer;
int hash = GetColumnHash(reader);
if (tuple.Func == null || tuple.Hash != hash)
{
if (reader.FieldCount == 0)
{
return(Enumerable.Empty <T>());
}
tuple = info.Deserializer = new DeserializerState(hash, GetDeserializer(effectiveType, reader, 0, -1, false));
if (command.AddToCache)
{
SetQueryCache(identity, info);
}
}
var func = tuple.Func;
if (command.Buffered)
{
var buffer = new List <T>();
var convertToType = Nullable.GetUnderlyingType(effectiveType) ?? effectiveType;
while (await reader.ReadAsync(cancel).ConfigureAwait(false))
{
object val = func(reader);
if (val == null || val is T)
{
buffer.Add((T)val);
}
else
{
buffer.Add((T)Convert.ChangeType(val, convertToType, CultureInfo.InvariantCulture));
}
}
while (await reader.NextResultAsync(cancel).ConfigureAwait(false)) /* ignore subsequent result sets */ } {
command.OnCompleted();
return(buffer);
}
else
{
// can't use ReadAsync / cancellation; but this will have to do
wasClosed = false; // don't close if handing back an open reader; rely on the command-behavior
var deferred = ExecuteReaderSync <T>(reader, func, command.Parameters);
reader = null; // to prevent it being disposed before the caller gets to see it
return(deferred);
}
}