Click here to Skip to main content
15,886,518 members
Please Sign up or sign in to vote.
5.00/5 (5 votes)
See more:
hi,

I am having the following problem. I have created an object and I populate the object with values from my MySql database. Each object in added to a list(collection). I then databind this list of objects to the itemsource of a datagrid. I make use of an objectdataprovider. My problem is that if changes are made in my database, my collection is not updated and therefore my UI isn't updated. Any suggestions on what can be done. I new to programming and WPF and I havent got a clue.
Please help.

Following is the code:

C#
 public class CompressorEngine : ObservableCollection <power>
    {
        public static ArrayList GetCompressor()
        {
            MySqlConnection conn = new MySqlConnection(" ");
            conn.Open();
            MySqlCommand comm = conn.CreateCommand();
            comm.CommandText = "SELECT ABIUnits.UnitSerialNumber, Region, AssetDescription, Location_Latitude, Location_Longitude, Street, City, ABIUnits_Alerts.Time, ABIUnits_Alerts.Comment, Alert_Type, Alert_ID FROM ABIUnits JOIN ABIUnits_Alerts ON ABIUnits.UnitSerialNumber = ABIUnits_Alerts.UnitSerialNumber WHERE ABIUnits_Alerts.Checked = '" + 0 + "' AND ABIUnits_Alerts.AlertGroup = '" + 3 + "' ORDER BY ABIUnits_Alerts.Time Desc";
           
            MySqlDataReader reader = comm.ExecuteReader();
            string alert_id;
            string unitserialnumber;
            string alerttype;
            string time1;
            string assetdescription;
            string region;
            string location_latitude;
            string location_longitude;
            string street;
            string city;
            ArrayList plist = new ArrayList();
            Compressor titlepower = new Compressor("Alert ID", "Unit Serial Number", "Alert", "Time", "Description", "Latitude", "Longitude", "Region", "Street", "City");
            plist.Add(titlepower);
            while (reader.Read())
            {
                alert_id = reader["ALert_ID"].ToString();
                unitserialnumber = reader["UnitSerialNumber"].ToString();
                alerttype = reader["ALert_Type"].ToString();
                time1 = reader["Time"].ToString();
                assetdescription = reader["AssetDescription"].ToString();
                region = reader["Region"].ToString();
                location_latitude = reader["Location_Latitude"].ToString();
                location_longitude = reader["Location_Longitude"].ToString();
                street = reader["Street"].ToString();
                city = reader["City"].ToString();
                Compressor compressor = new Compressor(alert_id, unitserialnumber, alerttype, time1, assetdescription, location_latitude, location_longitude, region, street, city);
                plist.Add(compressor);

            }
            conn.Close();
            return plist;
        }
    }
    public class Compressor : INotifyPropertyChanged
    {
        public event PropertyChangedEventHandler PropertyChanged;

        protected void OnPropertyChanged(string info)
        {
            PropertyChangedEventHandler handler = PropertyChanged;
            if (handler != null)
            {
                handler(this, new PropertyChangedEventArgs(info));
            }

        }
        private string _p_id;
        private string _units;
        private string _alert;
        private string _time;
        private string _assetdescription1;
        private string _location_latitude1;
        private string _location_longitude1;
        private string _region1;
        private string _street1;
        private string _city1;

        public Compressor(string p_id, string units, string alert, string time, string assetdescription1, string location_latitude1, string location_longitude1, string region1, string street1, string city1)
        {
            _p_id = p_id;
            _units = units;
            _alert = alert;
            _time = time;
            _assetdescription1 = assetdescription1;
            _location_latitude1 = location_latitude1;
            _location_longitude1 = location_longitude1;
            _region1 = region1;
            _city1 = city1;
            _street1 = street1;

        }


        public string P_id
        {
            get { return _p_id; }
            set { _p_id = value; OnPropertyChanged("P_id"); }

        }
        public string Units
        {
            get { return _units; }
            set { _units = value; OnPropertyChanged("Units"); }
        }
        public string Alert
        {
            get { return _alert; }
            set { _alert = value; OnPropertyChanged("Alert"); }
        }

        public string Time
        {
            get { return _time; }
            set { _time = value; OnPropertyChanged("Time"); }
        }
        public string AssetDescription1
        {
            get { return _assetdescription1; }
            set { _assetdescription1 = value; OnPropertyChanged("AssetDescription1"); }
        }
        public string Location_Latitude1
        {
            get { return _location_latitude1; }
            set { _location_latitude1 = value; OnPropertyChanged("Location_Latitude1"); }
        }
        public string Location_Longitude1
        {
            get { return _location_longitude1; }
            set { _location_longitude1 = value; OnPropertyChanged("Location_Longitude1"); }
        }
        public string Region1
        {
            get { return _region1; }
            set { _region1 = value; OnPropertyChanged("Region1"); }
        }
        public string Street1
        {
            get { return _street1; }
            set { _street1 = value; OnPropertyChanged("Street1"); }
        }
        public string City1
        {
            get { return _city1; }
            set { _city1 = value; OnPropertyChanged("City1"); }
        }
    }
</power>
Posted
Updated 12-Jan-11 13:12pm
v3
Comments
Tarun.K.S 13-Jan-11 1:12am    
Check out this link : http://msdn.microsoft.com/en-us/magazine/dd252944.aspx#id0080096

Is there any event by the user that would be a good place for a query to check for changes to the DB? What causes the change to the DB? Perhaps it can send signal or raise a flag to the program to check the DB for the update? Have you looked into using Triggers?

If the query for changes is not too taxing, could you just periodically check?

I'm just shooting off ideas, obviously some are preferred over others.
 
Share this answer
 
One popular approach is that the client that inserts/updates the database will broadcast a db-updated message that's received by all other active clients. The message can directly provide the inserted or updated data, or the clients can choose to do a db-refresh to get the changes (this is a design decision you need to make).

If your design will not let you do this broadcast, example when clients directly talk to a DB server (old school client-server app), and clients cannot communicate to each other, then you have to resort to polling the DB for changes at fixed intervals. This is error prone and can cause conflicts, so if you do have the option I'd strongly recommend that you take the above mentioned approach or a similar variation.
 
Share this answer
 
Comments
El_Codero 6-Mar-12 7:23am    
Nice Explanation. My 5!
Jitesh Hirani 25-Jan-16 0:17am    
I dont know about MySQL but in MSSQL and with ADO.Net we can create a database dependency and whenever the data of table changes it will notify you in the app.
Have a look at the following example of chat application

http://www.codeproject.com/Articles/12335/Using-SqlDependency-for-data-change-events
There might be possibly a way in MySQL to acheive the same.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900