On this page
Introduction
This tutorial describes how to use the BigQueryCommand,
BigQueryDataReader,
and BigQueryDataAdapter components.
Requirements
This tutorial assumes that you know how to connect to
Google BigQuery.
Note that you need to specify the license key in the License Key connection string parameter or activate the product on your computer.
This is described in the Licensing topic.
General Information
The main function of any database application is establishing a connection
to a data source and working with data contained in it. ADO.NET data providers
serve as a bridge between an application and a data source, and allow you to
execute commands as well as to retrieve data by using a DataReader or a DataAdapter.
Updating data involves using the Command and DataAdapter objects.
Retrieving Data Using BigQueryCommand and BigQueryDataReader
In this sample we will use BigQueryCommand
and BigQueryDataReader to retrieve data.
BigQueryDataReader retrieves data in pages: while you read from it, it automatically requests the next pages from Google BigQuery.
BigQueryDataReader offers higher performance than BigQueryDataAdapter, especially when you query a lot of data.
For more information, refer to the description of these classes in our documentation.
|
|---|
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 = "SELECT word, word_count FROM `bigquery-public-data.samples.shakespeare` WHERE corpus = 'hamlet' ORDER BY word_count DESC LIMIT 10";
using (BigQueryConnection connection = new BigQueryConnection(connectionString)) {
connection.Open();
using (BigQueryCommand command = connection.CreateCommand()) {
command.CommandText = sql;
using (BigQueryDataReader reader = command.ExecuteReader()) {
while (reader.Read()) {
Console.WriteLine("{0}\t{1}", reader.GetValue(0), reader.GetValue(1));
}
}
}
}
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 = "SELECT word, word_count FROM `bigquery-public-data.samples.shakespeare` WHERE corpus = 'hamlet' ORDER BY word_count DESC LIMIT 10"
Using connection As New BigQueryConnection(connectionString)
connection.Open()
Using command As BigQueryCommand = connection.CreateCommand()
command.CommandText = sql
Using reader As BigQueryDataReader = command.ExecuteReader()
While reader.Read()
Console.WriteLine("{0}" & vbTab & "{1}", reader.GetValue(0), reader.GetValue(1))
End While
End Using
End Using
End Using
Console.ReadKey()
End Sub
End Module
|
Retrieving Data Using BigQueryDataAdapter
In this sample we will use the BigQueryDataAdapter component. It retrieves data when you call its Fill method. Note that this method needs to retrieve all the queried data from Google BigQuery, which can take some time.
Here is a small sample that demonstrates the usage of BigQueryDataAdapter to retrieve data.
|
|---|
using System;
using System.Data;
using Devart.Data.BigQuery;
...
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 word, word_count FROM `bigquery-public-data.samples.shakespeare` WHERE corpus = 'hamlet' ORDER BY word_count DESC LIMIT 10";
using (BigQueryConnection connection = new BigQueryConnection(connectionString)) {
connection.Open();
DataTable table = new DataTable("Words");
using (BigQueryCommand command = connection.CreateCommand()) {
command.CommandText = sql;
using (BigQueryDataAdapter adapter = new BigQueryDataAdapter(command)) {
adapter.Fill(table);
}
}
foreach (DataRow row in table.Rows) {
Console.WriteLine("{0}\t{1}", row[0], row[1]);
}
}
Console.ReadKey();
}
|
|
|---|
Imports System
Imports System.Data
Imports Devart.Data.BigQuery
...
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 word, word_count FROM `bigquery-public-data.samples.shakespeare` WHERE corpus = 'hamlet' ORDER BY word_count DESC LIMIT 10"
Using connection As New BigQueryConnection(connectionString)
connection.Open()
Dim table As New DataTable("Words")
Using command As BigQueryCommand = connection.CreateCommand()
command.CommandText = sql
Using adapter As New BigQueryDataAdapter(command)
adapter.Fill(table)
End Using
End Using
For Each row As DataRow In table.Rows
Console.WriteLine("{0}" & vbTab & "{1}", row(0), row(1))
Next
End Using
Console.ReadKey()
End Sub
|
Additional Information
This tutorial describes only basic ways of working with data. For example, you can take advantage of
Entity Framework Core, an ORM technology intended for converting data between incompatible type systems
in different data sources and object-oriented programming languages. This technology allows you to decrease the
amount of code and maintenance required for data-oriented applications.
For more information, see Entity Framework Core Support.
See Also
Entity Framework Core Support
| Updating Data
