/// <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); } } } } }