/// <summary>
/// Creates and returns a SqlCommand adhering to the provided options
/// </summary>
private SqlCommand getCommand(string sql, object parameters, SqlConnection conn, QueryOptions options)
{
var cmd = new SqlCommand(sql, conn);
if (parameters != null)
{
AddParametersToCommand(cmd.Parameters, parameters);
}
if (options == null)
{
return(cmd);
}
if (options.CommandType != null)
{
cmd.CommandType = options.CommandType.Value;
}
if (options.CommandTimeout != null)
{
cmd.CommandTimeout = options.CommandTimeout.Value;
}
return(cmd);
}
/// <summary>
/// Executes the batch and returns a single row of data. If more than one row is is returned from the database,
/// all but the first will be discarded. The row will be mapped to the properties of the generic type.
/// </summary>
/// <param name="sql">The SQL statement to execute.</param>
/// <param name="parameters">Anonymous object providing parameters for the query.</param>
/// <param name="options">An optional set of query options</param>
public T GetRow <T>(string sql, object parameters = null, QueryOptions options = null) where T : new()
{
return(getRows <T>(sql, CommandBehavior.SingleResult | CommandBehavior.SingleRow, parameters, options).FirstOrDefault());
}
/// <summary>
/// Executes the batch and returns all rows from the single result set. Each row is mapped into a new instance of T, mapping the columns
/// to properties based on name matching.
/// </summary>
private IList <T> getRows <T>(string sql, CommandBehavior behavior, object parameters, QueryOptions options)
{
using (var conn = getConnection())
using (var cmd = getCommand(sql, parameters, conn, options))
using (var reader = cmd.ExecuteReader(CommandBehavior.SingleResult | behavior))
{
if (typeof(T).IsValueType || typeof(T) == typeof(string))
{
return(mapReaderRowsToList <T>(reader));
}
return(mapReaderRowsToType(reader, Activator.CreateInstance <T>).ToList());
}
}
/// <summary>
/// Executes the batch and returns all rows from the single result set. Each row is mapped into a new instance of T, mapping the columns
/// to properties based on name matching.
/// </summary>
/// <param name="sql">The SQL statement to execute.</param>
/// <param name="parameters">Anonymous object providing parameters for the query.</param>
/// <param name="options">An optional set of query options</param>
public IList <T> GetRows <T>(string sql, object parameters = null, QueryOptions options = null)
{
return(getRows <T>(sql, CommandBehavior.Default, parameters, options));
}
/// <summary>
/// Executes a batch and returns the number of rows affected.
/// </summary>
/// <param name="sql">The SQL statement to execute.</param>
/// <param name="parameters">Anonymous object providing parameters for the query.</param>
/// <param name="options">An optional set of query options</param>
public int Execute(string sql, object parameters = null, QueryOptions options = null)
{
using (var conn = getConnection())
using (var cmd = getCommand(sql, parameters, conn, options))
return(cmd.ExecuteNonQuery());
}
/// <summary>
/// Executes the batch and returns all rows from the single result set.
/// </summary>
private IList <dynamic> getRows(string sql, CommandBehavior behavior, object parameters, QueryOptions options)
{
using (var conn = getConnection())
using (var cmd = getCommand(sql, parameters, conn, options))
using (var reader = cmd.ExecuteReader(CommandBehavior.SingleResult | behavior))
return(mapReaderRowsToObject(reader).ToList());
}
/// <summary>
/// Executes a batch and returns the number of rows affected.
/// </summary>
/// <param name="sql">The SQL statement to execute.</param>
/// <param name="parameters">Anonymous object providing parameters for the query.</param>
/// <param name="options">An optional set of query options</param>
public static int Execute(string sql, object parameters = null, QueryOptions options = null)
{
return(db.Execute(sql, parameters, options));
}
/// <summary>
/// Executes the batch, and returns the first column of the first row of the first result set returned by the query.
/// </summary>
/// <param name="sql">The SQL statement to execute.</param>
/// <param name="parameters">Anonymous object providing parameters for the query.</param>
/// <param name="options">An optional set of query options</param>
/// <exception cref="NoRowsException" />
public static T GetScalar <T>(string sql, object parameters = null, QueryOptions options = null)
{
return(db.GetScalar <T>(sql, parameters, options));
}
/// <summary>
/// Executes the batch and returns a single row of data. If more than one row is is returned from the database,
/// all but the first will be discarded. The row will be mapped to the properties of the generic type.
/// </summary>
/// <param name="sql">The SQL statement to execute.</param>
/// <param name="parameters">Anonymous object providing parameters for the query.</param>
/// <param name="options">An optional set of query options</param>
public static T GetRow <T>(string sql, object parameters = null, QueryOptions options = null) where T : new()
{
return(db.GetRow <T>(sql, parameters, options));
}
/// <summary>
/// Executes the batch and returns a single row of data. If more than one row is is returned from the database,
/// all but the first will be discarded.
/// </summary>
/// <param name="sql">The SQL statement to execute.</param>
/// <param name="parameters">Anonymous object providing parameters for the query.</param>
/// <param name="options">An optional set of query options</param>
public static dynamic GetRow(string sql, object parameters = null, QueryOptions options = null)
{
return(db.GetRow(sql, parameters, options));
}
/// <summary>
/// Executes the batch and returns all rows from the single result set.
/// The rows will be mapped to the properties of the generic type.
/// </summary>
/// <param name="sql">The SQL statement to execute.</param>
/// <param name="parameters">Anonymous object providing parameters for the query.</param>
/// <param name="options">An optional set of query options</param>
public static IList <T> GetRows <T>(string sql, object parameters = null, QueryOptions options = null)
{
return(db.GetRows <T>(sql, parameters, options));
}
