public Task <AsyncResult <string> > GetUserAdditionalDataAsync(Guid profileId, string key, int dataNo = 0)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
int dataIndex;
using (IDbCommand command = UserProfileDataManagement_SubSchema.SelectUserAdditionalDataCommand(transaction, profileId,
key, dataNo, out dataIndex))
{
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
var data = database.ReadDBValue <string>(reader, dataIndex);
return(Task.FromResult(new AsyncResult <string>(true, data)));
}
}
}
return(Task.FromResult(new AsyncResult <string>(false, null)));
}
finally
{
transaction.Dispose();
}
}
protected ICollection <UserProfile> GetProfiles(Guid?profileId, string name)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
int profileIdIndex;
int nameIndex;
using (IDbCommand command = UserProfileDataManagement_SubSchema.SelectUserProfilesCommand(transaction, profileId, name,
out profileIdIndex, out nameIndex))
{
ICollection <UserProfile> result = new List <UserProfile>();
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Guid profileId_ = database.ReadDBValue <Guid>(reader, profileIdIndex);
string name_ = database.ReadDBValue <string>(reader, nameIndex);
result.Add(new UserProfile(profileId_, name_));
}
}
return(result);
}
}
finally
{
transaction.Dispose();
}
}
public async Task <Guid> CreateProfileAsync(string profileName)
{
//Profile might already exist.
var result = await GetProfileByNameAsync(profileName);
if (result.Success)
{
return(result.Result.ProfileId);
}
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
Guid profileId = Guid.NewGuid();
try
{
using (IDbCommand command = UserProfileDataManagement_SubSchema.CreateUserProfileCommand(transaction, profileId, profileName))
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception e)
{
ServiceRegistration.Get <ILogger>().Error("UserProfileDataManagement: Error creating user profile '{0}')", e, profileName);
transaction.Rollback();
throw;
}
return(profileId);
}
public bool GetUserAdditionalData(Guid profileId, string key, out string data)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
int dataIndex;
using (IDbCommand command = UserProfileDataManagement_SubSchema.SelectUserAdditionalDataCommand(transaction, profileId,
key, out dataIndex))
{
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
data = database.ReadDBValue <string>(reader, dataIndex);
return(true);
}
}
}
data = null;
return(false);
}
finally
{
transaction.Dispose();
}
}
public Task <bool> SetUserMediaItemDataAsync(Guid profileId, Guid mediaItemId, string key, string data)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
bool result;
using (IDbCommand command = UserProfileDataManagement_SubSchema.DeleteUserMediaItemDataCommand(transaction, profileId, mediaItemId, key))
command.ExecuteNonQuery();
// Allow "delete only", if new data is null. This is used to delete no longer required data.
if (!string.IsNullOrEmpty(data))
{
using (IDbCommand command = UserProfileDataManagement_SubSchema.CreateUserMediaItemDataCommand(transaction, profileId, mediaItemId, key, data))
result = command.ExecuteNonQuery() > 0;
}
else
{
result = true;
}
transaction.Commit();
return(Task.FromResult(result));
}
catch (Exception e)
{
ServiceRegistration.Get <ILogger>().Error("UserProfileDataManagement: Error setting media item data '{0}' for media item '{1}' in profile '{2}'", e, key, mediaItemId, profileId);
transaction.Rollback();
throw;
}
}
public Guid CreateProfile(string profileName)
{
//Profile might already exist.
UserProfile existingProfile;
if (GetProfileByName(profileName, out existingProfile))
{
return(existingProfile.ProfileId);
}
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
Guid profileId = Guid.NewGuid();
try
{
using (IDbCommand command = UserProfileDataManagement_SubSchema.CreateUserProfileCommand(transaction, profileId, profileName))
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception e)
{
ServiceRegistration.Get <ILogger>().Error("UserProfileDataManagement: Error creating user profile '{0}')", e, profileName);
transaction.Rollback();
throw;
}
return(profileId);
}
public Task <bool> ChangeProfileIdAsync(Guid profileId, Guid newProfileId)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
bool result;
int nameIndex;
string profileName;
using (IDbCommand command = UserProfileDataManagement_SubSchema.SelectUserProfileNameCommand(transaction, profileId, out nameIndex))
{
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
profileName = database.ReadDBValue <string>(reader, nameIndex);
}
else
{
transaction.Rollback();
return(Task.FromResult(false));
}
}
}
using (IDbCommand command = UserProfileDataManagement_SubSchema.UpdateUserProfileNameCommand(transaction, profileId, profileName + "_old"))
command.ExecuteNonQuery();
using (IDbCommand command = UserProfileDataManagement_SubSchema.CopyUserProfileCommand(transaction, profileId, newProfileId, profileName))
result = command.ExecuteNonQuery() > 0;
using (IDbCommand command = UserProfileDataManagement_SubSchema.CopyUserMediaItemDataCommand(transaction, profileId, newProfileId))
command.ExecuteNonQuery();
using (IDbCommand command = UserProfileDataManagement_SubSchema.CopyUserPlaylistDataCommand(transaction, profileId, newProfileId))
command.ExecuteNonQuery();
using (IDbCommand command = UserProfileDataManagement_SubSchema.CopyUserAdditionalDataCommand(transaction, profileId, newProfileId))
command.ExecuteNonQuery();
using (IDbCommand command = UserProfileDataManagement_SubSchema.DeleteUserProfileCommand(transaction, profileId))
command.ExecuteNonQuery();
transaction.Commit();
return(Task.FromResult(result));
}
catch (Exception e)
{
ServiceRegistration.Get <ILogger>().Error("UserProfileDataManagement: Error changing profile Id '{0}'", e, profileId);
transaction.Rollback();
throw;
}
}
public Task <AsyncResult <IEnumerable <Tuple <string, int, string> > > > GetUserSelectedAdditionalDataListAsync(Guid profileId, string[] keys, bool sortByKey = false, SortDirection sortDirection = SortDirection.Ascending,
uint?offset = null, uint?limit = null)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
int dataNoIndex;
int dataIndex;
int keyIndex;
List <Tuple <string, int, string> > list = new List <Tuple <string, int, string> >();
using (IDbCommand command = UserProfileDataManagement_SubSchema.SelectUserAdditionalDataListCommand(transaction, profileId,
keys, sortByKey, sortDirection, out keyIndex, out dataNoIndex, out dataIndex))
{
using (IDataReader reader = command.ExecuteReader())
{
var records = reader.AsEnumerable();
if (offset.HasValue)
{
records = records.Skip((int)offset.Value);
}
if (limit.HasValue)
{
records = records.Take((int)limit.Value);
}
foreach (var record in records)
{
list.Add(new Tuple <string, int, string>(database.ReadDBValue <string>(record, keyIndex), database.ReadDBValue <int>(record, dataNoIndex),
database.ReadDBValue <string>(record, dataIndex)));
}
}
}
IEnumerable <Tuple <string, int, string> > data = null;
if (list.Count > 0)
{
data = list;
}
return(Task.FromResult(new AsyncResult <IEnumerable <Tuple <string, int, string> > >(data != null, data)));
}
finally
{
transaction.Dispose();
}
}
private Guid CreateProfileInternal(Guid profileId, string profileName, UserProfileType profileType, string profilePassword)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
using (IDbCommand command = UserProfileDataManagement_SubSchema.CreateUserProfileCommand(transaction, profileId, profileName, profileType, profilePassword))
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception e)
{
ServiceRegistration.Get <ILogger>().Error("UserProfileDataManagement: Error creating user profile '{0}')", e, profileName);
transaction.Rollback();
throw;
}
return(profileId);
}
public Task <bool> ClearUserMediaItemDataKeyAsync(Guid profileId, string key)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
using (IDbCommand command = UserProfileDataManagement_SubSchema.DeleteUserMediaItemDataCommand(transaction, profileId, null, key))
command.ExecuteNonQuery();
transaction.Commit();
return(Task.FromResult(true));
}
catch (Exception e)
{
ServiceRegistration.Get <ILogger>().Error("UserProfileDataManagement: Error clearing user media item data for profile '{0}'", e, profileId);
transaction.Rollback();
throw;
}
}
public bool DeleteProfile(Guid profileId)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
bool result;
using (IDbCommand command = UserProfileDataManagement_SubSchema.DeleteUserProfileCommand(transaction, profileId))
result = command.ExecuteNonQuery() > 0;
transaction.Commit();
return(result);
}
catch (Exception e)
{
ServiceRegistration.Get <ILogger>().Error("UserProfileDataManagement: Error deleting profile '{0}'", e, profileId);
transaction.Rollback();
throw;
}
}
public Task <bool> LoginProfileAsync(Guid profileId)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
bool result;
using (IDbCommand command = UserProfileDataManagement_SubSchema.LoginUserProfileCommand(transaction, profileId))
result = command.ExecuteNonQuery() > 0;
transaction.Commit();
return(Task.FromResult(result));
}
catch (Exception e)
{
ServiceRegistration.Get <ILogger>().Error("UserProfileDataManagement: Error logging in profile '{0}'", e, profileId);
transaction.Rollback();
throw;
}
}
public Task <bool> UpdateProfileAsync(Guid profileId, string profileName, UserProfileType profileType, string profilePassword)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
bool result;
using (IDbCommand command = UserProfileDataManagement_SubSchema.UpdateUserProfileCommand(transaction, profileId, profileName, profileType, profilePassword))
result = command.ExecuteNonQuery() > 0;
transaction.Commit();
return(Task.FromResult(result));
}
catch (Exception e)
{
ServiceRegistration.Get <ILogger>().Error("UserProfileDataManagement: Error creating user profile '{0}')", e, profileName);
transaction.Rollback();
throw;
}
}
public bool SetUserAdditionalData(Guid profileId, string key, string data)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
bool result;
using (IDbCommand command = UserProfileDataManagement_SubSchema.DeleteUserAdditionalDataCommand(transaction, profileId, key))
command.ExecuteNonQuery();
using (IDbCommand command = UserProfileDataManagement_SubSchema.CreateUserAdditionalDataCommand(transaction, profileId, key, data))
result = command.ExecuteNonQuery() > 0;
transaction.Commit();
return(result);
}
catch (Exception e)
{
ServiceRegistration.Get <ILogger>().Error("UserProfileDataManagement: Error setting additional data '{0}' in profile '{1}'", e, key, profileId);
transaction.Rollback();
throw;
}
}
public Task <bool> SetUserPlaylistDataAsync(Guid profileId, Guid playlistId, string key, string data)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
bool result;
using (IDbCommand command = UserProfileDataManagement_SubSchema.DeleteUserPlaylistDataCommand(transaction, profileId, playlistId, key))
command.ExecuteNonQuery();
using (IDbCommand command = UserProfileDataManagement_SubSchema.CreateUserPlaylistDataCommand(transaction, profileId, playlistId, key, data))
result = command.ExecuteNonQuery() > 0;
transaction.Commit();
return(Task.FromResult(result));
}
catch (Exception e)
{
ServiceRegistration.Get <ILogger>().Error("UserProfileDataManagement: Error setting playlist data '{0}' for playlist '{1}' in profile '{2}'", e, key, playlistId, profileId);
transaction.Rollback();
throw;
}
}
public bool ClearAllUserData(Guid profileId)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
using (IDbCommand command = UserProfileDataManagement_SubSchema.DeleteUserPlaylistDataCommand(transaction, profileId, null, null))
command.ExecuteNonQuery();
using (IDbCommand command = UserProfileDataManagement_SubSchema.DeleteUserMediaItemDataCommand(transaction, profileId, null, null))
command.ExecuteNonQuery();
using (IDbCommand command = UserProfileDataManagement_SubSchema.DeleteUserAdditionalDataCommand(transaction, profileId, null))
command.ExecuteNonQuery();
transaction.Commit();
return(true);
}
catch (Exception e)
{
ServiceRegistration.Get <ILogger>().Error("UserProfileDataManagement: Error clearing user data for profile '{0}'", e, profileId);
transaction.Rollback();
throw;
}
}
//TODO: DbCommand Async call?
protected Task <ICollection <UserProfile> > GetProfiles(Guid?profileId, string name, bool loadData = true)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
int profileIdIndex;
int nameIndex;
int idIndex;
int dataIndex;
int lastLoginIndex;
int imageIndex;
ICollection <UserProfile> result = new List <UserProfile>();
using (IDbCommand command = UserProfileDataManagement_SubSchema.SelectUserProfilesCommand(transaction, profileId, name,
out profileIdIndex, out nameIndex, out idIndex, out dataIndex, out lastLoginIndex, out imageIndex))
{
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
result.Add(new UserProfile(
database.ReadDBValue <Guid>(reader, profileIdIndex),
database.ReadDBValue <string>(reader, nameIndex),
(UserProfileType)database.ReadDBValue <int>(reader, idIndex),
database.ReadDBValue <string>(reader, dataIndex),
database.ReadDBValue <DateTime?>(reader, lastLoginIndex),
database.ReadDBValue <byte[]>(reader, imageIndex))
);
}
}
}
if (loadData)
{
foreach (var user in result)
{
using (IDbCommand command = UserProfileDataManagement_SubSchema.SelectUserAdditionalDataListCommand(transaction, user.ProfileId, null, false, SortDirection.Ascending,
out nameIndex, out profileIdIndex, out dataIndex))
{
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string key = database.ReadDBValue <string>(reader, nameIndex);
if (!user.AdditionalData.ContainsKey(key))
{
user.AdditionalData.Add(key, new Dictionary <int, string>());
}
user.AdditionalData[key].Add(database.ReadDBValue <int>(reader, profileIdIndex), database.ReadDBValue <string>(reader, dataIndex));
}
}
}
}
}
return(Task.FromResult(result));
}
finally
{
transaction.Dispose();
}
}
