What is a CData ADO.NET Provider for PostgreSQL?

The CData ADO.NET Provider for PostgreSQL offers the most natural way to access PostgreSQL data from .NET applications. The provider wraps the complexity of accessing PostgreSQL data in an easy-to-integrate, fully managed ADO.NET Data Provider.

This article reviews the core features of CData's PostgreSQL ADO.NET Provider. It explores how to use the ADO.NET Provider to connect to PostgreSQL, query data from a specific table, update data in PostgreSQL, and execute a stored procedure.

Core features

The provider enables standards-based access to PostgreSQL databases version 7.4 and later.
Comprehensive support for CRUD (Create, Read, Update, and Delete) operations.
Supports ADO.NET Entity Framework (EF 6 & EFCore)
Support for 32-bit and 64-bit operating systems.
Supports .NET Framework 4.0 and above as well as .NET Standard 2.0.
Seamless integration with Microsoft Visual Studio versions 2012 and above as a registered ADO.NET provider.

Connecting to PostgreSQL

The CData ADO.NET Provider for PostgreSQL supports authentication to PostgreSQL via the following types of authentication schemes:

Standard (Password)
AzureAD
AzurePassword
AzureMSI
AwsIAMRoles
AwsEC2Roles
GCPServiceAccount

The following section reviews an example of using the Password authentication scheme to authenticate your connection to PostgreSQL:


using (PostgreSQLConnection connection =   

  new PostgreSQLConnection("User=postgres;Password=admin;Database=postgres;Server=127.0.0.1;Port=5432"))  
{
  connection.Open();  
}

VB.NET


Using connection As New PostgreSQLConnection("User=postgres;Password=admin;Database=postgres;Server=127.0.0.1;Port=5432") 

  connection.Open 

End Using

Read more about the CData PostgreSQL ADO.NET Provider authentication schemes and connection properties on the CData Documentation page: CData PostgreSQL ADO.NET Provider Documentation

Using the ADO.NET Provider

READ

The following code examples connect to PostgreSQL and run a SELECT SQL query to return ShipName and ShipCity columns of the Orders table:


string connectionString = "User=postgres;Password=admin;Database=postgres;Server=127.0.0.1;Port=5432"; 

  

using (PostgreSQLConnection connection = new PostgreSQLConnection(connectionString)) { 

  PostgreSQLDataAdapter dataAdapter = new PostgreSQLDataAdapter("SELECT ShipName, ShipCity FROM \"postgres\".\"schema01\".Orders", connection); 

  DataTable table = new DataTable(); 
  dataAdapter.Fill(table); 

  Console.WriteLine("Contents of Orders."); 

  foreach (DataRow row in table.Rows) { 
    Console.WriteLine("{0}: {1}", row["ShipName"], row["ShipCity"]); 
  } 

}

VB.NET


Dim connectionString As String = "User=postgres;Password=admin;Database=postgres;Server=127.0.0.1;Port=5432" 

Using connection As New PostgreSQLConnection(connectionString) 

  Dim dataAdapter As New PostgreSQLDataAdapter("SELECT ShipName, ShipCity FROM \"postgres\".\"schema01\".Orders", connection) 

  Dim table As New DataTable() 
  dataAdapter.Fill(table) 

  Console.WriteLine("Contents of Orders.") 

  For Each row As DataRow In table.Rows 
    Console.WriteLine("{0}: {1}", row("ShipName"), row("ShipCity")) 
  Next 

End Using

WRITE

The following code examples connect to PostgreSQL and run an UPDATE SQL Statement to update the ShipCity of one of the "postgres"."schema01".Orders entries:


using (PostgreSQLConnection connection = new PostgreSQLConnection(connectionString)) { 

  PostgreSQLDataAdapter dataAdapter = new PostgreSQLDataAdapter(
    "SELECT ShipName, ShipCity FROM \"postgres\".\"schema01\".Orders", connection); 

  dataAdapter.UpdateCommand = new PostgreSQLCommand( 
    "UPDATE \"postgres\".\"schema01\".Orders SET ShipCity = @ShipCity " + 
    "WHERE Id = @Id", connection); 

  dataAdapter.UpdateCommand.Parameters.Add(new PostgreSQLParameter("@ShipCity", "ShipCity", DbType.String )); 
  dataAdapter.UpdateCommand.Parameters.Add(new PostgreSQLParameter("@Id", "Id", DbType.String )); 
  dataAdapter.UpdateCommand.Parameters[1].SourceVersion = DataRowVersion.Original; 

  DataTable table = new DataTable(); 
  dataAdapter.Fill(table); 

  DataRow firstrow = table.Rows[0]; 
  firstrow["ShipCity"] = "Raleigh"; 
  dataAdapter.Update(table); 

  Console.WriteLine("Rows after update."); 

  foreach (DataRow row in table.Rows) { 
    Console.WriteLine("{0}: {1}", row["ShipName"], row["ShipCity"]); 
  } 
}

VB.NET


Using connection As New PostgreSQLConnection(connectionString) 

  Dim dataAdapter As New PostgreSQLDataAdapter( 
    "SELECT ShipName, ShipCity FROM \"postgres\".\"schema01\".Orders", connection) 

  dataAdapter.UpdateCommand = New PostgreSQLCommand( 
    "UPDATE \"postgres\".\"schema01\".Orders SET ShipCity = @ShipCity " + 
    "WHERE Id = @Id", connection) 

  dataAdapter.UpdateCommand.Parameters.Add(new PostgreSQLParameter("@ShipCity", "ShipCity" DbType.String )) 
  dataAdapter.UpdateCommand.Parameters.Add(new PostgreSQLParameter("@Id", "Id", DbType.String)) 
  dataAdapter.UpdateCommand.Parameters(1).SourceVersion = DataRowVersion.Original 

  Dim table As New DataTable() 
  dataAdapter.Fill(table)     

  Dim firstrow As DataRow = table.Rows(0) 
  firstrow("ShipCity") = "Raleigh" 

  dataAdapter.Update(table) 

  Console.WriteLine("Rows after update.") 

  For Each row As DataRow In table.Rows 
    Console.WriteLine("{0}: {1}", row("ShipName"), row("ShipCity")) 
  Next 
End Using

Stored procedure

The following code examples execute a Stored Procedure called SelectEntries that retrieves rows from a specified object in the connected PostgreSQL database:


string connectionString = "User=postgres;Password=admin;Database=postgres;Server=127.0.0.1;Port=5432"; 

using (PostgreSQLConnection connection = new PostgreSQLConnection(connectionString)) { 
  PostgreSQLCommand cmd = new PostgreSQLCommand("SelectEntries", connection); 
  cmd.CommandType = CommandType.StoredProcedure; 
  cmd.Parameters.Add(new PostgreSQLParameter("@ObjectName", "Account")); 
  // Add other parameters as needed ... 

  PostgreSQLDataReader rdr = cmd.ExecuteReader(); 
  while (rdr.Read()) { 
    for (int i = 0; i < rdr.FieldCount; i++) { 
      Console.WriteLine(rdr.GetName(i) + " --> " + rdr[i]); 
    } 
    Console.WriteLine(); 
  } 
}

VB.NET


Dim connectionString As String = "User=postgres;Password=admin;Database=postgres;Server=127.0.0.1;Port=5432" 

Using connection As New PostgreSQLConnection(connectionString) 
  Dim cmd As New PostgreSQLCommand("SelectEntries", connection) 
  cmd.CommandType = CommandType.StoredProcedure 
  cmd.Parameters.Add(New PostgreSQLParameter("@ObjectName", "Account")) 
  ' Add other parameters as needed ... 

  Dim rdr As PostgreSQLDataReader = cmd.ExecuteReader() 
  While rdr.Read() 
      For i As Integer = 0 To rdr.FieldCount - 1 
          Console.WriteLine(rdr.GetName(i) + " --> " + rdr(i)) 
      Next 
      Console.WriteLine() 
  End While 
End Using

Data type mapping

The CData Provider translates PostgreSQL data types into .NET-compatible types. A few examples to highlight:

PostgreSQL Schema	CData Schema
bigint	long
json	string
numeric	decimal
smallint	int
xml	string

Unlock seamless PostgreSQL connectivity in your .NET applications

Are you looking to unlock the full potential of your PostgreSQL data? CData's PostgreSQL ADO.NET Provider can seamlessly integrate your .NET applications with your PostgreSQL data. With the CData ADO.NET Provider for PostgreSQL Community License, you get free-forever libraries to access your data in personal .NET projects, all through familiar SQL. Request a license and start building PostgreSQL-connected apps today!

Feedback