public async Task <int> Add(string emailAddress, string password, string givenName, string familyName, string profilePicturePath)
{
using (var con = _databaseConnectionProvider.New())
{
var createdDate = DateTime.UtcNow;
var profilePictureFileName = string.Empty;
var userProfileId = Convert.ToInt32(await con.ExecuteScalarAsync(@"
INSERT INTO
UserProfiles
(EmailAddress, Password, GivenName, FamilyName, DateCreated, DateUpdated, ProfilePicturePath, ProfilePictureFileName)
VALUES
(@emailAddress, @password, @givenName, @familyName, @createdDate, @createdDate, @profilePicturePath, @profilePictureFileName)
SELECT SCOPE_IDENTITY()", new
{
emailAddress,
password,
givenName,
familyName,
createdDate,
profilePicturePath,
profilePictureFileName
}));
return(userProfileId);
}
}
public async Task <UserProfile> GetByUserProfileId(int userProfileId)
{
using (var con = _databaseConnectionProvider.New())
{
var userProfile = await con.QuerySingleOrDefaultAsync <UserProfile>(@"SELECT * FROM UserProfiles WHERE Id = @userProfileId", new
{
userProfileId
});
return(userProfile);
}
}
public async Task <IEnumerable <TodoItem> > GetByListId(int listId)
{
using (var con = _databaseConnectionProvider.New())
{
var todoItems = await con.QueryAsync <TodoItem>(@"SELECT * FROM TodoItems WHERE ListId = @listId AND State != @deletedState", new
{
listId,
deletedState = TodoItemState.Deleted
});
return(todoItems);
}
}
public async Task <IEnumerable <List> > GetByState(int userProfileId, ListState state)
{
using (var con = _databaseConnectionProvider.New())
{
var lists = await con.QueryAsync <List>(@"SELECT * FROM Lists WHERE UserId = @userProfileId AND State = @state", new
{
userProfileId,
state
});
return(lists);
}
}
public async Task <int> Add(int userId, string name, string description, ListState state, string backgroundImageFilePath)
{
using (var con = _databaseConnectionProvider.New())
{
var createdDate = DateTime.UtcNow;
var listId = Convert.ToInt32(await con.ExecuteScalarAsync(@"
INSERT INTO
Lists
(UserId, Name, Description, State, DateCreated, DateUpdated, BackgroundImageFilePath)
VALUES
(@userId, @name, @description, @state, @createdDate, @createdDate, @backgroundImageFilePath)
SELECT SCOPE_IDENTITY()", new
{
userId,
name,
description,
state,
createdDate,
backgroundImageFilePath
}));
return(listId);
}
}
public async Task <int> Add(string title, TodoItemState state, int listId, int userProfileId)
{
using (var con = _databaseConnectionProvider.New())
{
var createdDate = DateTime.UtcNow;
var todoItemId = Convert.ToInt32(await con.ExecuteScalarAsync(@"
INSERT INTO
TodoItems
(Title, State, DateCreated, DateUpdated, ListId, UserProfileId)
VALUES
(@title, @state, @createdDate, @createdDate, @listId, @userProfileId)
SELECT SCOPE_IDENTITY()", new
{
title,
state,
createdDate,
listId,
userProfileId
}));
return(todoItemId);
}
}
public async Task DeleteUserProfile(int userProfileId)
{
try
{
using (var con = _databaseConnectionProvider.New())
{
con.Open();
var transaction = con.BeginTransaction();
try
{
await _userProfileWriter.DeleteUserProfile(userProfileId, transaction);
await _listWriter.DeleteListsByUserProfileId(userProfileId, transaction);
await _todoItemsWriter.DeleteTodoItemsByUserProfileId(userProfileId, transaction);
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
con.Close();
throw ex;
}
finally
{
con.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
}