CODEWITHSUNDEEP
c#sql.netwpfwindows-services
Cabry
Cabry

Reputation: 125

How to execute SQL command in windows service?

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.sstatic.net/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.

Upvotes: 0

Views: 1443

Answers (1)

Matt Davis
Matt Davis

Reputation: 46034

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.

enter image description here

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

Upvotes: 1

Related Questions