Create Dynamic Databricks Grids Using the Infragistics XamDataGrid

Using Infragistics WPF UI controls, you can build contemporary applications reminiscent of Microsoft Office for both desktop and touch-based devices. When coupled with the CData ADO.NET Provider for Databricks, you gain the capability to construct interactive grids, charts, and various other visual elements while directly accessing real-time data from Databricks data. This article will guide you through the process of creating a dynamic grid within Visual Studio using the Infragistics XamDataGrid control.

You will need to install the Infragistics WPF UI components to continue. Download a free trial here: https://www.infragistics.com/products/wpf.

Create a WPF Project

Open VisualStudio and create a new WPF project.

Add a TextBox for passing a SQL query to the CData ADO.NET Provider and a Button for executing the query.

The XAML at this stage is as follows:

< Window
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:CDataXamDataGridGroupingListApp"
        xmlns:igWPF="http://schemas.infragistics.com/xaml/wpf" x:Class="CDataXamDataGridGroupingListApp.MainWindow"
        mc:Ignorable="d"
        Title="MainWindow" Height="450" Width="800">
  < Grid>
    < TextBox x:Name="textBox" HorizontalAlignment="Left" Height="44" TextWrapping="Wrap" Text="TextBox" VerticalAlignment="Top" Width="682" Margin="10,10,0,0"/>
    < Button x:Name="button" Content="Execute" HorizontalAlignment="Left" Margin="697,10,0,0" VerticalAlignment="Top" Width="85" Height="44"/>
  < /Grid>
< /Window>

Add and Configure a XamDataGrid

After adding the initial controls, add a XamDataGrid to the App. The component will appear in the Visual Studio toolbox.

Arrange the component on the designer so that it is below the TextBox & Button and linked to the boundaries of the app.

Once the XamDataGrid is placed, edit the XAML to set the XamDataGrid DataSource attribute to "{Binding}" and set the FieldSettings AllowRecordFiltering and AllowSummaries attributes to "true." Next, add an empty method as the Click event handler for the Button component. The XAML at this stage is as follows:

< Window
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:CDataXamDataGridGroupingListApp"
        xmlns:igWPF="http://schemas.infragistics.com/xaml/wpf" x:Class="CDataXamDataGridGroupingListApp.MainWindow"
        mc:Ignorable="d"
        Title="MainWindow" Height="450" Width="800">
  < Grid>
    < TextBox x:Name="textBox" HorizontalAlignment="Left" Height="44" TextWrapping="Wrap" Text="TextBox" VerticalAlignment="Top" Width="682" Margin="10,10,0,0"/>
    < Button x:Name="button" Content="Execute" HorizontalAlignment="Left" Margin="697,10,0,0" VerticalAlignment="Top" Width="85" Click="Button_Click" Height="44"/>

    < igWPF:XamDataGrid Margin="10,59,10,10" DataSource="{Binding}">
      < igWPF:XamDataGrid.FieldSettings>
        < igWPF:FieldSettings AllowSummaries="True" AllowRecordFiltering="True"/>
      < /igWPF:XamDataGrid.FieldSettings>
    < /igWPF:XamDataGrid>

  < /Grid>
< /Window>

Connect to and Query Databricks

The last step in building our WPG App with a dynamic DataGrid is connecting to and querying live Databricks data. First add a reference to the CData ADO.NET Provider to the project (typically found in C:\Program Files\CData\CData ADO.NET Provider for Databricks\lib).

Next, add the Provider to the namespace, along with the standard Data library:

using System.Data.CData.Databricks;
using System.Data;

Finally, add the code to connect to Databricks and query using the text from the TextBox to the Click event handler.

To connect to a Databricks cluster, set the properties as described below.

Personal Access Token

To authenticate using a Personal Access Token, set the following:

• AuthScheme: Set this to PersonalAccessToken.
• Token: The token used to access the Databricks server. It can be obtained by navigating to the User Settings page of your Databricks instance and selecting the Access Tokens tab.

Azure Active Directory

To authenticate to Databricks using Azure Service Principal:

• AuthScheme: Set this to AzureServicePrincipal.
• AzureTenantId: Set this to the tenant ID of your Microsoft Azure Active Directory.
• AzureClientId: Set to the application (client) ID of your Microsoft Azure Active Directory application.
• AzureClientSecret: Set to the application (client) secret of your Microsoft Azure Active Directory application.
• AzureSubscriptionId: Set this to the Subscription Id of your Microsoft Azure Databricks Service Workspace.
• AzureResourceGroup: Set this to the Resource Group name of your Microsoft Azure Databricks Service Workspace.
• AzureWorkspace: Set this to the name of your Microsoft Azure Databricks Service Workspace.

Connecting to Databricks

To connect to a Databricks cluster, set the properties as described below.

Note: You can find the required values in your Databricks instance by navigating to Clusters and selecting the desired cluster, and selecting the JDBC/ODBC tab under Advanced Options.

• Database: Set to the name of the Databricks database.
• Server: Set to the Server Hostname of your Databricks cluster.
• HTTPPath: Set to the HTTP Path of your Databricks cluster.
• Token: Set to your personal access token (you can obtain this value by navigating to the User Settings page of your Databricks instance and selecting the Access Tokens tab).

Cloud Storage Configuration

The provider supports DBFS, Azure Blob Storage, and AWS S3 for uploading CSV files.

DBFS Cloud Storage

To use DBFS for cloud storage, set the following:

• CloudStorageType: Set this to DBFS.

Azure Blob Storage

Set the following to use Azure Blob Storage for cloud storage:

• CloudStorageType: Set this to Azure Blob storage.
• StoreTableInCloud: Set this to True to store tables in cloud storage when creating a new table.
• AzureStorageAccount: Set this to the name of your Azure storage account.
• AzureAccessKey: Set to the storage key associated with your Databricks account. Find this via the azure portal (using the root acoount). Select your storage account and click Access Keys to find this value.
• AzureBlobContainer: Set to the name of you Azure Blob storage container.

AWS S3

Set the following to use AWS S3 for cloud storage:

• CloudStorageType: Set this to AWS S3.
• StoreTableInCloud: Set this to True to store tables in cloud storage when creating a new table.
• AWSAccessKey: The AWS account access key. This value is accessible from your AWS security credentials page.
• AWSSecretKey: Your AWS account secret key. This value is accessible from your AWS security credentials page.
• AWSS3Bucket: Set to the name of your AWS S3 bucket.
• AWSRegion: The hosting region for your Amazon Web Services. You can obtain the AWS Region value by navigating to the Buckets List page of your Amazon S3 service, for example, us-east-1.

private void Button_Click(object sender, RoutedEventArgs e)
{
  //connecting to Databricks
  string connString = "AuthScheme=PersonalAccessToken;Server=my.databricks.url;HTTPPath=MyHTTPPath;Token=MyToken;Database=default;";
  using (var conn = new DatabricksConnection(connString))
  {
    //using the query from the TextBox
    var dataAdapter = new DatabricksDataAdapter(textBox.Text, conn);
    var table = new DataTable();
    dataAdapter.Fill(table);
    
    //passing the DataRowCollection to the DataContext
    //  for use in the XamDataGrid
    this.DataContext = table.Rows;
  }
}

Run the Application

With the app fully configured, we are ready to display Databricks data in our XamDataGrid. When you click "Execute," the app connects to Databricks and submits the SQL query through the CData ADO.NET Provider.

Live Databricks data is displayed in the grid.

Group the data by dragging and dropping a column name into the header.

As you add groupings and filters, the underlying SQL query is submitted directly to Databricks, making it possible to drill down into live Databricks data to find only the specific information you need.

Free Trial & More Information

At this point, you have created a dynamic WPF App with access to live Databricks data. For more information, visit the CData ADO.NET Provider page. Download a free, 30-day trial and start working live Databricks data in apps built using the Infragistics UI controls today.