C# SqlConnection.ExecuteScalarAsync方法代码示例

 public async Task<long> GetRandomWodIdAsync()
 {
     using (var conn = new SqlConnection(AppConfig.ConnectionString))
     {
         return await conn.ExecuteScalarAsync<long>("SELECT TOP 1 [Id] FROM [Wod] ORDER BY NEWID()");
     }
 }

 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;
     }
 }

        /// <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 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;
     }
 }

        public async Task AppendStream(string streamId, int expectedVersion, IEnumerable<StreamEvent> streamEvents)
        {
            Guard.NullOrWhiteSpace(() => streamId);
            Guard.NullOrDefault(() => streamEvents);

            await _log.Debug("Appending stream {@streamId} with {@events}", streamId, streamEvents.ToArray());

            // create DataTable to send as a TVP
            var newStreamEventsTable = new DataTable();

            newStreamEventsTable.Columns.Add("Id", typeof(Guid));
            newStreamEventsTable.Columns.Add("Type", typeof(string));
            newStreamEventsTable.Columns.Add("Data", typeof(string));
            newStreamEventsTable.Columns.Add("Metadata", typeof(string));
            newStreamEventsTable.Columns.Add("StreamVersion", typeof(int));

            var eventVersion     = expectedVersion;
            var fallbackCommitId = Guid.NewGuid();

            newStreamEventsTable.BeginLoadData();

            foreach(var se in streamEvents) {
                se.EnrichMetadata(streamId, eventVersion++, fallbackCommitId);

                newStreamEventsTable.Rows.Add(
                    se.Metadata[EventMetadataKeys.Id],
                    se.Event.GetType().Name,
                    _serializer.Serialize(se.Event),
                    _serializer.Serialize(se.Metadata),
                    eventVersion);
            }

            newStreamEventsTable.EndLoadData();

            // create parameters
            var parameters = new DynamicParameters();

            parameters.AddDynamicParams(new {
                StreamId              = streamId,
                ExpectedStreamVersion = expectedVersion,
                StreamEvents          = newStreamEventsTable.AsTableValuedParameter("StreamEvents")
            });

            int actualVersion;

            // execute operation
            using(var connection = new SqlConnection(_settings.ConnectionString)) {
                actualVersion = await connection
                    .ExecuteScalarAsync<int>(
                        sql        : "AppendStream", 
                        param      : parameters, 
                        commandType: CommandType.StoredProcedure)
                    .ConfigureAwait(false);
            }

            // if the actual version is different from the expected version
            if(actualVersion != eventVersion) {
                throw new StreamConcurrencyException(streamId, expectedVersion, actualVersion);
            }

            await _log.Information("Events appended to stream {@streamId}", streamId);

            // dispatch events
            await _dispatcher.DispatchStreamEvents(streamEvents);
        }

        public async Task<CreateUserResult> CreateUserAsync(ApplicationUser user)
        {
            int? id = 0;
            string error = null;

            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                int existingId = await connection.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM dbo.Users WHERE [email protected]", new {Email = user.Email});
                if (existingId == 0)
                {
                    id = await connection.InsertAsync(user);
                }
                else
                {
                    error = "Email address already exists.";
                }
            }

            if (id > 0)
            {
                return new CreateUserResult {IsSuccess = true};
            }

            return new CreateUserResult {IsSuccess = false, ErrorMessage = error ?? "Error inserting user."};
        }