MySQL Data Binding

29 Nov 202411 minutes to read

This section describes how to use MySQL data to retrieve data from a MySQL server and bind it to the Blazor Pivot Table.

Connecting a MySQL database to a Syncfusion^® Blazor Pivot Table

1. Create a simple Blazor Pivot Table by following the “Getting Started” documentation link.

2. To connect a MySQL using the MySQL driver in our application, we need to install the MySQL.Data NuGet package. To do so, open the NuGet package manager of the project solution, search for the package MySQL.Data and install it.

Add the NuGet package "MySQL.Data" to the project

3. Next, in the Index.razor page, under the OnInitialized method, connect to MySQL. MySqlConnection helps to connect the MySQL database. Next, using MySqlCommand and MySqlDataAdapter you can process the desired query string and retrieve data from the MySQL database. The Fill method of the MySqlDataAdapter is used to populate the retrieved data into a DataTable, which is then converted to a List.

4. Finally, bind the list to the DataSource property in the PivotViewDataSourceSettings and configure the report to use the MySQL data.

@using Syncfusion.Blazor.PivotView
@using MySql.Data.MySqlClient;
@using Newtonsoft.Json;
@using System.Data;
@using Syncfusion.Blazor.Data;

<SfPivotView TValue="OrderDetails" Width="800" Height="340">
    <PivotViewDataSourceSettings TValue="OrderDetails" DataSource="@dataSource">
        <PivotViewColumns>
            <PivotViewColumn Name="ShipName"></PivotViewColumn>
        </PivotViewColumns>
        <PivotViewRows>
            <PivotViewRow Name="ShipCity"></PivotViewRow>
        </PivotViewRows>
        <PivotViewValues>
            <PivotViewValue Name="Freight"></PivotViewValue>
        </PivotViewValues>
        <PivotViewFormatSettings>
            <PivotViewFormatSetting Name="Freight" Format="N2"></PivotViewFormatSetting>
        </PivotViewFormatSettings>
        </PivotViewDataSourceSettings>
</SfPivotView>

@code {
    private List<OrderDetails> dataSource { get; set; }

    protected override void OnInitialized()
    {
        MySqlConnection connection = new MySqlConnection("<Enter your valid connection string here>");
        connection.Open();
        MySqlCommand command = new MySqlCommand("SELECT * FROM orders", connection);
        MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command);
        DataTable dataTable = new DataTable();
        dataAdapter.Fill(dataTable);
        connection.Close();
        dataSource = (from DataRow data in dataTable.Rows
                       select new OrderDetails()
                           {
                               OrderID = Convert.ToInt32(data["OrderId"]),
                               CustomerID = data["CustomerID"].ToString(),
                               ShipCity = data["ShipCity"].ToString(),
                               ShipName = data["ShipName"].ToString(),
                               Freight = Convert.ToDouble(data["Freight"])
                           }).ToList();

    }

    public class OrderDetails
    {
        public int OrderID { get; set; }
        public string CustomerID { get; set; }
        public string ShipCity { get; set; }
        public string ShipName { get; set; }
        public double Freight { get; set; }
        }
   
}

When you run the application, the resultant pivot table will look like this.

Blazor Pivot Table bound with MySQL data

Connecting a MySQL to a Syncfusion^® Blazor Pivot Table via Web API service

Create a Web API service to fetch MySQL data

1. Open Visual Studio and create an ASP.NET Core Web App project type, naming it MyWebService. To create an ASP.NET Core Web application, follow the documentation link.

Create ASP.NET Core Web App project

2. To connect a MySQL using the MySQL data in our application, we need to install the MySQL.Data NuGet package. To do so, open the NuGet package manager of the project solution, search for the package MySQL.Data and install it.

Add the NuGet package "MySQL.Data" to the project

3. Create a Web API controller (aka, PivotController.cs) file under Controllers folder that helps to establish data communication with the Pivot Table.

4. In the Web API controller (aka, PivotController), *MySqlConnection helps to connect the MySQL database. Next, using MySqlCommand and MySqlDataAdapter you can process the desired query string and retrieve data from the MySQL database. The Fill method of the MySqlDataAdapter is used to populate the retrieved data into a DataTable as shown in the following code snippet.

    using Microsoft.AspNetCore.Mvc;
    using MySql.Data.MySqlClient;
    using Newtonsoft.Json;
    using System.Data;

    namespace MyWebService.Controllers
    {
        [ApiController]
        [Route("[controller]")]
        public class PivotController : ControllerBase
        {
            public dynamic GetMySQLResult()
            {
                // Replace with your own connection string.
                MySqlConnection connection = new MySqlConnection("<Enter your valid connection string here>");
                connection.Open();
                MySqlCommand command = new MySqlCommand("SELECT * FROM orders", connection);
                MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);
                connection.Close();
                return dataTable;
            }
        }
    }

5. In the Get() method of the PivotController.cs file, the GetMySQLResult method is used to retrieve the MySQL data as a list, which is then serialized into JSON string using JsonConvert.SerializeObject().

    using Microsoft.AspNetCore.Mvc;
    using MySql.Data.MySqlClient;
    using Newtonsoft.Json;
    using System.Data;

    namespace MyWebService.Controllers
    {
        [ApiController]
        [Route("[controller]")]
        public class PivotController : ControllerBase
        {
            [HttpGet(Name = "GetMySQLResult")]
            public object Get()
            {
                return JsonConvert.SerializeObject(GetMySQLResult());
            }

            public dynamic GetMySQLResult()
            {
                // Replace with your own connection string.
                MySqlConnection connection = new MySqlConnection("<Enter your valid connection string here>");
                connection.Open();
                MySqlCommand command = new MySqlCommand("SELECT * FROM orders", connection);
                MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);
                connection.Close();
                return dataTable;
            }
        }
    }

6. Run the application and it will be hosted within the URL https://localhost:7146.

7. Finally, the retrieved data from MySQL database which is in the form of JSON can be found in the Web API controller available in the URL link https://localhost:7146/Pivot, as shown in the browser page below.

Hosted Web API URL

Connecting the Pivot Table to a MySQL using the Web API service

1. Create a simple Blazor Pivot Table by following the “Getting Started” documentation link.

2. Map the hosted Web API’s URL link https://localhost:7146/Pivot to the Pivot Table in Index.razor by using the Url property under PivotViewDataSourceSettings. This Url property aids in the de-serialization of MySQL data into instances of your model data class (aka, TValue=”ProductDetails”) while bound to the pivot table.

@using Syncfusion.Blazor.PivotView

<SfPivotView TValue="OrderDetails" Width="1000" Height="300" ShowFieldList="true">
    <PivotViewDataSourceSettings TValue="OrderDetails" Url="https://localhost:7146/Pivot" ExpandAll=false EnableSorting=true>
         <PivotViewColumns>
            <PivotViewColumn Name="ShipName"></PivotViewColumn>
        </PivotViewColumns>
        <PivotViewRows>
            <PivotViewRow Name="ShipCity"></PivotViewRow>
        </PivotViewRows>
        <PivotViewValues>
            <PivotViewValue Name="Freight"></PivotViewValue>
        </PivotViewValues>
        <PivotViewFormatSettings>
            <PivotViewFormatSetting Name="Freight" Format="N2"></PivotViewFormatSetting>
        </PivotViewFormatSettings>
    </PivotViewDataSourceSettings>
    <PivotViewGridSettings ColumnWidth="120"></PivotViewGridSettings>
</SfPivotView>

@code {
    public class OrderDetails
    {
        public int OrderID { get; set; }
        public string ShipName { get; set; }
        public string CustomerID { get; set; }
        public string ShipCity { get; set; }
        public double Freight { get; set; }
    }
}