dotConnect for Google BigQuery Documentation
On this page
    Updating Data
    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
    
    Note 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