/// <summary>
/// Retrieves dashboard gadgets for a given dashboard page id.
/// </summary>
/// <param name="dashboardPageId">Dashboard page id.</param>
/// <returns>List of Dashboard Gadget DTO objects.</returns>
public IEnumerable<DashboardGadgetDto> FetchDashboardGadgets(int dashboardPageId)
{
var result = new List<DashboardGadgetDto>();
const string CommandText = @"
SELECT [Id]
,[Guid]
,[Name]
,[GadgetType]
,[Settings]
,[IsLocked]
FROM [DashboardGadgets]
WHERE [DashboardPageId] = @dashboardPageId";
Database.GetDataReader(
CommandText,
r =>
{
while (r.Read())
{
var gadgetDto = new DashboardGadgetDto
{
Id = r.GetInt32(0),
Guid = r.GetGuid(1),
Name = r.GetString(2),
GadgetType = r.GetString(3),
Settings = r.GetString(4),
DashboardPageId = dashboardPageId,
IsLocked = r.GetSafeBool(5)
};
result.Add(gadgetDto);
}
},
CommandType.Text,
new SqlParameter("@dashboardPageId", dashboardPageId));
return result;
}
/// <summary>
/// Inserts new dashboard gadget.
/// </summary>
/// <param name="dto">The Dashboard Gadget DTO.</param>
/// <exception cref="System.ArgumentException"></exception>
/// <exception cref="ArgumentException">Input is null.</exception>
public void InsertDashboardGadget(DashboardGadgetDto dto)
{
if (dto == null) throw new ArgumentException(Resources.DtoNullException);
const string CommandText =
@"
INSERT INTO [dbo].[DashboardGadgets] (
[Guid]
, [Name]
, [GadgetType]
, [Settings]
, [DashboardPageId])
VALUES (
@p_Guid
, @p_Name
, @p_GadgetType
, @p_Settings
, @p_DashboardPageId);
SELECT [Id] FROM [dbo].[DashboardGadgets] 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_Guid", dto.Guid);
paramsInsert.Parameters.AddWithValue("@p_GadgetType", dto.GadgetType);
paramsInsert.Parameters.AddWithValue("@p_Settings", dto.Settings);
paramsInsert.Parameters.AddWithValue("@p_DashboardPageId", dto.DashboardPageId);
dto.Id = (int)paramsInsert.ExecuteScalar();
}
}
}
/// <summary>
/// Updates dashboard gadget information in the database.
/// </summary>
/// <param name="dto">The dashboard gadget 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 UpdateDashboardGadget(DashboardGadgetDto dto)
{
if (dto == null) throw new ArgumentException(Resources.DtoNullException);
const string CommandText =
@"
UPDATE [dbo].[DashboardGadgets]
SET
[Guid] = @p_Guid
, [Name] = @p_Name
, [GadgetType] = @p_GadgetType
, [Settings] = @p_Settings
, [DashboardPageId] = @p_DashboardPageId
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_Guid", dto.Guid);
paramsUpdate.Parameters.AddWithValue("@p_Name", dto.Name);
paramsUpdate.Parameters.AddWithValue("@p_GadgetType", dto.GadgetType);
paramsUpdate.Parameters.AddWithValue("@p_Settings", dto.Settings);
paramsUpdate.Parameters.AddWithValue("@p_DashboardPageId", dto.DashboardPageId);
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.DtoNullException);
}
}
}
}
/// <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);
}
}
}
}
}
