private async Task <int> DeleteFixtureData(string key, SpannerConnection connection, SpannerTransaction transaction)
{
var parameters = new SpannerParameterCollection
{
{ "Key", SpannerDbType.String, key },
{ "OriginalValue", SpannerDbType.Int64, 4 }
};
using (var command = connection.CreateDeleteCommand(_fixture.TableName, parameters))
{
command.Transaction = transaction;
return(await command.ExecuteNonQueryAsync());
}
}
public async Task <int> DeleteDataAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
var albums = new List <Album>
{
new Album {
SingerId = 2, AlbumId = 1, AlbumTitle = "Green"
},
new Album {
SingerId = 2, AlbumId = 3, AlbumTitle = "Terrified"
},
};
int rowCount = 0;
using (var connection = new SpannerConnection(connectionString))
{
await connection.OpenAsync();
// Delete individual rows from the Albums table.
await Task.WhenAll(albums.Select(async album =>
{
var cmd = connection.CreateDeleteCommand("Albums", new SpannerParameterCollection
{
{ "SingerId", SpannerDbType.Int64, album.SingerId },
{ "AlbumId", SpannerDbType.Int64, album.AlbumId }
});
rowCount += await cmd.ExecuteNonQueryAsync();
}));
Console.WriteLine("Deleted individual rows in Albums.");
// Delete a range of rows from the Singers table where the column key is >=3 and <5.
var cmd = connection.CreateDmlCommand("DELETE FROM Singers WHERE SingerId >= 3 AND SingerId < 5");
rowCount += await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) deleted from Singers.");
// Delete remaining Singers rows, which will also delete the remaining
// Albums rows since it was defined with ON DELETE CASCADE.
cmd = connection.CreateDmlCommand("DELETE FROM Singers WHERE true");
rowCount += await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) deleted from Singers.");
}
return(rowCount);
}
public async Task ReadUpdateDeleteAsync()
{
await _fixture.EnsureTestDatabaseAsync().ConfigureAwait(false);
// Sample: ReadUpdateDeleteAsync
using (var connection = new SpannerConnection(
$"Data Source=projects/{_projectId}/instances/{_instanceName}/databases/{_databaseName}"))
{
await connection.OpenAsync();
// Read the first two keys in the database.
var keys = new List <string>();
var selectCmd = connection.CreateSelectCommand("SELECT * FROM TestTable");
using (var reader = await selectCmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
if (keys.Count < 3)
{
keys.Add(reader.GetFieldValue <string>("Key"));
continue;
}
break;
}
}
// Update the Int64Value of keys[0]
// Include the primary key and update columns.
var updateCmd = connection.CreateUpdateCommand(
"TestTable", new SpannerParameterCollection
{
{ "Key", SpannerDbType.String, keys[0] },
{ "Int64Value", SpannerDbType.Int64, 0L }
});
await updateCmd.ExecuteNonQueryAsync();
// Delete row for keys[1]
var deleteCmd = connection.CreateDeleteCommand(
"TestTable", new SpannerParameterCollection
{
{ "Key", SpannerDbType.String, keys[1] }
});
await deleteCmd.ExecuteNonQueryAsync();
}
// End sample
}
private SpannerCommand CreateSpannerCommand(
SpannerConnection spannerConnection,
SpannerTransaction transaction,
ModificationCommand modificationCommand)
{
SpannerCommand cmd;
switch (modificationCommand.EntityState)
{
case EntityState.Deleted:
cmd = spannerConnection.CreateDeleteCommand(modificationCommand.TableName);
break;
case EntityState.Modified:
cmd = spannerConnection.CreateUpdateCommand(modificationCommand.TableName);
break;
case EntityState.Added:
cmd = spannerConnection.CreateInsertCommand(modificationCommand.TableName);
break;
default:
throw new NotSupportedException(
$"Modification type {modificationCommand.EntityState} is not supported.");
}
cmd.Logger = new SpannerLogBridge <DbLoggerCategory.Database.Command>(_logger);
cmd.Transaction = transaction;
foreach (var columnModification in modificationCommand.ColumnModifications)
{
cmd.Parameters.Add(
_typeMapper.GetMapping(columnModification.Property).CreateParameter(cmd,
columnModification.ColumnName,
columnModification.UseOriginalValueParameter
? columnModification.OriginalValue
: columnModification.Value, columnModification.Property.IsNullable));
}
return(cmd);
}
