Ejemplo n.º 1
0
        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;
        }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 4
0
 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;
     }
 }
Ejemplo n.º 5
0
 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();
     }
 }
Ejemplo n.º 6
0
 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();
     }
 }
Ejemplo n.º 7
0
 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();
     }
 }
Ejemplo n.º 8
0
 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);
        }
Ejemplo n.º 10
0
 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();
     }
 }
Ejemplo n.º 11
0
 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();
     }
 }
Ejemplo n.º 12
0
 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;
     }
 }
Ejemplo n.º 13
0
        /// <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;
            }
        }
Ejemplo n.º 14
0
        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);
            }
        }
Ejemplo n.º 15
0
     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;
     }
Ejemplo n.º 16
0
 /// <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&lt;string,object&gt;</remarks>
 public static async Task <IEnumerable <dynamic> > QueryAsync(this IDbConnection cnn, CommandDefinition command)
 {
     return(await QueryAsync <DapperRow>(cnn, typeof(DapperRow), command).ConfigureAwait(false));
 }
Ejemplo n.º 17
0
        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();
                }
            }
        }
Ejemplo n.º 18
0
        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();
                    }
                }
            }
        }
Ejemplo n.º 19
0
 /// <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));
 }
Ejemplo n.º 20
0
 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;
 }
Ejemplo n.º 21
0
 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;
 }
Ejemplo n.º 22
0
 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);
     }
 }
Ejemplo n.º 23
0
 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;
 }
Ejemplo n.º 24
0
 /// <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));
 }
Ejemplo n.º 25
0
 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>));
 }
Ejemplo n.º 26
0
 /// <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));
 }
Ejemplo n.º 27
0
 /// <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&lt;string,object&gt;</remarks>
 public static Task <dynamic> QueryFirstOrDefaultAsync(this IDbConnection cnn, CommandDefinition command)
 {
     return(QueryFirstOrDefaultAsync <dynamic>(cnn, typeof(DapperRow), command));
 }
Ejemplo n.º 28
0
 /// <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&lt;string,object&gt;</remarks>
 public static Task <IEnumerable <dynamic> > QueryAsync(this IDbConnection cnn, CommandDefinition command)
 {
     return(QueryAsync <dynamic>(cnn, typeof(DapperRow), command));
 }
Ejemplo n.º 29
0
        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);
        }
Ejemplo n.º 30
0
        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();
                    }
                }
            }
        }
Ejemplo n.º 31
0
        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);
            }
        }
Ejemplo n.º 32
0
        /// <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;
            }
        }
Ejemplo n.º 33
0
 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;
 }
Ejemplo n.º 34
0
 /// <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));
 }
Ejemplo n.º 35
0
 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;
 }
Ejemplo n.º 36
0
        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();
            }
        }
Ejemplo n.º 37
0
 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;
 }
Ejemplo n.º 38
0
 /// <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));
 }
Ejemplo n.º 39
0
        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();
                }
            }
        }
Ejemplo n.º 40
0
 /// <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));
 }
Ejemplo n.º 41
0
        /// <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)));
 }
Ejemplo n.º 43
-1
        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;
        }