C#编写的Windows Service执行LOAD DATA LOCAL INFILE时报错：给定路径格式不支持，Windows API中可正常运行

Let's break down why this issue pops up only in your Windows Service (and works fine in regular Windows apps) and walk through actionable fixes step by step:

Why this happens #

Windows Services run in a totally different context than desktop applications, which is the core of the problem:

• Default working directory mismatch: Services start in C:\Windows\System32 by default, not your app's installation folder. If any part of your code relies on relative paths or assumes the current directory is where your app lives, it'll break.
• Permission restrictions: Services usually run under accounts like Local System or Network Service, which might not have proper access to your E:\WS\Dump\ directory, or resolve paths differently than your user account.
• MySQL driver behavior: When using LOAD DATA LOCAL INFILE, the MySQL client driver needs to read the local file, and the Service's restricted context can throw off how the driver parses the file path.

Also, a key detail from your stack trace: the error is happening in File.WriteAllText, not directly when executing the SQL. That means there's a hidden file write operation in your code somewhere with an invalid path—don't forget to check that too!

Step-by-step fixes #

1. Double-check your file path (even if it looks correct) #

You're using an absolute path, but let's eliminate hidden issues like unescaped backslashes or invisible special characters. Build the path explicitly in code to avoid hardcoding mistakes:

var filePath = Path.Combine("E:", "WS", "Dump", "FInalMainFile01_28Jan2021.csv");
// MySQL accepts either / or escaped \\ for file paths
var sql = $"LOAD DATA LOCAL INFILE '{filePath.Replace("\\", "/")}' INTO TABLE xyz FIELDS TERMINATED BY '|' IGNORE 1 LINES;";

2. Set the Service's working directory to your app's folder #

Since the Service starts in System32, override this in your OnStart method to align it with your app's location:

protected override void OnStart(string[] args)
{
    // Get the folder where your service executable is stored
    var appDirectory = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);
    Directory.SetCurrentDirectory(appDirectory);
    
    // Your database logic goes here
}

This ensures any relative paths (if you use them later) resolve correctly, and helps the MySQL driver find files as expected.

3. Grant the Service's account access to the file directory #

• Open the Services console, find your service, right-click → Properties → Log On tab.
• Switch from the default Local System account to a local/domain account that has read (and write, if needed) permissions on E:\WS\Dump\.
• Restart the service and test again.

4. Enable local file loading in your MySQL connection string #

If you're using MySQL Connector/NET, you need to explicitly allow local file loads in your connection string—otherwise the driver blocks this operation by default:

var connectionString = "server=your-db-server;database=your-db;user=db-user;password=db-pass;AllowLoadLocalInfile=true;";

5. Track down that hidden File.WriteAllText call #

Your stack trace points to a file write operation failing, not the SQL execution. Go through your code and check every place you're writing to a file—make sure those paths are valid absolute paths, with no invalid characters like *, ?, or colons in the wrong position.

Verification steps #

1. First, run your LOAD DATA query directly in a MySQL client (like Workbench or Navicat) to confirm the file and SQL syntax are valid.
2. Add logging to your service to print out the exact file path you're trying to access—verify it matches what you expect.
3. Temporarily run the service as your own user account (in the Log On tab) to quickly rule out permission-related issues.

内容的提问来源于stack exchange，提问作者Harish Waghmare