Npgsql BeginTextImport try import from file (not from STDIN)

Question

I try import data to postgresql table from file via npgsql BeginTextImport This is my code:

public Object Copy(String sSchemaAndTableName, String sFilePath, Boolean bIsImport)
    {
        Boolean bRet = true;

        Object oResult = new Object();
        NpgsqlConnection conn = new NpgsqlConnection(sConnectionString);
        NpgsqlCommand cmd = new NpgsqlCommand();            

        try
        {
            conn.Open();
            NpgsqlTransaction transaction = conn.BeginTransaction();

            if (File.Exists(sFilePath))
            {
                try
                {
                    if (bIsImport)
                    {
                        conn.BeginTextImport("COPY " + sSchemaAndTableName + " FROM '" + sFilePath + "';");
                    }
                    else
                    {
                        conn.BeginTextExport("COPY " + sSchemaAndTableName + " TO '" + sFilePath + "';");
                    }
                }
                catch (Exception e)
                {
                    bRet = false;
                    transaction.Rollback();

                    throw e;
                }
                finally
                {
                    if (bRet)
                    {
                        transaction.Commit();
                    }
                }
            }
            else
            {
                throw new Exception("Plik nie istnieje: " + sFilePath);
            }

        }
        catch (Exception ex)
        {                                
            MW.Core.Common.Objects.Exceptions.Items.Add(ex);
            oResult = null;
        }
        finally
        {
            cmd.Dispose();

            conn.Close();
            conn.Dispose();
        }

        return oResult;
    }

when i run this i get errors - look at the screen's:

when i use myapp directory

when i use postresql server data directory - this works when i use pgadmin but from my app via npgsql not

It is possible to do?

Answer 1

PostgreSQL's "COPY from a file" feature doesn't do what you probably thinks it does; it doesn't import data from a file on the client side (where Npgsql is running), but rather from a file on the server side (where PostgreSQL is running). In other words, you can put a file on your PostgreSQL server and tell PostgreSQL to import it.

If you want to import a file on the client machine, you need to open it in C#, read from it and write into the TextWriter that BeginTextImport returns.

Answer 2

Now my code works great, (thank you @Shay Rojansky one more time)

public Boolean CopyFrom(String sDestinationSchemaAndTableName, String sFromFilePath)
    {
        Boolean bRet = true;

        NpgsqlConnection conn = new NpgsqlConnection(sConnectionString);
        NpgsqlCommand cmd = new NpgsqlCommand();            

        try
        {
            conn.Open();
            NpgsqlTransaction transaction = conn.BeginTransaction();

            if (File.Exists(sFromFilePath))
            {
                try
                {
                    using (var writer = conn.BeginTextImport("COPY " + sDestinationSchemaAndTableName + " FROM STDIN"))
                    {
                        foreach (String sLine in File.ReadAllLines(sFromFilePath))
                        {
                            writer.WriteLine(sLine);
                        }
                    }
                }
                catch (Exception e)
                {
                    bRet = false;
                    transaction.Rollback();

                    throw e;
                }
                finally
                {
                    if (bRet)
                    {
                        transaction.Commit();
                    }

                    transaction.Dispose();
                }
            }
            else
            {
                MW.Core.Common.Objects.Exceptions.Items.Add(new Exception("Plik nie istnieje: " + sFromFilePath));
            }
        }
        catch (Exception ex)
        {                                
            MW.Core.Common.Objects.Exceptions.Items.Add(ex);
            bRet = false;
        }
        finally
        {
            cmd.Dispose();

            conn.Close();
            conn.Dispose();
        }

        return bRet;
    }