C# SqlConnection.QueryAsync方法代码示例

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

        public async Task<IEnumerable<Album>> GetAllAlbumsDapperAsync()
        {
            List<Album> albums;
            using (var con = new SqlConnection(this.connectionString))
            {
                con.Open();
                var watch = new Stopwatch();
                watch.Start();

                var result = await con.QueryAsync<Album, Genre, Artist, Album>(
                    "[dbo].[HoleAlleAlben]",
                    (album, genre, artist) =>
                    {
                        album.Genre = genre;
                        album.Artist = artist;
                        return album;
                    },
                    commandType: CommandType.StoredProcedure,
                    splitOn: "GenreId, ArtistId");
                albums = result.ToList();
                watch.Stop();

                this.logger.LogInformation("SQL query {0} was done in {1} ms.", new object[] { "[dbo].[HoleAlleAlben] Dapper.net Async", watch.ElapsedMilliseconds});
                con.Close();
            }

            return albums;
        }

 public async Task<IEnumerable<ProposalReadModel>> GetAll()
 {
     using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString))
     {
         return await connection.QueryAsync<ProposalReadModel>("select Id, Status, CreatedBy, CreatedOn, UpdatedOn from Proposals;");
     }
 }

 public async Task<IEnumerable<Login>> GetLogins()
 {
     IEnumerable<Login> logins;
     using (var conn = new SqlConnection(_connectionString))
     {
         logins = await conn.QueryAsync<Login>("select * from dbo.Login");
     }
     return logins;
 }

        public async Task<Note> GetNoteByID( int id ) {
            using ( SqlConnection conn = new SqlConnection( connstring ) ) {
                string query = "select n.NoteID, n.NoteTypeID, s.SubName, n.Submitter, n.Message, n.AppliesToUsername, n.Url, n.Timestamp, n.ParentSubreddit "
                        + " from Notes n inner join Subreddits s on s.SubredditID = n.SubredditID "
                        + " where n.NoteID = @noteid";

                return ( await conn.QueryAsync<Note>( query, new { noteid = id } ) ).Single();
            }
        }

        public async Task<IEnumerable<NoteType>> GetNoteTypesForSubs( IEnumerable<string> subredditNames ) {
            using ( SqlConnection con = new SqlConnection( constring ) ) {
                string query = "select nt.NoteTypeID,s.SubName,nt.DisplayName,nt.ColorCode,nt.DisplayOrder,nt.Bold,nt.Italic from NoteTypes nt "
                        + " inner join Subreddits s on s.SubredditID = nt.SubredditID"
                        + " where s.SubName in @subs and nt.Disabled = 0";

                return await con.QueryAsync<NoteType>( query, new { subs = subredditNames } );
            }
        }

 public async Task<IEnumerable<UploadDto>> GetUploads(int parentId)
 {
     var q = @" SELECT * FROM  [dbo].[Upload] WHERE [email protected]";
     using (var con = new SqlConnection(ConnectionString))
     {
         con.Open();
         return await con.QueryAsync<UploadDto>(q,new { parentId });
     }
 }

 public async Task<bool> UserHasNotes(IEnumerable<string> subnames, string username ) {
     using ( SqlConnection conn = new SqlConnection( connstring ) ) {
         string query = "select count(*) "
                + " from Notes n inner join Subreddits s on s.SubredditID = n.SubredditID "
                + " where s.SubName in @subnames and n.AppliesToUsername = @username";
         int count = (await conn.QueryAsync<int>( query, new { subnames, username } )).FirstOrDefault();
         return count > 0;
     }
 }

        public async Task<IEnumerable<Note>> GetNotesForSubs( IEnumerable<string> subnames, IEnumerable<string> users ) {
            using ( SqlConnection conn = new SqlConnection( connstring ) ) {
                string query = "select n.NoteID, n.NoteTypeID, s.SubName, n.Submitter, n.Message, n.AppliesToUsername, n.Url, n.Timestamp, n.ParentSubreddit "
                        + " from Notes n inner join Subreddits s on s.SubredditID = n.SubredditID "
                        + " where s.SubName in @subnames and n.AppliesToUsername in @users";

                return await conn.QueryAsync<Note>( query, new { subnames, users } );
            }
        }

 public async Task<IEnumerable<TestData>> AllAsync()
 {
     using (SqlConnection connection = new SqlConnection(AppConfiguration.ConnectionString))
     {
         connection.Open();
         IEnumerable<TestData> result = await connection.QueryAsync<TestData>(getAllCommandText);
         return result;
     }
 }

        public async Task<IEnumerable<string>> GetUsersWithNotes( IEnumerable<string> subnames ) {
            using ( SqlConnection conn = new SqlConnection( connstring ) ) {
                string query = "select distinct n.AppliesToUsername "
                       + " from Notes n inner join Subreddits s on s.SubredditID = n.SubredditID "
                       + " where s.SubName in @subnames";

                return await conn.QueryAsync<string>( query, new { subnames } );
            }
        }

 static async Task<IEnumerable<dynamic>> QueryDatabase()
 {
     using (var connection = new SqlConnection("Data Source=localhost;Initial Catalog=Test;User ID=sa;Password=password;"))
     {
         await connection.OpenAsync();
         var data = await connection.QueryAsync<dynamic>("select * from dbo.DataSet");
         return data;
     }
 }

 public async Task<NoteType> GetNoteType( int id ) {
     using ( SqlConnection con = new SqlConnection( constring ) ) {
         string query = "select nt.NoteTypeID,s.SubName,nt.DisplayName,nt.ColorCode,nt.DisplayOrder,nt.Bold,nt.Italic from NoteTypes nt "
                 + " inner join Subreddits s on s.SubredditID = nt.SubredditID"
                 + " where NoteTypeID = @id";
         NoteType ntype = ( await con.QueryAsync<NoteType>( query, new { @id } ) ).First();
         return ntype;
     }
 }

        public async Task<Blog> GetAsync()
        {
            var sql = $"SELECT * FROM [Blog];";

            using(var connection = new SqlConnection(ConnectionString))
            {
                var objs = await connection.QueryAsync<Blog>(sql);
                return objs.FirstOrDefault();
            }
        }

        public async Task<IEnumerable<NameValueItem>> GetPriorities()
        {
            using (var con = new SqlConnection(ConnectionString))
            {
                con.Open();
                var projects = await con.QueryAsync<NameValueItem>("SELECT * from Priority  WITH (NOLOCK)");
                return projects;
            }

        }