/// <summary>
/// Update a setting.
/// </summary>
/// <returns></returns>
public bool Update(AppSetting setting)
{
// Set default value so visual studio doesn't moan
bool functionReturnValue = false;
DbConnection connection = new DbConnection(DatabaseSettings.ConnectionString);
DbAction action = connection.GetDbAction();
DataSet ds = null;
// Establish whether we need to insert or update
if (action.ExecDs(connection.GetConnection(), "SELECT SettingId FROM tblSettings", ref ds))
{
// If there is a record we need to update, otherwise insert
if (ds.Tables[0].Rows.Count > 0)
{
if (action.ExecNonQuery(connection.GetConnection(), "UPDATE tblSettings SET SettingValue = @P0 WHERE SettingId = @P1", setting, SettingValue))
{
// Insert successful
functionReturnValue = true;
}
else
{
LastMessage = action.LastMessage;
}
}
else
{
if (action.ExecNonQuery(connection.GetConnection(), "INSERT INTO tblSettings (SettingId, SettingValue) VALUES (@P0, @P1)", setting, SettingValue))
{
// Insert successful
functionReturnValue = true;
}
else
{
LastMessage = action.LastMessage;
}
}
}
else
{
LastMessage = action.LastMessage;
}
return(functionReturnValue);
}
/// <summary>
/// Delete the storage.
/// </summary>
/// <returns></returns>
public bool Delete()
{
DbConnection connection = new DbConnection(DatabaseSettings.ConnectionString);
DbAction action = connection.GetDbAction();
if (!action.ExecNonQuery(connection.GetConnection(), "DELETE FROM tblSDCardPlaylists WHERE SDCardId = " + StorageId))
{
LastMessage = action.LastMessage;
return(false);
}
if (!action.ExecNonQuery(connection.GetConnection(), "DELETE FROM tblSDCards WHERE SDCardId = " + StorageId))
{
LastMessage = action.LastMessage;
return(false);
}
// Return success.
return(true);
}
/// <summary>
/// Update the details of the storage.
/// </summary>
/// <returns></returns>
public bool Update()
{
DbConnection connection = new DbConnection(DatabaseSettings.ConnectionString);
DbAction action = connection.GetDbAction();
bool functionReturnValue = false;
// If a new record...
if (StorageId == 0)
{
// Get a new unique ID.
object returnValue = null;
if (action.ExecScalar(connection.GetConnection(), "SELECT MAX(SDCardId) FROM tblSDCards", ref returnValue))
{
// If table is empty, go to 1, otherwise go to the next Id value.
if (ReferenceEquals(returnValue, DBNull.Value))
{
StorageId = 1;
}
else
{
StorageId = Convert.ToInt32(returnValue) + 1;
}
if (action.ExecNonQuery(connection.GetConnection(), "INSERT INTO tblSDCards (SDCardId, SDCardDescription, SDCardSize) VALUES (@P0, @P1, @P2)", StorageId, StorageDescription, StorageSize))
{
functionReturnValue = true;
}
else
{
LastMessage = action.LastMessage;
}
}
else
{
LastMessage = action.LastMessage;
}
}
else
{
if (action.ExecNonQuery(connection.GetConnection(), "UPDATE tblSDCards SET SDCardDescription = @P0, SDCardSize = @P1 WHERE SDCardId = @P2", StorageDescription, StorageSize, StorageId))
{
functionReturnValue = true;
}
else
{
LastMessage = action.LastMessage;
}
}
// If the update happened successfully, now insert the list of tracks.
if (functionReturnValue)
{
DataSet ds = null;
// Remove any playlists from the database that are NOT in what is to be saved.
if (action.ExecDs(connection.GetConnection(), "SELECT SDCardId, PlaylistId FROM tblSDCardPlaylists WHERE SDCardId = " + StorageId, ref ds) && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows.Cast <DataRow>().Where(row => !Playlists.Contains(row["PlaylistId"].ToString())))
{
action.ExecNonQuery(connection.GetConnection(), "DELETE FROM tblSDCardPlaylists WHERE SDCardId = @P0 AND PlaylistId = @P1", StorageId, row["PlaylistId"].ToString());
}
}
// If playlist record does not exist, insert it.
foreach (string playlistId in Playlists.Where(playlistId => action.ExecDs(connection.GetConnection(), "SELECT SDCardId, PlaylistId FROM tblSDCardPlaylists WHERE SDCardId = " + StorageId + " AND PlaylistId = '" + playlistId + "'", ref ds) && ds.Tables[0].Rows.Count == 0))
{
action.ExecNonQuery(connection.GetConnection(), "INSERT INTO tblSDCardPlaylists (SDCardId, PlaylistId) VALUES (@P0, @P1)", StorageId, playlistId);
}
}
return(functionReturnValue);
}