/// <summary>
/// Retrieve the list of storage defined.
/// </summary>
/// <returns></returns>
public List <Storage> GetListOfStorage()
{
DbConnection connection = new DbConnection(DatabaseSettings.ConnectionString);
DbAction action = connection.GetDbAction();
DataSet ds = null;
List <Storage> listOfStorage = new List <Storage>();
if (action.ExecDs(connection.GetConnection(), "SELECT SDCardId, SDCardDescription, SDCardSize FROM tblSDCards ORDER BY SDCardDescription", ref ds))
{
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
Storage storage = new Storage();
storage.ReadRecord(row);
listOfStorage.Add(storage);
}
}
return(listOfStorage);
}
LastMessage = action.LastMessage;
return(null);
}
internal void ReadRecord(DataRow pDataRow)
{
StorageId = Convert.ToInt32(pDataRow["SDCardId"]);
StorageDescription = pDataRow["SDCardDescription"].ToString();
StorageSize = pDataRow["SDCardSize"].ToString();
DbConnection connection = new DbConnection(DatabaseSettings.ConnectionString);
DbAction action = connection.GetDbAction();
DataSet ds = null;
if (action.ExecDs(connection.GetConnection(), "SELECT SDCardId, PlaylistId FROM tblSDCardPlaylists WHERE SDCardId = " + StorageId, ref ds))
{
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
Playlists.Add(row["PlaylistId"].ToString());
}
}
}
else
{
LastMessage = action.LastMessage;
}
}
/// <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>
/// Find the storage.
/// </summary>
/// <param name="storageId"></param>
/// <returns></returns>
public bool Find(int storageId)
{
bool functionReturnValue = false;
DbConnection connection = new DbConnection(DatabaseSettings.ConnectionString);
DbAction action = connection.GetDbAction();
DataSet ds = null;
if (action.ExecDs(connection.GetConnection(), "SELECT SDCardId, SDCardDescription, SDCardSize FROM tblSDCards WHERE SDCardId = " + storageId, ref ds))
{
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
ReadRecord(ds.Tables[0].Rows[0]);
functionReturnValue = true;
}
}
else
{
LastMessage = action.LastMessage;
}
return(functionReturnValue);
}
/// <summary>
/// Find a setting.
/// </summary>
/// <param name="setting"></param>
/// <returns></returns>
public bool Find(AppSetting setting)
{
bool functionReturnValue = false;
DbConnection connection = new DbConnection(DatabaseSettings.ConnectionString);
DbAction action = connection.GetDbAction();
DataSet ds = null;
if (action.ExecDs(connection.GetConnection(), "SELECT SettingId, SettingValue FROM tblSettings WHERE SettingId = " + setting, ref ds))
{
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
ReadRecord(ds.Tables[0].Rows[0]);
// Successful read has happened
functionReturnValue = true;
}
}
else
{
LastMessage = action.LastMessage;
}
return(functionReturnValue);
}
/// <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);
}
