Database Connections

Introduction

Establishing a connection to a database is the first step in using Velocity. The framework provides flexible connection mechanisms that work with all supported database systems while maintaining a consistent API across vendors.

The Manager class serves as the central entry point for all Velocity operations. For connected operations, a Manager must be created with a database connection. Velocity supports two primary approaches for creating connections:

DatasourceConnection Objects - Strongly-typed connection objects with properties (recommended)
Connection Strings - Standard ADO.NET connection strings with additional context settings

Note

A Manager can also be created without a database connection using the parameterless constructor new Manager(). This is only for disconnected operations such as reading and writing schema files. See Disconnected Operations for details.

Connection Approaches

Approach 1: DatasourceConnection Objects (Recommended)

The recommended approach is to use database-specific connection classes. This approach provides:

Type safety and IntelliSense support
Clear, self-documenting connection parameters
Built-in validation
Access to database-specific advanced and context settings

DB2 Connection

DB2DatasourceConnection provides connection configuration for IBM DB2:

var connection = new DB2DatasourceConnection
{
    Hostname = "db2.example.com",
    Port = 50000,
    Username = "db2admin",
    Password = "password",
    Database = "MYDB"
};

// Set context settings individually
connection.ImportTimeout = 120;
connection.ImportBatchSize = 10000;
connection.UpdateFrequency = "d(0,1,2,3,4,5,6) h(0,12) m(0)";
connection.UpdateMinimum = 5;
connection.NumberOfSegments = 96;

using (var manager = new Manager(connection))
{
    var schema = manager.LoadSchema("myschema");
}

MySQL Connection

MySqlDatasourceConnection provides connection configuration for MySQL:

var connection = new MySqlDatasourceConnection
{
    Hostname = "mysql.example.com",
    Port = 3306,
    Username = "root",
    Password = "password",
    Database = "myapp_db"
};

// Set advanced settings for MySQL-specific connection behavior
connection.AllowLoadLocalInfile = true;
connection.AllowUserVariables = true";

// Set context settings individually using strongly-typed properties
connection.ImportTimeout = 120;
connection.LengthUnit = "metre";

using (var manager = new Manager(connection))
{
    var schema = manager.LoadSchema("myschema");
}

Oracle Connection

OracleDatasourceConnection provides connection configuration for Oracle Database:

var connection = new OracleDatasourceConnection
{
    Hostname = "oracle.example.com",
    Port = 1521,
    Username = "system",
    Password = "password",
    Database = "ORCL"
};

// Set context settings using strongly-typed properties
connection.Tablespace = "USERS";
connection.ImportTimeout = 120;
connection.ImportBatchSize = 10000;
connection.Tolerance = 0.01f;
connection.UseBooleanDataType = false;

using (var manager = new Manager(connection))
{
    var schema = manager.LoadSchema("myschema");
}

PostgreSQL Connection

PostgreSqlDatasourceConnection provides connection configuration for PostgreSQL:

var connection = new PostgreSqlDatasourceConnection
{
    Hostname = "postgres.example.com",
    Port = 5432,
    Username = "postgres",
    Password = "password",
    Database = "myapp_db"
};

// Set context settings individually
connection.ImportTimeout = 120;
connection.DefaultSrid = 4326;
connection.EscapeIdentifiers = true;

using (var manager = new Manager(connection))
{
    var database = manager.LoadDatabase();
}

SQLite Connection

SqliteDatasourceConnection provides connection configuration for SQLite. SQLite uses a unique folder-based approach where the Database property represents a folder path rather than a database name:

var connection = new SqliteDatasourceConnection
{
    Database = "./data"  // Folder path, not a database file
};

// Set context settings
connection.ImportTimeout = 120;

using (var manager = new Manager(connection))
{
    // Creates or opens ./data/main.db
    var schema = manager.LoadSchema("main");
}

SQLite Multi-Schema Support:

SQLite implements schemas as separate database files within the folder:

main.db - The default schema
{schemaname}.db - Additional schemas

Cross-schema operations use SQLite's ATTACH DATABASE feature automatically.

SQL Server Connection

SqlServerDatasourceConnection provides connection configuration for Microsoft SQL Server:

using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Connections;
using YndigoBlue.Velocity.Enums;

var connection = new SqlServerDatasourceConnection
{
    Hostname = "sqlserver.example.com",
    Port = 1433,
    Username = "myuser",
    Password = "mypassword",
    Database = "myapp_db"
};

// Set context settings individually using strongly-typed properties
connection.ImportTimeout = 120;           // Universal setting
connection.ImportBatchSize = 10000;       // Universal setting
connection.DefaultSrid = 4326;            // Universal setting

// Set advanced settings
connection.TrustServerCertificate = true;

using (var manager = new Manager(connection))
{
    // Perform database operations
    var schema = manager.LoadSchema("myschema");
}

Teradata Connection

TeradataDatasourceConnection provides connection configuration for Teradata Vantage:

var connection = new TeradataDatasourceConnection
{
    Hostname = "teradata.example.com",
    Port = 1025,
    Username = "dbc",
    Password = "password",
    Database = "myapp_db"
};

// Set context settings individually using strongly-typed properties
connection.PermanentSpace = 100000000;
connection.ImportBatchSize = 1000;
connection.NumberOfStagingTables = 4;       // Parallel staging tables for bulk import
connection.VarCharCastSize = 32000;         // VARCHAR size used for Clob/Geo casts
connection.ImportCastClob = true;           // Cast Clob columns to VARCHAR in staging tables
connection.ImportCastGeo = true;            // Cast Geometry/Geography columns to VARCHAR in staging tables
connection.SelectCastClob = true;           // Cast Clob columns to VARCHAR on SELECT (queries and exports)
connection.ImportTransaction = true;        // Wrap staging-to-target merge in a transaction
connection.DefaultSrid = 1619;
connection.IdentityStartValue = 1;

using (var manager = new Manager(connection))
{
    var schema = manager.LoadSchema("myschema");
}

Approach 2: Connection Strings

Velocity also supports standard ADO.NET connection strings with optional context settings. The DatasourceType enum is used to specify the database type. This approach is useful when:

Working with existing connection string configurations
Loading connection parameters from configuration files
Integrating with enterprise connection management systems

using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Enums;

// SQL Server with connection string
string connectionString = "Data Source=server,1433;Initial Catalog=mydb;User ID=username;Password=pwd1234;Trust Server Certificate=True";
string contextSettings = "ImportTimeout=120;ImportBatchSize=10000;DefaultSrid=4326";

using (var manager = new Manager(DatasourceType.SQLServer, connectionString, contextSettings))
{
    var schema = manager.LoadSchema("myschema");
}

Connection String Examples by Database

DB2:

string connectionString = "Server=localhost:50000;Database=MYDB;UID=db2admin;PWD=password";
using (var manager = new Manager(DatasourceType.DB2, connectionString))
{
    // Operations
}

MySQL:

string connectionString = "Server=localhost;Port=3306;Database=mydb;Uid=root;Pwd=password;AllowLoadLocalInfile=True;AllowUserVariables=True";
using (var manager = new Manager(DatasourceType.MySQL, connectionString))
{
    // Operations
}

Oracle:

string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));User Id=system;Password=password;";
using (var manager = new Manager(DatasourceType.Oracle, connectionString))
{
    // Operations
}

PostgreSQL:

string connectionString = "Host=localhost;Port=5432;Database=mydb;Username=postgres;Password=password";
using (var manager = new Manager(DatasourceType.PostgreSQL, connectionString))
{
    // Operations
}

SQLite:

string connectionString = "Data Source=./data/main.db";
using (var manager = new Manager(DatasourceType.Sqlite, connectionString))
{
    // Operations
}

SQL Server:

string connectionString = "Data Source=localhost,1433;Initial Catalog=mydb;User ID=sa;Password=password;Trust Server Certificate=True";
using (var manager = new Manager(DatasourceType.SQLServer, connectionString))
{
    // Operations
}

Teradata:

string connectionString = "Data Source=localhost;Database=mydb;User ID=dbc;Password=password";
using (var manager = new Manager(DatasourceType.Teradata, connectionString))
{
    // Operations
}

Understanding Settings: Advanced vs Context

Velocity uses two types of settings to configure database connections:

AdvancedSettings: Database-specific connection parameters passed to the underlying ADO.NET provider
ContextSettings: Velocity framework behavior settings that control how Velocity operates

Key Differences

Aspect	AdvancedSettings	ContextSettings
Purpose	Configure ADO.NET provider behavior	Configure Velocity framework behavior
Scope	Passed to database driver	Used by Velocity operations
Examples	`TrustServerCertificate`, `SSL Mode`, `Pooling`	`ImportTimeout`, `DefaultSrid`, `ImportBatchSize`
Where Used	Connection string to database	Velocity commands and operations
Timing	Applied during connection	Applied during operations

AdvancedSettings

AdvancedSettings control the underlying database connection behavior by passing parameters directly to the ADO.NET provider. These are standard connection string parameters specific to each database vendor.

How AdvancedSettings Work

When you create a Manager with a DatasourceConnection, Velocity:

Reads named properties (Hostname, Port, Username, Password, Database, Pooling, ConnectTimeout, MaxPoolSize, MinPoolSize)
Merges them with AdvancedSettings to build the final connection string
Passes the complete connection string to the ADO.NET provider

Reserved Settings are connection parameters that have corresponding named properties and cannot be overridden via SetAdvancedSettings():

Connection Parameters: Hostname/Host, Port, Username/UserID, Password, Database/InitialCatalog/DataSource
Universal Properties: Pooling, ConnectTimeout, MaxPoolSize, MinPoolSize

These reserved settings are always controlled through their strongly-typed properties to prevent conflicts and ensure consistency.

Setting AdvancedSettings

Use the SetAdvancedSettings() method to configure database-specific connection parameters:

var connection = new SqlServerDatasourceConnection
{
    // Required connection parameters (reserved settings)
    Hostname = "localhost",
    Port = 1433,
    Username = "sa",
    Password = "password",
    Database = "mydb",

    // Universal properties (reserved settings)
    Pooling = true,
    ConnectTimeout = 15,
    MaxPoolSize = 100,
    MinPoolSize = 0
};

// Additional provider-specific settings (non-reserved)
connection.SetAdvancedSettings("TrustServerCertificate=True;Encrypt=False;MultipleActiveResultSets=True");

Important: If you try to override reserved settings via SetAdvancedSettings(), they will be ignored:

connection.Pooling = true;
connection.ConnectTimeout = 15;
connection.MaxPoolSize = 100;
connection.MinPoolSize = 0;

// These will be IGNORED - reserved settings use named properties
connection.SetAdvancedSettings("Pooling=False;Connect Timeout=30;MaxPoolSize=50;MinPoolSize=5");

// connection.Pooling is still true
// connection.ConnectTimeout is still 15
// connection.MaxPoolSize is still 100
// connection.MinPoolSize is still 0

Database-Specific Advanced Properties

Some databases expose additional advanced settings as strongly-typed properties:

MySqlDatasourceConnection:

var connection = new MySqlDatasourceConnection
{
    Hostname = "localhost",
    Port = 3306,
    Username = "root",
    Password = "password",
    Database = "mydb"
};

// MySQL exposes these as strongly-typed properties
connection.AllowLoadLocalInfile = true;  // Exposed as property
connection.AllowUserVariables = true;    // Exposed as property

// Other settings via SetAdvancedSettings
connection.SetAdvancedSettings("SSL Mode=None;Default Command Timeout=30");

OracleDatasourceConnection:

var connection = new OracleDatasourceConnection
{
    Hostname = "localhost",
    Port = 1521,
    Username = "system",
    Password = "password",
    Database = "ORCL"
};

// Oracle has no database-specific strongly-typed advanced properties beyond universal settings
connection.MaxPoolSize = 100;
connection.MinPoolSize = 1;

// Additional settings via SetAdvancedSettings
connection.SetAdvancedSettings("Validate Connection=true;Statement Cache Size=10");

PostgreSqlDatasourceConnection:

var connection = new PostgreSqlDatasourceConnection
{
    Hostname = "localhost",
    Port = 5432,
    Username = "postgres",
    Password = "password",
    Database = "mydb"
};

// PostgreSQL has no database-specific strongly-typed properties beyond universal settings
connection.MaxPoolSize = 100;
connection.MinPoolSize = 1;

// Additional settings via SetAdvancedSettings
connection.SetAdvancedSettings("SSL Mode=Prefer;Command Timeout=60");

SqlServerDatasourceConnection:

var connection = new SqlServerDatasourceConnection
{
    Hostname = "localhost",
    Port = 1433,
    Username = "sa",
    Password = "password",
    Database = "mydb"
};

// SQL Server exposes TrustServerCertificate as a property
connection.TrustServerCertificate = true;

// Other settings via SetAdvancedSettings
connection.SetAdvancedSettings("Encrypt=False;MultipleActiveResultSets=True");

SqliteDatasourceConnection:

var connection = new SqliteDatasourceConnection
{
    Database = "./data"  // Folder path, not a database file
};

// SQLite has no database-specific strongly-typed advanced properties beyond universal settings
connection.MaxPoolSize = 100;

// Additional settings via SetAdvancedSettings
connection.SetAdvancedSettings("Cache=Shared;Foreign Keys=True");

TeradataDatasourceConnection:

var connection = new TeradataDatasourceConnection
{
    Hostname = "localhost",
    Port = 1025,
    Username = "dbc",
    Password = "password",
    Database = "mydb"
};

// Teradata exposes SessionCharacterSet as a property
connection.SessionCharacterSet = "UTF8";

// Additional Teradata-specific connection parameters
connection.SetAdvancedSettings("Connect Function=1;Charset=UTF8");

Strongly-Typed Advanced Properties by Database

Some databases expose additional advanced settings as strongly-typed properties (in addition to the universal reserved settings like MaxPoolSize and MinPoolSize):

DB2

DB2DatasourceConnection has no database-specific strongly-typed advanced properties beyond universal settings.

MySQL

MySqlDatasourceConnection exposes:

AllowLoadLocalInfile - Enable LOAD DATA LOCAL INFILE (required for imports)
AllowUserVariables - Enable user variable support (required for some operations)

Oracle

OracleDatasourceConnection has no database-specific strongly-typed advanced properties beyond universal settings.

PostgreSQL

PostgreSqlDatasourceConnection has no database-specific strongly-typed advanced properties beyond universal settings.

SQL Server

SqlServerDatasourceConnection exposes:

TrustServerCertificate - Bypass certificate validation (useful for development)

SQLite

SqliteDatasourceConnection has no database-specific strongly-typed advanced properties beyond universal settings.

Teradata

TeradataDatasourceConnection exposes:

SessionCharacterSet - Character set for session

Context Settings

Context Settings are Velocity-specific settings that control how the framework operates, as opposed to AdvancedSettings which control the database connection itself. These settings affect Velocity's behavior when executing operations like imports, queries, and schema management.

How Context Settings Work

Context Settings are:

Framework-Level: Applied to Velocity operations, not the database connection
Operation-Specific: Used by Velocity commands (e.g., ImportTimeout for import operations, CommandTimeout for queries)
Cross-Database: Universal settings work the same across all database types
Type-Safe: Always exposed as strongly-typed properties for compile-time checking

Key Difference from Advanced Settings:

ConnectTimeout (Advanced Setting) = time to establish connection to database
CommandTimeout (Context Setting) = time for a database command to execute
ImportTimeout (Context Setting) = time for bulk import operations to complete

Setting Context Settings

Context settings can be configured in two ways:

1. Individual strongly-typed properties (Recommended):

var connection = new SqlServerDatasourceConnection
{
    Hostname = "localhost",
    Port = 1433,
    Username = "sa",
    Password = "password",
    Database = "mydb"
};

// Set context settings using strongly-typed properties
connection.CommandTimeout = 30;        // Default timeout for queries/commands
connection.ImportTimeout = 120;        // Timeout for bulk import operations
connection.ImportBatchSize = 10000;    // Rows per batch during imports
connection.DefaultSrid = 4326;         // Default spatial reference ID
connection.EscapeIdentifiers = true;   // Quote table/column names

2. Connection string format using SetContextSettings():

var connection = new SqlServerDatasourceConnection
{
    Hostname = "localhost",
    Port = 1433,
    Username = "sa",
    Password = "password",
    Database = "mydb"
};

// Set multiple context settings at once
connection.SetContextSettings("CommandTimeout=30;ImportTimeout=120;ImportBatchSize=10000;DefaultSrid=4326;EscapeIdentifiers=true");

When using the connection string approach, context settings are passed as the third parameter to the Manager constructor:

string connectionString = "Data Source=localhost,1433;Initial Catalog=mydb;User ID=sa;Password=password";
string contextSettings = "CommandTimeout=30;ImportTimeout=120;ImportBatchSize=10000";

using (var manager = new Manager(DatasourceType.SQLServer, connectionString, contextSettings))
{
    // Operations will use these timeouts
    var schema = manager.LoadSchema("myschema");  // Uses CommandTimeout
    manager.Import(importConfig, dataTable);       // Uses ImportTimeout
}

Universal Context Settings

These settings are available on all DatasourceConnection classes and apply to all database types:

Property	Type	Default	Description
`CommandTimeout`	int	30	Timeout in seconds for database commands (queries, DDL, DML)
`DefaultSrid`	int	4326	Default spatial reference identifier for geospatial operations
`EscapeIdentifiers`	bool	true	Whether to escape database identifiers (table/column names)
`ImportTimeout`	int	120	Timeout in seconds for bulk import operations
`ImportBatchSize`	int	10000	Number of rows per batch during bulk imports

Example:

connection.CommandTimeout = 60;        // Queries timeout after 60 seconds
connection.DefaultSrid = 3857;
connection.EscapeIdentifiers = true;
connection.ImportTimeout = 180;        // Imports timeout after 180 seconds
connection.ImportBatchSize = 5000;

// Or using SetContextSettings
connection.SetContextSettings("CommandTimeout=60;DefaultSrid=3857;EscapeIdentifiers=true;ImportTimeout=180;ImportBatchSize=5000");

Understanding Timeouts:

ConnectTimeout (AdvancedSetting): Maximum time to establish database connection
CommandTimeout (ContextSetting): Maximum time for queries/DDL/DML to execute
ImportTimeout (ContextSetting): Maximum time for bulk import operations (often needs to be higher than CommandTimeout)

Database-Specific ContextSettings

Each database connection class exposes its context settings as strongly-typed properties for maximum type safety and IntelliSense support.

DB2

DB2DatasourceConnection exposes:

Property	Type	Default	Description
`UpdateFrequency`	string	"d(0,1,2,3,4,5,6) h(0,12) m(0)"	Statistics update frequency schedule
`UpdateMinimum`	int	5	Minimum number of changes before statistics update
`NumberOfSegments`	int	96	Number of segments for partitioned tables

Example:

connection.UpdateFrequency = "d(0,1,2,3,4,5,6) h(0,12) m(0)";
connection.UpdateMinimum = 5;
connection.NumberOfSegments = 96;

// Or using SetContextSettings
connection.SetContextSettings("UpdateFrequency=d(0,1,2,3,4,5,6) h(0,12) m(0);UpdateMinimum=5;NumberOfSegments=96");

MySQL

MySqlDatasourceConnection exposes:

Property	Type	Default	Description
`LengthUnit`	string	"metre"	Unit for spatial length calculations

Example:

connection.LengthUnit = "metre";

// Or using SetContextSettings
connection.SetContextSettings("LengthUnit=metre");

Oracle

OracleDatasourceConnection exposes:

Property	Type	Default	Description
`Tablespace`	string	""	Default tablespace for table creation
`Tolerance`	float	0.01	Tolerance for spatial operations
`UseBooleanDataType`	bool	false	Use Oracle 23c+ native boolean type (requires Oracle 23c+)

Example:

connection.Tablespace = "USERS";
connection.Tolerance = 0.01f;
connection.UseBooleanDataType = false;

// Or using SetContextSettings
connection.SetContextSettings("Tablespace=USERS;Tolerance=0.01;UseBooleanDataType=false");

PostgreSQL

PostgreSqlDatasourceConnection has no database-specific context settings beyond the universal settings.

All context settings for PostgreSQL are available through the universal ImportTimeout, ImportBatchSize, DefaultSrid, and EscapeIdentifiers properties.

SQL Server

SqlServerDatasourceConnection has no database-specific context settings beyond the universal settings.

All context settings for SQL Server are available through the universal ImportTimeout, ImportBatchSize, DefaultSrid, and EscapeIdentifiers properties.

SQLite

SqliteDatasourceConnection has no database-specific context settings beyond the universal settings.

All context settings for SQLite are available through the universal ImportTimeout, ImportBatchSize, DefaultSrid, and EscapeIdentifiers properties.

Teradata

TeradataDatasourceConnection exposes:

Property	Type	Default	Description
`PermanentSpace`	int	100000000	Permanent space allocation for user in bytes
`NumberOfStagingTables`	int	4	Number of parallel staging tables used during bulk import
`VarCharCastSize`	int	32000	VARCHAR size used when casting Clob and Geometry/Geography columns
`ImportCastClob`	bool	true	Cast Clob columns to `VARCHAR(VarCharCastSize)` in staging tables during import
`ImportCastGeo`	bool	true	Cast Geometry/Geography columns to `VARCHAR(VarCharCastSize)` in staging tables during import
`SelectCastClob`	bool	true	Cast Clob columns to `VARCHAR(VarCharCastSize)` on SELECT (affects queries and exports)
`ImportTransaction`	bool	true	Wrap the staging-table-to-target merge in a single transaction
`IdentityStartValue`	long	1	Starting value for identity columns
`SourceSpatialReference`	string	(WGS 84 WKT)	Source spatial reference for coordinate transformations
`TargetSpatialReference`	string	(NAD27 Albers WKT)	Target spatial reference for coordinate transformations

Example:

connection.PermanentSpace = 100000000;
connection.NumberOfStagingTables = 4;
connection.VarCharCastSize = 32000;
connection.ImportCastClob = true;
connection.ImportCastGeo = true;
connection.SelectCastClob = true;
connection.ImportTransaction = true;
connection.IdentityStartValue = 1;

// Or using SetContextSettings
connection.SetContextSettings("PermanentSpace=100000000;NumberOfStagingTables=4;VarCharCastSize=32000;ImportTransaction=true");

Connection Management Best Practices

1. Always Use Using Statements

Manager objects should always be wrapped in using statements to ensure proper disposal of database connections:

using (var manager = new Manager(connection))
{
    // Perform operations
    var schema = manager.LoadSchema("myschema");
} // Connection automatically closed and disposed

2. Store Connection Parameters Securely

Never hardcode credentials in source code. Use configuration files, environment variables, or secure credential stores:

using Microsoft.Extensions.Configuration;

// Load from configuration
var config = new ConfigurationBuilder()
    .AddJsonFile("appsettings.json")
    .AddEnvironmentVariables()
    .Build();

var connection = new SqlServerDatasourceConnection
{
    Hostname = config["Database:Hostname"],
    Port = int.Parse(config["Database:Port"]),
    Username = config["Database:Username"],
    Password = config["Database:Password"],
    Database = config["Database:Database"]
};

appsettings.json:

{
  "Database": {
    "Hostname": "localhost",
    "Port": "1433",
    "Username": "myuser",
    "Password": "mypassword",
    "Database": "mydb"
  }
}

3. Configure Appropriate Timeouts

Set timeouts based on your operational requirements. All connection types support CommandTimeout and ImportTimeout:

// Development environment - shorter timeouts for faster feedback
connection.ConnectTimeout = 5;      // AdvancedSetting: quick connection timeout
connection.CommandTimeout = 30;     // ContextSetting: standard query timeout
connection.ImportTimeout = 120;     // ContextSetting: moderate import timeout
connection.ImportBatchSize = 1000;  // Smaller batches for development

// Production environment - longer timeouts for reliability
connection.ConnectTimeout = 15;     // AdvancedSetting: allow more time for connection
connection.CommandTimeout = 60;     // ContextSetting: longer query timeout
connection.ImportTimeout = 300;     // ContextSetting: extended import timeout
connection.ImportBatchSize = 10000; // Larger batches for performance

Timeout Guidelines:

ConnectTimeout (AdvancedSetting): Usually 5-15 seconds
CommandTimeout (ContextSetting): 30-60 seconds for most queries
ImportTimeout (ContextSetting): 120-300+ seconds depending on data volume

4. Use Context Settings for Environment-Specific Behavior

Separate connection parameters from operational settings:

// Development environment
connection.ImportBatchSize = 1000;
connection.ImportTimeout = 60;

// Production environment
connection.ImportBatchSize = 10000;
connection.ImportTimeout = 300;

5. Test Connections Before Operations

Use the GetDatabaseInfo method on Manager to verify database connectivity:

var connection = new PostgreSqlDatasourceConnection
{
    Hostname = "postgres.example.com",
    Port = 5432,
    Username = "postgres",
    Password = "password",
    Database = "mydb"
};

try
{
    using (var manager = new Manager(connection))
    {
        var info = manager.GetDatabaseInfo();

        Console.WriteLine($"Vendor: {info.Vendor}");
        Console.WriteLine($"Name: {info.Name}");
        Console.WriteLine($"Edition: {info.Edition}");
        Console.WriteLine($"Version: {info.MajorVersion}.{info.MinorVersion}");
    }
}
catch (Exception ex)
{
    Console.WriteLine($"Connection failed: {ex.Message}");
}

6. Prefer Strongly-Typed Properties Over String Parsing

When possible, use the strongly-typed properties for better type safety and compile-time checking. Universal settings like ImportTimeout and ImportBatchSize are available on all connection types:

// Preferred: Type-safe, compile-time checked
connection.ImportTimeout = 120;
connection.ImportBatchSize = 10000;
connection.DefaultSrid = 4326;

// Alternative: Useful when loading from configuration
connection.SetContextSettings("ImportTimeout=120;ImportBatchSize=10000;DefaultSrid=4326");

Disconnected Operations

For operations that don't require a database connection (reading and writing Schema files using ReadSchemaFromFile and WriteSchema), use the parameterless Manager constructor:

using (var manager = new Manager())
{
    // Load schema from file without connecting to database
    Schema schema = manager.ReadSchemaFromFile("schema.json", ConfigType.Json);

    // Modify schema programmatically
    schema.CreateTable("new_table").AddColumn("id", DataType.Long);

    // Write back to file
    manager.WriteSchema(schema, "modified_schema.json", ConfigType.Json);
}

Note: Attempting to perform connected operations (like LoadSchema, LoadDatabase, etc.) with a disconnected Manager will throw an exception.

Connection String vs DatasourceConnection Tradeoffs

Use DatasourceConnection When:

Building new applications
You want type safety and IntelliSense
Connection parameters are constructed programmatically
You need clear, self-documenting code
Advanced validation is beneficial

Use Connection Strings When:

Integrating with existing systems
Connection strings are managed externally
Working with configuration management systems
You need maximum flexibility in connection parameters
Migrating from legacy ADO.NET code

Both approaches are fully supported and provide identical functionality—choose based on your application architecture and requirements.

Summary

Velocity provides flexible connection mechanisms to suit different application architectures:

DatasourceConnection objects offer type safety with strongly-typed properties (recommended for new development)
Connection strings provide compatibility with existing configuration systems
AdvancedSettings control ADO.NET provider behavior (passed to database connection string):
- Set via SetAdvancedSettings() method
- Map to database-specific connection string properties
- Reserved settings (Pooling, ConnectTimeout, etc.) use named properties
- Cannot override reserved settings via SetAdvancedSettings()
- Some databases expose common settings as strongly-typed properties
ContextSettings configure Velocity framework behavior (used by Velocity operations):
- Set via strongly-typed properties or SetContextSettings() method
- Control how Velocity executes operations (CommandTimeout, ImportTimeout, etc.)
- Universal settings work across all database types
- Applied to Velocity commands, not the connection itself
Understand timeout types:
- ConnectTimeout (AdvancedSetting) = time to establish connection
- CommandTimeout (ContextSetting) = time for queries/DDL/DML
- ImportTimeout (ContextSetting) = time for bulk imports
Always use using statements for proper resource management
Store credentials securely outside source code
Configure timeouts and batch sizes appropriate to your environment
Prefer strongly-typed properties over string parsing when possible

By understanding the distinction between AdvancedSettings (connection behavior) and ContextSettings (framework behavior), you can build robust, secure, and performant database applications with Velocity.

Table of Contents

Database Connections

Introduction

Note

Connection Approaches

Approach 1: DatasourceConnection Objects (Recommended)

DB2 Connection

MySQL Connection

Oracle Connection

PostgreSQL Connection

SQLite Connection

SQL Server Connection

Teradata Connection

Approach 2: Connection Strings

Connection String Examples by Database

Understanding Settings: Advanced vs Context

Key Differences

AdvancedSettings

How AdvancedSettings Work

Setting AdvancedSettings

Database-Specific Advanced Properties

Strongly-Typed Advanced Properties by Database

DB2

MySQL

Oracle

PostgreSQL

SQL Server

SQLite

Teradata

Context Settings

How Context Settings Work

Setting Context Settings

Universal Context Settings

Database-Specific ContextSettings

DB2

MySQL

Oracle

PostgreSQL

SQL Server

SQLite

Teradata

Connection Management Best Practices

1. Always Use Using Statements

2. Store Connection Parameters Securely

3. Configure Appropriate Timeouts

4. Use Context Settings for Environment-Specific Behavior

5. Test Connections Before Operations

6. Prefer Strongly-Typed Properties Over String Parsing

Disconnected Operations

Connection String vs DatasourceConnection Tradeoffs

Use DatasourceConnection When:

Use Connection Strings When:

Summary