/// <summary>
/// Retrieves dashboard DTO object for a given account id.
/// </summary>
/// <param name="accountId">The account id.</param>
/// <returns>The <see cref="DashboardDto" />.</returns>
public DashboardDto FetchDashboard(int accountId)
{
var result = new DashboardDto();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
const string CommandText = @"
SELECT [Id]
, [SelectedPageIndex]
FROM [dbo].[Dashboards]
WHERE [AccountId] = @accountId
";
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
using (var cmd = new SqlCommand(CommandText, cn))
{
cmd.Parameters.AddWithValue("@accountId", accountId);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
if (reader.Read())
{
result.Id = reader.GetInt32(0);
result.SelectedPageIndex = reader.GetInt32(1);
}
else return null;
}
}
ReadDashboardPages(result);
return result;
}
}
/// <summary>
/// Inserts new dashboard.
/// </summary>
/// <param name="dto">The dashboard DTO object.</param>
/// <exception cref="System.ArgumentException"></exception>
/// <exception cref="ArgumentException">Input is null.</exception>
public void InsertDashboard(DashboardDto dto)
{
if (dto == null)
throw new ArgumentException(Resources.DtoNullException);
const string CommandText =
@"
INSERT INTO [dbo].[Dashboards] (
[LastModifiedOn]
, [SelectedPageIndex]
, [AccountId])
VALUES (
@p_LastModifiedOn
, @p_SelectedPageIndex
, @p_AccountId);
SELECT [Id] FROM [dbo].[Dashboards] WHERE Id = SCOPE_IDENTITY()";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
using (var paramsInsert = new SqlCommand(CommandText, cn))
{
paramsInsert.Parameters.AddWithValue("@p_LastModifiedOn", DateTime.Now);
paramsInsert.Parameters.AddWithValue("@p_SelectedPageIndex", dto.SelectedPageIndex);
paramsInsert.Parameters.AddWithValue("@p_AccountId", dto.AccountId);
dto.Id = (int)paramsInsert.ExecuteScalar();
}
}
}
/// <summary>
/// Retrieves list of dashboard pages for a given dashboard ordered by index.
/// </summary>
/// <param name="dto">The dashboard DTO object.</param>
/// <exception cref="System.ArgumentException"></exception>
/// <exception cref="ArgumentException">Input is null.</exception>
private static void ReadDashboardPages(DashboardDto dto)
{
if (dto == null)
throw new ArgumentException(Resources.DtoNullException);
const string CommandText = @"
SELECT [Id]
,[Name]
,[DashboardPageIndex]
FROM [DashboardPages]
WHERE [DashboardId] = @dashboardId
ORDER BY [DashboardPageIndex]
";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
using (var cmd = new SqlCommand(CommandText, cn))
{
cmd.Parameters.AddWithValue("@dashboardId", dto.Id);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var pageDto = new DashboardPageDto
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Index = reader.GetInt32(2)
};
dto.Pages.Add(pageDto);
}
}
}
foreach (var page in dto.Pages)
{
ReadDashboardGadgets(page);
}
}
}
/// <summary>
/// Updates database with the information provided in Dashboard DTO object.
/// </summary>
/// <param name="dto">Dashboard DTO object.</param>
/// <exception cref="System.ArgumentException"></exception>
/// <exception cref="System.Data.DBConcurrencyException">The entity is out of date on the client. Please update the entity and try again. This could also be thrown if the sql statement failed to execute.</exception>
/// <exception cref="ArgumentException">Input is null.</exception>
/// <exception cref="DBConcurrencyException">Indicates stale data.</exception>
public void UpdateDashboard(DashboardDto dto)
{
if (dto == null)
throw new ArgumentException(Resources.DtoNullException);
const string CommandText =
@"
UPDATE [dbo].[Dashboards]
SET
[LastModifiedOn] = @p_LastModifiedOn
, [SelectedPageIndex] = @p_SelectedPageIndex
, [AccountId] = @p_AccountId
WHERE [Id] = @p_Id
";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
using (var paramsUpdate = new SqlCommand(CommandText, cn))
{
paramsUpdate.Parameters.AddWithValue("@p_Id", dto.Id);
paramsUpdate.Parameters.AddWithValue("@p_LastModifiedOn", DateTime.Now);
paramsUpdate.Parameters.AddWithValue("@p_SelectedPageIndex", dto.SelectedPageIndex);
paramsUpdate.Parameters.AddWithValue("@p_AccountId", dto.AccountId);
using (var reader = new SafeDataReader(paramsUpdate.ExecuteReader()))
{
//RecordsAffected: The number of rows changed, inserted, or deleted. -1 for select statements; 0 if no rows were affected, or the statement failed.
if (reader.RecordsAffected == 0)
throw new DBConcurrencyException(
"The entity is out of date on the client. Please update the entity and try again. This could also be thrown if the sql statement failed to execute.");
}
}
}
}