C# UWP MySql BulkLoader - Unable to load data using csv

Question

I'm having trouble loading data from csv into mysql database using UWP application. What I'm basically trying to do is:

• Read the data from the csv file via HttpClient (200k records, 32 megabytes)
• Save the data into a csv file in my application's temp folder
• Read the file and identify the columns that need to be inserted
• Use the MySqlBulkLoader class to load the csv into the database

Below is the code that I'm trying:

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

As soon as the debugger hits the

result = mySqlBulkLoader.Load();

line, it throws the below error:

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.

To understand the actual reason, I had downloaded the source code of the MySql.Data.UWP.dll from https://github.com/nudaca/MySql.Data.UWP and found out that the actual issue occurs while trying to open the csv file in FileStream.cs at function

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

and the actual error is "Access is Denied"!

When I execute the command string generated from the SqlCommand in Workbench, it loads the data successfully.

I have enabled the capabilities in the Package.manifest for Internet(Client), Private Network(Client & Server) and Removable Storage.

I searched various posts and did try all the solutions, like so:

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

I've spent a long time before posting this here and ripped a portion of my fertile scalp while scratching my head to resolve this issue. Please help me out to bulk insert the data!

Answer 1

In your code, you create the StorageFile csv file in the DownloadsFolder and use the file path to get the file in the OpenFile method. UWP applications run sandboxed and have very limited access to the file system and StorageItems(i.e. StorageFolder and StorageFile) are the canonical storage identifiers for Windows Store apps, not the path. See Rob's blog Skip the path: stick to the StorageFile.

As for your issue, you can try to use the LocalFolder instead of the DownloadsFolder, because the data that are stored in our local folder (ApplicationData.Current.LocalFolder), we can use the path. See File access permissions to learn more about the file system locations that UWP apps can access.