Connect C# to Google BigQuery in .NET via ADO.NET

Google BigQuery is a fully managed, serverless data warehouse that lets you run SQL queries over large analytical datasets. To access this data from a .NET application, developers need a reliable way to authenticate in Google Cloud, send queries, and read the results with familiar data access tools.

In this article, we'll explore how to connect to Google BigQuery and retrieve its data in a C# application using dotConnect for Google BigQuery.

Why dotConnect for Google BigQuery?

Easy integration

dotConnect enables straightforward connectivity between .NET applications and Google BigQuery, allowing developers to query datasets and tables with SQL without working directly with the BigQuery REST API.

Familiar ADO.NET classes

Using familiar ADO.NET components, dotConnect for Google BigQuery allows developers to work with data through standard .NET data access patterns such as connections, commands, and data readers.

Advanced ORM support

dotConnect works with EF Core, Dapper, and NHibernate, enabling flexible object-relational mapping and efficient interaction with BigQuery data in .NET applications.

Google Cloud authentication

Connect with a Google Cloud service account and its JSON key, or with a Google user account through OAuth 2.0. All requests to BigQuery are sent over HTTPS.

ADO.NET compatibility

Devart implements the standard ADO.NET provider model in dotConnect solutions, ensuring consistent behavior and compatibility with existing .NET frameworks and development tools.

Priority support and frequent updates

Devart provides professional technical assistance, comprehensive documentation, and ongoing product enhancements, helping developers maintain stable and reliable integrations with Google BigQuery.

Download and activate dotConnect for Google BigQuery

You can try dotConnect for Google BigQuery free for 30 days. Choose one of the following installation options:

30-day free trial version

dotnet add package Devart.Data.BigQuery

Install-Package Devart.Data.BigQuery

Start using dotConnect for Google BigQuery in your project today with a free trial

Free 30-day trial

Connect to Google BigQuery using C#

Before you connect, prepare the Google Cloud credentials. The simplest option for applications is a service account: create it in the Google Cloud console, grant it the BigQuery Data Viewer and BigQuery Job User roles, and download its JSON key file. You also need the ID of your Google Cloud project and, optionally, the ID of the dataset to use by default. Store the key file securely and do not commit it to source control: anyone who has the file can access your data.

For the supported .NET platforms and Visual Studio versions, see Features & compatibility.

Create a console application, then use the code below to check that the connection to Google BigQuery is successful. The ServiceAccount authentication type takes the path to the JSON key file in the ServiceAccount Json parameter.

using System;
using Devart.Data.BigQuery;

namespace BigQueryConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString =
                "Authentication Type=ServiceAccount;" +
                "Project Id=YOUR_PROJECT_ID;" +
                "DataSet Id=YOUR_DATASET_ID;" +
                "ServiceAccount Json=PATH_TO_SERVICE_ACCOUNT_JSON;" +
                "License Key=**********";

            try
            {
                using (BigQueryConnection connection = new BigQueryConnection(connectionString))
                {
                    connection.Open();
                    Console.WriteLine("Connection successful.");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("An error occurred: " + ex.Message);
            }
        }
    }
}

To connect using a Google user account instead, set the authentication type to UserAccount and pass the OAuth 2.0 client ID, client secret, and refresh token. You can get the token by clicking Sign In in the connection dialog of Visual Studio Server Explorer.

Authentication Type=UserAccount;Project Id=YOUR_PROJECT_ID;DataSet Id=YOUR_DATASET_ID;Client Id=YOUR_CLIENT_ID;Client Secret=YOUR_CLIENT_SECRET;Refresh Token=YOUR_REFRESH_TOKEN;License Key=**********

Main connection properties

Retrieve BigQuery data

Once the connection is open, you can run GoogleSQL queries with BigQueryCommand and read the results with BigQueryDataReader. The example below reads rows from the employees table of the default dataset. The connectionString variable is the one defined in the previous example.

using System;
using Devart.Data.BigQuery;

...

using (BigQueryConnection connection = new BigQueryConnection(connectionString))
{
    connection.Open();

    using (BigQueryCommand command = new BigQueryCommand("SELECT first_name, last_name, job_title FROM employees LIMIT 10", connection))
    using (BigQueryDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"{reader["first_name"]} {reader["last_name"]}, {reader["job_title"]}");
        }
    }
}

To query a table from another dataset or project, use its fully qualified name in the project.dataset.table format, enclosed in backticks, for example, `my-project.my_dataset.employees`. Backticks are required when the project ID contains hyphens.

Connect using Server Explorer

You can also connect to Google BigQuery using Server Explorer in Visual Studio. Right-click Data Connections and select Add Connection from the context menu. In the Add Connection dialog, click Change and select Google BigQuery from the list of data sources.

Select the authentication type and fill in the connection properties. For ServiceAccount, specify the project ID, the dataset ID, and the path to the JSON key file. For UserAccount, click Sign In to log in to your Google account and get the refresh token. Then click Test Connection to verify the settings.

Click OK and expand the new data connection in Server Explorer. You can view the dataset structure, including tables and their columns. To display data from a table, right-click it and select Retrieve Data.

Connect with EF Core using Entity Developer

Entity Developer allows you to visually design and generate Entity Framework Core (EF Core) models, simplifying database application development. If Entity Developer is not installed, close your Visual Studio instance, download Entity Developer, and install it following the on-screen instructions.

Then follow the detailed illustrated guide to create your model using Entity Developer. When connecting, select dotConnect for Google BigQuery as the provider and specify the same connection properties as in Server Explorer. The dataset specified in the DataSet Id parameter acts as the database of the model, so this parameter is required for EF Core.

BigQuery supports primary and foreign key constraints but does not enforce them. If the tables of your dataset have no foreign key constraints declared, the model is generated without associations between entities. You can add associations manually in the model designer to use navigation properties in your code.

You can see the generated DbContext and entity classes in Visual Studio under the model. To read BigQuery data using EF Core, execute the code below. Replace BigQueryModel with the name of the context class generated for your model.

using System;
using System.Linq;

namespace BigQueryEFCore
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new BigQueryModel())
            {
                var employees = context.Employees
                    .Where(e => e.Dept == "Legal")
                    .OrderBy(e => e.LastName)
                    .Take(10)
                    .ToList();

                foreach (var employee in employees)
                {
                    Console.WriteLine($"{employee.FirstName} {employee.LastName}, {employee.JobTitle}");
                }
            }
        }
    }
}

Connect with EF Core using Scaffold-DbContext

Another way to connect to your BigQuery data is the Scaffold-DbContext command of EF Core. This command generates the entity classes and a DbContext class based on the tables of your dataset.

First, install the following NuGet packages using the NuGet Package Manager in Visual Studio:

• Microsoft.EntityFrameworkCore.Tools
• Devart.Data.BigQuery.EFCore

The Devart.Data.BigQuery.EFCore package is released in several builds, one for each supported EF Core version. Package versions without a suffix (X.Y.Z) target EF Core 3.1, and package versions ending in .8, .9, .10, and .11 target EF Core 8, 9, 10, and 11, respectively. Select the build that matches the EF Core version of your project.

After that, open the Package Manager Console via Tools > NuGet Package Manager > Package Manager Console and run the following command to scaffold the DbContext and entity classes from your dataset.

Scaffold-DbContext "Authentication Type=ServiceAccount;Project Id=YOUR_PROJECT_ID;DataSet Id=YOUR_DATASET_ID;ServiceAccount Json=PATH_TO_SERVICE_ACCOUNT_JSON;License Key=**********" Devart.Data.BigQuery.EFCore -OutputDir Models

The -OutputDir Models parameter specifies the output directory to store the generated model classes.

After running the scaffold command, EF Core generates the DbContext class and entity classes in the specified output directory. Review the generated code to ensure it reflects your BigQuery dataset.

Conclusion

This tutorial has demonstrated several methods for connecting to Google BigQuery and accessing its data. You can choose the approach that best fits your project requirements: plain ADO.NET classes for SQL queries, Server Explorer for exploring data in Visual Studio, or EF Core for working with BigQuery data as .NET objects.

dotConnect for Google BigQuery provides a fast and straightforward way to connect with a Google Cloud service account or a Google user account. Entity Developer allows you to visually design data models and work with BigQuery data through EF Core.

Both dotConnect for Google BigQuery and Entity Developer offer fully functional free trials, allowing you to integrate them into your workflow and evaluate their capabilities firsthand.

FAQ