How to execute SQL command in windows service?

Question

So I have created service in my program. I need this service to update my table. It should work like check for delivery reports of sms messages.

So in this service I would like to execute api from sms gateway to obtain data and then insert them into Log table in SQL.

I managed to create timer in service, with some eventLog updates. So my service is alive, but problem is that when I am trying to do anything with my sql server it probably won't execute anything and I dont really know why.

service:

    public partial class Service1 : ServiceBase
{
    private int eventId = 1;
    DataSet ds = new DataSet();
    SqlConnection cs = new SqlConnection("Data Source=lvrabel; Initial Catalog=EagleTest; Integrated Security=TRUE;");
    SqlDataAdapter da = new SqlDataAdapter();



    public Service1()
    {
        InitializeComponent();

        eventLog1 = new EventLog();
        if (!EventLog.SourceExists("MySource"))
        {
            EventLog.CreateEventSource("MySource", "MyNewLog");
        }
        eventLog1.Source = "MySource";
        eventLog1.Log = "MyNewLog";
    }

    protected override void OnStart(string[] args)
    {
        eventLog1.WriteEntry("In OnStart");

        Timer timer = new Timer();
        timer.Interval = 60000; // 60 seconds
        timer.Elapsed += new ElapsedEventHandler(this.GetReports);
        timer.Start();          
    }

    protected override void OnStop()
    {
        eventLog1.WriteEntry("In OnStop.");
    }

    public void OnTimer(object sender, ElapsedEventArgs args)
    {
        // TODO: Insert monitoring activities here.
       // eventLog1.WriteEntry("Monitoring the System", EventLogEntryType.Information, eventId++);
    }

    public void GetReports(object sender, ElapsedEventArgs args)
    {
        //SqlCommand selectTelephone = new SqlCommand("select telephone from Contacts where LASTNAME = 'test'", cs);
        //cs.Open();
        //SqlDataAdapter da = new SqlDataAdapter(selectTelephone);
        //DataTable dt = new DataTable();
        //da.Fill(dt);

        //foreach (DataRow dr in dt.Rows)
        //{
        //    eventLog1.WriteEntry(dr["telephone"].ToString(), EventLogEntryType.Information, eventId++);
        //}
        //cs.Close();


        cs.Open();
        da.InsertCommand = new SqlCommand("insert into Log(IDContacts, IDEvent, Description, smsID) VALUES('5', '3', 'SMS OUT', '123')", cs); // dodelat id contacts

        da.InsertCommand.ExecuteNonQuery();
        cs.Close();
    }
}

Is there a possibility how can I troubleshoot service or do you see any mistakes in my code? (atm I have there only insertCommand. I ll add the http api to the code later)

https://i.stack.imgur.com/I5MRr.jpg - imgur image of my installers in service

ADDED SOLUTION:

So all the problem was in user privilegies. I was using local system service, instead of network service. Now it is all working.

try instead of Integrated Security=TRUE;" Integrated Security=SSPI;" — fireshark519, Apr 05 '19 at 12:04
@fireshark519 it still doing nothing. It have to get stuck somewhere around ExecuteNonQuery(); because if I entry _eventLog1.WriteEntry("Test", EventLogEntryType.Information, eventId++);_ It wont make this line into eventlog — Cabry, Apr 05 '19 at 12:32
SqlCommand command command.ExecuteNonQuery(); instead of the insertcommand? Sorry I am not the best on C# but just looking at the ones I've written and have working :) I would suggest adding the error you get as well. — fireshark519, Apr 05 '19 at 12:47

score 1 · Answer 1 · answered Apr 11 '19 at 06:17

Providing you have adequate permissions on your system, you can actively debug the service while it's running to see what exception(s) might be occurring. You can refer to the accepted answer here, or simply make the following call in your OnStart() callback:

System.Diagnostics.Debugger.Break();

When you start your service, you should get a prompt indicating an unhandled exception has occurred.

Click the Yes option, answer Yes to the UAC prompt, select which instance of Visual Studio you want to use, and then debug normally. This will hopefully give a clearer picture as to what's going on rather than having to guess.

HTH

Thank you for this. It might really help me in future. – Cabry Apr 11 '19 at 07:05 — Cabry, Apr 11 '19 at 07:05

How to execute SQL command in windows service?

1 Answers1