public async Task<IEnumerable<Subreddit>> GetSubreddits( IEnumerable<string> subnames ) { using ( SqlConnection conn = new SqlConnection( connstring ) ) { string query = "select s.SubredditID, s.SubName, s.Active, s.DirtbagUrl, s.DirtbagUsername, s.DirtbagPassword, "+ "ss.AccessMask, ss.TempBanID, ss.PermBanID, " + "nt.NoteTypeID, s.SubName,nt.DisplayName,nt.ColorCode,nt.DisplayOrder,nt.Bold,nt.Italic " + "from Subreddits s " + "left join SubredditSettings ss on ss.SubRedditID = s.SubredditID " + "left join NoteTypes nt on nt.SubredditID = s.SubredditID " + "where s.SubName in @subnames and nt.Disabled = 0"; var lookup = new Dictionary<int, Subreddit>(); var result = await conn.QueryAsync<Subreddit, DirtbagSettings, SubredditSettings, NoteType, Subreddit>( query, ( s, bs, ss, nt ) => { Subreddit sub; if ( !lookup.TryGetValue( s.SubredditID, out sub ) ) { lookup.Add( s.SubredditID, sub = s ); } if ( ss == null && sub.Settings == null ) { ss = new SubredditSettings(); sub.Settings = ss; } else if ( sub.Settings == null ) { sub.Settings = ss; } sub.BotSettings = bs; sub.Settings.NoteTypes.Add( nt ); return sub; }, splitOn: "DirtbagUrl,AccessMask,NoteTypeID", param: new { subnames } ); //string noteTypeQuery = "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 @subnames"; //var noteTypes = con.Query<NoteType>(noteTypeQuery, new { subnames }); return lookup.Values.AsEnumerable(); } }
public async Task<List<Subreddit>> GetActiveSubs() { using ( SqlConnection conn = new SqlConnection( connstring ) ) { string query = "select s.SubredditID, s.SubName, s.Active, ss.AccessMask, ss.TempBanID, ss.PermBanID from Subreddits s left join " + "SubredditSettings ss on ss.SubRedditID = s.SubredditID " + "where active = 1"; var result = await conn.QueryAsync<Subreddit, SubredditSettings, Subreddit>( query, ( s, ss ) => { if ( ss == null ) { ss = new SubredditSettings(); } s.Settings = ss; return s; }, splitOn: "AccessMask" ); return result.ToList(); } }