Application Connectivity

55.1 Drivers and Providers

Microsoft.Data.SqlClient (replaces System.Data.SqlClient) Microsoft JDBC Driver for SQL Server

pyodbc, mssql-python (new Microsoft driver), SQLAlchemy mssql / tedious sqlsrv / pdo_sqlsrv ODBC Driver 18 for SQL Server

55.2 Connection String Essentials

Server=tcp:sql1.corp.local,1433;Database=ShopDB;Integrated Security=True;
Encrypt=True;TrustServerCertificate=False;MultiSubnetFailover=True;
ApplicationIntent=ReadOnly;Application Name=ShopAPI;Connect Timeout=30;

55.3 Examples

// C# with parameterized query
await using var conn = new SqlConnection(connStr);
await conn.OpenAsync();
await using var cmd = new SqlCommand("SELECT OrderID, OrderDate FROM dbo.Orders WHERE CustomerID = @cid", conn);
cmd.Parameters.Add("@cid", SqlDbType.Int).Value = 1;
await using var rdr = await cmd.ExecuteReaderAsync();
while (await rdr.ReadAsync()) Console.WriteLine(rdr.GetInt32(0));

import pyodbc
conn = pyodbc.connect("DRIVER={ODBC Driver 18 for SQL Server};SERVER=localhost;DATABASE=ShopDB;Trusted_Connection=yes;Encrypt=yes;TrustServerCertificate=yes")
cur = conn.cursor()
cur.execute("SELECT OrderID, OrderDate FROM dbo.Orders WHERE CustomerID = ?", 1)
for row in cur.fetchall():
    print(row.OrderID, row.OrderDate)

55.4 Application-Side Best Practices

Connection pooling (on by default). Always close and dispose connections.

Always parameterize. Set explicit parameter types and lengths to avoid plan cache bloat.

Implement retry logic for transient errors (deadlock 1205, Azure transient errors 40613, 40197, etc.).

ORMs (Entity Framework Core, Dapper, Hibernate): watch for N+1 queries and inspect the generated SQL.

Don't keep transactions open across user interactions.

Use async I/O. Stream large results.

Data API builder: REST and GraphQL over SQL with no custom code.