Amazon QLDB driver for .NET – Cookbook reference - Amazon Quantum Ledger Database (Amazon QLDB)

Amazon QLDB driver for .NET – Cookbook reference

This reference guide shows common use cases of the Amazon QLDB driver for .NET. It provides C# code examples that demonstrate how to use the driver to run basic create, read, update, and delete (CRUD) operations. It also includes code examples for processing Amazon Ion data. In addition, this guide highlights best practices for making transactions idempotent and implementing uniqueness constraints.

Importing the driver

The following code example imports the driver.

using Amazon.QLDB.Driver; using Amazon.IonDotnet.Builders;
Note

This example also imports an Amazon Ion package (Amazon.IonDotnet.Builders). You need this package to process Ion data when running some data operations in this reference. To learn more, see Working with Amazon Ion.

Instantiating the driver

The following code example creates an instance of the driver that connects to a specified ledger name using default settings.

Async
IAsyncQldbDriver driver = AsyncQldbDriver.Builder().WithLedger("vehicle-registration").Build();
Sync
IQldbDriver driver = QldbDriver.Builder().WithLedger("vehicle-registration").Build();

CRUD operations

QLDB runs create, read, update, and delete (CRUD) operations as part of a transaction.

Warning

As a best practice, make your write transactions strictly idempotent.

Making transactions idempotent

We recommend that you make write transactions idempotent to avoid any unexpected side effects in the case of retries. A transaction is idempotent if it can run multiple times and produce identical results each time.

For example, consider a transaction that inserts a document into a table named Person. The transaction should first check whether or not the document already exists in the table. Without this check, the table might end up with duplicate documents.

Suppose that QLDB successfully commits the transaction on the server side, but the client times out while waiting for a response. If the transaction is not idempotent, the same document could be inserted more than once in the case of a retry.

Using indexes to avoid full table scans

We also recommend that you run statements with a WHERE predicate clause using an equality operator on an indexed field or a document ID; for example, WHERE indexedField = 123 or WHERE indexedField IN (456, 789). Without this indexed lookup, QLDB needs to do a table scan, which can lead to transaction timeouts or optimistic concurrency control (OCC) conflicts.

For more information about OCC, see Amazon QLDB concurrency model.

Implicitly created transactions

The Amazon.QLDB.Driver.IQldbDriver.Execute method accepts a lambda function that receives an instance of Amazon.QLDB.Driver.TransactionExecutor, which you can use to run statements. The instance of TransactionExecutor wraps an implicitly created transaction.

You can run statements within the lambda function by using the Execute method of the transaction executor. The driver implicitly commits the transaction when the lambda function returns.

The following sections show how to run basic CRUD operations, specify custom retry logic, and implement uniqueness constraints.

Creating tables

Async
// The result from driver.Execute() is buffered into memory because once the // transaction is committed, streaming the result is no longer possible. IAsyncResult result = await driver.Execute(async txn => { return await txn.Execute("CREATE TABLE Person"); }); await foreach (IIonValue row in result) { Console.WriteLine(row.ToPrettyString()); // The statement returns the created table ID: // { // tableId: "4o5Uk09OcjC6PpJpLahceE" // } }
Sync
// The result from driver.Execute() is buffered into memory because once the // transaction is committed, streaming the result is no longer possible. IResult result = driver.Execute(txn => { return txn.Execute("CREATE TABLE Person"); }); foreach (IIonValue row in result) { Console.WriteLine(row.ToPrettyString()); // The statement returns the created table ID: // { // tableId: "4o5Uk09OcjC6PpJpLahceE" // } }

Creating indexes

Async
IAsyncResult result = await driver.Execute(async txn => { return await txn.Execute("CREATE INDEX ON Person(GovId)"); }); await foreach (IIonValue row in result) { Console.WriteLine(row.ToPrettyString()); // The statement returns the updated table ID: // { // tableId: "4o5Uk09OcjC6PpJpLahceE" // } }
Sync
IResult result = await driver.Execute(txn => { return txn.Execute("CREATE INDEX ON Person(GovId)"); }); foreach (IIonValue row in result) { Console.WriteLine(row.ToPrettyString()); // The statement returns the updated table ID: // { // tableId: "4o5Uk09OcjC6PpJpLahceE" // } }

Reading documents

Async
// Assumes that Person table has documents as follows: // { "GovId": "TOYENC486FH", "FirstName" : "Brent" } IAsyncResult result = await driver.Execute(async txn => { return await txn.Execute("SELECT * FROM Person WHERE GovId = 'TOYENC486FH'"); }); await foreach (IIonValue row in result) { Console.WriteLine(row.GetField("GovId").StringValue); // Prints TOYENC486FH. Console.WriteLine(row.GetField("FirstName").StringValue); // Prints Brent. }
Sync
// Assumes that Person table has documents as follows: // { "GovId": "TOYENC486FH", "FirstName" : "Brent" } IResult result = driver.Execute(txn => { return txn.Execute("SELECT * FROM Person WHERE GovId = 'TOYENC486FH'"); }); foreach (IIonValue row in result) { Console.WriteLine(row.GetField("GovId").StringValue); // Prints TOYENC486FH. Console.WriteLine(row.GetField("FirstName").StringValue); // Prints Brent. }
Note

When you run a query without an indexed lookup, it invokes a full table scan. In this example, we recommend having an index on the GovId field to optimize performance. Without an index on GovId, queries can have more latency and can also lead to OCC conflict exceptions or transaction timeouts.

Using query parameters

The following code example uses an Ion type query parameter.

Async
IValueFactory valueFactory = new ValueFactory(); IIonValue ionFirstName = valueFactory.NewString("Brent"); IAsyncResult result = await driver.Execute(async txn => { return await txn.Execute("SELECT * FROM Person WHERE FirstName = ?", ionFirstName); }); await foreach (IIonValue row in result) { Console.WriteLine(row.GetField("GovId").StringValue); // Prints TOYENC486FH. Console.WriteLine(row.GetField("FirstName").StringValue); // Prints Brent. }
Sync
IValueFactory valueFactory = new ValueFactory(); IIonValue ionFirstName = valueFactory.NewString("Brent"); IResult result = driver.Execute(txn => { return txn.Execute("SELECT * FROM Person WHERE FirstName = ?", ionFirstName); }); foreach (IIonValue row in result) { Console.WriteLine(row.GetField("GovId").StringValue); // Prints TOYENC486FH. Console.WriteLine(row.GetField("FirstName").StringValue); // Prints Brent. }

The following code example uses multiple query parameters.

Async
IIonValue ionGovId = valueFactory.NewString("TOYENC486FH"); IIonValue ionFirstName = valueFactory.NewString("Brent"); IAsyncResult result = await driver.Execute(async txn => { return await txn.Execute("SELECT * FROM Person WHERE GovId = ? AND FirstName = ?", ionGovId, ionFirstName); }); await foreach (IIonValue row in result) { Console.WriteLine(row.GetField("GovId").StringValue); // Prints TOYENC486FH. Console.WriteLine(row.GetField("FirstName").StringValue); // Prints Brent. }
Sync
IIonValue ionGovId = valueFactory.NewString("TOYENC486FH"); IIonValue ionFirstName = valueFactory.NewString("Brent"); IResult result = driver.Execute(txn => { return txn.Execute("SELECT * FROM Person WHERE GovId = ? AND FirstName = ?", ionGovId, ionFirstName); }); foreach (IIonValue row in result) { Console.WriteLine(row.GetField("GovId").StringValue); // Prints TOYENC486FH. Console.WriteLine(row.GetField("FirstName").StringValue); // Prints Brent. }

The following code example uses a list of query parameters.

Async
// Assumes that Person table has documents as follows: // { "GovId": "TOYENC486FH", "FirstName" : "Brent" } // { "GovId": "ROEE1C1AABH", "FirstName" : "Jim" } // { "GovId": "YH844DA7LDB", "FirstName" : "Mary" } IIonValue[] ionIds = { valueFactory.NewString("TOYENC486FH"), valueFactory.NewString("ROEE1C1AABH"), valueFactory.NewString("YH844DA7LDB") }; IAsyncResult result = await driver.Execute(async txn => { return await txn.Execute("SELECT * FROM Person WHERE GovId IN (?,?,?)", ionIds); }); await foreach (IIonValue row in result) { Console.WriteLine(row.GetField("FirstName").StringValue); // Prints Brent on first iteration. // Prints Jim on second iteration. // Prints Mary on third iteration. }
Sync
// Assumes that Person table has documents as follows: // { "GovId": "TOYENC486FH", "FirstName" : "Brent" } // { "GovId": "ROEE1C1AABH", "FirstName" : "Jim" } // { "GovId": "YH844DA7LDB", "FirstName" : "Mary" } IIonValue[] ionIds = { valueFactory.NewString("TOYENC486FH"), valueFactory.NewString("ROEE1C1AABH"), valueFactory.NewString("YH844DA7LDB") }; IResult result = driver.Execute(txn => { return txn.Execute("SELECT * FROM Person WHERE GovId IN (?,?,?)", ionIds); }); foreach (IIonValue row in result) { Console.WriteLine(row.GetField("FirstName").StringValue); // Prints Brent on first iteration. // Prints Jim on second iteration. // Prints Mary on third iteration. }

The following code example uses an Ion list as a value. To learn more about using different Ion types, see Working with Amazon Ion data types in Amazon QLDB.

Async
// Assumes that Person table has document as follows: // { "GovId": "TOYENC486FH", // "FirstName" : "Brent", // "Vehicles": [ // { "Make": "Volkswagen", // "Model": "Golf"}, // { "Make": "Honda", // "Model": "Civic"} // ] // } IIonValue ionVehicle1 = valueFactory.NewEmptyStruct(); ionVehicle1.SetField("Make", valueFactory.NewString("Volkswagen")); ionVehicle1.SetField("Model", valueFactory.NewString("Golf")); IIonValue ionVehicle2 = valueFactory.NewEmptyStruct(); ionVehicle2.SetField("Make", valueFactory.NewString("Honda")); ionVehicle2.SetField("Model", valueFactory.NewString("Civic")); IIonValue ionVehicles = valueFactory.NewEmptyList(); ionVehicles.Add(ionVehicle1); ionVehicles.Add(ionVehicle2); IAsyncResult result = await driver.Execute(async txn => { return await txn.Execute("SELECT * FROM Person WHERE Vehicles = ?", ionVehicles); }); await foreach (IIonValue row in result) { Console.WriteLine(row.ToPrettyString()); // Prints: // { // GovId: "TOYENC486FN", // FirstName: "Brent", // Vehicles: [ // { // Make: "Volkswagen", // Model: "Golf" // }, // { // Make: "Honda", // Model: "Civic" // } // ] // } }
Sync
// Assumes that Person table has document as follows: // { "GovId": "TOYENC486FH", // "FirstName" : "Brent", // "Vehicles": [ // { "Make": "Volkswagen", // "Model": "Golf"}, // { "Make": "Honda", // "Model": "Civic"} // ] // } IIonValue ionVehicle1 = valueFactory.NewEmptyStruct(); ionVehicle1.SetField("Make", valueFactory.NewString("Volkswagen")); ionVehicle1.SetField("Model", valueFactory.NewString("Golf")); IIonValue ionVehicle2 = valueFactory.NewEmptyStruct(); ionVehicle2.SetField("Make", valueFactory.NewString("Honda")); ionVehicle2.SetField("Model", valueFactory.NewString("Civic")); IIonValue ionVehicles = valueFactory.NewEmptyList(); ionVehicles.Add(ionVehicle1); ionVehicles.Add(ionVehicle2); IResult result = driver.Execute(txn => { return txn.Execute("SELECT * FROM Person WHERE Vehicles = ?", ionVehicles); }); foreach (IIonValue row in result) { Console.WriteLine(row.ToPrettyString()); // Prints: // { // GovId: "TOYENC486FN", // FirstName: "Brent", // Vehicles: [ // { // Make: "Volkswagen", // Model: "Golf" // }, // { // Make: "Honda", // Model: "Civic" // } // ] // } }

Inserting documents

The following code example inserts Ion data types.

Async
IIonValue ionGovId = valueFactory.NewString("TOYENC486FH"); IIonValue ionPerson = valueFactory.NewEmptyStruct(); ionPerson.SetField("GovId", valueFactory.NewString("TOYENC486FH")); ionPerson.SetField("FirstName", valueFactory.NewString("Brent")); await driver.Execute(async txn => { // Check if a document with GovId:TOYENC486FH exists // This is critical to make this transaction idempotent IAsyncResult result = await txn.Execute("SELECT * FROM Person WHERE GovId = ?", ionGovId); // Check if there is a record in the cursor. int count = await result.CountAsync(); if (count > 0) { // Document already exists, no need to insert return; } // Insert the document. await txn.Execute("INSERT INTO Person ?", ionPerson); });
Sync
IIonValue ionGovId = valueFactory.NewString("TOYENC486FH"); IIonValue ionPerson = valueFactory.NewEmptyStruct(); ionPerson.SetField("GovId", valueFactory.NewString("TOYENC486FH")); ionPerson.SetField("FirstName", valueFactory.NewString("Brent")); driver.Execute(txn => { // Check if a document with GovId:TOYENC486FH exists // This is critical to make this transaction idempotent IResult result = txn.Execute("SELECT * FROM Person WHERE GovId = ?", ionGovId); // Check if there is a record in the cursor. int count = result.Count(); if (count > 0) { // Document already exists, no need to insert return; } // Insert the document. txn.Execute("INSERT INTO Person ?", ionPerson); });

This transaction inserts a document into the Person table. Before inserting, it first checks if the document already exists in the table. This check makes the transaction idempotent in nature. Even if you run this transaction multiple times, it will not cause any unintended side effects.

Note

In this example, we recommend having an index on the GovId field to optimize performance. Without an index on GovId, statements can have more latency and can also lead to OCC conflict exceptions or transaction timeouts.

Inserting multiple documents in one statement

To insert multiple documents by using a single INSERT statement, you can pass a parameter of type Ion list to the statement as follows.

Async
IIonValue ionPerson1 = valueFactory.NewEmptyStruct(); ionPerson1.SetField("FirstName", valueFactory.NewString("Brent")); ionPerson1.SetField("GovId", valueFactory.NewString("TOYENC486FH")); IIonValue ionPerson2 = valueFactory.NewEmptyStruct(); ionPerson2.SetField("FirstName", valueFactory.NewString("Jim")); ionPerson2.SetField("GovId", valueFactory.NewString("ROEE1C1AABH")); IIonValue ionPeople = valueFactory.NewEmptyList(); ionPeople.Add(ionPerson1); ionPeople.Add(ionPerson2); IAsyncResult result = await driver.Execute(async txn => { return await txn.Execute("INSERT INTO Person ?", ionPeople); }); await foreach (IIonValue row in result) { Console.WriteLine(row.ToPrettyString()); // The statement returns the created documents' ID: // { // documentId: "6BFt5eJQDFLBW2aR8LPw42" // } // // { // documentId: "K5Zrcb6N3gmIEHgGhwoyKF" // } }
Sync
IIonValue ionPerson1 = valueFactory.NewEmptyStruct(); ionPerson1.SetField("FirstName", valueFactory.NewString("Brent")); ionPerson1.SetField("GovId", valueFactory.NewString("TOYENC486FH")); IIonValue ionPerson2 = valueFactory.NewEmptyStruct(); ionPerson2.SetField("FirstName", valueFactory.NewString("Jim")); ionPerson2.SetField("GovId", valueFactory.NewString("ROEE1C1AABH")); IIonValue ionPeople = valueFactory.NewEmptyList(); ionPeople.Add(ionPerson1); ionPeople.Add(ionPerson2); IResult result = driver.Execute(txn => { return txn.Execute("INSERT INTO Person ?", ionPeople); }); foreach (IIonValue row in result) { Console.WriteLine(row.ToPrettyString()); // The statement returns the created documents' ID: // { // documentId: "6BFt5eJQDFLBW2aR8LPw42" // } // // { // documentId: "K5Zrcb6N3gmIEHgGhwoyKF" // } }

You don't enclose the variable placeholder (?) in double angle brackets ( <<...>> ) when passing an Ion list. In manual PartiQL statements, double angle brackets denote an unordered collection known as a bag.

Updating documents

Async
IIonValue ionGovId = valueFactory.NewString("TOYENC486FH"); IIonValue ionFirstName = valueFactory.NewString("John"); IAsyncResult result = await driver.Execute(async txn => { return await txn.Execute("UPDATE Person SET FirstName = ? WHERE GovId = ?", ionFirstName , ionGovId); }); await foreach (IIonValue row in result) { Console.WriteLine(row.ToPrettyString()); // The statement returns the updated document ID: // { // documentId: "Djg30Zoltqy5M4BFsA2jSJ" // } }
Sync
IIonValue ionGovId = valueFactory.NewString("TOYENC486FH"); IIonValue ionFirstName = valueFactory.NewString("John"); IResult result = driver.Execute(txn => { return txn.Execute("UPDATE Person SET FirstName = ? WHERE GovId = ?", ionFirstName , ionGovId); }); foreach (IIonValue row in result) { Console.WriteLine(row.ToPrettyString()); // The statement returns the updated document ID: // { // documentId: "Djg30Zoltqy5M4BFsA2jSJ" // } }
Note

In this example, we recommend having an index on the GovId field to optimize performance. Without an index on GovId, statements can have more latency and can also lead to OCC conflict exceptions or transaction timeouts.

Deleting documents

Async
IIonValue ionGovId = valueFactory.NewString("TOYENC486FH"); IAsyncResult result = await driver.Execute(async txn => { return await txn.Execute("DELETE FROM Person WHERE GovId = ?", ionGovId); }); await foreach (IIonValue row in result) { Console.WriteLine(row.ToPrettyString()); // The statement returns the deleted document ID: // { // documentId: "Djg30Zoltqy5M4BFsA2jSJ" // } }
Sync
IIonValue ionGovId = valueFactory.NewString("TOYENC486FH"); IResult result = driver.Execute(txn => { return txn.Execute("DELETE FROM Person WHERE GovId = ?", ionGovId); }); foreach (IIonValue row in result) { Console.WriteLine(row.ToPrettyString()); // The statement returns the deleted document ID: // { // documentId: "Djg30Zoltqy5M4BFsA2jSJ" // } }
Note

In this example, we recommend having an index on the GovId field to optimize performance. Without an index on GovId, statements can have more latency and can also lead to OCC conflict exceptions or transaction timeouts.

Running multiple statements in a transaction

Async
// This code snippet is intentionally trivial. In reality you wouldn't do this because you'd // set your UPDATE to filter on vin and insured, and check if you updated something or not. public static async Task<bool> InsureVehicle(IAsyncQldbDriver driver, string vin) { ValueFactory valueFactory = new ValueFactory(); IIonValue ionVin = valueFactory.NewString(vin); return await driver.Execute(async txn => { // Check if the vehicle is insured. Amazon.QLDB.Driver.IAsyncResult result = await txn.Execute( "SELECT insured FROM Vehicles WHERE vin = ? AND insured = FALSE", ionVin); if (await result.CountAsync() > 0) { // If the vehicle is not insured, insure it. await txn.Execute( "UPDATE Vehicles SET insured = TRUE WHERE vin = ?", ionVin); return true; } return false; }); }

Retry logic

For information about the driver's built-in retry logic, see Understanding retry policy with the driver in Amazon QLDB.

Implementing uniqueness constraints

QLDB does not currently support unique indexes. But it's easy to implement this behavior in your application.

Suppose that you want to implement a uniqueness constraint on the GovId field in the Person table. To do this, you can write a transaction that does the following:

  1. Assert that the table has no existing documents with a specified GovId.

  2. Insert the document if the assertion passes.

If a competing transaction concurrently passes the assertion, only one of the transactions will commit successfully. The other transaction will fail with an OCC conflict exception.

The following code example shows how to implement this uniqueness constraint logic.

Async
IIonValue ionGovId = valueFactory.NewString("TOYENC486FH"); IIonValue ionPerson = valueFactory.NewEmptyStruct(); ionPerson.SetField("GovId", valueFactory.NewString("TOYENC486FH")); ionPerson.SetField("FirstName", valueFactory.NewString("Brent")); await driver.Execute(async txn => { // Check if a document with GovId:TOYENC486FH exists // This is critical to make this transaction idempotent IAsyncResult result = await txn.Execute("SELECT * FROM Person WHERE GovId = ?", ionGovId); // Check if there is a record in the cursor. int count = await result.CountAsync(); if (count > 0) { // Document already exists, no need to insert return; } // Insert the document. await txn.Execute("INSERT INTO Person ?", ionPerson); });
Sync
IIonValue ionGovId = valueFactory.NewString("TOYENC486FH"); IIonValue ionPerson = valueFactory.NewEmptyStruct(); ionPerson.SetField("GovId", valueFactory.NewString("TOYENC486FH")); ionPerson.SetField("FirstName", valueFactory.NewString("Brent")); driver.Execute(txn => { // Check if a document with GovId:TOYENC486FH exists // This is critical to make this transaction idempotent IResult result = txn.Execute("SELECT * FROM Person WHERE GovId = ?", ionGovId); // Check if there is a record in the cursor. int count = result.Count(); if (count > 0) { // Document already exists, no need to insert return; } // Insert the document. txn.Execute("INSERT INTO Person ?", ionPerson); });
Note

In this example, we recommend having an index on the GovId field to optimize performance. Without an index on GovId, statements can have more latency and can also lead to OCC conflict exceptions or transaction timeouts.

Working with Amazon Ion

The following sections show how to use the Amazon Ion module to process Ion data.

Importing the Ion module

using Amazon.IonDotnet.Builders;

Creating Ion types

The following code examples show the two ways to create Ion values.

Using ValueFactory

using Amazon.IonDotnet.Tree; using Amazon.IonDotnet.Tree.Impl; IValueFactory valueFactory = new ValueFactory(); IIonValue ionPerson = valueFactory.NewEmptyStruct(); ionPerson.SetField("firstName", valueFactory.NewString("John")); ionPerson.SetField("age", valueFactory.NewInt(13)); Console.WriteLine(ionPerson.GetField("firstName").StringValue); Console.WriteLine(ionPerson.GetField("age").IntValue);

Using IonLoader

using Amazon.IonDotnet.Builders; using Amazon.IonDotnet.Tree; // Load will take in Ion text and return a datagram; a top level container of Ion values. IIonValue ionDatagram = IonLoader.Default.Load("{firstName: \"John\", age: 13}"); // To get the Ion value within the datagram, we call GetElementAt(0). IIonValue ionPerson = ionDatagram.GetElementAt(0); Console.WriteLine(ionPerson.GetField("firstName").StringValue); Console.WriteLine(ionPerson.GetField("age").IntValue);

Getting an Ion binary dump

// ionObject is an Ion struct MemoryStream stream = new MemoryStream(); using (var writer = IonBinaryWriterBuilder.Build(stream)) { ionObject.WriteTo(writer); writer.Finish(); } Console.WriteLine(BitConverter.ToString(stream.ToArray()));

Getting an Ion text dump

// ionObject is an Ion struct StringWriter sw = new StringWriter(); using (var writer = IonTextWriterBuilder.Build(sw)) { ionObject.WriteTo(writer); writer.Finish(); } Console.WriteLine(sw.ToString());

For more information about working with Ion, see the Amazon Ion documentation on GitHub. For more code examples of working with Ion in QLDB, see Working with Amazon Ion data types in Amazon QLDB.