Oracle & PostgreSQL: Complete Database Developer Learning Guide for .NET Developers

Learn relational database development across Oracle Database and PostgreSQL with clear definitions, practical SQL and procedural-language examples, and real C# integration patterns using ADO.NET, Dapper, and Entity Framework Core.

Introduction

Modern .NET applications often work with more than one database platform. A developer may maintain an application that uses Oracle in one organization and PostgreSQL in another. The relational concepts are familiar, but data types, SQL syntax, procedural languages, identity generation, parameter conventions, provider behavior, and deployment practices can differ.

This guide focuses specifically on Oracle Database and PostgreSQL. It is written for .NET developers who need to understand both databases, write reliable SQL, build database-side logic when appropriate, and connect applications through C#.

The guide starts with shared relational and SQL foundations, then explains Oracle SQL and PL/SQL alongside PostgreSQL SQL and PL/pgSQL. It finishes with .NET data access, transactions, security, performance, testing, deployment, and a practical application workflow. It does not attempt to teach Microsoft SQL Server T-SQL, which belongs in a separate learning guide.

Technology baseline: Examples use broadly established Oracle Database SQL/PLSQL syntax and PostgreSQL 18 SQL/PL/pgSQL documentation as the learning reference. Some capabilities vary by database release, edition, configuration, and provider version. Check the documentation for the exact environment before using version-specific features. Oracle's current documentation is branded Oracle AI Database 26ai; PostgreSQL 18 is the current documented major version in this guide's reference baseline.

Important distinction: SQL concepts are shared, but Oracle SQL is not identical to PostgreSQL SQL. Oracle uses PL/SQL for procedural database programming; PostgreSQL commonly uses PL/pgSQL. Treat examples marked Oracle or PostgreSQL as platform-specific unless explicitly labelled common SQL.

Understanding Relational Databases

What is a database?

A database is an organized collection of information stored so that applications and people can create, retrieve, update, and manage it. A database management system (DBMS) provides the software that stores the data, enforces rules, coordinates concurrent access, and offers tools for backup and recovery.

What is a relational database?

A relational database stores related information in tables. Each table represents an entity or subject, such as Customer, Product, or Order. Rows represent individual records; columns represent attributes of those records. Relationships connect records across tables using keys.

Primary key and foreign key

Primary key: a column or group of columns that uniquely identifies each row. Foreign key: a column or group of columns whose values reference a candidate key in another table, helping preserve referential integrity.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    full_name   VARCHAR(120) NOT NULL,
    email       VARCHAR(200) UNIQUE
);

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date  DATE NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

.NET perspective: A relational model often becomes a set of C# entities and relationships. Design database constraints even when the application validates input; multiple applications, jobs, and integrations may write to the same database.

Oracle Database and PostgreSQL Overview

Oracle Database

Oracle Database is a relational database management system used in many enterprise environments. It provides SQL for data operations and PL/SQL for procedural logic that runs inside the database. Oracle applications commonly connect through Oracle Data Provider for .NET (ODP.NET).

PostgreSQL

PostgreSQL is an open-source object-relational database system. It supports standard SQL plus platform capabilities such as rich data types, JSON/JSONB, extensibility, and PL/pgSQL for procedural database programming. .NET applications commonly connect through Npgsql.

Shared concepts, different implementations

Both platforms support tables, constraints, joins, transactions, views, indexes, functions, and database security. The details differ: for example, Oracle traditionally uses sequences and supports identity columns; PostgreSQL supports identity columns and sequences. Oracle uses NUMBER while PostgreSQL has types such as numeric, integer, and bigint.

Learning strategy: First understand the database concept. Then learn how Oracle expresses it and how PostgreSQL expresses it. This makes it easier to move between projects without assuming syntax is interchangeable.

Set Up Your Database Development Environment

Tools for Oracle

Use Oracle Database (local, container-based, or a managed environment) and a SQL client such as Oracle SQL Developer or SQLcl. For .NET development, install the supported Oracle Data Provider for .NET package that matches your project and deployment requirements.

Tools for PostgreSQL

Install PostgreSQL or use a managed PostgreSQL service. pgAdmin provides a graphical administration interface; psql is the interactive command-line client. For .NET, use Npgsql and, when needed, its Entity Framework Core provider.

Useful .NET packages

# PostgreSQL ADO.NET provider
 dotnet add package Npgsql

# PostgreSQL EF Core provider (select a version compatible with your EF Core version)
 dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

# Oracle managed provider (confirm current package/version in Oracle documentation)
 dotnet add package Oracle.ManagedDataAccess.Core

Keep package versions aligned with your target .NET and EF Core versions. Do not copy an old package version from a tutorial into a new project without checking its support status.

Schemas, Tables, Rows and Columns

A schema is a logical namespace for database objects such as tables, views, and procedures. Oracle associates schemas with database users. PostgreSQL databases contain schemas, and each schema groups objects within that database. A table is a named structure of columns and rows.

Qualified object names

-- Oracle: schema.table
SELECT * FROM HR.EMPLOYEES;

-- PostgreSQL: schema.table
SELECT * FROM public.employees;

Unquoted identifiers are normalized differently: Oracle stores unquoted identifiers in uppercase, while PostgreSQL folds unquoted identifiers to lowercase. Quoted identifiers preserve case and must then be quoted consistently. Prefer simple, unquoted, lowercase names for PostgreSQL and conventional unquoted names for Oracle to avoid needless quoting complexity.

A schema is not exactly the same thing in every database. In Oracle, a schema is closely tied to a user. In PostgreSQL, a database can contain multiple schemas, and users/roles are separate concepts.

Data Types: Oracle vs PostgreSQL

Why data types matter

A data type defines which values a column can store and which operations are valid. Choosing the correct type improves data quality, query behavior, storage efficiency, and application mapping.

Purpose	Oracle	PostgreSQL	.NET mapping (typical)
Whole number	NUMBER(p,0)	smallint / integer / bigint	short / int / long / decimal
Decimal	NUMBER(p,s)	numeric(p,s)	decimal
Text	VARCHAR2(n)	varchar(n) / text	string
Fixed text	CHAR(n)	char(n)	string
Date and time	DATE / TIMESTAMP	date / timestamp	DateTime / DateTimeOffset*
Boolean	Often NUMBER(1) or CHAR convention; SQL BOOLEAN availability depends on release/context	boolean	bool
Binary	BLOB / RAW	bytea	byte[] / Stream
JSON	JSON / JSON-related types by release	json / jsonb	JSON model / string / provider mapping

Precision and scale

For NUMBER(10,2) or numeric(10,2), precision is the total number of digits and scale is the number of digits after the decimal point. This is commonly appropriate for exact monetary amounts, although currency and rounding rules still need to be defined by the application.

-- Oracle
CREATE TABLE invoices (
    invoice_id NUMBER(10) PRIMARY KEY,
    total      NUMBER(12,2) NOT NULL
);

-- PostgreSQL
CREATE TABLE invoices (
    invoice_id BIGINT PRIMARY KEY,
    total      NUMERIC(12,2) NOT NULL
);

Do not assume Oracle DATE and PostgreSQL DATE/TIMESTAMP have identical semantics. Oracle DATE stores date and time to seconds; PostgreSQL DATE stores a calendar date only. Select timestamp types deliberately and define time-zone policy for the application.

Create Tables and Define Constraints

A constraint is a rule enforced by the database to protect data integrity. Application validation improves user experience, but database constraints protect the data regardless of which application writes it.

Common constraints

NOT NULL: requires a value.
UNIQUE: prevents duplicate values for a key.
PRIMARY KEY: uniquely identifies rows and implies non-nullability.
FOREIGN KEY: enforces a relationship to another table.
CHECK: restricts values using a condition.
DEFAULT: supplies a value when an insert omits the column.

-- Common pattern supported by both platforms
CREATE TABLE products (
    product_id   INTEGER PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL,
    unit_price   DECIMAL(10,2) NOT NULL,
    is_active    CHAR(1) NOT NULL,
    CONSTRAINT ck_products_price CHECK (unit_price >= 0),
    CONSTRAINT ck_products_active CHECK (is_active IN ('Y', 'N'))
);

Use explicit constraint names. Named constraints make schema reviews and error diagnosis easier. Note that boolean representations and some default expressions may differ between Oracle and PostgreSQL.

Insert, Update and Delete Data

INSERT

INSERT adds new rows. Specify the target columns so that the statement remains understandable if the table definition changes.

INSERT INTO customers (customer_id, full_name, email)
VALUES (101, 'Asha Mehta', 'asha@example.com');

UPDATE

UPDATE changes existing rows. A missing or incorrect WHERE clause can change every row in the table.

UPDATE customers
SET email = 'asha.mehta@example.com'
WHERE customer_id = 101;

DELETE

DELETE removes rows that match its condition. Use a transaction for multi-step changes that must succeed or fail together.

DELETE FROM customers
WHERE customer_id = 101;

Before running a destructive UPDATE or DELETE, run a SELECT with the same WHERE condition, confirm the affected rows, and use an appropriately scoped transaction. Never build these statements by concatenating untrusted input.

Writing SELECT Queries

SELECT retrieves data. A query can return selected columns, calculate expressions, filter rows, combine tables, and aggregate results.

SELECT customer_id, full_name, email
FROM customers
WHERE is_active = 'Y'
ORDER BY full_name;

Column aliases

SELECT
    product_name AS name,
    unit_price * 1.18 AS price_with_tax
FROM products;

DISTINCT

DISTINCT removes duplicate result rows. Use it when uniqueness is part of the required result, not as a way to hide an incorrect join.

SELECT DISTINCT country_code
FROM customers;

Filtering, Sorting and Pagination

WHERE and common operators

SELECT order_id, order_date, status
FROM orders
WHERE status IN ('NEW', 'PROCESSING')
  AND order_date >= DATE '2026-01-01'
  AND order_date <  DATE '2027-01-01'
ORDER BY order_date DESC, order_id DESC;

Use IS NULL and IS NOT NULL to test null values. Comparisons such as = NULL do not work as ordinary equality checks because NULL represents an unknown or missing value.

Pagination differences

-- PostgreSQL
SELECT order_id, order_date
FROM orders
ORDER BY order_date DESC, order_id DESC
LIMIT 20 OFFSET 40;

-- Oracle (12c and later syntax)
SELECT order_id, order_date
FROM orders
ORDER BY order_date DESC, order_id DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

Always use a deterministic ORDER BY for pagination. Include a unique tie-breaker such as the primary key; otherwise rows with equal sort values may appear inconsistently between pages.

Joins and Relationships

A JOIN combines rows from related tables. The join condition explains how records are related. Incorrect join conditions can multiply rows and produce misleading totals.

INNER JOIN

SELECT o.order_id, c.full_name, o.order_date
FROM orders o
INNER JOIN customers c
    ON c.customer_id = o.customer_id;

LEFT JOIN

SELECT c.customer_id, c.full_name, o.order_id
FROM customers c
LEFT JOIN orders o
    ON o.customer_id = c.customer_id;

Other join types

RIGHT JOIN preserves all rows from the right side. FULL OUTER JOIN preserves unmatched rows from both sides where supported. CROSS JOIN returns combinations of each row from both inputs. Self joins relate rows within the same table, such as employees and their managers.

If a parent row has multiple child rows, the join returns multiple result rows for that parent. Aggregate at the correct level or use EXISTS when you only need to test whether a related row exists.

Aggregate Functions and GROUP BY

Aggregate functions calculate one result from a set of rows. Common examples are COUNT, SUM, AVG, MIN, and MAX.

SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS lifetime_value,
    AVG(total_amount) AS average_order
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 3
ORDER BY lifetime_value DESC;

WHERE filters rows before grouping. HAVING filters groups after aggregation. In most cases, every selected non-aggregated expression must be included in GROUP BY.

When calculating totals after joining multiple one-to-many relationships, verify that the join has not duplicated the amount being summed.

Subqueries, CTEs and Set Operations

Subquery

A subquery is a query nested inside another SQL statement. It can supply a value, a set of values, or a derived table.

SELECT customer_id, full_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Common Table Expression (CTE)

A CTE names a query result for use by the statement that follows it. It can make complex SQL easier to read and can support recursive queries.

WITH customer_totals AS (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT c.full_name, ct.total_spent
FROM customer_totals ct
JOIN customers c ON c.customer_id = ct.customer_id
WHERE ct.total_spent > 10000;

Set operations

UNION combines compatible result sets and removes duplicates; UNION ALL retains duplicates. INTERSECT returns common rows. EXCEPT is supported by PostgreSQL; Oracle uses MINUS for the corresponding difference operation in its SQL syntax.

Views, Materialized Views and Sequences

View

A view is a named query that can be used like a table in many SQL statements. It can simplify repeated queries and expose a controlled representation of data.

CREATE VIEW active_customers AS
SELECT customer_id, full_name, email
FROM customers
WHERE is_active = 'Y';

Materialized view

A materialized view stores the result of a query physically. It can speed up read-heavy reporting, but its data must be refreshed according to the platform's rules and the application's freshness requirements. Oracle and PostgreSQL differ in refresh capabilities and syntax.

Sequence

A sequence generates numeric values independently of table rows. It is commonly used to generate surrogate keys. A generated value is not necessarily gap-free: rollbacks and concurrent requests can consume values.

-- Oracle
CREATE SEQUENCE order_seq START WITH 1 INCREMENT BY 1;
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (order_seq.NEXTVAL, 101, SYSDATE);

-- PostgreSQL
CREATE SEQUENCE order_seq START WITH 1 INCREMENT BY 1;
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (nextval('order_seq'), 101, CURRENT_DATE);

For new schemas, identity columns may be more convenient than manually managing a sequence. Review the exact identity syntax and provider behavior for each platform.

Indexes and Query Execution Plans

An index is a data structure that helps the database locate rows without scanning every row in a table. Indexes can improve reads, but they consume storage and add work to inserts, updates, and deletes.

B-tree index example

CREATE INDEX ix_orders_customer_date
ON orders (customer_id, order_date);

A composite index is built on more than one column. Column order matters: an index on (customer_id, order_date) is generally most useful for predicates that begin with customer_id, although the optimizer and query shape determine actual use.

Inspect the execution plan

-- PostgreSQL: inspect estimated plan
EXPLAIN
SELECT * FROM orders WHERE customer_id = 101;

-- PostgreSQL: execute and inspect actual runtime statistics
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 101;

-- Oracle: inspect plan using the available plan tools
EXPLAIN PLAN FOR
SELECT * FROM orders WHERE customer_id = 101;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN ANALYZE actually executes the PostgreSQL statement. Be careful with statements that modify data. Do not add indexes based only on intuition; measure representative queries and consider write overhead.

Transactions, ACID and Concurrency

A transaction groups database operations into a unit of work. The ACID properties describe key transaction guarantees: Atomicity, Consistency, Isolation, and Durability.

Transaction example

BEGIN;
UPDATE accounts SET balance = balance - 250 WHERE account_id = 10;
UPDATE accounts SET balance = balance + 250 WHERE account_id = 20;
COMMIT;

-- If validation fails before commit:
-- ROLLBACK;

Both Oracle and PostgreSQL support transactions, but transaction start behavior and some isolation/locking details differ. PostgreSQL starts a transaction explicitly with BEGIN or through client-managed transaction APIs; Oracle sessions commonly operate with implicit transaction boundaries and explicit COMMIT/ROLLBACK.

Concurrency concepts

Concurrent requests may update the same records. Understand row-level locks, deadlocks, optimistic concurrency, isolation levels, and retry behavior. Keep transactions short and access rows in a consistent order where possible.

.NET rule: Keep all commands that belong to one unit of work on the same connection and transaction. A transaction object created on one connection cannot be used with commands executed on another connection.

Oracle SQL: Important Platform Concepts

Oracle data dictionary and schemas

Oracle exposes metadata through data dictionary views such as USER_TABLES, USER_TAB_COLUMNS, ALL_TABLES, and ALL_TAB_COLUMNS. The exact view determines whether you see objects owned by the current user or objects accessible to it.

DUAL and built-in values

-- Common Oracle expression query
SELECT SYSDATE AS current_date_time FROM dual;

-- Current timestamp
SELECT SYSTIMESTAMP FROM dual;

DUAL is a one-row table traditionally used when selecting an expression without reading an application table. Some newer Oracle contexts allow expression queries without explicitly referencing DUAL; existing code commonly still uses it.

ROWNUM and FETCH

ROWNUM is an Oracle pseudocolumn assigned as rows are selected. For modern pagination, prefer the row-limiting clause such as FETCH FIRST / OFFSET FETCH when supported by the target Oracle version.

MERGE

MERGE can conditionally update or insert rows based on a source-to-target match. It is useful for synchronization and upsert-style workflows, but concurrency and uniqueness rules still need careful design.

MERGE INTO inventory target
USING incoming_inventory source
ON (target.product_id = source.product_id)
WHEN MATCHED THEN
    UPDATE SET target.quantity = source.quantity
WHEN NOT MATCHED THEN
    INSERT (product_id, quantity)
    VALUES (source.product_id, source.quantity);

Oracle PL/SQL Fundamentals

PL/SQL is Oracle Database’s procedural extension to SQL. It adds variables, conditions, loops, exception handling, procedures, functions, packages, and triggers so that logic can run close to the data.

PL/SQL block structure

DECLARE
    v_message VARCHAR2(100) := 'Welcome to Oracle PL/SQL';
BEGIN
    DBMS_OUTPUT.PUT_LINE(v_message);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred');
        RAISE;
END;
/

A block has an optional DECLARE section, a required BEGIN/END executable section, and an optional EXCEPTION section. The slash on a separate line is a client command used by tools such as SQL*Plus and SQLcl to submit the block; it is not part of the PL/SQL language itself.

Variables and anchored types

DECLARE
    v_customer_id customers.customer_id%TYPE := 101;
    v_customer_name customers.full_name%TYPE;
BEGIN
    SELECT full_name
    INTO v_customer_name
    FROM customers
    WHERE customer_id = v_customer_id;

    DBMS_OUTPUT.PUT_LINE(v_customer_name);
END;
/

%TYPE lets a PL/SQL variable derive its type from a table column or another variable. This reduces duplicated type declarations when the underlying column definition changes.

Oracle Procedures, Functions and Packages

Stored procedure

A procedure is a named PL/SQL program unit stored in the database. It can accept IN, OUT, and IN OUT parameters and is commonly used for a business operation or controlled data change.

CREATE OR REPLACE PROCEDURE update_customer_email (
    p_customer_id IN customers.customer_id%TYPE,
    p_email       IN customers.email%TYPE
) AS
BEGIN
    UPDATE customers
    SET email = p_email
    WHERE customer_id = p_customer_id;

    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Customer not found');
    END IF;
END;
/

Function

A function returns a value and can be called from PL/SQL expressions. Whether it is appropriate to call a function from SQL depends on its behavior and SQL-callability rules. Avoid hiding complex side effects inside functions used by queries.

CREATE OR REPLACE FUNCTION calculate_tax (
    p_amount IN NUMBER,
    p_rate   IN NUMBER
) RETURN NUMBER AS
BEGIN
    RETURN ROUND(p_amount * p_rate / 100, 2);
END;
/

Package

A package groups related procedures, functions, variables, types, and cursors. The package specification defines the public interface; the package body contains implementations. Packages help organize database APIs for larger systems.

CREATE OR REPLACE PACKAGE customer_api AS
    PROCEDURE activate_customer(p_customer_id IN NUMBER);
END customer_api;
/

CREATE OR REPLACE PACKAGE BODY customer_api AS
    PROCEDURE activate_customer(p_customer_id IN NUMBER) AS
    BEGIN
        UPDATE customers SET is_active = 'Y'
        WHERE customer_id = p_customer_id;
    END activate_customer;
END customer_api;
/

Oracle Cursors, Exceptions and Triggers

Cursor

A cursor represents a query result that PL/SQL can process. Implicit cursors are managed by Oracle for SQL statements. Explicit cursors give the developer control over opening, fetching, and closing a query result. Prefer set-based SQL when it can express the operation clearly; row-by-row loops can be slower.

DECLARE
    CURSOR c_active_customers IS
        SELECT customer_id, full_name
        FROM customers
        WHERE is_active = 'Y';
BEGIN
    FOR r IN c_active_customers LOOP
        DBMS_OUTPUT.PUT_LINE(r.customer_id || ': ' || r.full_name);
    END LOOP;
END;
/

Exception handling

BEGIN
    UPDATE customers
    SET email = 'new@example.com'
    WHERE customer_id = 999;
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        RAISE_APPLICATION_ERROR(-20002, 'Email already exists');
    WHEN OTHERS THEN
        -- Record safe diagnostic information if appropriate.
        RAISE;
END;
/

Trigger

A trigger is database code that runs automatically in response to a defined event, such as INSERT, UPDATE, or DELETE. Triggers can enforce cross-cutting rules or audit changes, but hidden side effects make systems harder to reason about. Use them deliberately and document them.

CREATE OR REPLACE TRIGGER trg_customers_updated
BEFORE UPDATE ON customers
FOR EACH ROW
BEGIN
    :NEW.updated_at := SYSTIMESTAMP;
END;
/

A trigger does not replace application-level authorization or a clear domain workflow. Avoid creating complex chains of triggers that make one application command cause many invisible database changes.

PostgreSQL SQL: Important Platform Concepts

Schemas and search_path

PostgreSQL objects live in schemas. The search_path controls which schemas are searched for unqualified object names. In production, qualify important objects or configure the search path intentionally to avoid ambiguity and security surprises.

Identity columns

CREATE TABLE customers (
    customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name   TEXT NOT NULL,
    email       TEXT UNIQUE
);

Identity columns generate values using an associated sequence. GENERATED ALWAYS is stricter about manually supplied values than GENERATED BY DEFAULT. Choose based on whether imports or controlled explicit IDs are required.

RETURNING

PostgreSQL supports RETURNING on data modification statements, allowing the application to retrieve generated or changed values without a separate SELECT.

INSERT INTO customers (full_name, email)
VALUES ('Asha Mehta', 'asha@example.com')
RETURNING customer_id, full_name;

ON CONFLICT

PostgreSQL supports INSERT ... ON CONFLICT for conflict-aware inserts and updates. Choose a unique constraint or index that represents the intended conflict key.

INSERT INTO customers (email, full_name)
VALUES ('asha@example.com', 'Asha Mehta')
ON CONFLICT (email)
DO UPDATE SET full_name = EXCLUDED.full_name
RETURNING customer_id;

PostgreSQL PL/pgSQL Fundamentals

PL/pgSQL is PostgreSQL’s procedural language for writing functions, procedures, and triggers. It supports variables, branching, loops, exceptions, and SQL statements within a block.

Basic PL/pgSQL function

CREATE OR REPLACE FUNCTION calculate_tax(
    p_amount NUMERIC,
    p_rate   NUMERIC
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN ROUND(p_amount * p_rate / 100, 2);
END;
$$;

SELECT calculate_tax(1000, 18);

Variables and SELECT INTO

DO $$
DECLARE
    v_customer_name TEXT;
BEGIN
    SELECT full_name
    INTO v_customer_name
    FROM customers
    WHERE customer_id = 101;

    RAISE NOTICE 'Customer: %', v_customer_name;
END;
$$;

A PL/pgSQL function can return a scalar, a composite value, a set of rows, or a table-shaped result. Select the return shape that best expresses the database operation and maps cleanly to the .NET caller.

PostgreSQL Functions, Procedures and Triggers

Function returning a result set

CREATE OR REPLACE FUNCTION get_customer_orders(p_customer_id BIGINT)
RETURNS TABLE(order_id BIGINT, order_date DATE, total_amount NUMERIC)
LANGUAGE sql
AS $$
    SELECT o.order_id, o.order_date, o.total_amount
    FROM orders o
    WHERE o.customer_id = p_customer_id
    ORDER BY o.order_date DESC;
$$;

Procedure

PostgreSQL supports stored procedures invoked with CALL. Functions and procedures have different invocation and return semantics. In general, functions are used in expressions and queries; procedures are invoked as commands and can support transaction control in permitted contexts.

CREATE OR REPLACE PROCEDURE mark_customer_inactive(p_customer_id BIGINT)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE customers
    SET is_active = FALSE
    WHERE customer_id = p_customer_id;
END;
$$;

CALL mark_customer_inactive(101);

Trigger function and trigger

CREATE OR REPLACE FUNCTION set_customer_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_customers_updated
BEFORE UPDATE ON customers
FOR EACH ROW
EXECUTE FUNCTION set_customer_updated_at();

PostgreSQL triggers call a trigger function. The trigger function receives context such as NEW and OLD row values for row-level triggers. Trigger timing, event, and level determine when it runs.

Working with JSON and Semi-Structured Data

Both databases support working with JSON, which is useful when an application stores flexible attributes alongside relational data. JSON should not automatically replace relational columns: fields used for joins, constraints, and frequent filters often belong in typed columns.

PostgreSQL JSONB

CREATE TABLE customer_profiles (
    customer_id BIGINT PRIMARY KEY,
    preferences JSONB NOT NULL DEFAULT '{}'::jsonb
);

SELECT customer_id
FROM customer_profiles
WHERE preferences ->> 'language' = 'en';

UPDATE customer_profiles
SET preferences = jsonb_set(preferences, '{theme}', '"dark"'::jsonb)
WHERE customer_id = 101;

PostgreSQL JSONB stores a decomposed binary representation that supports indexing and efficient operators. JSON preserves input text representation more closely; JSONB is often selected when querying and indexing are important.

Oracle JSON

Oracle provides JSON support with syntax and storage options that depend on the database release. JSON data can be validated and queried with SQL/JSON functions. Consult the target release documentation for supported native JSON data types and indexing choices.

From .NET, map JSON deliberately. Decide whether your C# model owns a strongly typed structure, a JSON document, or a provider-specific JSON representation. Validate shape and version changes at the application boundary.

Connecting Oracle to .NET with ODP.NET

ODP.NET is Oracle’s data provider for .NET. It exposes ADO.NET classes for connections, commands, parameters, transactions, and data readers. The managed provider package commonly used by modern .NET applications is Oracle.ManagedDataAccess.Core; verify the current package and supported runtime in Oracle documentation.

Connection string configuration

{
  "ConnectionStrings": {
    "Oracle": "User Id=app_user;Password=use-a-secret-store;Data Source=//db-host:1521/app_service"
  }
}

Parameterized query with OracleConnection

using Oracle.ManagedDataAccess.Client;

var connectionString = configuration.GetConnectionString("Oracle");

await using var connection = new OracleConnection(connectionString);
await connection.OpenAsync(cancellationToken);

await using var command = connection.CreateCommand();
command.CommandText = "SELECT customer_id, full_name FROM customers WHERE customer_id = :customerId";
command.Parameters.Add(new OracleParameter("customerId", customerId));

await using var reader = await command.ExecuteReaderAsync(cancellationToken);
while (await reader.ReadAsync(cancellationToken))
{
    var id = reader.GetInt32(0);
    var name = reader.GetString(1);
    // Map the row to an application model.
}

Oracle SQL commonly uses named bind variables prefixed with a colon, such as :customerId. Bind values as parameters rather than concatenating them into SQL text. For stored procedures, set CommandType.StoredProcedure and configure parameter direction and OracleDbType when necessary.

Use the exact CLR and OracleDbType mappings required by your schema. Numeric precision, timestamps, REF CURSOR outputs, and large objects may need explicit parameter configuration.

Connecting PostgreSQL to .NET with Npgsql

Npgsql is the ADO.NET data provider for PostgreSQL. It supports PostgreSQL-specific types and features and also provides an Entity Framework Core provider.

Parameterized query with Npgsql

using Npgsql;

var connectionString = configuration.GetConnectionString("Postgres");

await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync(cancellationToken);

const string sql = """
    SELECT customer_id, full_name
    FROM customers
    WHERE customer_id = @customerId
    """;

await using var command = new NpgsqlCommand(sql, connection);
command.Parameters.AddWithValue("customerId", customerId);

await using var reader = await command.ExecuteReaderAsync(cancellationToken);
while (await reader.ReadAsync(cancellationToken))
{
    var id = reader.GetInt64(0);
    var name = reader.GetString(1);
}

Npgsql supports named placeholders such as @customerId and positional placeholders. Prefer consistent parameter naming and explicit parameter types where inference could be ambiguous. The PostgreSQL provider can expose native types such as UUID, arrays, ranges, enums, and JSONB.

Npgsql recommends reusing data source objects and relying on connection pooling. Opening and disposing logical connections per operation is normal: pooling manages the underlying physical connections.

Using Dapper with Both Databases

Dapper is a lightweight object mapper built on ADO.NET. It maps query results to C# objects while leaving SQL and database-specific syntax visible to the developer. You can use it with Oracle and PostgreSQL by supplying the appropriate provider connection.

Simple DTO

public sealed class CustomerDto
{
    public long CustomerId { get; init; }
    public string FullName { get; init; } = "";
}

PostgreSQL example

using Dapper;
using Npgsql;

await using var connection = new NpgsqlConnection(postgresConnectionString);

var customer = await connection.QuerySingleOrDefaultAsync<CustomerDto>(
    "SELECT customer_id AS CustomerId, full_name AS FullName " +
    "FROM customers WHERE customer_id = @CustomerId",
    new { CustomerId = customerId });

Oracle example

using Dapper;
using Oracle.ManagedDataAccess.Client;

await using var connection = new OracleConnection(oracleConnectionString);

var customer = await connection.QuerySingleOrDefaultAsync<CustomerDto>(
    "SELECT customer_id AS CustomerId, full_name AS FullName " +
    "FROM customers WHERE customer_id = :CustomerId",
    new { CustomerId = customerId });

Dapper does not make Oracle and PostgreSQL SQL interchangeable. Keep provider-specific SQL in a deliberate data-access boundary, and test mapping for numeric types, dates, output parameters, and stored procedure result sets.

Using Entity Framework Core

Entity Framework Core (EF Core) is an object-relational mapper (ORM). It maps C# entity types to relational tables and translates LINQ queries into SQL. EF Core can reduce repetitive data-access code, but developers still need to understand generated SQL, database constraints, indexes, and transaction behavior.

Entity model and DbContext

public sealed class Customer
{
    public long CustomerId { get; set; }
    public string FullName { get; set; } = "";
    public string? Email { get; set; }
}

public sealed class AppDbContext(DbContextOptions<AppDbContext> options)
    : DbContext(options)
{
    public DbSet<Customer> Customers => Set<Customer>();
}

PostgreSQL provider configuration

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(
        builder.Configuration.GetConnectionString("Postgres")));

Oracle provider configuration

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseOracle(
        builder.Configuration.GetConnectionString("Oracle")));

Install the matching provider package and follow its provider-specific setup documentation. Oracle’s EF Core provider and Npgsql’s EF Core provider differ in supported features, SQL translation, migrations, and type mappings. Do not assume one migration set can be applied unchanged to both database engines.

LINQ query

var activeCustomers = await dbContext.Customers
    .Where(c => c.Email != null)
    .OrderBy(c => c.FullName)
    .Select(c => new CustomerDto
    {
        CustomerId = c.CustomerId,
        FullName = c.FullName
    })
    .ToListAsync(cancellationToken);

Inspect generated SQL for important queries. EF Core does not guarantee that every LINQ expression translates identically across providers. Avoid client-side evaluation of large datasets and avoid loading entire tables when only a small projection is required.

Repository and Data Access Design for .NET

A data access layer isolates persistence details from application behavior. It can use EF Core, Dapper, ADO.NET, or a combination. The right abstraction depends on the complexity of the application; do not add repository interfaces mechanically around every DbSet if they provide no meaningful boundary.

A simple application service

public sealed class CustomerService(AppDbContext dbContext)
{
    public async Task<CustomerDto?> GetByIdAsync(
        long customerId,
        CancellationToken cancellationToken)
    {
        return await dbContext.Customers
            .Where(c => c.CustomerId == customerId)
            .Select(c => new CustomerDto
            {
                CustomerId = c.CustomerId,
                FullName = c.FullName
            })
            .SingleOrDefaultAsync(cancellationToken);
    }
}

Provider-specific data access

If an application supports both Oracle and PostgreSQL, define a clear strategy. You may use EF Core with separate provider configurations for common operations, or use provider-specific repositories for SQL that depends on features such as Oracle packages or PostgreSQL JSONB operators. Keep platform-specific behavior explicit rather than hiding it behind a misleading universal SQL abstraction.

Keep connection strings in configuration backed by a secret store or environment-specific secret management. Register DbContext with the normal scoped lifetime in ASP.NET Core; do not share a DbContext instance across concurrent operations.

Security and Safe SQL

Parameterized queries

SQL injection occurs when untrusted input is treated as part of executable SQL. Parameterized commands separate SQL structure from values so user input is handled as data.

// Safe: value is sent separately as a parameter
const string sql = "SELECT customer_id FROM customers WHERE email = @email";
var customer = await connection.QuerySingleOrDefaultAsync<long?>(
    sql, new { email = requestedEmail });

Parameters cannot generally replace table names, column names, or SQL keywords. If a user must choose a sort column, map an allowed application value to a fixed, trusted SQL fragment. Never concatenate raw user input into identifiers or WHERE clauses.

Database permissions

Use separate database identities for applications, migrations, reporting, and administration where practical. Grant only the permissions each identity requires. Avoid connecting an application as a schema owner or database superuser.

Secrets and transport

Store credentials outside source control, rotate them appropriately, use encrypted connections where required, and restrict network access to database endpoints. Audit access and avoid logging passwords, tokens, full connection strings, or sensitive parameter values.

A hidden UI field, frontend validation, or API authorization rule is not a substitute for database least privilege. Protect the data at every relevant boundary.

Performance Tuning for .NET Applications

Measure before optimizing

Performance tuning starts with evidence. Identify the slow endpoint, inspect application timings, capture database execution plans, and measure the query using representative data and parameters.

Practical checklist

Select only the columns required by the response.
Filter and paginate in the database rather than loading all rows into memory.
Use indexes that match real filtering, joining, and ordering patterns.
Check execution plans and current table statistics.
Avoid N+1 query patterns in ORM code.
Use async I/O for database operations in web applications.
Keep transactions short and avoid holding locks while doing network calls.
Reuse provider data sources and use connection pooling correctly.
Set command timeouts deliberately and implement bounded retries only for transient failures.
Monitor connection pool saturation, wait time, query duration, and database resource use.

Avoid SELECT * in application queries

Selecting only required columns reduces data transfer and makes the contract between the query and application model clearer. It also reduces accidental exposure of newly added sensitive columns.

Cancellation and timeout

await using var command = new NpgsqlCommand(sql, connection)
{
    CommandTimeout = 30
};

await using var reader = await command.ExecuteReaderAsync(cancellationToken);

Pass request cancellation tokens to database APIs where supported. Cancellation allows work to stop when a client disconnects or an operation is no longer needed; it does not guarantee that every server-side operation stops instantly.

Migrations, Deployment and Operations

Schema migration

A migration is a controlled change to the database structure, such as adding a column, creating an index, or changing a constraint. Store migration scripts in source control, review them, and apply them through a repeatable deployment process.

Oracle and PostgreSQL migration strategy

Maintain platform-specific migration scripts where syntax or behavior differs. Test migrations against the same database engine and a representative version used in production. Consider backward-compatible expand-and-contract changes when application versions are deployed gradually.

Deployment checklist

Confirm database version and required extensions/features.
Back up or confirm recoverability before high-risk changes.
Review locks, table rewrites, and index creation impact.
Apply least-privilege migration credentials.
Validate schema and data after deployment.
Keep application and database changes compatible during rollout.
Document rollback or forward-fix procedures.
Monitor errors, query latency, storage, and connection usage after release.

A database rollback is not always as simple as reversing a script. Data transformations and destructive changes may not be reversible. Plan recovery before production deployment.

Testing Database Code

Unit tests vs integration tests

Unit tests validate isolated application logic. Integration tests validate behavior across real components, including the database provider, SQL translation, constraints, transactions, and stored procedures. Mocking a repository cannot prove that a query works on Oracle or PostgreSQL.

Test against the real database engine

Use disposable database instances, containers, or dedicated test databases where practical. A SQLite substitute is not sufficient to validate Oracle-specific PL/SQL or PostgreSQL-specific SQL behavior.

Test important cases

Valid and invalid input values.
Unique and foreign-key constraint violations.
Null values and boundary date/time values.
Concurrent updates and transaction rollback.
Stored procedure/function parameter directions and result shapes.
Pagination ordering and large result sets.
Provider-specific type conversions.
Cancellation, timeouts, and transient connection failures.

If an application supports both Oracle and PostgreSQL, run the same behavioral test suite against both providers, while maintaining platform-specific tests for features that intentionally differ.

Practical .NET Database Project

Build a small Customer Orders API to connect the concepts in this guide. Use the same application behavior with either Oracle or PostgreSQL, while keeping database-specific scripts and provider configuration separate.

Suggested database model

-- Conceptual schema; adapt identity and data types per database
CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    full_name   VARCHAR(120) NOT NULL,
    email       VARCHAR(200) NOT NULL UNIQUE
);

CREATE TABLE orders (
    order_id     BIGINT PRIMARY KEY,
    customer_id  BIGINT NOT NULL,
    order_date   TIMESTAMP NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL CHECK (total_amount >= 0),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    );

API requirements

GET /api/customers/{id} - retrieve one customer.
GET /api/customers/{id}/orders - retrieve a paginated order list.
POST /api/customers - create a customer with validated input.
POST /api/orders - create an order and validate the customer relationship.
PATCH /api/customers/{id} - update selected customer fields.

Suggested implementation steps

Create separate Oracle and PostgreSQL schema scripts.
Configure the provider using environment-specific settings.
Implement data access using either EF Core or Dapper.
Use DTOs and validate requests at the API boundary.
Use parameterized commands for all values.
Wrap multi-step order creation in a transaction.
Add structured logging without exposing secrets or sensitive data.
Write integration tests against each supported database.
Measure query plans and add indexes based on actual access patterns.

The project should share business behavior, not force identical SQL. It is acceptable to maintain separate Oracle and PostgreSQL query implementations behind a well-defined interface.

Common Oracle and PostgreSQL Differences

Topic	Oracle	PostgreSQL
Procedural language	PL/SQL	PL/pgSQL (also supports other procedural languages)
Common text type	VARCHAR2	varchar / text
Exact decimal	NUMBER(p,s)	numeric(p,s)
Boolean column	Historically commonly represented with NUMBER/CHAR; availability depends on release/context	Native boolean
Auto-generated key	Identity column or sequence	Identity column or sequence
Current date/time	SYSDATE / SYSTIMESTAMP	CURRENT_DATE / CURRENT_TIMESTAMP / now()
Pagination	OFFSET ... FETCH (modern versions)	LIMIT ... OFFSET or FETCH
Return inserted values	RETURNING ... INTO in supported contexts	RETURNING in DML result sets
Upsert-style operation	MERGE	INSERT ... ON CONFLICT; MERGE also exists in current releases
Set difference	MINUS	EXCEPT
Application provider	ODP.NET	Npgsql
EF Core provider	Oracle EF Core provider	Npgsql EF Core provider

This table is a quick orientation, not a complete compatibility matrix. Check release-specific documentation, provider support, SQL semantics, identifier rules, type mappings, and transaction behavior before porting production code.

Practice Exercises and Knowledge Check

Hands-on exercises

Create Customer and Order tables in both database platforms with primary and foreign keys.
Insert at least five customers and ten orders.
Write a query that returns each customer and their total order value.
Find customers who have never placed an order using NOT EXISTS.
Return the latest three orders per customer using a window function.
Create a view for active customers.
Create an index for a query filtering by customer and order date; inspect the execution plan.
Write an Oracle PL/SQL procedure to update a customer email.
Write a PostgreSQL PL/pgSQL function that returns a customer's orders.
Call both databases from a .NET console application using parameterized ADO.NET commands.
Implement the same read operation using Dapper and EF Core.
Write an integration test that verifies a foreign-key violation is handled correctly.

Knowledge check

What is the difference between a primary key and a foreign key?
Why should an application use parameterized SQL?
What is the difference between Oracle PL/SQL and PostgreSQL PL/pgSQL?
Why can a join unexpectedly increase the number of rows?
What is the purpose of a database index?
What does ACID stand for?
How does PostgreSQL RETURNING differ from a typical Oracle INSERT pattern?
What is the role of ODP.NET?
What is Npgsql used for?
Why should database integration tests run against the actual target database engine?

Official Learning Resources

Use official documentation for exact syntax, supported features, provider setup, version compatibility, and operational recommendations. Database and provider behavior can change between releases.

What’s Next?

You now have a foundation for working with Oracle Database and PostgreSQL, from relational modelling and SQL queries to database-side programming and C# integration. The next step is to practise the same application workflow on both platforms: design the schema, implement data access, test transactions and constraints, inspect query plans, and deploy schema changes safely.

As you work through real .NET projects, keep a small comparison notebook for syntax and provider differences. That habit helps you write database-aware code without confusing platform-specific behavior with general relational database principles.

Programming

Oracle & PostgreSQL Developer Learning Guide – Complete Guide for .NET Developers