My stumbling through the wilds of .Net, ADO.Net and PostGRESQL continues...
I left out a critical requirement from my discussion of .Net providers: They must be compatible with some generic data type inside a .Net application. The most that is allowed to change is a connection string. If I use ODBC, I can access the database through OdbcConnection and its related classes. All I have to do to change from PostGRESQL to SQL Server is to change from a DSN that refers to a PostGRESQL database to a DSN that refers to a SQL Server database. When I was trying to get the PgOleDb provider to work, I was using the OleDbConnectioni class and its relatives with this connection string:
"Provider=PostgreSQL OLE DB Provider;Data Source=localhost;location=Great_Lakes_10_09;User ID=caps;Password=asdlkjqp"
Somewhere in the depths of the registry, "PostgreSQL OLE DB Provider" is associated with the PgOleDb provider. If I want to connect to a SQL Server database, I presume that I would be just change the name of the provider in this string to another name that is associated in the registry with a SQL Server provider. I need a PostGRESQL provider that can be used through an OleDbConnection object just by using a connection string like:
"Provider=Some PostgreSQL Provider That Actually Works;Data Source=localhost;location=Great_Lakes_10_09;User ID=caps;Password=asdlkjqp"
All of this is because the first thing a user sees when he starts our application is a dialog box in which he selects a database. An ini file contains the list of available names, and each name is associated with a connection string. When the user selects a name from a listbox, the connection string is read from the ini file. So the only thing I can change once the application is built is the connection string. The code has to work for PostGRESQL, SQL Server, and any other reasonably popular database system. We can't have one version of the code built for PostGRESQL and another for SQL Server. Therefore, unless there's some other way of setting it up, I cannot use Npgsql because I cannot use an NpgsqlConnection object.
RobR