使用Windows Service在FileSystemEventHandler上插入数据库 [英] Database insert on FileSystemEventHandler with Windows Service

问题描述

我已经设法使Service正常运行，同时将FileSystemEventHandler插入文本文件，但是现在需要更改它才能插入数据库和文本文件。

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);
                }
            }
        }
    }
}

我认为数据库插入错误，因为将catch块插入了文本文件。但是，我已经在一个单独的项目中单独运行了代码，并将其插入到控制台应用程序的数据库中。

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.

推荐答案

Windows服务在与控制台应用程序不同的安全上下文中运行。正如评论所揭示的，该异常与您的连接字符串有关。
如果我们分析connectiong字符串，我们可以看到您正在使用 IntegratedSecurity = True进行身份验证。因为您的Windows服务在
a服务帐户下运行身份验证失败。我已指定2个解决方案。

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.

选项1：以Windows帐户运行服务（不推荐，但可以用于测试）

1. 打开运行框（Win Flag + R）


2. 键入Services.MSC


3. 找到您的服务并右键单击属性


4. 选择登录选项卡


5. 输入Windows身份验证用户名和密码以使服务运行为

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

选项2：创建SQL Server帐户

1. 在SQL中为数据库创建用户名和密码


2. 更新连接字符串以指定创建的新用户名和密码

这篇关于使用Windows Service在FileSystemEventHandler上插入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！