On this page
You can update the Google BigQuery data either by modifying data returned by the BigQueryDataAdapter class and then calling its Update method or by performing corresponding DML statements (INSERT, DELETE, UPDATE, MERGE) via BigQueryCommand.
The samples below use the dept table from the dataset specified in the DataSet Id connection string parameter:
|
|---|
CREATE TABLE dept (deptno INT64, dname STRING, loc STRING) |
Here is an example showing how to update Google BigQuery data using BigQueryDataAdapter.
|
|---|
using System;
using System.Data;
using Devart.Data.BigQuery;
...
class Program
{
static void Main(string[] args) {
const string connectionString = @"Authentication Type=ServiceAccount;ServiceAccount Json=C:\keys\service-account.json;Project Id=your_project;DataSet Id=your_dataset;License Key=**********";
const string sql = "SELECT deptno, dname, loc FROM dept ORDER BY deptno";
using (BigQueryConnection connection = new BigQueryConnection(connectionString)) {
connection.Open();
DataTable table = new DataTable("Dept");
using (BigQueryCommand command = connection.CreateCommand()) {
command.CommandText = sql;
using (BigQueryDataAdapter adapter = new BigQueryDataAdapter(command)) {
adapter.Fill(table);
adapter.UpdateCommand = new BigQueryCommand("UPDATE dept SET loc = @loc WHERE deptno = @deptno", connection);
adapter.UpdateCommand.Parameters.Add("deptno", DbType.Int64).SourceColumn = "deptno";
adapter.UpdateCommand.Parameters["deptno"].SourceVersion = DataRowVersion.Original;
adapter.UpdateCommand.Parameters.Add("loc", DbType.String).SourceColumn = "loc";
DataRow firstrow = table.Rows[0];
firstrow["loc"] = "BOSTON";
Console.WriteLine(adapter.Update(table));
}
}
Console.WriteLine("Rows after update.");
foreach (DataRow row in table.Rows) {
Console.WriteLine("{0}\t{1}\t{2}", row[0], row[1], row[2]);
}
}
Console.ReadKey();
}
}
|
|
|---|
Imports System
Imports System.Data
Imports Devart.Data.BigQuery
...
Module Program
Sub Main()
Const connectionString As String = "Authentication Type=ServiceAccount;ServiceAccount Json=C:\keys\service-account.json;Project Id=your_project;DataSet Id=your_dataset;License Key=**********"
Const sql As String = "SELECT deptno, dname, loc FROM dept ORDER BY deptno"
Using connection As New BigQueryConnection(connectionString)
connection.Open()
Dim table As New DataTable("Dept")
Using command As BigQueryCommand = connection.CreateCommand()
command.CommandText = sql
Using adapter As New BigQueryDataAdapter(command)
adapter.Fill(table)
adapter.UpdateCommand = New BigQueryCommand("UPDATE dept SET loc = @loc WHERE deptno = @deptno", connection)
adapter.UpdateCommand.Parameters.Add("deptno", DbType.Int64).SourceColumn = "deptno"
adapter.UpdateCommand.Parameters("deptno").SourceVersion = DataRowVersion.Original
adapter.UpdateCommand.Parameters.Add("loc", DbType.String).SourceColumn = "loc"
Dim firstrow As DataRow = table.Rows(0)
firstrow("loc") = "BOSTON"
Console.WriteLine(adapter.Update(table))
End Using
End Using
Console.WriteLine("Rows after update.")
For Each row As DataRow In table.Rows
Console.WriteLine("{0}" & vbTab & "{1}" & vbTab & "{2}", row(0), row(1), row(2))
Next
End Using
Console.ReadKey()
End Sub
End Module
|
The following example updates Google BigQuery data using BigQueryCommand.
|
|---|
using System;
using Devart.Data.BigQuery;
...
class Program
{
static void Main(string[] args) {
const string connectionString = @"Authentication Type=ServiceAccount;ServiceAccount Json=C:\keys\service-account.json;Project Id=your_project;DataSet Id=your_dataset;License Key=**********";
const string sql = "UPDATE dept SET loc = 'SEATTLE' WHERE dname = 'SALES'";
using (BigQueryConnection connection = new BigQueryConnection(connectionString)) {
connection.Open();
using (BigQueryCommand command = connection.CreateCommand()) {
command.CommandText = sql;
Console.WriteLine(command.ExecuteNonQuery());
}
}
Console.ReadKey();
}
}
|
|
|---|
Imports System
Imports Devart.Data.BigQuery
...
Module Program
Sub Main()
Const connectionString As String = "Authentication Type=ServiceAccount;ServiceAccount Json=C:\keys\service-account.json;Project Id=your_project;DataSet Id=your_dataset;License Key=**********"
Const sql As String = "UPDATE dept SET loc = 'SEATTLE' WHERE dname = 'SALES'"
Using connection As New BigQueryConnection(connectionString)
connection.Open()
Using command As BigQueryCommand = connection.CreateCommand()
command.CommandText = sql
Console.WriteLine(command.ExecuteNonQuery())
End Using
End Using
Console.ReadKey()
End Sub
End Module
|
BigQuery requires the WHERE clause in UPDATE and DELETE statements. To update or delete all rows of a table, use WHERE TRUE.
See Also
Entity Framework Core Support
| Retrieving Data
| Using Parameters