CODEWITHSUNDEEP
c#sqlnpgsqldataadapter
Bunzitop
Bunzitop

Reputation: 204

DataAdapter throws error while filling DataTable

I am trying to fill DataTable using NpgsqlDataAdapter. I have prepared my command as 

string commandString=@" drop  table if exists tempdata;  
create temp table tempdata as  SELECT X X X X from (_query_);
SELECT x+x, xx, x-y INTO newTempTable FROM tempdata;

And using below function to fill data in datatable

public DataTable Searchpg(string CommandString, NpgsqlParameter[] param)
{
    DataTable ResultTable = new DataTable();
    try
    {

        OpenConnection();

        DbCommandpg.CommandText = CommandString;
        DbCommandpg.Connection = DatabaseConnectionpg;
        DbCommandpg.Parameters.Clear();
        if (param != null)
        {

            DbCommandpg.Parameters.AddRange(param);
        }
        adappg.SelectCommand = DbCommandpg;
        ResultTable.Clear();
        adappg.Fill(ResultTable);
    }
    catch (Exception ex)
    {
        File.writeException(ex.Message, null);
        throw ex;
    }
    finally
    {
        DatabaseConnectionpg.Close();
    }
    return ResultTable;
}

Error Occurs at adappg.Fill(ResultTable);

And error message is {"42P01: relation \"tempdata\" does not exist"} enter image description here

I am using NpgSql Version 3.0.2.0 , VS 2013 and Postgres 9.3

BUT when i run same query in sql editor in pgadmin, it runs finely and return result as per desired.

UPDATE: The query works smoothly with Npgsql 2.0.1.0 but not with 3.x

Upvotes: 0

Views: 1580

Answers (1)

Shay Rojansky
Shay Rojansky

Reputation: 16672

This is a known issue starting Npgsql 3.x, described here: https://github.com/npgsql/npgsql/issues/641.

In a nutshell, you cannot create an entity (e.g. table) and use that entity inside the same NpgsqlCommand - simply send your CREATE TABLE and your SELECT in separate commands.

See the issue above for an explanation, it has to do with some pretty low-level details on how Npgsql communicates with PostgreSQL. Unfortunately it's unlikely we'll fix this very soon.

Upvotes: 1

Related Questions