BlazorData.Net
Sign in with your social account rss_feed

 


9/9/2024 M. Washington (Admin)

Microsoft Fabric and the Blazor Data Sheet

image

Using  the Blazor DataSheet allows you to easily view and update data in your Microsoft Fabric Warehouse.

 

Creating Forms is the biggest cost in an application

Creating forms is often one of the most time-consuming and costly parts of building an application, but utilizing a Blazor DataSheet can significantly reduce both. By allowing users to view and update data directly within a flexible, grid-like interface, Blazor DataSheet eliminates the need for complex form-building, streamlining development and maintenance efforts.

Additionally, it supports custom validation, ensuring that specific business rules are enforced without the overhead of designing and managing traditional form structures. This approach not only saves time but also lowers development costs while maintaining robust data integrity.

 

Using Parquet Files To Update Data

When connecting to a SQL data source TSQL commands can be uses to read, update, and delete data as covered in the following article: Using a Blazor Excel Datasheet To Update A Database. However, when using Fabric data updates can be very slow, especially when updating large amounts of data.

Therefore, to perform updates, the following method will be used:

 

image

 

  • Delete rows that have been removed or updated
    • TSQL will be used for this step because deleting data is a fast process
    • Any rows that are removed from the DataSheet will be deleted
    • Any rows that are updated in the DataSheet will be deleted (to be added in the subsequent step)
  • Create parquet file from the data
    • Parquet is a the native format of data in the Fabric warehouse. However, it is being used in this instance as a way to compress the data into a format that is easily imported into Fabric
      • Any rows that are added in the DataSheet will be added
      • Any rows that are updated in the DataSheet will be added
  • Import Parquet file into Azure Storage
    • The parquet file must be placed in a location that allows the TSQL statement: COPY INTO to import the data. Azure Storage provides such a location. a SAS token will be created to allow Fabric the authentication to access the parquet file
  • Use COPY INTO to import the parquet file into Fabric
    • This TSQL statement can import a parquet file that has a million rows in seconds.

 

Walk-Thru

image

When you download and run the project at: https://github.com/ADefWebserver/FabricDataExplorer navigate to the Datasheet tab.

 

image

Click the Gear icon next to Fabric Connection to open the Database Connection dialog.

Connect to any Fabric Data Warehouse that can be reached by the Blazor application and click the Set Database Connection button.

Note: See the following article: Connect to Microsoft Fabric Using TSQL and Microsoft Blazor for instructions on connecting to Fabric using TSQL.

 

image

Click the Gear icon next to Azure Storage Connection to open the Azure Storage Connection dialog.

Enter the connection information and click the Set Storage Connection button.

Note: See https://learn.microsoft.com/en-us/azure/storage/blobs/storage-quickstart-blobs-portal for assistance on creating Azure Storage containers.

 

image

The data will be retrieved.

 

image

The data will be displayed in the Blazor DataSheet.

 

image

The tables in the Fabric Data Warehouse will display.

Click the table dropdown to select a table to display.

 

image

Make any changes to the data including adding and deleting rows.

Click the Save button to save the changes.

 

image

When the update is complete, the confirmation result will display.

 

image

You can verify the data has been updated in the Fabric website.

 

image

You can also verify the data has been imported using the Microsoft SQL Server Management Studio.

Note: See the following article: Connect to Microsoft Fabric Using TSQL and Microsoft Blazor for instructions on connecting to Fabric using Microsoft SQL Server Management Studio.

 

The Application Code

image

Most of the code is found in the DatasheetEditor.razor file.

 

The Blazor DataSheet

Blazor DataSheet serves as the interface for displaying and editing data within the application, streamlining user interactions.

 

            <Datasheet @ref="_datasheet"

                       Sheet="sheet"

                       Virtualise="true"

                       CanUserMergeRows="false"

                       ShowColHeadings="true"

                       ShowRowHeadings="true"

                       CanUserInsertRows="true"

                       CanUserRemoveRows="true"

                       CanUserInsertCols="false"

                       CanUserRemoveCols="false"

                       CanUserSort="false" />

 

The following code is used to display the toolbar:

 

        <div class="rz-p-sm-1" style="background-color:whitesmoke;border-block:thin;border-block-color:lightgray;text-align:left">

            <button @onclick="btnSave" disabled="@SavingDisabled" style="background:none;border:none;">

                <i class="material-icons" style="color:black;">save</i>

            </button>

            <span style="border-right: 2px solid #D3D3D3;"></span>

            <button @onclick="() => sheet.Commands.Undo()" title="Undo" style="background:none;border:none;">

                <i class="material-icons" style="color:black;">undo</i>

            </button>

            <button @onclick="() => sheet.Commands.Redo()" title="Redo" style="background:none;border:none;">

                <i class="material-icons" style="color:black;">redo</i>

            </button>

            <span style="border-right: 2px solid #D3D3D3;"></span>

            <button @onclick="InsertRowAfterSelection" title="Insert Row" style="background:none;border:none;">

                <i class="material-icons" style="color:black;">input</i>

            </button>

            <button @onclick="RemoveRow" title="Delete Row" style="background:none;border:none;">

                <i class="material-icons" style="color:black;">logout</i>

            </button>

            <span style="border-right: 2px solid #D3D3D3;"></span>

            <span style="border-right: 2px solid #D3D3D3;"></span>

            <span>&nbsp; Table: </span>

            <select @onchange="OnSelectedTableChanged" style="width: 300px; border-block:hidden">

                @foreach (var tableName in tableNames)

                {

                    @if (tableName == selectedTable)

                    {

                        <option value="@tableName" selected>@tableName</option>

                    }

                    else

                    {

                        <option value="@tableName">@tableName</option>

                    }

                }

            </select>

        </div>

 

When data is retrieved, this SQL statement is used to pull in the data with the first field set to: __Id. This auto number field allows the various DataTables to be compared to determine which rows have been inserted and deleted.

 

                    // Get the data from the selected table

                    sql = $"SELECT ROW_NUMBER() OVER (ORDER BY {strPrimaryKeys}) AS __Id, {strTableNames} FROM {selectedTable}";

 

It is also used to retrieve rows from the DataTable that have been updated. This is possible because this method is fired when any row is changed and records the value of the row’s __Id field:

 

    private void CellChanged(object sender, CellDataChangedEventArgs args)

    {

        // Only add the row if one cell is changed

        if ((args.Positions.Count() > 0) && (args.Positions.Count() < 2))

        {

            // Get the row number

            int row = Convert.ToInt32(args.Positions.FirstOrDefault().row);

            // Use that row number to get the hidden __Id value

            int __Id = Convert.ToInt32(sheet.Cells[row, 0].Value);

            // Add the row to the list of changed rows

            ColChangedRows.Add(__Id);

        }

    }


 


Updating Data


image 

When the Save button is pressed, the data in the Blazor DataSheet is turned into a DataTable. This DataTable is compared to a DataTable that was created when the Blazor DataSheet was first loaded. 

This allows the code to determine which rows have been deleted, Witch rows have been added, and which rows have been updated. 

When rows are deleted, ProcessDeletedRows() is triggered to remove them from the Fabric database. Similarly, new rows are handled by ProcessInsertedRows(), and changes to existing rows are processed by ProcessUpdatedRows(). 


Once these operations are complete, if any updates exist, the system imports the modified data into the Fabric environment using a parquet file. 


 
            // *****************************************

            // Deletes

            // *****************************************

            ProcessDeletedRows();

            // *****************************************

            // Inserts

            // *****************************************

            ProcessInsertedRows();

            // *****************************************

            // Updates

            // *****************************************

            ProcessUpdatedRows();


  
The code then creates an instance of the ImportDataTableIntoFabric class, which is responsible for importing data into Fabric. It sets various properties of the objImportDataTableIntoFabric object, including the DataTable, table name, Fabric connection string, and Azure storage account details. 

The code then calls the ImportDatAsynca() method of the objImportDataTableIntoFabric object, which performs the actual data import process. This method returns a message indicating the result of the import.
 
                ImportDataTableIntoFabric objImportDataTableIntoFabric = new ImportDataTableIntoFabric();

                objImportDataTableIntoFabric.InputDataTable = UpdateDataTable;

                objImportDataTableIntoFabric.TableName = selectedTable;

                objImportDataTableIntoFabric.DataWarehouseConnectionString = FabricConnectionString;

                objImportDataTableIntoFabric.StorageAccountName = StorageAccountName;

                objImportDataTableIntoFabric.StorageAccountKey = AccountKey;

                objImportDataTableIntoFabric.StorageContainer = ContainerName;

                var result = await objImportDataTableIntoFabric.ImportDataAsync();
 
 
 


The ImportDataTableIntoFabric Class


This code defines the ImportDataTableIntoFabric class that facilitates the process of importing the data into the Fabric Data Warehouse through Azure Blob Storage using Parquet format. 

The main method ImportDataAsync parses the DataTable and extracts data, and transforms the result set into a Parquet table. 

 
           var databaseFields = ExtractFieldsFromDataTable(InputDataTable);

           var parquetSchema =

               new ParquetSchema(databaseFields.Select(f => new DataField<string>(f)).ToList());

           var parquetTable =

               new Parquet.Rows.Table(parquetSchema);

           // Loop through the DataTable and add rows to the Parquet table

           foreach (DataRow dataRow in InputDataTable.Rows)

           {

               var row = new Row(

                   databaseFields.Select(f => CleanValue(dataRow[f]?.ToString())).ToArray()

               );

               parquetTable.Add(row);

           }


  
It then writes the table into an in-memory stream and uploads it to an Azure Blob Storage container.
 
            using var ms = new MemoryStream();

            await parquetTable.WriteAsync(ms);

            ms.Position = 0;

            var blobClient = new BlobContainerClient(

                $"DefaultEndpointsProtocol=https;" +

                $"AccountName={StorageAccountName};" +

                $"AccountKey={StorageAccountKey};" +

                $"EndpointSuffix=core.windows.net",

                StorageContainer);

            string fileName = Path.GetFileName(TableName) + ".parquet";

            var blob = blobClient.GetBlobClient($"{fileName}");

            await blob.UploadAsync(ms, true);

            await Task.Delay(5000);
 
 


After uploading the data, a SAS (Shared Access Signature) token is generated, which allows access to the blob with limited permissions.
 
    private string GenerateSasToken()

    {

        var sasBuilder = new AccountSasBuilder

        {

            Protocol = SasProtocol.Https,

            Services = AccountSasServices.Blobs,

            ResourceTypes = AccountSasResourceTypes.Container | AccountSasResourceTypes.Object,

            ExpiresOn = DateTimeOffset.UtcNow.AddHours(1)

        };

        sasBuilder.SetPermissions(AccountSasPermissions.Read | AccountSasPermissions.Write);

        return sasBuilder.ToSasQueryParameters(

            new StorageSharedKeyCredential(

                StorageAccountName,

                StorageAccountKey)).ToString();

    }
 
 


This SAS token is used to construct a SQL script that constructs the SQL command for performing a bulk data load using the COPY INTO command.
 
    private List<string> GenerateSqlScripts(string tableName, IList<string> fields, string sasToken)

    {

        var fieldSchema =

            string.Join(", ", fields.Select(f => $"[{f}] VARCHAR(4000)"));

        var filePath =

            $"https://{StorageAccountName}.blob.core.windows.net/{StorageContainer}/{tableName}.parquet";

        return new List<string>

        {

            $"COPY INTO {tableName} " +

            $"FROM '{filePath}' " +

            $"WITH (file_type = 'PARQUET', CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='{sasToken}'))"

        };

    }
 


Finally the script is executed to import the data.
 
            using (var connection = new SqlConnection(DataWarehouseConnectionString))

            {

                connection.Open();

                foreach (var sql in sqlScripts)

                {

                    using var command = new SqlCommand(sql, connection);

                    await command.ExecuteNonQueryAsync();

                    await Task.Delay(5000); // To ensure operations are sequenced properly

                }

            }


 


 


 


Links


 


FabricDataExplorer (Github - Source code) 

Import Data Fast into Fabric Using Parquet Files 

Connect to Microsoft Fabric Using TSQL and Microsoft Blazor 

https://github.com/anmcgrath/BlazorDatasheet 

https://github.com/aloneguid/parquet-dotnet 

https://learn.microsoft.com/en-us/fabric/data-warehouse/ingest-data

    

    



    An unhandled error has occurred.
    Reload
    🗙