EzDev.org

npgsql

npgsql - Npgsql is the .NET data provider for PostgreSQL. Npgsql - .NET Access to PostgreSQL | Npgsql Documentation


How to pass parameter to sql 'in' statement?

I want to create this query:

select * from products where number in ('123', '234', '456');

but I can't find any example of achiving this with Npgsql and NpgsqlParameter. I tried like this:

string[] numbers = new string[] { "123", "234" };

NpgsqlCommands cmd = new NpgsqlCommands("select * from products where number in (:numbers)");
NpgsqlParameter p = new NpgsqlParameter("numbers", numbers);
command.Parameters.Add(p);

but it didn't work ;)


Source: (StackOverflow)

NpGsql EntityFramework 6 - "An operation is already in progress"

I am working on a project to connect to PostgreSQL database using NpGsql EntityFramework 6. I am getting the exception in question heading, when I try to execute the query in GetAdminUsersCount:

public class GenieRepository : IDisposable
{
    GenieDbContext db = new GenieDbContext();
    public IEnumerable<User> GetUsers()
    {
        return db.Users;
    }   
}

public int GetAdminUsersCount()
{
    return repo.GetUsers().Where(u => u.Role.RoleName == "Administrator").Count();
}

What is the reason for this error and how to resolve it?


Source: (StackOverflow)

FSharp data type provider for Postgresql

I was trying FSharp data provider but against the Postgresql using npgsql. And I busted at very first line.

When I am trying to create SqlDataConnection it is throwing error with message the connection string is not correct.

The type provider 'Microsoft.FSharp.Data.TypeProviders.DesignTime.DataProviders' reported an error: Keyword not supported: 'port:5432;database'.

Now, I test connection string and also data using Servicestack.Ormlite. That basically uses IdbConnection. So, connection is all correct. But I don't know why Type Provider is not working.

Here is code.

    //type dbSchema = SqlDataConnection<ConnectionString = "Server=localhost;Port=5432; Database=TestDB;User Id=postgres;Password=g00gle*92;" >
[<CLIMutable>]
type Person = 
    { ID : int;
      FirstName : string;
      LastName : string }

[<EntryPoint>]
let main args = 
    let dbFactory = 
        OrmLiteConnectionFactory
            (
             "Server=localhost;Port=5432; Database=TestDB;User Id=postgres;Password=*****;", 
             PostgreSqlDialect.Provider)
    use dbConnection = dbFactory.OpenDbConnection()
    Console.WriteLine dbConnection.State
    let persons = dbConnection.Select<Person>()
    persons.ForEach(fun p -> Console.WriteLine p.FirstName)
    Console.Read() |> ignore
    0

In above code first commented line is not working while with same settings below code is working. That means issue is only with type provider not with connections IMHO.

Do I need to do any other settings.

Please let me know if any other details are required.

UPDATE

After kvb's comment I tried both. Here is updated code with web config.

//type dbSchema = SqlEntityConnection<ConnectionStringName = "TestDB", Provider="Npgsql">
    type dbSchema = SqlEntityConnection< ConnectionStringName="TestDB" >

    [<CLIMutable>]
    type Person = 
        { ID : int;
          FirstName : string;
          LastName : string }

    [<EntryPoint>]
    let main args = 
        let dbFactory = 
            OrmLiteConnectionFactory
                (
                 "Server=localhost;Port=5432; Database=TestDB;User Id=postgres;Password=*******;", 
                 PostgreSqlDialect.Provider)
        use dbConnection = dbFactory.OpenDbConnection()
        Console.WriteLine dbConnection.State
        let persons = dbConnection.Select<Person>()
        persons.ForEach(fun p -> Console.WriteLine p.FirstName)
        Console.Read() |> ignore
        0

And here is web config

  <system.data>
    <DbProviderFactories>
      <add name="Npgsql Data Provider"
            invariant="Npgsql"
            description="Data Provider for PostgreSQL"
            type="Npgsql.NpgsqlFactory, Npgsql" />
    </DbProviderFactories>
  </system.data>
  <connectionStrings>
    <add name="TestDB"
          connectionString="Server=localhost:5432; Database=TestDB;User Id=postgres;Password=******;"
          providerName="Npgsql" />

  </connectionStrings>

and here is assembly in appconfig. I don't think it will be in GAC as I added via nuget

 <dependentAssembly>
    <assemblyIdentity name="Npgsql" publicKeyToken="5d8b90d52f46fda7" culture="neutral" />
    <bindingRedirect oldVersion="0.0.0.0-2.0.12.0" newVersion="2.0.12.0" />
 </dependentAssembly>

Above both one is commented and another one without which is not commented both is failing with different error. First one is failing with error

The type provider 'Microsoft.FSharp.Data.TypeProviders.DesignTime.DataProviders' reported an error: Error reading schema. error 7001: The specified store provider 'Npgsql' cannot be found in the configuration, or 'Npgsql' is not valid. Unable to find the requested .Net Framework Data Provider. It may not be installed.

and second one is with this error

The type provider 'Microsoft.FSharp.Data.TypeProviders.DesignTime.DataProviders' reported an error: Error reading schema. error 7001: The provider did not return a ProviderManifestToken string. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) The network path was not found

I still not understand why it is searching for SQL server.

Please let me know if any further infromation required.


Source: (StackOverflow)

How to add custom DB provider to be accessible in Visual Studio?

I wanted to work with custom DB provider in Visual Studio. I need it to use Entity Framework.

For example, I downloaded NpgSQL, registered them in GAC:

gacutil  -i  c:\temp\npgsql.dll
gacutil  -i  c:\temp\mono.security.dll

and added to machine.config file:

<add name="Npgsql Data Provider"
invariant="Npgsql"  support="FF"
description=".Net Framework Data Provider for Postgresql Server"
type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.6.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />

But Npgsql did not appear in Datasource list in Visual Studio:

Data source in VS

How to add custom DB provider to this list?

UPD: If I use command string edmgen.exe I got error:

error 7001: Failed to find or load the registered .Net Framework Data Provider.


Source: (StackOverflow)

PostgreSQL - best way to return an array of key-value pairs

I'm trying to select a number of fields, one of which needs to be an array with each element of the array containing two values. Each array item needs to contain a name (character varying) and an ID (numeric). I know how to return an array of single values (using the ARRAY keyword) but I'm unsure of how to return an array of an object which in itself contains two values.

The query is something like

SELECT
    t.field1,
    t.field2,
    ARRAY(--with each element containing two values i.e. {'TheName', 1 })
FROM MyTable t

I read that one way to do this is by selecting the values into a type and then creating an array of that type. Problem is, the rest of the function is already returning a type (which means I would then have nested types - is that OK? If so, how would you read this data back in application code - i.e. with a .Net data provider like NPGSQL?)

Any help is much appreciated.


Source: (StackOverflow)

npgsql Leaking Postgres DB Connections: Way to monitor connections?

Background: I'm moving my application from npgsql v1 to npgsql v2.0.9. After a few minutes of running my application, I get a System.Exception: Timeout while getting a connection from the pool.

The web claims that this is due to leaking connections (opening a db connection, but not properly closing them).

So

I'm trying to diagnose leaking postgres connections in npgsql.

From the various web literature around; one way to diagnose leaking connections is to setup logging on npgsql, and look for the leaking connection warning message in the log. Problem is, I'm not seeing this message in the logs anywhere.

I also found utility that monitors npgsql connections, but it's unstable and crashes.

So I'm left manually inspecting code. For everyplace that creates an npgsql connection, there is a finally block disposing of it. For everyplace that opens a datareader, CommandBehavior.CloseConnection is used (and the datareader is disposed).

Any other places to check or can someone recommend a way to look for leaking pool connections?


Source: (StackOverflow)

Npgsql: Timeout while getting a connection from the pool

Background: I'm moving my application from mssql to npgsql v2.0.11.92. After a few minutes or hours of running my application, I get a System.Exception: Timeout while getting a connection from the pool.

I'm using lib version 2.0.11.92 in x86 environment on x64 platform. All connections are closed and disposed in finally block at the latest, but after a while, I receive the error. So, it should be a problem of the Npgsql library.

In my application, there are lots of queries happening in the background every few seconds, but most of the times, they're just running sequentially, not in paralell.

My used connection string is: SERVER=mydbserver;MINPOOLSIZE=3;MaxPoolSize=15;ConnectionLifeTime=7;DATABASE=Test DB;UID=user;PWD=pass

I'm trying to diagnose leaking postgres connections in npgsql.

FYI: The Npgsql library had already got a leaking problem which has been fixed in latest version 2.0.11.92. But still there are some problems, I assume.

Has anybody experienced similar problems with the current library version (npgsql v2.0.11.92) ?


Source: (StackOverflow)

How to report progress from long-running PostgreSQL function to client

I have a C# client application that uses Npgsql to call a plpgsql function in PostgreSQL 9.1.4. The function takes a very long time and I'd like to report progress to the client in some way. How should I do this?

The LISTEN/NOTIFY mechanism sounds perfect for this, except that the whole thing runs inside a transaction and NOTIFY events are not sent until the end of the transaction, which is useless to me.

The other thing I've tried is RAISE NOTICE, which I can process on the client, but even those notices seem to be buffered for a while and sent in batches. It's better than nothing, but not ideal. Is there any way I can "flush" them, so they're sent to the client immediately?


Source: (StackOverflow)

Entity Framework 6 Programmatically Connect to Postgres

I'm working on programmatically establishing a connection to PostgresSQL using Entity Framework 6. I have this class:

public class ClearspanDatabaseContext : DbContext

with this constructor:

public ClearspanDatabaseContext()
    : base(buildConnectionString())
{
}

Here's the static method that makes the connection string programmatically:

private static string buildConnectionString()
{
    RegisterDbProvider("Npgsql", ".Net Framework Data Provider for Postgresql", "Npgsql Data Provider", "Npgsql.NpgsqlFactory, Npgsql");
    EntityConnectionStringBuilder entityConnectionStringBuilder = new EntityConnectionStringBuilder();
    entityConnectionStringBuilder.Provider = "Npgsql";
    entityConnectionStringBuilder.ProviderConnectionString = "host=192.168.168.140;Port=5432;username=ClearspanDevLogin;password=*******;database=ClearspanWebServerDev";
    return entityConnectionStringBuilder.ToString();
}

And here's the method that registers Npgsql as a database provider, taken from this source:

public static bool RegisterDbProvider(string invariant, string description, string name, string type)
{
    try
    {
        DataSet ds = ConfigurationManager.GetSection("system.data") as DataSet;
        foreach (DataRow row in ds.Tables[0].Rows)
        {
            if (row["InvariantName"].ToString() == invariant)
            {
                return true;
            }
        }
        ds.Tables[0].Rows.Add(name, description, invariant, type);
        return true;
    }
    catch
    {
    }
    return false;
}

This generates a string like this:

"provider=Npgsql;provider connection string=\"host=192.168.168.140;Port=5432;username=ClearspanDevLogin;password=********;database=ClearspanWebServerDev\""

But I get an ArgumentException:

Keyword not supported: 'provider'.

I think I am close to the programmatic connection, but am missing something small. What can I do to resolve this exception and properly setup this connection programmatically? No app.config answers, I'm working in a class library, which ignores app.config (see the comments of the accepted answer to this question). This program must remain this way because it is used as a plugin - it does not (nor should it) run on its own. Thanks in advance.


Source: (StackOverflow)

Dapper Bulk Insert Returning Serial IDs

I am attempting to perform a bulk-insert using Dapper over Npgsql, that returns the ids of the newly inserted rows. The following insert statement is used in both of my examples:

var query = "INSERT INTO \"MyTable\" (\"Value\") VALUES (@Value) RETURNING \"ID\"";

First, I attempted to add an array of objects with a "Value" property:

var values = new[] {
    new { Value = 0.0 },
    new { Value = 0.5 }
};
var ids = connection.Query<int>(query, values);

However, that fails with the NpgsqlException: "ERROR: 42703: column "value" does not exist". After reading this question, I thought that perhaps I have to pass a DataTable object instead of an object array:

var dataTable = new DataTable();
dataTable.Columns.Add("Value", typeof(double));
dataTable.Rows.Add(0.0);
dataTable.Rows.Add(0.5);
var ids = connection.Query<int>(query, dataTable);

However, this fails with the exact same exception. How can I perform a bulk-insert and get the resulting serial ids out of Dapper over Npgsql?

I did note that the casing of the exception does not match the column name, but I am certain that I have quotes around the table and column names, so I'm not certain why it says "value" instead of "Value" in the exception. Just thought I would mention it in case it is related to the error somehow, as it is easy to overlook casing.

-- EDIT --

To clarify, this is the SQL to create the table

CREATE TABLE "MyTable" (
    "ID" SERIAL PRIMARY KEY,
    "Value" DOUBLE PRECISION NOT NULL
);

And using the variables "query" and "values" defined above, this is the code that is working on a per-row basis:

var ids = new List<int>();
foreach (var valueObj in values) {
    var queryParams = new DynamicParamaters();
    queryParams.Add("Value", valueObj.Value);
    ids.AddRange(connection.Query<int>(query, queryParams));
}

The issue is that I need to be able to insert hundreds (perhaps thousands in the near future) of rows per second into "MyTable", so waiting for this loop to iteratively send each value to the database is cumbersome and (I assume, but have yet to benchmark) time consuming. Further, I perform additional computation on the values that may or may not result in additional inserts where I need a foreign key reference to the "MyTable" entry.

Because of these issues, I am looking for an alternative that sends all values in a single statement to the database, in order to reduce network traffic and processing latency. Again, I have NOT benchmarked the iterative approach yet... what I am looking for is an alternative that does a bulk insert so I can benchmark the two approaches against each other.


Source: (StackOverflow)

"WHERE x IN y" clause with dapper and postgresql throwing 42601: syntax error at or near \"$1\"

I have an array of strings, and I'd like to have a query containing an IN clause, like:

"... WHERE t.name IN ('foo', 'bar', 'baz')..>"

Here's the final bit of my query, which contains a "where X in Y" clause:

...
left join genre_tag_band_join tj on hb.id = tj.band_id or ob.id = tj.band_id
left join genre_tags t on tj.genre_tag_id = t.id
inner join venues v on e.venue_id = v.id

where t.name IN @tagsParam...

I make a Dapper call like this

var shows = con.Query<Event, Band, Band, GenreTag, Venue, Event>(query, (e, hb, ob, gt, v) =>
{
    Event show;
    ...
    return e;
},
new { tagsParam = tagsArr}).AsQueryable();

where tagsArr is a string[].

I get exception:

{"42601: syntax error at or near \"$1\""}


Source: (StackOverflow)

What happens when Npgsql connection pool reaches Max

Both the name of the connection string parameter and this blog post - http://fxjr.blogspot.co.uk/2010/04/npgsql-connection-pool-explained.html - lead me to believe that Npgsql wont exceed the MaxPoolSize value set in the connection string. However the docs (http://npgsql.projects.postgresql.org/docs/manual/UserManual.html) say "Max size of connection pool. Pooled connections will be disposed of when returned to the pool if the pool contains more than this number of connections. Default: 20"

This suggests that the pool can actually grow larger than MaxPoolSize and it is in fact just a level at which Npgsql starts to aggressively remove connections from the pool as soon as they are returned.

I've been searching to try and find an answer but I can find out exactly what happens when you reach MaxPoolSize. Anyone else know?

edit: I should add we are using Npgsql 2.0.6.0 due to another dependency being supported only up to that version.


Source: (StackOverflow)

How to define 'geography' type using Npgsql and OrmLite (using postgresql, postgis, c#)

How do I define a postgis 'geography' type in my C# class model so that OrmLite can easily pass it through to Postgresql so I can run spatial queries in addition to saving spatial data to the 'geography' column?


Source: (StackOverflow)

Connection still idle after close

I've a C# client application that need to checks a table on a Postgres db every 15 minutes. The problem is that I need to install this client into more or less 200 client so, for that I need to close the DB connection after the query.

I use .Close() method but, if I check on pg_stat_activity table on Postgres DB, I can see the connection still open in IDLE status. How can I fix that issue? Is it possible to close definitely the connection?

thanks, Andrea


Source: (StackOverflow)

How to use Entity Framework + PostgreSQL from connection?

I have already seen threads discussing the use of Entity Framework and PostgreSQL with official instructions. Those instructions need to run gacutil for every install which is not so handy for deployment purposes.

What I want to do here is passing PostgreSQL connection directly to the DbContext constructor. This is enough for me because I am going to use CodeFirst without designer. This is what I do:

public class Context : DbContext
{
    Context(System.Data.Common.DbConnection connection)
        : base(connection, true)
    {
    }

    public static Context CreateContext()
    {
        NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=********;Database=xxx;");
        conn.Open();

        return new Context(conn);
    }
}

But using this method I get a NotSupportedException with message:

Unable to determine the provider name for connection of type 'Npgsql.NpgsqlConnection'.

What should I do?


Source: (StackOverflow)