Core Operations

Core Operations

Once you have a connected SQLServerClient or SQLServerConnection, you can issue commands to the server. SQLServerNIO provides multiple methods tailored for the exact expected return type.

---

Executing Simple Queries

For DDL statements (CREATE, ALTER, DROP) or DML statements (INSERT, UPDATE, DELETE), use execute().

let result = try await client.execute("INSERT INTO Logs(Message) VALUES (N'Application started.')")

// The result contains the total number of rows affected by the batch
print("Affected rows: \(result.done.first?.rowCount ?? 0)")

Querying Rows

When you expect tabular data (SELECT), use query(). The returned collection allows safe extraction of column values.

let rows = try await client.query("SELECT id, username, is_active FROM sys.sql_logins ORDER BY name")

for row in rows {
    let id = row.column("id")?.int ?? 0
    let username = row.column("username")?.string ?? "Unknown"
    let isActive = row.column("is_active")?.bool ?? false
    
    print("User \(username) (ID: \(id)): Active? \(isActive)")
}

Scalar Queries

If your query is guaranteed to return exactly one row and one column (e.g., aggregates), use queryScalar().

let databaseName: String? = try await client.queryScalar("SELECT DB_NAME()", as: String.self)
let tableCount: Int? = try await client.queryScalar("SELECT COUNT(*) FROM sys.tables", as: Int.self)

---

Stored Procedures & Typed Return Values

When executing RPC commands (Remote Procedure Calls) or complex statements with output parameters, TDS emits RETURNVALUE tokens. SQLServerNIO automatically parses these into typed accessors.

// Assume dbo.usp_Calculate has an output parameter @Out
let result = try await client.execute("EXEC dbo.usp_Calculate @Input = 1, @Out = @o OUTPUT")

// Safely access returned outputs
for rv in result.returnValues {
    print("Parameter: \(rv.name), Value: \(rv.int ?? 0)")
}

---

Streaming Large Results

Loading millions of rows into RAM via .query() will crash your application. SQLServerNIO natively supports Swift AsyncSequence to pull rows directly off the network buffer as they arrive.

// We use `withConnection` because a stream requires a persistent TCP connection state 
// during the duration of the stream processing.
try await client.withConnection { connection in
    
    let stream = connection.streamQuery("SELECT * FROM GiganticLogTable")
    
    for try await event in stream {
        switch event {
        case .metadata(let columns):
            // Fired once before rows begin
            print("Received schema for columns: \(columns.map(\.name))")
            
        case .row(let row):
            // Process immediately and let the memory be garbage collected
            let timestamp = row.column("timestamp")?.date
            
        case .done(let done):
            print("Stream complete. Total processed: \(done.rowCount)")
            
        case .message(let message):
            print("Server generated a message: \(message.message)")
        }
    }
}

---

Batch Processing & Scripts

Sometimes you have a giant .sql file loaded with GO separators.

let script = """
CREATE TABLE TempData (id INT PRIMARY KEY)
GO
INSERT INTO TempData VALUES (1), (2), (3)
GO
SELECT COUNT(*) FROM TempData
GO
"""

let scriptResults = try await client.executeScript(script)
print("Processed \(scriptResults.count) separate batches.")

---

Transactions

Transactions allow you to rollback operations if any step fails. You must reserve a dedicated connection from the pool using withConnection so that your BEGIN, COMMIT, and ROLLBACK commands hit the exact same session on the server.

try await client.withConnection { connection in
    
    _ = try await connection.execute("BEGIN TRANSACTION")
    
    do {
        _ = try await connection.execute("INSERT INTO Accounts (id) VALUES (1)")
        _ = try await connection.execute("INSERT INTO Log (msg) VALUES ('Account Created')")
        
        // Everything succeeded
        _ = try await connection.execute("COMMIT")
        
    } catch {
        // Something failed. Rollback the database state.
        _ = try await connection.execute("ROLLBACK")
        throw error // Rethrow to let your application know it failed
    }
}