dotConnect for SQL Server Standard

Top 10 Tips and Best Practices for dotConnect for SQL Server Standard

1. Use the latest compatible provider version

Keep dotConnect for SQL Server Standard updated to the newest release that’s compatible with your .NET runtime to get performance fixes, security patches, and new features.

2. Choose the right ADO.NET model for your app

Prefer the native ADO.NET provider APIs (DbConnection, DbCommand, DbDataAdapter) for maximum control and compatibility. Use Entity Framework support only when you need ORM conveniences and are aware of its query-generation patterns.

3. Optimize connection management

• Pooling: Ensure connection pooling is enabled (default). Tune Max Pool Size and Min Pool Size per workload.
• Open late, close early: Open connections just before use and close/dispose immediately after. Use using blocks to ensure disposal.

4. Parameterize queries and use prepared statements

Always use parameters to avoid SQL injection and improve plan reuse. For repeated commands, use prepared statements (DbCommand.Prepare) when appropriate to reduce parsing/compilation overhead.

5. Tune batch and bulk operations

For large inserts/updates, use bulk-copy features or batched commands rather than executing single-row statements. Configure batch sizes to balance transaction log pressure and throughput.

6. Use appropriate transaction scopes

Wrap related changes in transactions to maintain consistency. For distributed or ambient transactions, prefer explicit TransactionScope usage with proper timeout and isolation level settings. Avoid long-running transactions that hold locks.

7. Optimize data retrieval

• Select only needed columns instead of SELECT.
• Use paging for large result sets (OFFSET/FETCH or provider-supported paging).
• Stream large binary/text fields (e.g., GetBytes/GetChars or sequential access) to avoid loading entire fields into memory.

8. Monitor and profile database calls

Instrument and log command text, parameters, durations, and exceptions. Use SQL Server Profiler, Extended Events, or third-party profilers to find slow queries, N+1 issues, and blocking.

9. Configure provider-specific settings thoughtfully

Familiarize yourself with dotConnect-specific configuration options (e.g., SQL dialect settings, command timeout defaults, parameter mapping quirks) and set them in your connection string or provider configuration to match your environment.

10. Handle errors and transient faults robustly

Implement retry logic for transient errors (e.g., network blips, throttling). Catch and log SqlException/DbException details, and classify transient vs. fatal errors. Use exponential backoff with a capped number of retries.

Bonus quick checklist

• Use strong typing and nullable-aware mappings for columns.
• Validate mappings and schema when using ORMs.
• Test under production-like load and with realistic data shapes.

If you want, I can expand any tip into a step‑by‑step how‑to or provide sample code for your .NET version.