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