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; } }
public async Task<Note> AddNoteToCabal(Note anote, string cabalSub ) { using ( SqlConnection conn = new SqlConnection( connstring ) ) { string query = @" INSERT INTO Notes(NoteTypeID, SubredditID, Submitter, Message, AppliesToUsername, Url, Timestamp, ParentSubreddit ) OUTPUT INSERTED.NoteID, INSERTED.NoteTypeID, @CabalSub as 'SubName', INSERTED.Submitter, INSERTED.Message, INSERTED.AppliesToUsername, INSERTED.Url, INSERTED.Timestamp, INSERTED.ParentSubreddit SELECT @NoteTypeID, (SELECT SubredditID from Subreddits where SubName = @CabalSub), @Submitter, n.Message, n.AppliesToUsername, n.Url, @Timestamp, @OriginalSubreddit FROM Notes n INNER JOIN Subreddits s on s.SubredditID = n.SubredditID WHERE n.NoteID = @NoteID AND s.SubName like @OriginalSubreddit "; Note insertedNote = ( await conn.QueryAsync<Note>( query, new { anote.NoteID, anote.NoteTypeID, CabalSub = cabalSub, anote.Submitter, anote.Timestamp, OriginalSubreddit = anote.SubName } ) ).Single(); return insertedNote; } }
/// <summary> /// Returns True if user has no more notes in subreddit /// </summary> /// <param name="anote"></param> /// <param name="uname"></param> /// <returns></returns> public async Task<bool> DeleteNoteForUser( Note anote, string uname ) { using ( SqlConnection conn = new SqlConnection( connstring ) ) { string query = @" delete n OUTPUT GETUTCDATE() as 'HistTimestamp','D' as 'HistAction',@uname as 'HistUser', DELETED.NoteID, DELETED.NoteTypeID, DELETED.SubRedditID,DELETED.Submitter,DELETED.Message,DELETED.AppliesToUsername,DELETED.URL,DELETED.TimeStamp,DELETED.ParentSubreddit into Notes_History from Notes n INNER JOIN Subreddits sr on n.SubredditID = sr.SubredditID where NoteID = @noteid and sr.SubName = @subname; Select count(*) from Notes n INNER JOIN Subreddits sr on n.SubredditID = sr.SubredditID WHERE n.AppliesToUsername = @AppliesToUsername and sr.SubName = @subname; "; int count = (await conn.QueryAsync<int>( query, new { anote.NoteID, anote.SubName, uname, anote.AppliesToUsername } )).FirstOrDefault(); return count == 0; } }