IBE.SQL.EliteDBIO.DeleteNoLongerExistingMarketData C# (CSharp) Method

DeleteNoLongerExistingMarketData() public method

delete all data for every station, older than "youngest update" - "n days"
public DeleteNoLongerExistingMarketData ( int days, int singleStationID = null ) : void
days int
singleStationID int
return void
        public void DeleteNoLongerExistingMarketData(int days, int? singleStationID=null)
        {
            String sqlString;
            Int32 deletedRows = 0;
            Int32 deletedRowsSum = 0;
            Int32 stationsToCheck;
            String sqlBaseString_Get;
            String sqlBaseString_Delete;
            DataTable data = new DataTable();
            Int32 firstPosition = 0;
            Int32 quantity      = 500;
            Int32 deleted = 0;
            Int32 checkedStations = 0;

            try
            {
                sqlBaseString_Get = " select * from" + 
                                    " 				(select * from tbStations) L1" + 
                                    " 	left join " +
                                    " 				(select id " +
                                    " 				from " +
                                    " 					(select * from tbStations) L3 " +
                                    " 				order by L3.id asc" +
                                    " 				limit {0}) L2 " +
                                    " on L1.id = L2.id " +
                                    " where L2.id is null " +
                                    " order by L1.id asc" +
                                    " limit {1}";

                sqlBaseString_Delete =  " delete C2 from tbCommodityData C2 " +
                                        "  where C2.station_id = {0}  " +
                                        "  and   C2.timestamp <  " +
                                        "      (select * from " +
                                        "           (select Date_Add(max(timestamp), INTERVAL -{1} DAY)  " +
                                        "             from tbCommodityData where station_id = {0} " +
                                        "           ) D1  " +
                                        " 	   ) ";


                if(!singleStationID.HasValue)
                    stationsToCheck = Program.DBCon.Execute<Int32>("select count(*) from tbstations"); 
                else
                    stationsToCheck = 1;   

                sqlString = String.Format("select id from tbStations",days);
                sendProgressEvent(new ProgressEventArgs() { Info=String.Format("delete prices of no longer existing commodities on stations...", days), CurrentValue=0, TotalValue=stationsToCheck, AddSeparator=true });

                do
                {
                    if(singleStationID.HasValue)
                    {
                        deleted += Program.DBCon.Execute(String.Format(sqlBaseString_Delete, singleStationID.Value, days));
                    }
                    else
                    {
                        Program.DBCon.Execute(String.Format(sqlBaseString_Get, firstPosition, quantity), data);
                
                        foreach (DataRow stationID in data.Rows)
                        {
                            deleted += Program.DBCon.Execute(String.Format(sqlBaseString_Delete, stationID["id"], days));

                            checkedStations++;

                            if(sendProgressEvent(new ProgressEventArgs() { Info=String.Format("delete prices of no longer existing commodities on stations - deleted prices={0}...", deleted), CurrentValue=checkedStations, TotalValue = stationsToCheck, Unit=" stations" }))
                                break;

                        }

                        firstPosition+=quantity;
                    }

                } while ((data.Rows.Count > 0) && (!singleStationID.HasValue));

                sendProgressEvent(new ProgressEventArgs() { Info=String.Format("delete prices of no longer existing commodities on stations - deleted prices={0}...", deleted), CurrentValue=checkedStations, TotalValue = stationsToCheck, Unit=" stations", ForceRefresh=true });

            }
            catch (Exception ex)
            {
                throw new Exception("Error while deleting no more existing commodities from stations", ex);
            }
        }