Database insert on FileSystemEventHandler with Win

问题:

I have managed to get the Service working, along with the FileSystemEventHandler inserting into a text file, but this now needs to be changed to insert into a database and a text file.

using System;  
using System.Collections.Generic;  
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;  
using System.IO;  
using System.Linq;  
using System.ServiceProcess;  
using System.Text;  
using System.Threading.Tasks;  
using System.Timers;  
namespace WindowsServiceTest
{
    public partial class Service1 : ServiceBase
    {
        Timer timer = new Timer(); // name space(using System.Timers;)  
        public static string path = ConfigurationManager.AppSettings["findpath"];
        public Service1()
        {
            InitializeComponent();
        } 

        protected override void OnStart(string[] args)
        {
            WriteToFile("Service is started at " + DateTime.Now);
            timer.Elapsed += new ElapsedEventHandler(OnElapsedTime);
            timer.Interval = 10000; //number in milisecinds  
            timer.Enabled = true;
            FileSystemWatcher watcher = new FileSystemWatcher
            {
                Path = path,
                NotifyFilter = NotifyFilters.LastWrite,
            };
            watcher.Created += new FileSystemEventHandler(FileSystemWatcher_Changed);
            watcher.Renamed += new RenamedEventHandler(FileSystemWatcher_Renamed);
            watcher.Changed += new FileSystemEventHandler(FileSystemWatcher_Changed);
            watcher.EnableRaisingEvents = true;
        }

        public static void FileSystemWatcher_Changed(object source, FileSystemEventArgs e)
        {
            using (SqlConnection con = new SqlConnection("Data Source=localhost\\SQLEXPRESS;Database=ServiceTest;Integrated Security=True;"))
            {
                try
                {
                    con.Open();
                    var command = new SqlCommand("Insert into test(URL, Location) values(@URL, @agendaname);", con);
                    command.Parameters.Add("@URL", System.Data.SqlDbType.VarChar, 100).Value = e.Name;
                    command.Parameters.Add("@agendaname", System.Data.SqlDbType.VarChar, 100).Value = "Case History";
                    command.ExecuteNonQuery();
                }
                catch
                {
                    WriteToFile($"Failed to insert: {e.Name} into the database");
                }
            }
        }
        public static void FileSystemWatcher_Renamed(object source, RenamedEventArgs e)
        {
            WriteToFile($"File Renamed: {e.OldFullPath} renamed to {e.FullPath}");
        }
        private void OnElapsedTime(object source, ElapsedEventArgs e)
        {
            WriteToFile("Service is recalled at " + DateTime.Now);
        }
        protected override void OnStop()
        {

            WriteToFile("Service is stopped at " + DateTime.Now);
        }

        public static void WriteToFile(string Message)
        {
            string path = AppDomain.CurrentDomain.BaseDirectory + "\\Logs";
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            string filepath = AppDomain.CurrentDomain.BaseDirectory + "\\Logs\\ServiceLog_" + DateTime.Now.Date.ToShortDateString().Replace('/', '_') + ".txt";
            if (!File.Exists(filepath))
            {
                // Create a file to write to.   
                using (StreamWriter sw = File.CreateText(filepath))
                {
                    sw.WriteLine(Message);
                }
            }
            else
            {
                using (StreamWriter sw = File.AppendText(filepath))
                {
                    sw.WriteLine(Message);
                }
            }
        }
    }
}

I think that I've done the database insert wrong because the catch block is being inserted into the text file. However, I've run the code by itself in a separate project and was inserting into the database in a Console Application.

Any help is appreciated, kind regards.

回答1:

Windows services run under a different security context than console apps. As the comments have disclosed, the exception is related to your connection string. If we analyze the connectiong string we can see that you are authenticating with IntegratedSecurity="True". Because your windows service is running under a service account authentication is failing. I've specified 2 options for resolving this.

Option 1: Have Service run as windows account (Not recommended but will work for testing)

1. Open run box (Win Flag + R)
2. Type Services.MSC
3. Locate your service and right click properties
4. Choose the logon tab
5. Enter your windows auth username and password for service to run as

Option 2: Create SQL Server account

1. Create username and password in SQL for database
2. Update connection string to specify new username and password created