0

I am trying to edit a database using a service that I have created, that is set to run every minute using a timer. But I cannot for the life of me get it to work, if i run the code for updating the database as a console application then it works fine and updates the database. If i run the timer on its own and just get it to output a message to a text file every time it runs that works, but as soon as i try to get the timer to run the code that updates the database nothing happens. Please see below for the relevant bits of code:

Timer:

protected override void OnStart(string[] args)
    {
        timer1 = new Timer();
        this.timer1.Interval = 10000;
        this.timer1.Elapsed += new System.Timers.ElapsedEventHandler(this.timer1_Tick);
        timer1.Enabled = true;
        Library.WriteErrorLog("Service Started");
    }

    private void timer1_Tick(object sender, ElapsedEventArgs e)
    {
        Library.UpdateDatabase();
    }

Library.UpdateDatabase()

public static void UpdateDatabase()
  {
        try
        {
            string outcome = "blah";
            string reason = "testblah";
            string company = "test";
            string MyConnection2 = "datasource=***.***.***.***;port=3306;username=*********;password=**********";
            string Query = "insert into CompanyBackups.Backups(outcome, reason, company) values('" + outcome + "','" + reason + "','" + company + "');";
            MySqlConnection MyConn2 = new MySqlConnection(MyConnection2);
            MySqlCommand MyCommand2 = new MySqlCommand(Query, MyConn2);
            MySqlDataReader MyReader2;
            MyConn2.Open();
            MyReader2 = MyCommand2.ExecuteReader();
            while (MyReader2.Read())
            {
            }
            MyConn2.Close();
            WriteErrorLog("Database has been updated");
        }catch(Exception ex)
        {
            WriteErrorLog(ex.Message);
        }

      }

From what I can figure out the code isn't being run at all...

xjacksssss
  • 49
  • 9
  • Did you check the Logger for error messages? – jdweng May 21 '17 at 15:57
  • @jdweng yep absolutely nothing, real helpful i know – xjacksssss May 21 '17 at 16:01
  • that would mean that the code is never run, or logging itself fails. It would write something to the log in either case. are you using the same unusual approach with `ExecuteReader` in combination with an `INSERT` statement in the successful (console) test case? – Cee McSharpface May 21 '17 at 16:02
  • What do you mean "run the timer on its own"? Show the context around that timer code. – Crowcoder May 21 '17 at 16:02
  • 1
    `ExecuteReader` is not what you should be calling when you execute an insert statement, use `ExecuteNonQuery` instead. – Igor May 21 '17 at 16:03
  • @Igor if i try to use ExecuteNonQuery then i get "Cannot implicitly convert type 'int' to 'MySql.Data.MySqlClient.MySqlDataReader" – xjacksssss May 21 '17 at 16:11
  • @Crowcoder Sorry what i meant by that is running the timer but instead of having it execute "Library.UpdateDatabse" I have it just run "WriteErrorLog("blah")" – xjacksssss May 21 '17 at 16:12
  • 1
    `ExecuteNonQuery` does not return a data reader. when you execute an insert statement (as you do), there is no resultset. – Cee McSharpface May 21 '17 at 16:12
  • Does the service start successfully? is at least the "Service Started" string written to the log, or not even that? If not, you will probably find an exception logged in the Windows Application Event Log. – Cee McSharpface May 21 '17 at 16:13
  • @dlatikay yeah it outputs service started – xjacksssss May 21 '17 at 16:14
  • Why do you go through all the trouble of creating (and debugging) a windows service if you simply could run an .exe from Windows Task Scheduler? – Filburt May 21 '17 at 16:23
  • @Filburt because it is being deployed to multiple sites and i don't want to have to configure a Scheduled Task on each machine. – xjacksssss May 21 '17 at 16:24
  • Just export the scheduler task to Xml and hand it out with your executable - not much more required to do than installing the service. – Filburt May 21 '17 at 16:27
  • @dlatikay yes i have used the exact same way of updating the database in both the console version and the service version. – xjacksssss May 21 '17 at 16:54
  • [SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](https://xkcd.com/327/) – marc_s May 21 '17 at 17:27
  • OP care to check and [try this](http://stackoverflow.com/q/4962172/1132334), @marc_s [did you know](http://bobby-tables.com)? – Cee McSharpface May 21 '17 at 17:33
  • thanks @marc_s but i think i'll get it working before i worry about that – xjacksssss May 21 '17 at 17:39
  • @dlatikay will have a look now thanks! – xjacksssss May 21 '17 at 17:40
  • @dlatikay I'm using "System.Timers.Timer" so I don't think that it applies to me from what i can figure out, maybe I'm wrong – xjacksssss May 21 '17 at 17:46
  • right. in this case, I'm out of ideas. Attach a debugger to the running service and look what's going on. – Cee McSharpface May 21 '17 at 17:52
  • @dlatikay dont know if this helps but if i put Library.UpdateDatabase() as well as Library.WriteErrorLog("test") neither one works but if Library.WriteErrorLog("test") is on it's own it works fine... – xjacksssss May 21 '17 at 17:54
  • you mean in `timer1_Tick`? then swap the lines, so that writeerrorlog would be called before updatedatabase. if you see a log message then, we could narrow it down to two possible causes: 1) you still have the reader loop in there, and it is endless; 2) something is waiting forever or has a very long timeout (connection or query execution); 3) `WriteErrorLog` fails in the catch block. – Cee McSharpface May 21 '17 at 18:15
  • You should really **learn it the PROPER** way - don't get into a bad habit by "making it work first" ! – marc_s May 21 '17 at 19:20
  • @dlatikay right so if in "timer1_Tick" i put Library.WriteErrorLog("test") above Library.UpdateDatabase() it writes test but doesn't update the database if i put Library.UpdateDatabase() above Library.WriteErrorLog("test") then nothing happens... – xjacksssss May 21 '17 at 19:56
  • first remove the while loop. then, if you cannot debug the running service, insert `WriteErrorLog` calls all over the place in `UpdateDatabase` to find the statement that does not finish. – Cee McSharpface May 21 '17 at 20:32

1 Answers1

0

Right so after all of this and several hours of me wanting to punch my computer until either it or I start crying. I have now discovered that it was Inno Setup that was causing issues with it, I didn't realise that Inno was not installing the required DLL for the program... So it all comes down to me being thick basically. Thanks everyone for your help!!

xjacksssss
  • 49
  • 9