C# UWP MySql BulkLoader - 无法使用 csv 加载数据

Question

我在使用 UWP 应用程序将数据从 csv 加载到 mysql 数据库时遇到问题。我基本上想做的是:

• 通过 HttpClient 从 csv 文件读取数据(200k 条记录，32 MB)
• 将数据保存到应用程序临时文件夹中的 csv 文件
• 读取文件并识别需要插入的列
• 使用 MySqlBulkLoader 类将 csv 加载到数据库中

下面是我正在尝试的代码:

public static async Task<int> MemberEnrollment_GetFromServer_FirstSync(string _url)
{
    int insertedRecords = 0;
    string csv_data = await DownloadDataAsync(_url);

    //StorageFolder localFolder = ApplicationData.Current.RoamingFolder;
    //StorageFile csv = await localFolder.CreateFileAsync(DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".csv", CreationCollisionOption.ReplaceExisting);
    StorageFile csv = await DownloadsFolder.CreateFileAsync(DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".csv");

    var token = Windows.Storage.AccessCache.StorageApplicationPermissions.FutureAccessList.Add(csv);

    try
    {
        await FileIO.WriteTextAsync(csv, csv_data, Windows.Storage.Streams.UnicodeEncoding.Utf8);

        var testRead = await OpenFile(csv.Path, FileAccess.Read);

        //batch insert into the memberenrollment table
        insertedRecords = await DataServices.MemberEnrollment_PostToLocal_Bulk(csv);
    }
    catch (Exception exInsertBatch)
    {
        throw exInsertBatch;
    }
    finally
    {
        if (File.Exists(csv.Path))
            await csv.DeleteAsync(StorageDeleteOption.PermanentDelete);
    }
    return insertedRecords;
}
private static async Task<string> DownloadDataAsync(string url)
    {
        string data = string.Empty;
        using (HttpClient Client = GetHttpClient())
        {
            Client.Timeout = TimeSpan.FromMinutes(60);
            data = await Client.GetStringAsync(url);
        }
        return data;
    }
private async static Task<Stream> OpenFile(string path, FileAccess access)
{
    StorageFile file = await StorageFile.GetFileFromPathAsync(path);
    if (access == FileAccess.Read)
        return await file.OpenStreamForReadAsync();
    else
        return await file.OpenStreamForWriteAsync();
}

public static async Task<int> MemberEnrollment_PostToLocal_Bulk(StorageFile File)
{
    int result = 0;
    List<string> columns = new List<string>();
    List<string> expressions = new List<string>();
    try
    {
        //read the column names
        var lines = await FileIO.ReadLinesAsync(File, Windows.Storage.Streams.UnicodeEncoding.Utf8);
        foreach (var line in lines)
        {
            //columns = string.Join(",", line.Replace("\"", "").Split(new char[] { '\t' }, StringSplitOptions.RemoveEmptyEntries));
            string[] arrHeaders = line.Replace("\"", "").Split(new char[] { '\t' }, StringSplitOptions.RemoveEmptyEntries);
            foreach (string header in arrHeaders)
            {
                columns.Add(header);
            }

            break;
        }
        result = await Task.Run(() =>
        {
            return DataLayer.MemberEnrollment_Write_Bulk(File.Path, columns, expressions);
        });
    }
    catch (Exception ex)
    {
        throw ex;
    }
    return result;
}


public static int MemberEnrollment_Write_Bulk(string FilePath,List<string> columns,List<string> expressions)
{
    int result = 0;
    using (MySqlConnection con = DataProvider.GetConnection())
    {
        MySqlBulkLoader mySqlBulkLoader = new MySqlBulkLoader(con);
        //mySqlBulkLoader.CharacterSet = "utf8";
        mySqlBulkLoader.FieldQuotationCharacter = '"';
        mySqlBulkLoader.Expressions.AddRange(expressions);
        //mySqlBulkLoader.EscapeCharacter = '\\';
        //mySqlBulkLoader.FieldTerminator = "\t";
        mySqlBulkLoader.FileName = FilePath;
        mySqlBulkLoader.LineTerminator = "\\r\\n";
        mySqlBulkLoader.NumberOfLinesToSkip = 1;
        mySqlBulkLoader.TableName = "AC_Memberenrollment";
        mySqlBulkLoader.Timeout = 120;
        mySqlBulkLoader.Columns.AddRange(columns);
        result = mySqlBulkLoader.Load();
    }
    return result;
}

一旦调试器点击

result = mySqlBulkLoader.Load();

行，它抛出以下错误:

MySql.Data.MySqlClient.MySqlException occurred
  HResult=0x80131500
  Message=Fatal error encountered attempting to read the resultset.
  Source=<Cannot evaluate the exception source>
  StackTrace:
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) in C:\Users\ramesh\Downloads\MySql.Data.UWP-master\MySql.Data.UWP-master\MySql.Data.UWP\command.cs:line 545
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader() in C:\Users\ramesh\Downloads\MySql.Data.UWP-master\MySql.Data.UWP-master\MySql.Data.UWP\command.cs:line 397
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() in C:\Users\ramesh\Downloads\MySql.Data.UWP-master\MySql.Data.UWP-master\MySql.Data.UWP\command.cs:line 340
   at MySql.Data.MySqlClient.MySqlBulkLoader.Load() in C:\Users\ramesh\Downloads\MySql.Data.UWP-master\MySql.Data.UWP-master\MySql.Data.UWP\BulkLoader.cs:line 277
   at Controller.DAL.DataLayer.MemberEnrollment_Write_Bulk(String FilePath, List`1 columns, List`1 expressions) in E:\Work\Controller\DAL\DataLayer.cs:line 344
   at Controller.Classes.DataServices.<>c__DisplayClass17_0.<MemberEnrollment_PostToLocal_Bulk>b__0() in E:\Work\Controller\Classes\DataServices.cs:line 1333
   at System.Threading.Tasks.Task`1.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()

Inner Exception 1: MySqlException: Fatal error encountered attempting
 to read the resultset.

Inner Exception 2: MySqlException: Error during LOAD DATA LOCAL INFILE

Inner Exception 3: NullReferenceException: Object reference not set to
an instance of an object.

为了了解真正的原因，我从https://github.com/nudaca/MySql.Data.UWP下载了MySql.Data.UWP.dll的源代码并发现实际问题发生在尝试在 FileStream.cs 函数中打开 csv 文件时

private async void OpenFile(string path, FileAccess access)
{
  StorageFile file = await StorageFile.GetFileFromPathAsync(path);//<--error here
  if (access == FileAccess.Read)
    stream = await file.OpenStreamForReadAsync();
  else
    stream = await file.OpenStreamForWriteAsync();
}

实际错误是“访问被拒绝”!

当我在 Workbench 中执行从 SqlCommand 生成的命令字符串时，它成功加载数据。

我已在 Package.manifest 中启用 Internet(客户端)、专用网络(客户端和服务器)和可移动存储的功能。

我搜索了各种帖子并尝试了所有解决方案，如下所示:

• UWP UnauthorizedException
• FileStream "access is denied" error all of a sudden
• Access to file in UWP
• Windows 10 Universal App File/Directory Access

我花了很长时间才将这篇文章发布到这里，并在挠头解决这个问题时撕裂了我肥沃的头皮。请帮我批量插入数据!

Answer 1

在您的代码中，您在 DownloadsFolder 中创建 StorageFile csv 文件并使用文件路径在OpenFile方法中获取文件。UWP 应用程序在沙盒中运行，对文件系统的访问非常有限，StorageItems(即 StorageFolder 和 StorageFile)是 Windows 应用商店应用程序的规范存储标识符，而不是路径。请参阅 Rob 的博客 Skip the path: stick to the StorageFile .

对于您的问题，您可以尝试使用LocalFolder而不是DownloadsFolder ，因为数据存储在我们的本地文件夹(ApplicationData.Current.LocalFolder)中，所以我们可以使用路径。请参阅File access permissions了解有关 UWP 应用可以访问的文件系统位置的详细信息。