Windows Service C# Database connection problems

Question

I try to make a windows service in C# to send daily automatically emails, based on data read from a specified SQL server database.

I mananged to make the service run, to send email automatically but when I try to insert a sqlconnection service just doesn't work anymore. Any ideas?

It works if I remove SqlConnection and data reader.

Here is my code:

    System.Timers.Timer createOrderTimer;
    public Service1()
    {
        InitializeComponent();
    }

    protected override void OnStart(string[] args)
    {
        createOrderTimer = new System.Timers.Timer();
        createOrderTimer.Elapsed += new System.Timers.ElapsedEventHandler(GetMail);
        createOrderTimer.Interval = 2500;
        createOrderTimer.Enabled = true;
        createOrderTimer.AutoReset = true;
        createOrderTimer.Start();
    }
    protected override void OnStop()
    {
    }
    public void GetMail(object sender, System.Timers.ElapsedEventArgs args)
    {
        SqlConnection connect = new SqlConnection("Server=10.222.160.17,5356;"  
            + "Database=tracking_tool;"
            + "Trusted_Connection=True;"
            + "user=admin;"
            + "password=root");

        string line = "";
        connect.Open();
        SqlCommand GetData = new SqlCommand("select * from validation", connect);
        SqlDataReader ReadData = null;
        ReadData = GetData.ExecuteReader();

        while (ReadData.Read())
        {

            line = ReadData["Line"].ToString();

        }
        connect.Close();

        MailMessage mailMessage = new MailMessage();
        mailMessage.To.Add("[email protected]");
        mailMessage.From = new MailAddress("[email protected]");
        mailMessage.Subject = "example";
        mailMessage.Body = "example";
        SmtpClient smtpClient = new SmtpClient("10.214.81.97");
        smtpClient.Send(mailMessage);
    }

So are you getting any exceptions, this might help a lot investigating the issue. Also are you sure that the connection string is right? There is a comma in the ip. I am no expert but I never encountered this before. — チーズパン
– チーズパン, Commented Jul 13, 2016 at 7:21
Trusted_Connection=True obscures the username and password and being you in a service, the user running the service is the one presented to sql server authentication. I would try to remove the Trusted_Connection and use the username and password — Steve
– Steve, Commented Jul 13, 2016 at 7:24
your connection string is weird, @Käsebrot is right. why are you concatenating it? it just makes it harder to read and easier to make mistakes. try something like this: Data Source=yourserver; Initial Catalog=yourdb; User Id=youruser; Password=yourpword; and definately remove trusted connection — Ted
– Ted, Commented Jul 13, 2016 at 7:24
also, what are you doing with the line variable? you just keep replacing its value in the loop. what's the endgame here? — Ted
– Ted, Commented Jul 13, 2016 at 7:25

Rene Gijzemijter · Accepted Answer · 2016-07-13 08:52:34Z

1

You are reading data from SQL into variable line, but afterwards you don't do anything with it. My guess is, there is actually nothing wrong. How are you determining that something is not working?

answered Jul 13, 2016 at 8:52

Rene Gijzemijter

879 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Voila Daniel Over a year ago

Because there is no email send automatically. If i comment lines where i read data, windows service sends emails. (that reader is just for testing and show you what it`s not working)

Thorsten Dittmar Over a year ago

Well, if it doesn't send an email, an exception has occurred. As I don't see any exception handling code, the service should be stopped and you should see an error message in the Windows event log.

Rene Gijzemijter Over a year ago

Not necessarily. The mail is sent regardless of the SQL data, this is what I meant with my answer. I think you are getting data back, it's stored in your line and then it continues to sending the mail. Now I understand you're not getting mail, but the problem in that case would be in the sending the mail part, not your db-code. You could insert a breakpoint at the line = Readdata part to see if it's being filled. But like Ted said, you're overwriting it. Eventhough you have only one row in your table.

Mohammad Nikravesh · Accepted Answer · 2017-03-07 09:14:16Z

0

Your ConnectionString is wrong. Try this : (just replace your configs)

server={YourSqlInstanceFullName};database={YourDatabaseName};Integrated Security=false;User ID={Database Username};Password={Password}

answered Mar 7, 2017 at 9:14

Mohammad Nikravesh

9751 gold badge8 silver badges27 bronze badges

Collectives™ on Stack Overflow

Windows Service C# Database connection problems

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related