/// <summary>
/// Retrieves dashboard gadgets for a given Dashboard page and fill up given DTO object collection.
/// </summary>
/// <param name="page">Dashboard page DTO Object.</param>
private static void ReadDashboardGadgets(DashboardPageDto page)
{
const string sql = @"
SELECT [Id]
,[Guid]
,[Name]
,[GadgetType]
,[Settings]
,[IsLocked]
FROM [DashboardGadgets]
WHERE [DashboardPageId] = @pageId";
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(sql, cn))
{
cmd.Parameters.AddWithValue("@pageId", page.Id);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var gadgetDto = new DashboardGadgetDto
{
Id = reader.GetInt32(0),
Guid = reader.GetGuid(1),
Name = reader.GetString(2),
GadgetType = reader.GetString(3),
Settings = reader.GetString(4),
IsLocked = reader.GetSafeBool(5)
};
page.Gadgets.Add(gadgetDto);
}
}
}
}
}
/// <summary>
/// Inserts new dashboard page.
/// </summary>
/// <param name="dto">The DTO object.</param>
/// <exception cref="System.ArgumentException"></exception>
/// <exception cref="ArgumentException">Input is null.</exception>
public void InsertDashboardPage(DashboardPageDto dto)
{
if (dto == null)
{
throw new ArgumentException(Resources.DtoNullException);
}
const string CommandText =
@"
INSERT INTO [dbo].[DashboardPages]
( [Name] ,
[DashboardPageIndex] ,
[DashboardId]
)
VALUES ( @p_Name ,
@p_DashboardPageIndex ,
@p_DashboardId
);
SELECT [Id]
FROM [dbo].[DashboardPages]
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_Name", dto.Name);
paramsInsert.Parameters.AddWithValue("@p_DashboardPageIndex", dto.Index);
paramsInsert.Parameters.AddWithValue("@p_DashboardId", dto.DashboardId);
dto.Id = (int)paramsInsert.ExecuteScalar();
}
}
}
/// <summary>
/// Updates dashboard page with information provided in DTO object.
/// </summary>
/// <param name="dto">The DTO object.</param>
/// <exception cref="System.ArgumentException"></exception>
/// <exception cref="System.Data.DBConcurrencyException"></exception>
/// <exception cref="ArgumentException">Input is null.</exception>
/// <exception cref="DBConcurrencyException">Indicates stale data.</exception>
public void UpdateDashboardPage(DashboardPageDto dto)
{
if (dto == null) throw new ArgumentException(Resources.DtoNullException);
const string CommandText =
@"
UPDATE [dbo].[DashboardPages]
SET [Name] = @p_Name ,
[DashboardPageIndex] = @p_DashboardPageIndex ,
[DashboardId] = @p_DashboardId
WHERE [DashboardId] = @p_DashboardId
AND [DashboardPageIndex] = @p_DashboardPageIndex
";
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_Name", dto.Name);
paramsUpdate.Parameters.AddWithValue("@p_DashboardPageIndex", dto.Index);
paramsUpdate.Parameters.AddWithValue("@p_DashboardId", dto.DashboardId);
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(Resources.StaleDataException);
}
}
}
}
/// <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);
}
}
}
