public List<WidgetObject> GetDashBoardWidget(int rowId)
        {
            List<WidgetObject> lWidgets = new List<WidgetObject>();
            SqlDataReader oReader;

            try
            {

                using (SqlCommand oComm = new SqlCommand())
                {

                    if (Sqlconn.State == ConnectionState.Closed)
                        Sqlconn.Open();

                    oComm.Connection = Sqlconn;
                    oComm.CommandType = CommandType.StoredProcedure;
                    oComm.CommandText = "DashBoard.GetDashBoardWidget";

                    SqlParameter pRowID = new SqlParameter("@IDROW", SqlDbType.Int);
                    pRowID.Value = rowId;
                    oComm.Parameters.Add(pRowID);

                    oReader = oComm.ExecuteReader();

                    while (oReader.Read())
                    {
                        WidgetObject widget = new WidgetObject()
                        {
                            id = (int)oReader["wdg_id"],
                            cssClass = oReader["wdg_class"] != DBNull.Value ? oReader["wdg_class"].ToString() : string.Empty,
                            rowID = (int)oReader["wdg_row_id"],
                            cell = (int)oReader["wdg_cell"],
                            type = oReader["wdg_type"] != DBNull.Value ? oReader["wdg_type"].ToString() : string.Empty,
                            chartype = oReader["wdg_chartType"] != DBNull.Value ? oReader["wdg_chartType"].ToString() : string.Empty,
                            v = oReader["wdg_v"] != DBNull.Value ? (bool)oReader["wdg_v"] : false,
                            vt = oReader["wdg_vt"] != DBNull.Value ? (bool)oReader["wdg_vt"] : false,
                            vc = oReader["wdg_vc"] != DBNull.Value ? (bool)oReader["wdg_vc"] : false,
                            endPoint = oReader["wdg_endPoint"] != DBNull.Value ? oReader["wdg_endPoint"].ToString() : string.Empty,
                            endPointType = oReader["wdg_endPointType"] != DBNull.Value ? oReader["wdg_endPointType"].ToString() : string.Empty,
                            endPointV20 = oReader["wdg_endPointV20"] != DBNull.Value ? oReader["wdg_endPointV20"].ToString() : string.Empty,
                            endPointSource = oReader["wdg_endPointSource"] != DBNull.Value ? oReader["wdg_endPointSource"].ToString() : string.Empty,
                            endPointDecimalSeparator = oReader["wdg_decimalCulture"] != DBNull.Value ? oReader["wdg_decimalCulture"].ToString() : string.Empty,
                            dataflow_id = oReader["wdg_dataflow_id"] != DBNull.Value ? oReader["wdg_dataflow_id"].ToString() : string.Empty,
                            dataflow_agency_id = oReader["wdg_dataflow_agency_id"] != DBNull.Value ? oReader["wdg_dataflow_agency_id"].ToString() : string.Empty,
                            dataflow_version = oReader["wdg_dataflow_version"] != DBNull.Value ? oReader["wdg_dataflow_version"].ToString() : string.Empty,
                            criteria = oReader["wdg_criteria"] != DBNull.Value ? oReader["wdg_criteria"].ToString() : string.Empty,
                            layout = oReader["wdg_layout"] != DBNull.Value ? oReader["wdg_layout"].ToString() : string.Empty
                        };

                        lWidgets.Add(widget);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Sqlconn.State == ConnectionState.Open)
                    Sqlconn.Close();
            }

            return lWidgets;
        }
        public WidgetObject UpdateWidget(WidgetObject widgetObject)
        {
            bool ret = true;

            try
            {

                using (SqlCommand oComm = new SqlCommand())
                {

                    if (Sqlconn.State == ConnectionState.Closed)
                        Sqlconn.Open();
                    oComm.Connection = Sqlconn;
                    oComm.CommandType = CommandType.StoredProcedure;
                    oComm.CommandText = "DashBoard.UpdateWidget";

                    SqlParameter pWDGID = new SqlParameter("@WDG_ID", SqlDbType.Int);
                    pWDGID.Value = widgetObject.id;
                    oComm.Parameters.Add(pWDGID);

                    SqlParameter pWdgClass = new SqlParameter("@WDG_CLASS", SqlDbType.NVarChar);
                    pWdgClass.Value = widgetObject.cssClass;
                    oComm.Parameters.Add(pWdgClass);

                    SqlParameter pRowID = new SqlParameter("@WDG_ROW_ID", SqlDbType.Int);
                    pRowID.Value = widgetObject.rowID;
                    oComm.Parameters.Add(pRowID);

                    SqlParameter pWdgCell = new SqlParameter("@WDG_CELL", SqlDbType.Int);
                    pWdgCell.Value = widgetObject.cell;
                    oComm.Parameters.Add(pWdgCell);

                    SqlParameter pType = new SqlParameter("@WDG_TYPE", SqlDbType.NVarChar);
                    pType.Value = widgetObject.type;
                    oComm.Parameters.Add(pType);

                    SqlParameter pChartType = new SqlParameter("@WDG_CHARTTYPE", SqlDbType.NVarChar);
                    pChartType.Value = widgetObject.chartype;
                    oComm.Parameters.Add(pChartType);

                    SqlParameter pV = new SqlParameter("@WDG_V", SqlDbType.Bit);
                    pV.Value = widgetObject.v;
                    oComm.Parameters.Add(pV);

                    SqlParameter pVT = new SqlParameter("@WDG_VT", SqlDbType.Bit);
                    pVT.Value = widgetObject.vt;
                    oComm.Parameters.Add(pVT);

                    SqlParameter pVC = new SqlParameter("@WDG_VC", SqlDbType.Bit);
                    pVC.Value = widgetObject.vc;
                    oComm.Parameters.Add(pVC);

                    SqlParameter pEndPoint = new SqlParameter("@WDG_ENDPOINT", SqlDbType.NVarChar);
                    pEndPoint.Value = widgetObject.endPoint;
                    oComm.Parameters.Add(pEndPoint);

                    SqlParameter pEndPointType = new SqlParameter("@WDG_ENDPOINTTYPE", SqlDbType.NVarChar);
                    pEndPointType.Value = widgetObject.endPointType;
                    oComm.Parameters.Add(pEndPointType);

                    SqlParameter pEndPointV20 = new SqlParameter("@WDG_ENDPOINTV20", SqlDbType.NVarChar);
                    pEndPointV20.Value = widgetObject.endPointV20;
                    oComm.Parameters.Add(pEndPointV20);

                    SqlParameter pEndPointSource = new SqlParameter("@WDG_ENDPOINTSOURCE", SqlDbType.NVarChar);
                    pEndPointSource.Value = widgetObject.endPointSource;
                    oComm.Parameters.Add(pEndPointSource);

                    SqlParameter pEndPointDecimal = new SqlParameter("@WDG_ENDPOINTDECIMAL", SqlDbType.NVarChar);
                    pEndPointDecimal.Value = widgetObject.endPointDecimalSeparator;
                    oComm.Parameters.Add(pEndPointDecimal);

                    SqlParameter pDataFlowID = new SqlParameter("@WDG_DATAFLOW_ID", SqlDbType.NVarChar);
                    pDataFlowID.Value = widgetObject.dataflow_id;
                    oComm.Parameters.Add(pDataFlowID);

                    SqlParameter pAgencyID = new SqlParameter("@WDG_DATAFLOW_AGENCY_ID", SqlDbType.NVarChar);
                    pAgencyID.Value = widgetObject.dataflow_agency_id;
                    oComm.Parameters.Add(pAgencyID);

                    SqlParameter pVersion = new SqlParameter("@WDG_DATAFLOW_VERSION", SqlDbType.NVarChar);
                    pVersion.Value = widgetObject.dataflow_version;
                    oComm.Parameters.Add(pVersion);

                    SqlParameter pCriteria = new SqlParameter("@WDG_CRITERIA", SqlDbType.NText);
                    pCriteria.Value = widgetObject.criteria;
                    oComm.Parameters.Add(pCriteria);

                    SqlParameter pLayout = new SqlParameter("@WDG_LAYOUT", SqlDbType.NText);
                    pLayout.Value = widgetObject.layout;
                    oComm.Parameters.Add(pLayout);

                    oComm.ExecuteNonQuery();

                    foreach (TextLocalised tl in widgetObject.text)
                    {
                        UpdateWidgetText(widgetObject.id, tl);
                    }

                }

            }
            catch (Exception ex)
            {
                ret = false;
                throw ex;
            }
            finally
            {
                if (Sqlconn.State == ConnectionState.Open)
                    Sqlconn.Close();
            }

            return widgetObject;
        }
        public List<WidgetObject> GetUpdateableWidgets()
        {
            List<WidgetObject> lWidgets = new List<WidgetObject>();
            SqlDataReader oReader;

            try
            {

                using (SqlCommand oComm = new SqlCommand())
                {

                    if (Sqlconn.State == ConnectionState.Closed)
                        Sqlconn.Open();

                    oComm.Connection = Sqlconn;
                    oComm.CommandType = CommandType.StoredProcedure;
                    oComm.CommandText = "Caching.GetUpdateableWidgets";

                    oReader = oComm.ExecuteReader();

                    while (oReader.Read())
                    {
                        WidgetObject widget = new WidgetObject()
                        {
                            id = (int)oReader["wdg_id"],
                            cssClass = oReader["wdg_class"] != DBNull.Value ? oReader["wdg_class"].ToString() : string.Empty,
                            rowID = (int)oReader["wdg_row_id"],
                            cell = (int)oReader["wdg_cell"],
                            type = oReader["wdg_type"] != DBNull.Value ? oReader["wdg_type"].ToString() : string.Empty,
                            chartype = oReader["wdg_chartType"] != DBNull.Value ? oReader["wdg_chartType"].ToString() : string.Empty,
                            v = oReader["wdg_v"] != DBNull.Value ? (bool)oReader["wdg_v"] : false,
                            vt = oReader["wdg_vt"] != DBNull.Value ? (bool)oReader["wdg_vt"] : false,
                            vc = oReader["wdg_vc"] != DBNull.Value ? (bool)oReader["wdg_vc"] : false,
                            endPoint = oReader["wdg_endPoint"] != DBNull.Value ? oReader["wdg_endPoint"].ToString() : string.Empty,
                            endPointType = oReader["wdg_endPointType"] != DBNull.Value ? oReader["wdg_endPointType"].ToString() : string.Empty,
                            endPointV20 = oReader["wdg_endPointV20"] != DBNull.Value ? oReader["wdg_endPointV20"].ToString() : string.Empty,
                            endPointDecimalSeparator = oReader["wdg_decimalCulture"] != DBNull.Value ? oReader["wdg_decimalCulture"].ToString() : string.Empty,
                            endPointSource = oReader["wdg_endpointSource"] != DBNull.Value ? oReader["wdg_endpointSource"].ToString() : string.Empty,
                            dataflow_id = oReader["wdg_dataflow_id"] != DBNull.Value ? oReader["wdg_dataflow_id"].ToString() : string.Empty,
                            dataflow_agency_id = oReader["wdg_dataflow_agency_id"] != DBNull.Value ? oReader["wdg_dataflow_agency_id"].ToString() : string.Empty,
                            dataflow_version = oReader["wdg_dataflow_version"] != DBNull.Value ? oReader["wdg_dataflow_version"].ToString() : string.Empty,
                            criteria = oReader["wdg_criteria"] != DBNull.Value ? oReader["wdg_criteria"].ToString() : string.Empty,
                            layout = oReader["wdg_layout"] != DBNull.Value ? oReader["wdg_layout"].ToString() : string.Empty
                        };

                        widget.text = new List<TextLocalised>() { new TextLocalised() { locale = oReader["wdg_text_Locale"].ToString() } };

                        lWidgets.Add(widget);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Sqlconn.State == ConnectionState.Open)
                    Sqlconn.Close();
            }

            return lWidgets;
        }