CODEWITHSUNDEEP
c#postgresql
John
John

Reputation: 426

Writing queries for a PostgreSQL DB

To start, here's what I got so far:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;         // Npgsql .NET Data Provider for PostgreSQL

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {
            NpgsqlConnection conn = new NpgsqlConnection ("Server=127.0.0.1;Port=5432;UserId=postgres;Password=test;Database=dbab;") ;
            conn.Open() ;

            // Define a query
            NpgsqlCommand cmd = new NpgsqlCommand("set schema 'data'; select test_run.id from test_run;", conn);
            cmd.ExecuteNonQuery();

            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine(reader.GetString(0));
                }
            }

            // Close connection
            conn.Close();

            System.Console.WriteLine("Press any key to close...");
            Console.ReadKey(); 
        }
    }
}

Whenever I run this, I get NpgsqlException was unhandled. Under that it says:

An unhandled exception of type 'Npgsql.NpgsqlException' occurred in Npgsql.dll
Additional information: External component has thrown an exception.

When I click view detail, this shows: {"42P01: relation \"test_run\" does not exist"}

When I saw this, I thought maybe my query somehow messed up, but it actually is fine because I ran it in pgAdmin with no problems finding 'test_run' table.

I installed Npgsql -Version 3.0.5 and Mono.Security 3.2.3.0 with Nuget. I also added to two .dll's (not sure how to check this) into the GAC.

In addition, I uninstalled Npgsql, installed it again, but instead got version 2.2.3, and still ran into the same problem. I'm now back with version 3.0.5.

I'm not sure how to fix this, please help.

EDIT:

As jackmott said, I needed to split up the queries. Here's what I did:

NpgsqlCommand cmd = new NpgsqlCommand("set schema 'data';", conn);
cmd.ExecuteNonQuery();
NpgsqlCommand cmd2 = new NpgsqlCommand("select test_run.id from test_run;", conn);
cmd2.ExecuteNonQuery();

And it works.

Upvotes: 1

Views: 892

Answers (1)

jackmott
jackmott

Reputation: 1172

instead of set schema try:

set search_path to 'schema'

Which is what set schema should alias to, but perhaps that alias doesn't work in NpgSQL

The other thing I would try is to run the set search_path as it's own command, then run the query as the next command..

Third thing to check, is if the user has permissions to read that table in that schema.

Upvotes: 2

Related Questions