Example #1
0
        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;
 }
Example #5
0
        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();
            }
        }
Example #6
0
        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 ParentId=@parentId";
     using (var con = new SqlConnection(ConnectionString))
     {
         con.Open();
         return await con.QueryAsync<UploadDto>(q,new { parentId });
     }
 }
Example #8
0
 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;
     }
 }
Example #9
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 } );
            }
        }
Example #10
0
 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;
     }
 }
Example #11
0
        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 } );
            }
        }
Example #12
0
 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;
     }
 }
Example #13
0
 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;
     }
 }
Example #14
0
        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;
            }

        }
 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();
     }
 }
Example #17
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();
     }
 }
Example #18
0
        public async Task<DirtbagSettings> GetBotSettings(string subName ) {
            using (SqlConnection conn = new SqlConnection( connstring ) ) {
                string query = @"
select s.DirtbagUrl, s.DirtbagUsername, s.DirtbagPassword
FROM Subreddits s
WHERE s.SubName = @subName
";
                return ( await conn.QueryAsync<DirtbagSettings>( query, new { subName } ) ).FirstOrDefault();
            }
        }
 public async Task<EmailTemplateDto> GetEmailTemplate(string name)
 {
     var q = @"SELECT Name,EmailBody,EmailSubject as Subject FROM EmailTemplate  WITH (NOLOCK) WHERE Name=@name";
     using (var con = new SqlConnection(ConnectionString))
     {
         con.Open();
         var teams = await con.QueryAsync<EmailTemplateDto>(q, new { @name = name });
         return teams.FirstOrDefault();
     }
 }
Example #20
0
        /// <summary>
        /// Create a role
        /// </summary>
        /// <param name="role">The role to create</param>
        public async Task Insert(Role role)
        {
            using (var sqlConnection = new SqlConnection(applicationDatabaseConfiguration.GetConnectionString()))
            {
                await sqlConnection.OpenAsync();

                var id = await sqlConnection.QueryAsync<int>(RoleTsql.Insert, role);
                role.Id = id.Single();
            }
        }
Example #21
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();
     }
 }
 public async Task<int> SaveUpload(UploadDto uploadDto)
 {
     var q =
       @"INSERT INTO [dbo].[Upload](Filename,Type,Url,CreatedDate,CreatedById,ParentId) VALUES(@fileName,@Type,@Url,@CreatedDate,@CreatedById,@ParentId);SELECT CAST(SCOPE_IDENTITY() as int)";
     using (var con = new SqlConnection(ConnectionString))
     {
         con.Open();
         var ss = await con.QueryAsync<int>(q,uploadDto);
         return ss.Single();
     }
 }
Example #23
0
        /// <summary>
        /// Get all the roles
        /// </summary>
        /// <returns>A list of role names</returns>
        public async Task<List<Role>> GetRoles()
        {
            using (var sqlConnection = new SqlConnection(applicationDatabaseConfiguration.GetConnectionString()))
            {
                await sqlConnection.OpenAsync();

                var roleEntities = await sqlConnection.QueryAsync<Role>(RoleTsql.GetAll);

                return roleEntities.ToList();
            }
        }
Example #24
0
 public async Task<IEnumerable<Reason>> GetReasons()
 {
     using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString))
     {
         return (await connection.QueryAsync("select Id, Description, Fields from Reasons")).Select(a => new Reason
         {
             Id = a.Id,
             Description = a.Description,
             Fields = FieldsXmlHelpers.FieldInfoFromXml((string)a.Fields).ToArray()
         });
     }
 }
 public async Task<UserAccountDto> GetUser(string email)
 {
     var q = @"SELECT [ID],[FirstName] as Name ,[EmailAddress],Password,[Avatar] as GravatarUrl ,[DefaultTeamID]
             FROM [dbo].[User] 
             WHERE EmailAddress=@id";
     using (var con = new SqlConnection(ConnectionString))
     {
         con.Open();
         var com = await con.QueryAsync<UserAccountDto>(q, new { @id = email });
         return com.FirstOrDefault();
     }
 }
 public async Task<IEnumerable<UserDto>> GetSubscribers(int teamId, string notificationTypeCode)
 {
     const string q = @"SELECT U.ID,U.FirstName AS Name,U.EmailAddress 
             FROM UserNotificationSubscription UNS WITH (NOLOCK) 
             JOIN [User] U  WITH (NOLOCK)  ON U.ID=UNS.UserID
             JOIN NotificationType NT WITH (NOLOCK) ON NT.Id=UNS.NotificationTypeID
             WHERE NT.Code=@notificationTypeCode AND TeamId=@teamId";
     using (var con = new SqlConnection(ConnectionString))
     {
         con.Open();
         return await con.QueryAsync<UserDto>(q, new { notificationTypeCode, teamId });
     }
 }
Example #27
0
        public async Task<DbSchema> BuildSchemaAsync()
        {
			if (schema != null)
				return schema;

            List<DbObjectInfo> objectInfoList = null;
            List<DbObjectReferenceRow> referenceTable = null;

            //get reference table from sql
            using (IDbConnection conn = new SqlConnection(sqlConnString))
            {
                objectInfoList = (await conn.QueryAsync<DbObjectInfo>(getQuery_SpData())).ToList();
                referenceTable = (await conn.QueryAsync<DbObjectReferenceRow>(getQuery_SpReferenceTable())).ToList();
            }
            
            //refTable complete
			return schema = new DbSchema 
            {
                Objects = objectInfoList,
                References = referenceTable,
            };
        }
Example #28
0
        public async Task<Note> AddNoteForUser( Note anote ) {
            //anote.AppliesToUsername = anote.AppliesToUsername.ToLower();
            using ( SqlConnection conn = new SqlConnection( connstring ) ) {
                string query = "insert into Notes(NoteTypeID,SubredditID,Submitter,Message,AppliesToUsername, n.Url, n.Timestamp) "
                    + " values (@NoteTypeID,(select SubredditID from Subreddits where SubName = @SubName),@Submitter,@Message,@AppliesToUsername, @Url, @Timestamp);"
                    + " select n.NoteID, n.NoteTypeID, s.SubName, n.Submitter, n.Message, n.AppliesToUsername, n.Url, n.Timestamp "
                        + " from Notes n inner join Subreddits s on s.SubredditID = n.SubredditID "
                        + " where n.NoteID = cast(SCOPE_IDENTITY() as int) ";
                Note insertedNote = ( await conn.QueryAsync<Note>( query, new { anote.NoteTypeID, anote.SubName, anote.Submitter, anote.Message, anote.AppliesToUsername, anote.Url, anote.Timestamp } ) ).Single();

                return insertedNote;
            }
        }
Example #29
0
 private async static Task QueryAsync()
 {
     using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=DapperTryDb;Integrated Security=true;Asynchronous Processing=True"))
     {
         await conn.OpenAsync();
         using (SqlTransaction trans = conn.BeginTransaction())
         {
             IEnumerable<Person> people = await conn.QueryAsync<Person>("SELECT * FROM People WHERE Age = @Age", new { Age = 17 }, trans);
             foreach (Person person in people)
             {
                 Console.WriteLine(person);
             }
         }
     }
 }
 public async Task<int> SaveTeamMemberRequest(AddTeamMemberRequestVM teamMemberRequest)
 {
     var q = @"INSERT INTO TeamMemberRequest(EmailAddress,TeamId,ActivationCode,CreatedByID,CreatedDate) VALUES(@email,@teamId,@a,@userId,@dt);;SELECT CAST(SCOPE_IDENTITY() as int)";
     using (var con = new SqlConnection(ConnectionString))
     {
         var a = Guid.NewGuid().ToString("n").Replace("-", "");
         con.Open();
         var p = await con.QueryAsync<int>(q, new
         {
             @teamId = teamMemberRequest.TeamId,
             @email = teamMemberRequest.EmailAddress,
             @a = a,
             @userId = teamMemberRequest.CreatedById,
             @dt = DateTime.Now
         });
         return p.First();
     }
 }