Syncfusion Blazor Pivot Table is a powerful control used to organize and summarize business data and display the result in a cross-table format. Its rich feature set includes data binding, drilling up and down, Excel-like filtering and sorting, editing, and more. A high volume of pivot data can be loaded without any performance degradation using row and column virtualization.
SQL Server is a relational database management system. It can be used to store and manage a huge volume of data.
In this blog, we’ll see how to save and load reports from a SQL Server database using the Microsoft SqlClient and load them into the Syncfusion Blazor Pivot Table at runtime.
To save a report to a SQL Server database, first you need to create a table in the database. Then, you can use SQL commands such as INSERT, UPDATE, and MERGE to populate or modify the table in the report. To load a report from a SQL Server database, you can use the SELECT command to retrieve the report from the table and then assign it to the Blazor Pivot Table and update it as needed.
While saving a report in SQL Server, it’s better to save it in a denormalized (i.e. serialized) form so that the configurations inside the report will be maintained with privacy and loaded back in the Pivot Table. Our Blazor Pivot Table follows the same procedure to save and load a report from SQL Server.
Usually, a report contains configuration settings, such as rows, columns, values, filters, sorting, filtering, grouping, formatting, and calculated fields, that are used to render the Pivot Table. It also stores the current UI information related to both the table and chart UIs.
To connect a SQL Server database using Microsoft SqlClient in your app, you need to install the Microsoft.Data.SqlClient NuGet package. To do so, open Visual Studio and then select the NuGet Package Manager option from the Tools tab. Search for the package Microsoft.Data.SqlClient and install it.
Note: If you’re new to the Syncfusion Blazor Pivot Table, please refer to its getting started documentation before proceeding.
The Syncfusion Blazor Pivot Table has a built-in toolbar UI with predefined report manipulation options. With the help of these options, you can easily perform operations like save, save as, rename, load, delete, and add on reports dynamically at runtime.
Let’s explore how to utilize these built-in toolbar actions.
Once the Pivot Table UI is loaded, select the Save a report icon in the toolbar to save the report settings in the SQL Server database.
When you click the Save a report icon, the SaveReport event will be triggered. This event will pass the current report name and the current report in a serialized form to the SQL Server database.
The following code displays the report along with the report name Sample Report (the name Sample Report comes from the source code by default, but you can rename it as explained later in this blog) that will be passed to the event to save them in the database.
@using System.Data
@using Microsoft.Data.SqlClient;
@using EFPivotTable.Data;
<SfPivotView @ref=PivotRef TValue="ProductDetails" ID="pivot-table" Height="450" ShowToolbar="true" ShowFieldList=true AllowExcelExport=true AllowPdfExport=true Toolbar="@PivotToolbar">
<PivotViewDisplayOption View="View.Both"></PivotViewDisplayOption>
<PivotViewDataSourceSettings DataSource="@DataSource" EnableSorting="true">
<PivotViewColumns>
<PivotViewColumn Name="Year"></PivotViewColumn>
<PivotViewColumn Name="Quarter"></PivotViewColumn>
</PivotViewColumns>
<PivotViewRows>
<PivotViewRow Name="Country"></PivotViewRow>
<PivotViewRow Name="Products"></PivotViewRow>
</PivotViewRows>
<PivotViewValues>
<PivotViewValue Name="Sold" Caption="Units Sold"></PivotViewValue>
<PivotViewValue Name="Amount" Caption="Sold Amount"></PivotViewValue>
</PivotViewValues>
<PivotViewFormatSettings>
<PivotViewFormatSetting Name="Amount" Format="C"></PivotViewFormatSetting>
</PivotViewFormatSettings>
</PivotViewDataSourceSettings>
<PivotViewEvents TValue="ProductDetails" SaveReport="SaveReport"></PivotViewEvents>
</SfPivotView>
@code {
SfPivotView<ProductDetails> PivotRef { get; set; }
public List<ProductDetails> DataSource { get; set; }
public List<ToolbarItems> PivotToolbar = new List<ToolbarItems> {
ToolbarItems.New,
ToolbarItems.Save,
ToolbarItems.SaveAs,
ToolbarItems.Rename,
ToolbarItems.Remove,
ToolbarItems.Load,
ToolbarItems.Grid,
ToolbarItems.Chart,
ToolbarItems.Export,
ToolbarItems.SubTotal,
ToolbarItems.GrandTotal,
ToolbarItems.FieldList
};
protected override void OnInitialized()
{
this.DataSource = ProductDetails.GetProductData().ToList();
}
public void SaveReport(SaveReportArgs args)
{
SaveReportToDB(args.ReportName, args.Report);
}
public void SaveReportToDB(string reportName, string report)
{
SqlConnection sqlConn = OpenConnection();
bool isDuplicate = true;
SqlCommand cmd1 = null;
foreach (DataRow row in GetDataTable(sqlConn).Rows)
{
if ((row["ReportName"] as string).Equals(reportName))
{
isDuplicate = false;
cmd1 = new SqlCommand("UPDATE ReportTable set Report=@Report where ReportName like @ReportName", sqlConn);
}
}
if (isDuplicate)
{
cmd1 = new SqlCommand("INSERT into ReportTable (ReportName,Report) Values(@ReportName,@Report)", sqlConn);
}
cmd1.Parameters.AddWithValue("@ReportName", reportName);
cmd1.Parameters.AddWithValue("@Report", report.ToString());
cmd1.ExecuteNonQuery();
sqlConn.Close();
}
private SqlConnection OpenConnection()
{
// Use your connection string to access your own SQL database.
string connectionString = @"Data Source=(LocalDB)\ProjectModels;AttachDbFilename=" + Environment.CurrentDirectory + @"\App_Data\NORTHWND.mdf;Integrated Security=True";
SqlConnection sqlConn = new SqlConnection(connectionString);
sqlConn.Open();
return sqlConn;
}
private DataTable GetDataTable(SqlConnection sqlConn)
{
string xquery = "SELECT * FROM ReportTable";
SqlCommand cmd = new SqlCommand(xquery, sqlConn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
} Refer to the following image.
You can later modify the current report, such as rearrange the countries in descending order and expand the United States in the row axis. To re-save the modified report to the same database with the same report name (Sample Report), simply click the Save a report icon in the toolbar once more, which will trigger the SaveReport event. This event will pass the current report name (Sample Report) and the modified report in a serialized form to save it in the same database.
Refer to the following image.
If you want to save a copy of the current report to the database with a different name, then use the Save as current report option in the toolbar UI.
Refer to the following image.
When you click the Save as current report icon, a dialog will appear in which you can provide a new name to the same report. In our example, we give it the name Sample Report 2.
After entering the report name, click OK to close the dialog. Now, the SaveReport event will be triggered and it will save the report in serialized form with the new report name Sample Report 2 in the SQL Server database.
Note: The code examples for saving, saving a modified report, and saving a copy of the current report are all the same.
The dropdown in the toolbar will be automatically loaded with a list of previously saved reports that have been retrieved from the SQL database using the FetchReport event. This event is always triggered to retrieve the most recent report list from the SQL data whenever a report is saved, updated, removed, or added to the Blazor Pivot Table.
Refer to the following code example.
@using System.Data
@using Microsoft.Data.SqlClient;
@using EFPivotTable.Data;
<SfPivotView @ref=PivotRef TValue="ProductDetails" ID="pivot-table" Height="450" Width="700" ShowToolbar="true" ShowFieldList=true AllowExcelExport=true AllowPdfExport=true Toolbar="@PivotToolbar">
<PivotViewDisplayOption View="View.Both"></PivotViewDisplayOption>
<PivotViewDataSourceSettings DataSource="@DataSource" EnableSorting="true">
<PivotViewColumns>
<PivotViewColumn Name="Year"></PivotViewColumn>
<PivotViewColumn Name="Quarter"></PivotViewColumn>
</PivotViewColumns>
<PivotViewRows>
<PivotViewRow Name="Country"></PivotViewRow>
<PivotViewRow Name="Products"></PivotViewRow>
</PivotViewRows>
<PivotViewValues>
<PivotViewValue Name="Sold" Caption="Units Sold"></PivotViewValue>
<PivotViewValue Name="Amount" Caption="Sold Amount"></PivotViewValue>
</PivotViewValues>
<PivotViewFormatSettings>
<PivotViewFormatSetting Name="Amount" Format="C"></PivotViewFormatSetting>
</PivotViewFormatSettings>
</PivotViewDataSourceSettings>
<PivotViewEvents TValue="ProductDetails" LoadReport="LoadReport" FetchReport="FetchReport"></PivotViewEvents>
</SfPivotView>
@code {
SfPivotView<ProductDetails> PivotRef { get; set; }
public List<ProductDetails> DataSource { get; set; }
public List<ToolbarItems> PivotToolbar = new List<ToolbarItems> {
ToolbarItems.New,
ToolbarItems.Save,
ToolbarItems.SaveAs,
ToolbarItems.Rename,
ToolbarItems.Remove,
ToolbarItems.Load,
ToolbarItems.Grid,
ToolbarItems.Chart,
ToolbarItems.Export,
ToolbarItems.SubTotal,
ToolbarItems.GrandTotal,
ToolbarItems.FieldList
};
protected override void OnInitialized()
{
this.DataSource = ProductDetails.GetProductData().ToList();
}
public void SaveReport(SaveReportArgs args)
{
SaveReportToDB(args.ReportName, args.Report);
}
// To fetch saved reports from the SQL database.
public void FetchReport(FetchReportArgs args)
{
args.ReportName = FetchReportListFromDB().ToArray();
}
public List<string> FetchReportListFromDB()
{
SqlConnection sqlConn = OpenConnection();
List<string> reportNames = new List<string>();
foreach (DataRow row in GetDataTable(sqlConn).Rows)
{
if (!string.IsNullOrEmpty(row["ReportName"] as string))
{
reportNames.Add(row["ReportName"].ToString());
}
}
sqlConn.Close();
return reportNames;
}
// To retrieve selected report from the SQL database and then load it to the pivot table.
public async Task LoadReport(LoadReportArgs args)
{
await this.PivotRef.LoadPersistDataAsync(LoadReportFromDB(args.ReportName).ToString());
}
public object LoadReportFromDB(string reportName)
{
SqlConnection sqlConn = OpenConnection();
string report = string.Empty;
foreach (DataRow row in GetDataTable(sqlConn).Rows)
{
if ((row["ReportName"] as string).Equals(reportName))
{
report = (string)row["Report"];
break;
}
}
sqlConn.Close();
return report;
}
private SqlConnection OpenConnection()
{
// Use your connection string to access your own SQL database.
string connectionString = @"Data Source=(LocalDB)\ProjectModels;AttachDbFilename=" + Environment.CurrentDirectory + @"\App_Data\NORTHWND.mdf;Integrated Security=True";
SqlConnection sqlConn = new SqlConnection(connectionString);
sqlConn.Open();
return sqlConn;
}
private DataTable GetDataTable(SqlConnection sqlConn)
{
string xquery = "SELECT * FROM ReportTable";
SqlCommand cmd = new SqlCommand(xquery, sqlConn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
} Choose a report from the dropdown menu, say Sample Report 2.
Now, the LoadReport event will be triggered with the selected report name Sample Report 2. This event will search for and fetch the report from the SQL database and load it into the Blazor Pivot Table.
You can use the Rename a current report icon in the toolbar to rename the current report and save it back to the SQL database.
As shown in the previous image, we’re going to rename the current report, Sample Report 2. To do so, click the Rename a current report icon. A dialog will appear in which you can provide a new name to the current report (for example, Global Trend Report).
If you specify a report name that is already in the report list, the Blazor Pivot Table will show the following alert. You can choose whether to replace that report.
Suppose you specify a report name that is not already in the report list. In that case, the dialog closes on clicking the OK button and the RenameReport event is triggered. This event will pass the current report name Sample Report 2 and the modified report name (Global Trend Report). Based on this information, the report name will be updated in the database.
Refer to the following code example.
@using System.Data
@using Microsoft.Data.SqlClient;
@using EFPivotTable.Data;
<SfPivotView @ref=PivotRef TValue="ProductDetails" ID="pivot-table" Height="450" Width="700" ShowToolbar="true" ShowFieldList=true AllowExcelExport=true AllowPdfExport=true Toolbar="@PivotToolbar">
<PivotViewDisplayOption View="View.Both"></PivotViewDisplayOption>
<PivotViewDataSourceSettings DataSource="@DataSource" EnableSorting="true">
<PivotViewColumns>
<PivotViewColumn Name="Year"></PivotViewColumn>
<PivotViewColumn Name="Quarter"></PivotViewColumn>
</PivotViewColumns>
<PivotViewRows>
<PivotViewRow Name="Country"></PivotViewRow>
<PivotViewRow Name="Products"></PivotViewRow>
</PivotViewRows>
<PivotViewValues>
<PivotViewValue Name="Sold" Caption="Units Sold"></PivotViewValue>
<PivotViewValue Name="Amount" Caption="Sold Amount"></PivotViewValue>
</PivotViewValues>
<PivotViewFormatSettings>
<PivotViewFormatSetting Name="Amount" Format="C"></PivotViewFormatSetting>
</PivotViewFormatSettings>
</PivotViewDataSourceSettings>
<PivotViewEvents TValue="ProductDetails" RenameReport="RenameReport" ></PivotViewEvents>
</SfPivotView>
@code {
SfPivotView<ProductDetails> PivotRef { get; set; }
public List<ProductDetails> DataSource { get; set; }
public List<ToolbarItems> PivotToolbar = new List<ToolbarItems> {
ToolbarItems.New,
ToolbarItems.Save,
ToolbarItems.SaveAs,
ToolbarItems.Rename,
ToolbarItems.Remove,
ToolbarItems.Load,
ToolbarItems.Grid,
ToolbarItems.Chart,
ToolbarItems.Export,
ToolbarItems.SubTotal,
ToolbarItems.GrandTotal,
ToolbarItems.FieldList
};
protected override void OnInitialized()
{
this.DataSource = ProductDetails.GetProductData().ToList();
}
// To rename a report.
public void RenameReport(RenameReportArgs args)
{
RenameReportInDB(args.ReportName, args.Rename);
}
public void RenameReportInDB(string reportName, string renameReport)
{
SqlConnection sqlConn = OpenConnection();
SqlCommand cmd1 = null;
foreach (DataRow row in GetDataTable(sqlConn).Rows)
{
if ((row["ReportName"] as string).Equals(reportName))
{
cmd1 = new SqlCommand("UPDATE ReportTable set ReportName=@RenameReport where ReportName like '%" + reportName + "%'", sqlConn);
break;
}
}
cmd1.Parameters.AddWithValue("@RenameReport", renameReport);
cmd1.ExecuteNonQuery();
sqlConn.Close();
}
private SqlConnection OpenConnection()
{
// Use your connection string to access your own SQL database.
string connectionString = @"Data Source=(LocalDB)\ProjectModels;AttachDbFilename=" + Environment.CurrentDirectory + @"\App_Data\NORTHWND.mdf;Integrated Security=True";
SqlConnection sqlConn = new SqlConnection(connectionString);
sqlConn.Open();
return sqlConn;
}
private DataTable GetDataTable(SqlConnection sqlConn)
{
string xquery = "SELECT * FROM ReportTable";
SqlCommand cmd = new SqlCommand(xquery, sqlConn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
} You can easily delete a report from the Pivot Table and the SQL database using the Delete a current report icon in the toolbar. Just search for and delete the report using the report name. In this example, we will delete the currently loaded Global Trend Report.
When you click the Delete a current report icon, the following alert dialog will appear with the Yes and No options to delete the current report.
On choosing Yes, the RemoveReport event will be triggered with the name of the selected report (Global Trend Report). This will automatically delete the report for the Pivot Table.
Refer to the following code example.
@using System.Data
@using Microsoft.Data.SqlClient;
@using EFPivotTable.Data;
<SfPivotView @ref=PivotRef TValue="ProductDetails" ID="pivot-table" Height="450" Width="700" ShowToolbar="true" ShowFieldList=true AllowExcelExport=true AllowPdfExport=true Toolbar="@PivotToolbar">
<PivotViewDisplayOption View="View.Both"></PivotViewDisplayOption>
<PivotViewDataSourceSettings DataSource="@DataSource" EnableSorting="true">
<PivotViewColumns>
<PivotViewColumn Name="Year"></PivotViewColumn>
<PivotViewColumn Name="Quarter"></PivotViewColumn>
</PivotViewColumns>
<PivotViewRows>
<PivotViewRow Name="Country"></PivotViewRow>
<PivotViewRow Name="Products"></PivotViewRow>
</PivotViewRows>
<PivotViewValues>
<PivotViewValue Name="Sold" Caption="Units Sold"></PivotViewValue>
<PivotViewValue Name="Amount" Caption="Sold Amount"></PivotViewValue>
</PivotViewValues>
<PivotViewFormatSettings>
<PivotViewFormatSetting Name="Amount" Format="C"></PivotViewFormatSetting>
</PivotViewFormatSettings>
</PivotViewDataSourceSettings>
<PivotViewEvents TValue="ProductDetails" RemoveReport="RemoveReport" ></PivotViewEvents>
</SfPivotView>
@code {
SfPivotView<ProductDetails> PivotRef { get; set; }
public List<ProductDetails> DataSource { get; set; }
public List<ToolbarItems> PivotToolbar = new List<ToolbarItems> {
ToolbarItems.New,
ToolbarItems.Save,
ToolbarItems.SaveAs,
ToolbarItems.Rename,
ToolbarItems.Remove,
ToolbarItems.Load,
ToolbarItems.Grid,
ToolbarItems.Chart,
ToolbarItems.Export,
ToolbarItems.SubTotal,
ToolbarItems.GrandTotal,
ToolbarItems.FieldList
};
protected override void OnInitialized()
{
this.DataSource = ProductDetails.GetProductData().ToList();
}
//To delete a report.
public void RemoveReport(RemoveReportArgs args)
{
RemoveReportFromDB(args.ReportName);
}
public void RemoveReportFromDB(string reportName)
{
SqlConnection sqlConn = OpenConnection();
SqlCommand cmd1 = null;
foreach (DataRow row in GetDataTable(sqlConn).Rows)
{
if ((row["ReportName"] as string).Equals(reportName))
{
cmd1 = new SqlCommand("DELETE FROM ReportTable WHERE ReportName LIKE '%" + reportName + "%'", sqlConn);
break;
}
}
cmd1.ExecuteNonQuery();
sqlConn.Close();
}
private SqlConnection OpenConnection()
{
// Use your connection string to access your own SQL database.
string connectionString = @"Data Source=(LocalDB)\ProjectModels;AttachDbFilename=" + Environment.CurrentDirectory + @"\App_Data\NORTHWND.mdf;Integrated Security=True";
SqlConnection sqlConn = new SqlConnection(connectionString);
sqlConn.Open();
return sqlConn;
}
private DataTable GetDataTable(SqlConnection sqlConn)
{
string xquery = "SELECT * FROM ReportTable";
SqlCommand cmd = new SqlCommand(xquery, sqlConn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
} Note: If the current report is removed from the Pivot Table, then the next report from the report list will be automatically loaded into the Pivot Table.
Note: If there is only one report in the Pivot Table report list and it is removed, then the Pivot Table will display the removed report’s state until a new report is added to it.
Use the Create a new report option in the toolbar to add a new report in the Pivot Table.
Selecting the Create a new report icon will display the following dialog in which you can provide name for your new report (for example, Garment Report).
Once you specify a new report name, click OK. A new empty report will be loaded into the Pivot Table as shown in the following image.
Once you modify and update the report, you can then save, save as, rename, load, and remove the current report as mentioned in the previous sections of this blog.
Keep the following points in mind when manipulating reports in the Blazor Pivot Table and storing them in a SQL Server database:
While retrieving reports, all the reports will be displayed regardless of the data source model in which it was saved. However, you should load the report based on the data source model that is currently bound to the Blazor Pivot Table.
For more details, refer to the project Save and load report from SQL Sever database to Blazor Pivot Table on GitHub.
Thanks for reading! In this blog, we learned how to easily connect to a SQL Server database to save reports and load them into the Syncfusion Blazor Pivot Table. Try out the process and leave your feedback in the comments section below!
Try our Blazor components by downloading a free 30-day trial to explore their features.
You can also contact us through our support forum, support portal, or feedback portal. We are always happy to assist you!