implement bulk copy in different environment

Question

I have a problem about bulk copy command in PostgreSQL. For instance i have a sample command of copying file CSV to table like below

CREATE TEMPORARY TABLE AlterTable (
    a1 varchar(50),
    a2 varchar(50),
    a3 varchar(50),
    a4 varchar(50),
    a5 varchar(50),
    a6 varchar(255),
    a7 varchar(255),
    a8 varchar(255),
    a9 varchar(50),
    a10 varchar(50),
    a11 varchar(50),
    a12 varchar(50),
    a13 varchar(50),
    a14 varchar(50),
    a15 varchar(50),
    a16 varchar(50),
    a17 varchar(50),
    a18 varchar(50),
    a19 varchar(50),
    a20 varchar(50),
    a21 varchar(50));
COPY AlterTable FROM 'D:\1364746_855575_20221001_20221231.csv' DELIMITER ';' CSV HEADER;

This command worked perfectly, but i need to implement this command in my visual studio code with C#, and later i need to implement this command in different environment through C# code in develop, staging and product environment. I intend to create new csv file in wwwroot folder and then implement the command above through the path in wwwroot folder like the command below

var extension = Path.GetExtension(file.FileName);
var fileName = Path.GetFileNameWithoutExtension(file.FileName)
+ "_" + fileImport.Id.ToString() + extension;
var filePath = $"{AppConstants.SettingKeys.StoredFilesImportingPath}/{fileName}";
if (!Directory.Exists($"wwwroot/{AppConstants.SettingKeys.StoredFilesImportingPath}"))
{
Directory.CreateDirectory($"wwwroot/{AppConstants.SettingKeys.StoredFilesImportingPath}");
}
await using (var streaming = File.Create($"wwwroot//{filePath}"))
{
    await file.CopyToAsync(streaming);
}
string getAbsolutePath = Path.GetFullPath($"wwwroot//{filePath}");

After I get the getAbsolutePath is 'C:\Users{myComputerName}\Desktop{myfolder}{anothermyfolder}{anotheranothermyfolder}\wwwroot\importings\1112223_855575_20220401_20220630_10f7d877-baa2-4fcb-8500-88195f9a4596.csv' I put it to the command like below

CREATE TEMPORARY TABLE AlterTable (
    a1 varchar(50),
    a2 varchar(50),
    a3 varchar(50),
    a4 varchar(50),
    a5 varchar(50),
    a6 varchar(255),
    a7 varchar(255),
    a8 varchar(255),
    a9 varchar(50),
    a10 varchar(50),
    a11 varchar(50),
    a12 varchar(50),
    a13 varchar(50),
    a14 varchar(50),
    a15 varchar(50),
    a16 varchar(50),
    a17 varchar(50),
    a18 varchar(50),
    a19 varchar(50),
    a20 varchar(50),
    a21 varchar(50));
COPY AlterTable FROM 'C:\Users\{myComputerName}\Desktop\{myfolder}\{anothermyfolder}\{anotheranothermyfolder}\wwwroot\importings\1112223_855575_20220401_20220630_10f7d877-baa2-4fcb-8500-88195f9a4596.csv' DELIMITER ';' CSV HEADER;

And it represented me an error

ERROR: could not open file "C:\Users{myComputerName}\Desktop{myfolder}{anothermyfolder}{anotheranothermyfolder}\wwwroot\importings\1112223_855575_20220401_20220630_10f7d877-baa2-4fcb-8500-88195f9a4596.csv" for reading: Permission denied HINT: COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy. SQL state: 42501

Could anyone help me with the issue above, i really desperate with it. And later could i implement it to different environment like in develop and staging environment. Thanks you in advance.

Answer 1

What you are doing (or at least seem to be doing), strikes me as being highly inefficient. You have the data as a stream, are writing it to a physical file and then attempting to read that file into PostgreSQL.

There are other ways to go about this. My answer here, shows how to upload an IEnumerable or a DataTable directly into PostreSQL from C# using a wrapper around the built-in COPY command. If you already have the data in memory, can you not simply create one of these objects from it and then upload it using code similar to mine? This is far preferable to using the server's hard drive.