public static RSVP EditRSVP(RSVP rsvp)
{
using (var con = Open())
{
rsvp.EditDate = DateTime.Now;
rsvp.ConfimationNumber = new Guid();
CommandDefinition command = new CommandDefinition(StoredProcedures.Proc_Custom_RSVPRepositories_EditRSVP.ToString(),
new
{
rsvp.RSVPId,
rsvp.CreateDate,
rsvp.ConfimationNumber,
rsvp.isAttending,
rsvp.AttendingGuests,
rsvp.FavoriteWine,
rsvp.FirstName,
rsvp.LastName,
rsvp.ShortDescription,
}, commandType: CommandType.StoredProcedure);
rsvp = con.Query<RSVP>(command).FirstOrDefault();
}
if (rsvp == null)
{
rsvp = new RSVP();
}
return rsvp;
}
public static Event EditEvent(Event eventModel)
{
using (var con = Open())
{
eventModel.EditDate = DateTime.Now;
CommandDefinition command = new CommandDefinition(StoredProcedures.Proc_Custom_EventRepositories_EditEvent.ToString(),
new
{
eventModel.EventId,
eventModel.EventDate,
eventModel.EventLocation,
eventModel.GuestsAttending,
eventModel.Title,
eventModel.EventDescription,
eventModel.CreateDate,
}, commandType: CommandType.StoredProcedure);
eventModel = con.Query<Event>(command).FirstOrDefault();
}
if (eventModel == null)
{
eventModel = new Event();
}
return eventModel;
}
public void AssertNoCacheWorksForQueryMultiple()
{
int a = 123, b = 456;
var cmdDef = new CommandDefinition(@"select @a; select @b;", new
{
a, b
}, commandType: CommandType.Text, flags: CommandFlags.NoCache);
int c, d;
Dapper.SqlMapper.PurgeQueryCache();
int before = Dapper.SqlMapper.GetCachedSQLCount();
using (var sqlConnection = Program.GetOpenConnection(true))
{
using (var multi = sqlConnection.QueryMultiple(cmdDef))
{
c = multi.Read<int>().Single();
d = multi.Read<int>().Single();
}
}
int after = Dapper.SqlMapper.GetCachedSQLCount();
before.IsEqualTo(0);
after.IsEqualTo(0);
c.IsEqualTo(123);
d.IsEqualTo(456);
}
public async Task<long> CreateAlbum(NewAlbum album)
{
using (var connection = new SqlConnection(_connectionString))
{
var cmd = new CommandDefinition(SqlStatements.InsertAlbum, new { artistId = album.ArtistId, albumArtId = album.AlbumArtId, name = album.Name, releaseDate = album.ReleaseDate });
await connection.OpenAsync();
var albumArtId = await connection.ExecuteScalarAsync<long>(cmd);
return albumArtId;
}
}
private async Task<Artist> GetArtistByName(string artistName)
{
using (var connection = new SqlConnection(_connectionString))
{
var cmd = new CommandDefinition(SqlStatements.SelectArtistByName, new { name = artistName });
await connection.OpenAsync();
var artists = await connection.QueryAsync<Artist>(cmd);
return artists.FirstOrDefault();
}
}
public async Task<AlbumTrack> GetAlbumTrack(long albumTrackId)
{
using (var connection = new SqlConnection(_connectionString))
{
var cmd = new CommandDefinition(SqlStatements.SelectAlbumTrack, new { id = albumTrackId });
await connection.OpenAsync();
var tracks = await connection.QueryAsync<AlbumTrack>(cmd);
return tracks.FirstOrDefault();
}
}
public async Task<Artist> GetArtist(long artistId)
{
using (var connection = new SqlConnection(_connectionString))
{
var cmd = new CommandDefinition(SqlStatements.SelectArtist, new { id = artistId });
await connection.OpenAsync();
var artists = await connection.QueryAsync<Artist>(cmd);
return artists.FirstOrDefault();
}
}
public static void RegistreerDeelnemer(Deelnemer deelnemer)
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Correspondentie"].ConnectionString))
{
string commandText = @"
INSERT INTO [dbo].[Deelnemer] ([Id], [Nummer], [Naam], [EmailAdres], [Straat], [Huisnummer],
[HuisnummerToevoeging], [Postcode], [Plaats])
VALUES (@id, @Nummer, @Naam, @EmailAdres, @Straat, @Huisnummer, @HuisnummerToevoeging, @Postcode, @Plaats)";
CommandDefinition cmd = new CommandDefinition(commandText, deelnemer);
connection.Execute(cmd);
}
}
/// <summary>
/// Creates a <see cref="Dapper.CommandDefinition"/> from the given SQL statement.
/// </summary>
/// <param name="statement"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandFlags"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public static Dapper.CommandDefinition ToDapperCommand(this Sql.SqlStatement statement, System.Data.IDbTransaction transaction = null, int?commandTimeout = null, Dapper.CommandFlags commandFlags = Dapper.CommandFlags.Buffered, System.Threading.CancellationToken cancellationToken = default)
{
var cmd = new Dapper.CommandDefinition(statement.CommandText,
commandType: System.Data.CommandType.Text,
parameters: statement.Parameters.ToDynamicParameters(),
transaction: transaction,
commandTimeout: commandTimeout,
flags: commandFlags,
cancellationToken: cancellationToken
);
return(cmd);
}
public static void DeleteRSVP(int id)
{
RSVP rsvp = new RSVP();
using (var con = Open())
{
CommandDefinition command = new CommandDefinition(StoredProcedures.Proc_Custom_RSVPRepositories_DeleteRSVP.ToString(),
new
{
id = id
}, commandType: CommandType.StoredProcedure);
rsvp = con.Query<RSVP>(command).FirstOrDefault();
}
}
public static void DeleteEvent(int id)
{
Event rsvp = new Event();
using (var con = Open())
{
CommandDefinition command = new CommandDefinition(StoredProcedures.Proc_Custom_EventRepositories_DeleteEvent.ToString(),
new
{
id = id
}, commandType: CommandType.StoredProcedure);
rsvp = con.Query<Event>(command).FirstOrDefault();
}
}
public async Task<long> CreateAlbumTrack(NewAlbumTrack albumTrack)
{
using (var connection = new SqlConnection(_connectionString))
{
var cmd = new CommandDefinition(SqlStatements.InsertAlbumTrack, new
{
albumId = albumTrack.AlbumId,
trackNumber = albumTrack.TrackNumber,
name = albumTrack.Name,
durationMs = albumTrack.LengthInMs
});
await connection.OpenAsync();
var trackId = await connection.ExecuteScalarAsync<long>(cmd);
return trackId;
}
}
/// <summary>
/// Insert a new artist in the database, or return its ID if it already exists.
/// </summary>
/// <param name="artist">The artist to insert.</param>
/// <returns>The ID of the new artist inserted, or the existing ID if it is a duplicate.</returns>
public async Task<long> CreateArtist(NewArtist artist)
{
//var existingArtist = await GetArtistByName(artist.Name);
//if (existingArtist != null)
//{
// return existingArtist.Id;
//}
using (var connection = new SqlConnection(_connectionString))
{
var cmd = new CommandDefinition(SqlStatements.InsertArtist, new { name = artist.Name, country = artist.Country });
await connection.OpenAsync();
var artistId = await connection.ExecuteScalarAsync<long>(cmd);
return artistId;
}
}
public IDictionary<Guid, int> CountMessages()
{
using (var sqlConnection = new SqlConnection(_connectionString))
{
var commandBuilder = new StringBuilder();
commandBuilder.Append("exec CountMessages @categoryId;");
commandBuilder.Append("exec CountAllMessages;");
var command = new CommandDefinition(commandBuilder.ToString(), new { categoryId = Guid.Empty });
var resultReader = sqlConnection.QueryMultiple(command);
var byCategoriesResult = resultReader.Read().ToDictionary(row => (Guid)row.CategoryId, row => (int)row.MessageCount);
var totalResult = resultReader.Read().ToDictionary(row => (Guid)row.CategoryId, row => (int)row.MessageCount);
return byCategoriesResult.Union(totalResult).ToDictionary(x => x.Key, x => x.Value);
}
}
public EpplusResult ToExcel(string type)
{
bool? nontaxdeductible = null; // both
switch (TaxDedNonTax)
{
case "TaxDed":
nontaxdeductible = false;
break;
case "NonTaxDed":
nontaxdeductible = true;
break;
}
switch (type)
{
case "ledgerincome":
var cd = new CommandDefinition("dbo.LedgerIncomeExport", new
{
fd = Dt1,
td = Dt2,
campusid,
nontaxded = nontaxdeductible,
includeunclosed = IncUnclosedBundles
}, commandType: CommandType.StoredProcedure);
return DbUtil.Db.Connection.ExecuteReader(cd).ToExcel("LedgerIncome.xlsx");
case "donorfundtotals":
return ExportPeople.ExcelDonorFundTotals(Dt1, Dt2, fundid, campusid, false, nontaxdeductible, IncUnclosedBundles)
.ToExcel("DonorFundTotals.xlsx");
case "donortotals":
return ExportPeople.ExcelDonorTotals(Dt1, Dt2, campusid, false, nontaxdeductible, IncUnclosedBundles)
.ToExcel("DonorTotals.xlsx");
case "donordetails":
return ExportPeople.DonorDetails(Dt1, Dt2, fundid, campusid, false, nontaxdeductible, IncUnclosedBundles)
.ToExcel("DonorDetails.xlsx");
}
return null;
}
/// <summary>
/// Execute a query asynchronously using .NET 4.5 Task.
/// </summary>
/// <remarks>Note: each row can be accessed via "dynamic", or by casting to an IDictionary<string,object></remarks>
public static async Task <IEnumerable <dynamic> > QueryAsync(this IDbConnection cnn, CommandDefinition command)
{
return(await QueryAsync <DapperRow>(cnn, typeof(DapperRow), command).ConfigureAwait(false));
}
private static async Task <IEnumerable <TReturn> > MultiMapAsync <TReturn>(this IDbConnection cnn, CommandDefinition command, Type[] types, Func <object[], TReturn> map, string splitOn)
{
if (types.Length < 1)
{
throw new ArgumentException("you must provide at least one type to deserialize");
}
object param = command.Parameters;
var identity = new Identity(command.CommandText, command.CommandType, cnn, types[0], param?.GetType(), types);
var info = GetCacheInfo(identity, param, command.AddToCache);
bool wasClosed = cnn.State == ConnectionState.Closed;
try {
if (wasClosed)
{
await((DbConnection)cnn).OpenAsync().ConfigureAwait(false);
}
using (var cmd = (DbCommand)command.SetupCommand(cnn, info.ParamReader))
using (var reader = await cmd.ExecuteReaderAsync(GetBehavior(wasClosed, CommandBehavior.SequentialAccess | CommandBehavior.SingleResult), command.CancellationToken).ConfigureAwait(false)) {
var results = MultiMapImpl <TReturn>(null, default(CommandDefinition), types, map, splitOn, reader, identity, true);
return(command.Buffered ? results.ToList() : results);
}
}
finally {
if (wasClosed)
{
cnn.Close();
}
}
}
private 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 = (DbCommand)command.SetupCommand(cnn, info.ParamReader))
{
DbDataReader reader = null;
try
{
if (wasClosed)
{
await((DbConnection)cnn).OpenAsync(cancel).ConfigureAwait(false);
}
reader = await cmd.ExecuteReaderAsync(GetBehavior(wasClosed, CommandBehavior.SequentialAccess | CommandBehavior.SingleResult), cancel).ConfigureAwait(false);
var tuple = info.Deserializer;
int hash = GetColumnHash(reader);
if (tuple.Func == null || tuple.Hash != hash)
{
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)
{
List <T> 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))
{
}
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);
}
}
finally
{
using (reader) { } // dispose if non-null
if (wasClosed)
{
cnn.Close();
}
}
}
}
/// <summary>
/// Execute a query asynchronously using .NET 4.5 Task.
/// </summary>
public static Task <IEnumerable <T> > QueryAsync <T>(this IDbConnection cnn, CommandDefinition command)
{
return(QueryAsync <T>(cnn, typeof(T), command));
}
public static List<RSVP> GetAllRSVPEntries()
{
List<RSVP> rsvp = new List<RSVP>();
using (var con = Open())
{
CommandDefinition command = new CommandDefinition(StoredProcedures.Proc_Custom_RSVPRepositories_GetAllRSVPEntries.ToString(), commandType: CommandType.StoredProcedure);
rsvp = con.Query<RSVP>(command).ToList();
}
return rsvp;
}
private bool HandleEvent(DeelnemerGeregistreerd e)
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DeelnemerBeheer"].ConnectionString))
{
string commandText = @"
INSERT INTO [dbo].[Deelnemer] ([Nummer], [Version], [Naam], [EmailAdres], [WoonAdresStraat], [WoonAdresHuisnummer],
[WoonAdresHuisnummerToevoeging], [WoonAdresPostcode], [WoonAdresPlaats], [Id])
VALUES (@Nummer, @Version, @Naam, @EmailAdres, @Straat, @Huisnummer, @HuisnummerToevoeging, @Postcode, @Plaats, @Id)";
CommandDefinition cmd = new CommandDefinition(commandText, e);
connection.Execute(cmd);
}
return true;
}
private void UpdateProcesState(ProcesState state)
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ProcesManagement"].ConnectionString))
{
string commandText = @"
UPDATE [dbo].[RegistrerenAanmelding]
SET [DeelnemerNummer] = @DeelnemerNummer
,[WerkgeverNummer] = @WerkgeverNummer
,[Status] = @Status
,[Foutmelding] = @Foutmelding
WHERE [CorrelationId] = @CorrelationId";
CommandDefinition cmd = new CommandDefinition(commandText, state);
connection.Execute(cmd);
}
}
public static Event GetEventById(int id)
{
Event eventModel = new Event();
using (var con = Open())
{
CommandDefinition command = new CommandDefinition(StoredProcedures.Proc_Custom_EventRepositories_GetEventById.ToString(),
new
{
id = id
}, commandType: CommandType.StoredProcedure);
eventModel = con.Query<Event>(command).FirstOrDefault();
}
return eventModel;
}
/// <summary>
/// Execute a query asynchronously using .NET 4.5 Task.
/// </summary>
public static Task <IEnumerable <object> > QueryAsync(this IDbConnection cnn, Type type, CommandDefinition command)
{
return(QueryAsync <object>(cnn, type, command));
}
private static Action <IDbCommand, object> GetParameterReader(string identity, IDbConnection cnn, CommandDefinition command)
{
return(parameterreaders.Get(identity, () => methodGetParameterReader.Invoke(null, new object[] { cnn, command }) as Action <IDbCommand, object>));
}
/// <summary>
/// Perform a multi mapping query with 7 input parameters
/// </summary>
public static Task <IEnumerable <TReturn> > QueryAsync <TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn>(this IDbConnection cnn, CommandDefinition command, Func <TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn> map, string splitOn = "Id")
{
return(MultiMapAsync <TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn>(cnn, command, map, splitOn));
}
/// <summary>
/// Execute a single-row query asynchronously using .NET 4.5 Task.
/// </summary>
/// <remarks>Note: the row can be accessed via "dynamic", or by casting to an IDictionary<string,object></remarks>
public static Task <dynamic> QueryFirstOrDefaultAsync(this IDbConnection cnn, CommandDefinition command)
{
return(QueryFirstOrDefaultAsync <dynamic>(cnn, typeof(DapperRow), command));
}
/// <summary>
/// Execute a query asynchronously using .NET 4.5 Task.
/// </summary>
/// <remarks>Note: each row can be accessed via "dynamic", or by casting to an IDictionary<string,object></remarks>
public static Task <IEnumerable <dynamic> > QueryAsync(this IDbConnection cnn, CommandDefinition command)
{
return(QueryAsync <dynamic>(cnn, typeof(DapperRow), command));
}
private static async Task <int> ExecuteMultiImplAsync(IDbConnection cnn, CommandDefinition command, IEnumerable multiExec)
{
bool isFirst = true;
int total = 0;
bool wasClosed = cnn.State == ConnectionState.Closed;
try
{
if (wasClosed)
{
await((DbConnection)cnn).OpenAsync(command.CancellationToken).ConfigureAwait(false);
}
CacheInfo info = null;
string masterSql = null;
if ((command.Flags & CommandFlags.Pipelined) != 0)
{
const int MAX_PENDING = 100;
var pending = new Queue <AsyncExecState>(MAX_PENDING);
DbCommand cmd = null;
try
{
foreach (var obj in multiExec)
{
if (isFirst)
{
isFirst = false;
cmd = (DbCommand)command.SetupCommand(cnn, null);
masterSql = cmd.CommandText;
var identity = new Identity(command.CommandText, cmd.CommandType, cnn, null, obj.GetType(), null);
info = GetCacheInfo(identity, obj, command.AddToCache);
}
else if (pending.Count >= MAX_PENDING)
{
var recycled = pending.Dequeue();
total += await recycled.Task.ConfigureAwait(false);
cmd = recycled.Command;
cmd.CommandText = masterSql; // because we do magic replaces on "in" etc
cmd.Parameters.Clear(); // current code is Add-tastic
}
else
{
cmd = (DbCommand)command.SetupCommand(cnn, null);
}
info.ParamReader(cmd, obj);
var task = cmd.ExecuteNonQueryAsync(command.CancellationToken);
pending.Enqueue(new AsyncExecState(cmd, task));
cmd = null; // note the using in the finally: this avoids a double-dispose
}
while (pending.Count != 0)
{
var pair = pending.Dequeue();
using (pair.Command) { } // dispose commands
total += await pair.Task.ConfigureAwait(false);
}
} finally
{
// this only has interesting work to do if there are failures
using (cmd) { } // dispose commands
while (pending.Count != 0)
{ // dispose tasks even in failure
using (pending.Dequeue().Command) { } // dispose commands
}
}
}
else
{
using (var cmd = (DbCommand)command.SetupCommand(cnn, null))
{
foreach (var obj in multiExec)
{
if (isFirst)
{
masterSql = cmd.CommandText;
isFirst = false;
var identity = new Identity(command.CommandText, cmd.CommandType, cnn, null, obj.GetType(), null);
info = GetCacheInfo(identity, obj, command.AddToCache);
}
else
{
cmd.CommandText = masterSql; // because we do magic replaces on "in" etc
cmd.Parameters.Clear(); // current code is Add-tastic
}
info.ParamReader(cmd, obj);
total += await cmd.ExecuteNonQueryAsync(command.CancellationToken).ConfigureAwait(false);
}
}
}
command.OnCompleted();
}
finally
{
if (wasClosed)
{
cnn.Close();
}
}
return(total);
}
private static async Task <T> QueryFirstOrDefaultAsync <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 = (DbCommand)command.SetupCommand(cnn, info.ParamReader))
{
DbDataReader reader = null;
try
{
if (wasClosed)
{
await((DbConnection)cnn).OpenAsync(cancel).ConfigureAwait(false);
}
reader = await cmd.ExecuteReaderAsync(GetBehavior(wasClosed, CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow), cancel).ConfigureAwait(false);
T result = default(T);
if (await reader.ReadAsync(cancel).ConfigureAwait(false))
{
var tuple = info.Deserializer;
int hash = GetColumnHash(reader);
if (tuple.Func == null || tuple.Hash != hash)
{
tuple = info.Deserializer = new DeserializerState(hash, GetDeserializer(effectiveType, reader, 0, -1, false));
if (command.AddToCache)
{
SetQueryCache(identity, info);
}
}
var func = tuple.Func;
object val = func(reader);
if (val == null || val is T)
{
result = (T)val;
}
else
{
var convertToType = Nullable.GetUnderlyingType(effectiveType) ?? effectiveType;
result = (T)Convert.ChangeType(val, convertToType, CultureInfo.InvariantCulture);
}
while (await reader.ReadAsync(cancel).ConfigureAwait(false))
{
}
}
while (await reader.NextResultAsync(cancel).ConfigureAwait(false))
{
}
return(result);
}
finally
{
using (reader) { } // dispose if non-null
if (wasClosed)
{
cnn.Close();
}
}
}
}
private void RegistreerProcesStart(ProcesState state)
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ProcesManagement"].ConnectionString))
{
string commandText = @"
INSERT INTO [dbo].[RegistrerenAanmelding]
([CorrelationId]
,[InitierendCommand]
,[DeelnemerNummer]
,[WerkgeverNummer]
,[StartTijdstip]
,[Status])
VALUES
(@CorrelationId
,@InitierendCommand
,@DeelnemerNummer
,@WerkgeverNummer
,@StartTijdstip
,@Status)";
dynamic parameters = new
{
CorrelationId = state.CorrelationId,
InitierendCommand = JsonConvert.SerializeObject(state.InitierendCommand),
DeelnemerNummer = state.DeelnemerNummer,
WerkgeverNummer = state.WerkgeverNummer,
Status = state.Status,
StartTijdstip = state.StartTijdstip
};
CommandDefinition cmd = new CommandDefinition(commandText, parameters);
connection.Execute(cmd);
}
}
/// <summary>
/// Execute a command that returns multiple result sets, and access each in turn
/// </summary>
public static async Task <GridReader> QueryMultipleAsync(this IDbConnection cnn, CommandDefinition command)
{
object param = command.Parameters;
Identity identity = new Identity(command.CommandText, command.CommandType, cnn, typeof(GridReader), param?.GetType(), null);
CacheInfo info = GetCacheInfo(identity, param, command.AddToCache);
DbCommand cmd = null;
IDataReader reader = null;
bool wasClosed = cnn.State == ConnectionState.Closed;
try
{
if (wasClosed)
{
await((DbConnection)cnn).OpenAsync(command.CancellationToken).ConfigureAwait(false);
}
cmd = (DbCommand)command.SetupCommand(cnn, info.ParamReader);
reader = await cmd.ExecuteReaderAsync(GetBehavior(wasClosed, CommandBehavior.SequentialAccess), command.CancellationToken).ConfigureAwait(false);
var result = new GridReader(cmd, reader, identity, command.Parameters as DynamicParameters, command.AddToCache, command.CancellationToken);
wasClosed = false; // *if* the connection was closed and we got this far, then we now have a reader
// with the CloseConnection flag, so the reader will deal with the connection; we
// still need something in the "finally" to ensure that broken SQL still results
// in the connection closing itself
return(result);
}
catch
{
if (reader != null)
{
if (!reader.IsClosed)
{
try
{ cmd.Cancel(); }
catch
{ /* don't spoil the existing exception */ }
}
reader.Dispose();
}
cmd?.Dispose();
if (wasClosed)
{
cnn.Close();
}
throw;
}
}
public static RSVP GetRSVPByConfirmationNumber(string number)
{
RSVP rsvp = new RSVP();
using (var con = Open())
{
CommandDefinition command = new CommandDefinition(StoredProcedures.Proc_Custom_RSVPRepositories_GetRSVPByConfimationNumber.ToString(),
new
{
ConfimationNumber = number
}, commandType: CommandType.StoredProcedure);
rsvp = con.Query<RSVP>(command).FirstOrDefault();
}
return rsvp;
}
/// <summary>
/// Execute parameterized SQL and return an <see cref="IDataReader"/>
/// </summary>
/// <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="DataSet"/>.
/// </remarks>
public static Task <IDataReader> ExecuteReaderAsync(this IDbConnection cnn, CommandDefinition command)
{
return(ExecuteReaderImplAsync(cnn, command));
}
public static List<Event> GetAllEvents()
{
List<Event> eventModel = new List<Event>();
using (var con = Open())
{
CommandDefinition command = new CommandDefinition(StoredProcedures.Proc_Custom_EventRepositories_GetAllEvents.ToString(), commandType: CommandType.StoredProcedure);
eventModel = con.Query<Event>(command).ToList();
}
return eventModel;
}
private static async Task <IDataReader> ExecuteReaderImplAsync(IDbConnection cnn, CommandDefinition command)
{
Action <IDbCommand, object> paramReader = GetParameterReader(cnn, ref command);
DbCommand cmd = null;
bool wasClosed = cnn.State == ConnectionState.Closed;
try
{
cmd = (DbCommand)command.SetupCommand(cnn, paramReader);
if (wasClosed)
{
await((DbConnection)cnn).OpenAsync(command.CancellationToken).ConfigureAwait(false);
}
var reader = await cmd.ExecuteReaderAsync(GetBehavior(wasClosed, CommandBehavior.Default), command.CancellationToken).ConfigureAwait(false);
wasClosed = false;
return(reader);
}
finally
{
if (wasClosed)
{
cnn.Close();
}
cmd?.Dispose();
}
}
private bool HandleEvent(DeelnemerVerhuisd e)
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DeelnemerBeheer"].ConnectionString))
{
string commandText = @"
UPDATE [dbo].[Deelnemer]
Set [WoonAdresStraat] = @Straat,
[WoonAdresHuisnummer] = @Huisnummer,
[WoonAdresHuisnummerToevoeging] = @HuisnummerToevoeging,
[WoonAdresPostcode] = @Postcode,
[WoonAdresPlaats] = @Plaats,
[Version] = @Version
WHERE [Nummer] = @Nummer";
CommandDefinition cmd = new CommandDefinition(commandText, e);
connection.Execute(cmd);
}
return true;
}
/// <summary>
/// Execute parameterized SQL that selects a single value
/// </summary>
/// <returns>The first cell selected</returns>
public static Task <T> ExecuteScalarAsync <T>(this IDbConnection cnn, CommandDefinition command)
{
return(ExecuteScalarImplAsync <T>(cnn, command));
}
private static async Task <IEnumerable <TReturn> > MultiMapAsync <TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn>(this IDbConnection cnn, CommandDefinition command, Delegate map, string splitOn)
{
object param = command.Parameters;
var identity = new Identity(command.CommandText, command.CommandType, cnn, typeof(TFirst), param?.GetType(), new[] { typeof(TFirst), typeof(TSecond), typeof(TThird), typeof(TFourth), typeof(TFifth), typeof(TSixth), typeof(TSeventh) });
var info = GetCacheInfo(identity, param, command.AddToCache);
bool wasClosed = cnn.State == ConnectionState.Closed;
try
{
if (wasClosed)
{
await((DbConnection)cnn).OpenAsync(command.CancellationToken).ConfigureAwait(false);
}
using (var cmd = (DbCommand)command.SetupCommand(cnn, info.ParamReader))
using (var reader = await cmd.ExecuteReaderAsync(GetBehavior(wasClosed, CommandBehavior.SequentialAccess | CommandBehavior.SingleResult), command.CancellationToken).ConfigureAwait(false))
{
if (!command.Buffered)
{
wasClosed = false; // handing back open reader; rely on command-behavior
}
var results = MultiMapImpl <TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn>(null, CommandDefinition.ForCallback(command.Parameters), map, splitOn, reader, identity, true);
return(command.Buffered ? results.ToList() : results);
}
} finally
{
if (wasClosed)
{
cnn.Close();
}
}
}
/// <summary>
/// Execute a single-row query asynchronously using .NET 4.5 Task.
/// </summary>
public static Task <object> QueryFirstOrDefaultAsync(this IDbConnection cnn, Type type, CommandDefinition command)
{
return(QueryFirstOrDefaultAsync <object>(cnn, type, command));
}
/// <summary>
/// Perform a multi mapping query with arbitrary input parameters
/// </summary>
/// <typeparam name="TReturn">The return type</typeparam>
/// <param name="cnn"></param>
/// <param name="sql"></param>
/// <param name="types">array of types in the recordset</param>
/// <param name="map"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <param name="buffered"></param>
/// <param name="splitOn">The Field we should split and read the second object from (default: id)</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></returns>
public static Task <IEnumerable <TReturn> > QueryAsync <TReturn>(this IDbConnection cnn, string sql, Type[] types, Func <object[], TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int?commandTimeout = null, CommandType?commandType = null)
{
var command = new CommandDefinition(sql, param, transaction, commandTimeout, commandType, buffered ? CommandFlags.Buffered : CommandFlags.None, default(CancellationToken));
return(MultiMapAsync <TReturn>(cnn, command, types, map, splitOn));
}
private static SqlCommandResult ExecuteCommand(this IDbConnection connection, CommandDefinition command)
{
return(new SqlCommandResult(connection.Execute(command)));
}
public async Task<Album> GetAlbum(long albumId)
{
Album albumAggregate;
using(var connection = new SqlConnection(_connectionString))
{
var cmd = new CommandDefinition(SqlStatements.SelectAlbumAggregate, new { albumId = albumId });
await connection.OpenAsync();
var albums = await connection.QueryAsync<Album, Artist, AlbumArt, Album>(cmd, (album, artist, art) => { album.Artist = artist; album.AlbumArt = art; return album; });
albumAggregate = albums.FirstOrDefault();
if (albumAggregate == null)
return null;
cmd = new CommandDefinition(SqlStatements.SelectAlbumTracksByAlbum, new { albumId = albumId });
var tracks = await connection.QueryAsync<AlbumTrack>(cmd);
var trackList = tracks.OrderBy(track => track.TrackNumber)
.Select(track => new Track { Id = track.Id, Name = track.Name, DuationMs = track.DurationMs })
.ToList();
albumAggregate.TrackList = trackList;
}
return albumAggregate;
}