synchronized fusionBlazor turntableis a powerful control used to organize and summarize business data and display the result in a crosstab format. Its rich feature set includes data linking, drilling up and down, Excel-like filtering and sorting, editing, and more. Large amounts of dynamic data can be loaded without any performance degradation by using row and column virtualization.
servidor SQLis a relational database management system. It can be used to store and manage a large amount of data.
In this blog we will see how to save and load reports from a SQL Server database usingMicrosoft SQL Clientand load them into the Syncfusion Blazor pivot table at runtime.
Begin
To save a report to a SQL Server database, you must first create a table in the database. Then you can use SQL commands likeINSERT,UPDATE, yFUSIONto complete or change the table in the report. To load a report from a SQL Server database, you can useCHOOSEcommand to retrieve the table report and then assign it to the Blazor pivot table and update it as needed.
When saving a report to SQL Server, it's best to save it in a denormalized (ie, serialized) format so that settings within the report are kept private and loaded back into the PivotTable. Our Blazor pivot table follows the same procedure for saving and loading a SQL Server report.
Typically, a report contains settings such as rows, columns, values, filters, sorting, filtering, grouping, formatting, and calculated fields that are used to render the PivotTable. It also stores current user interface information related to the table and graph user interfaces.
Install the Microsoft.Data.SqlClientNuGet package
To connect to a SQL Server database using theMicrosoft SQL Clientin your app you must installMicrosoft.Data.SqlClientNuGet package. To do this, you must openeye studioand then selectOption for NuGet Package ManagerofToolsthe board. Find the packageMicrosoft.Data.SqlClientand install it.
Syncfusion Blazor Pivot Tables Toolbar
Use:If you are new to Syncfusion Blazor Pivot Table, check outyour initial documentationBefore continuing.
HeSyncfusion Blazor turntablehave abuilt-in toolbarUser interface with predefined options for handling reports. With these options, you can easily perform operations such asSaving,save as,rename,charge,delete,yaddin reports dynamically at runtime.
Let's explore how to use these built-in toolbar actions.
save a report
Once the PivotTable UI loads, selectsave a reporton the toolbar to save the report settings to the SQL Server database.
When you clicksave a reportthe icon, thesave reportthe event will be triggered. This event will pass the current report name and the current report in serialized form to the SQL Server database.
The following code displays the report along with the report namesample report(namesample reportcomes 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 store them in the database.
@using System.Data@using Microsoft.Data.SqlClient;@using EFPivotTable.Data;/PivotViewRows> View PivotViewFormatSetting Name="Quantity" Format="C"> Product Details> PivotRef { get; place; } public list datasource { get; place; } public list PivotToolbar = new list { ToolbarItems.New, ToolbarItems.Save, ToolbarItems.SaveAs, ToolbarItems.Rename, ToolbarItems.Remove, ToolbarItems.Load, ToolbarItems.Grid, ToolbarItems.Chart. ToolbarItems.SubTotal, ToolbarItems.GrandTotal, ToolbarItems.FieldList }; beskyttet tilsidesættelse 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 = arena; SqlCommand cmd1 = nulo; foreach (DataRow række i GetDataTable(sqlConn).Rows) { if ((række["ReportName"] som streng).Equals(reportName)) { isDuplicate = false; cmd1 = new SqlCommand("OPDATERING Rapporttabelsæt Rapport=@Rapport hvor Rapportnavn som @Rapportnavn", sqlConn); } } if (isDuplicate) { cmd1 = new SqlCommand("INSERTAR en ReportTable (ReportName, Report) Values(@ReportName,@Report)", sqlConn); } cmd1.Parameters.AddWithValue("@ReportName", reportName); cmd1.Parameters.AddWithValue("@Report", report.ToString()); cmd1.ExecuteNonQuery(); sqlConn.Cerrar(); } private SqlConnection OpenConnection() {// Brug din forbindelsesstreng til at få adgang til din egen SQL-database. string connectionString = @"Datakilde=(LocalDB)\ProjectModels;AttachDbFilename=" + Environment.CurrentDirectory + @"\App_Data\NORTHWND.mdf;Integrated Security=True"; SqlConnection sqlConn = ny SqlConnection(cadena de conexión); sqlConn.Open(); returnere sqlConn; } Private DataTable GetDataTable(SqlConnection sqlConn) { string xquery = "SELECT * FROM ReportTable"; SqlCommand cmd = ny SqlCommand(xquery, sqlConn); SqlDataAdapter da = ny SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fyld(dt); volver dt; }}
See the following image.
Updating a saved report
Later you can change the current report, e.g. Rearrange the countries in descending order and expand the United States on the row axis. To save the modified report in the same database with the same report name (Sample Report), just click onsave a reporticon on the toolbar again, which will activatesave reportevent. This event will pass the name of the current report (Sample Report) and the modified report in serialized form to be stored in the same database.
See the following image.
Save a copy of a report with a different name
To save a copy of the current report to the database under a different name, useSave as current reportoption on the UI toolbar.
See the following image.
When you clickSave as current reportA dialog box appears where you can give the same report a new name.In ourExamplewe give the nameReport Example 2.
After entering the report name, clickBuenoto close the dialog. now the onesave reportthe event will fire and save the report in serialized form with the new report nameReport Example 2i SQL Server database.
Use:The code samples for saving, saving a modified report, and saving a copy of the current report are all the same.
Loading a report
The toolbar dropdown menu will automatically load with a list of previously saved reports that were retrieved from the SQL database usingget reportevent. This event is always fired to retrieve the latest report list of SQL data when a report is saved, updated, deleted, or added to the Blazor PivotTable.
See the following code example.
@using System.Data@using Microsoft.Data.SqlClient;@using EFPivotTable.Data;@code { SfPivotView PivotViewColumn Name="Year"> name=" PivotReport="FetchReport="FetchReport="FetchReport="FetchReport" > PivotRef { get; place; } public list datasource { get; place; } public list PivotToolbar = new list { ToolbarItems.New, ToolbarItems.Save, ToolbarItems.SaveAs, ToolbarItems.Rename, ToolbarItems.Remove, ToolbarItems.Load, ToolbarItems.Grid, ToolbarItems.Chart. ToolbarItems.SubTotal, ToolbarItems.GrandTotal, ToolbarItems.FieldList }; Protected tilsidesettlement void OnInitialized() { this.DataSource = ProductDetails.GetProductData().ToList(); } public void SaveReport(SaveReportArgs args) { SaveReportToDB(args.ReportName, args.Report); } // Para obtener los informes de la base de datos SQL. public void FetchReport(FetchReportArgs args) { args.ReportName = FetchReportListFromDB().ToArray(); PublicList FetchReportListFromDB() { SqlConnection sqlConn = OpenConnection(); Lista reportNames = new list (); foreach (DataRow-rækken i GetDataTable(sqlConn).Rows) { if (!string.IsNullOrEmpty(row["ReportName"] as string)) { reportNames.Add(row["ReportName"].ToString()); } } sqlConn.Close(); returnere rapportNavne; } // Por lo tanto, vale la pena relacionar la base de datos de SQL y la derivación de la base de datos hasta la pivotación. Public async Task LoadReport(LoadReportArgs args) { espera esto.PivotRef.LoadPersistDataAsync(LoadReportFromDB(args.ReportName).ToString()); } objeto público LoadReportFromDB(string reportName) { SqlConnection sqlConn = OpenConnection(); informe de cadena = cadena.Vacío; foreach (Fila DataRow en GetDataTable(sqlConn).Rows) { if ((row["ReportName"] as string).Equals(reportName)) { report = (string)row["Report"]; pausa; } } sqlConn.Close(); relación de retorno; } private SqlConnection OpenConnection() {// Brug din forbindelsesstreng til at få adgang til din egen SQL-database. string connectionString = @"Datakilde=(LocalDB)\ProjectModels;AttachDbFilename=" + Environment.CurrentDirectory + @"\App_Data\NORTHWND.mdf;Integrated Security=True"; SqlConnection sqlConn = ny SqlConnection(cadena de conexión); sqlConn.Open(); returnere sqlConn; } Private DataTable GetDataTable(SqlConnection sqlConn) { string xquery = "SELECT * FROM ReportTable"; SqlCommand cmd = ny SqlCommand(xquery, sqlConn); SqlDataAdapter da = ny SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fyld(dt); volver dt; }
Select a report from the dropdown menu, e.g.Report Example 2.
now the oneload reportthe event will be fired with the name of the selected reportReport Example 2. This event will fetch and retrieve the report from the SQL database and load it into the Blazor pivot table.
Rename a report
You can useRename a current reporton the toolbar to rename the current report and save it back to the SQL database.
As shown in the image above, we will change the name of the current report,Report Example 2. To do so, clickRename a current reporticon. A dialog appears where you can give the current report a new name(f.eks. Global Trends Report).
If you specify a report name that is already in the report list, the Blazor PivotTable displays the following warning. You can choose if you want to replace this report.
Suppose you enter a report name that is not already in the list of reports. In that case, click to close the dialog box.Buenobutton andname change reportthe event is triggered. This event passes the name of the current reportReport Example 2and the name of the modified report(Global Trends Report). Based on this information, the report name will be updated in the database.
See the following code example.
@using System.Data@using Microsoft.Data.SqlClient;@using EFPivotTable.Data;PivotViewColumn Name="Year"> name=" PivotRef { get; place; } public list datasource { get; place; } public list PivotToolbar = new list { ToolbarItems.New, ToolbarItems.Save, ToolbarItems.SaveAs, ToolbarItems.Rename, ToolbarItems.Remove, ToolbarItems.Load, ToolbarItems.Grid, ToolbarItems.Chart. ToolbarItems.SubTotal, ToolbarItems.GrandTotal, ToolbarItems.FieldList }; beskyttet tilsidesættelse void OnInitialized() { this.DataSource = ProductDetails.GetProductData().ToList(); } // Para en omdøbe en rapport. public void RenameReport(RenameReportArgs args) { RenameReportInDB(args.ReportName, args.Rename); } public void RenameReportInDB(string reportName, string renameReport) { SqlConnection sqlConn = OpenConnection(); SqlCommand cmd1 = nulo; foreach (Fila DataRow en GetDataTable(sqlConn).Rows) { if ((row["ReportName"] as string).Equals(reportName)) { cmd1 = new SqlCommand("ACTUALIZAR ReportTable set ReportName=@RenameReport where ReportName like '% " + nombreinforme + "%'", sqlConn); pausa; } } cmd1.Parameters.AddWithValue("@RenameReport", renameReport); cmd1.ExecuteNonQuery(); sqlConn.Cerrar(); } private SqlConnection OpenConnection() {// Brug din forbindelsesstreng til at få adgang til din egen SQL-database. string connectionString = @"Datakilde=(LocalDB)\ProjectModels;AttachDbFilename=" + Environment.CurrentDirectory + @"\App_Data\NORTHWND.mdf;Integrated Security=True"; SqlConnection sqlConn = ny SqlConnection(cadena de conexión); sqlConn.Open(); returnere sqlConn; } Private DataTable GetDataTable(SqlConnection sqlConn) { string xquery = "SELECT * FROM ReportTable"; SqlCommand cmd = ny SqlCommand(xquery, sqlConn); SqlDataAdapter da = ny SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fyld(dt); volver dt; }}
Delete a report
You can easily delete a report from pivot table and SQL database usingDelete a current reporticon on the toolbar. Just find and delete the report using the report name. In this example, we will delete the one that is currently loaded.List of global trends.
When you clickDelete a current reporticon, the following warning dialog is displayed withYyNooptions to delete the current report.
When choosingY, thatdelete reportthe event will be fired with the name of the selected report(Global Trends Report). This will automatically remove the report from the pivot table.
See the following code example.
@using System.Data@using Microsoft.Data.SqlClient;@using EFPivotTable.Data;PivotViewColumn Name="Year"> name=" <> @code { SfPivotView PivotRef { get; place; } public list datasource { get; place; } public list PivotToolbar = new list { ToolbarItems.New, ToolbarItems.Save, ToolbarItems.SaveAs, ToolbarItems.Rename, ToolbarItems.Remove, ToolbarItems.Load, ToolbarItems.Grid, ToolbarItems.Chart. ToolbarItems.SubTotal, ToolbarItems.GrandTotal, ToolbarItems.FieldList }; beskyttet tilsidesættelse void OnInitialized() { this.DataSource = ProductDetails.GetProductData().ToList(); } //Para slette en rapport. public void RemoveReport(RemoveReportArgs args) { RemoveReportFromDB(args.ReportName); } public void RemoveReportFromDB(string reportName) { SqlConnection sqlConn = OpenConnection(); SqlCommand cmd1 = nulo; foreach (Fila DataRow en GetDataTable(sqlConn).Rows) { if ((row["ReportName"] as string).Equals(reportName)) { cmd1 = new SqlCommand("ELIMINAR DE ReportTable DONDE ReportName LIKE '%" + reportName + "%'", sqlConn); pausa; } } cmd1.ExecuteNonQuery(); sqlConn.Cerrar(); } private SqlConnection OpenConnection() {// Brug din forbindelsesstreng til at få adgang til din egen SQL-database. string connectionString = @"Datakilde=(LocalDB)\ProjectModels;AttachDbFilename=" + Environment.CurrentDirectory + @"\App_Data\NORTHWND.mdf;Integrated Security=True"; SqlConnection sqlConn = ny SqlConnection(cadena de conexión); sqlConn.Open(); returnere sqlConn; } Private DataTable GetDataTable(SqlConnection sqlConn) { string xquery = "SELECT * FROM ReportTable"; SqlCommand cmd = ny SqlCommand(xquery, sqlConn); SqlDataAdapter da = ny SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fyld(dt); volver dt; }}
Use:If the current report is removed from the PivotTable, the next report in the report list will be automatically loaded into the PivotTable.
Use:If there is only one report in the PivotTable's report list and it is deleted, the PivotTable will display the status of the deleted report until a new report is added to it.
Add a report
To useCreate a new reporton the toolbar to add a new report in the pivot table.
choice ofCreate a new reportThe icon displays the following dialog where you can enter a name for your new report(eg garment report).
After entering a new report name, clickBueno. A new blank report will be loaded into the pivot table as shown in the following image.
Once you have modified and updated the report, you can save, save as, rename, load, and delete the current report as mentioned in the previous sections of this blog.
Points to remember
Keep the following points in mind when manipulating reports in the Blazor Pivot Table and saving them to a SQL Server database:
- Data source:Both raw data and aggregated data will not be stored or loaded from the database.
- Conditional format:PivotTable appearance properties such as background color, font color, font family, and font size, depending on specific conditions, will not be saved or loaded from the database.
- hyperlinks:Links to external resources in pivot table cells will not be saved or loaded from the database.
When retrieving reports, all reports will be displayed regardless of the data source model they were saved to. However, you must load the report based on the data source model currently linked to the Blazor PivotTable.
GitHub Reference
For more details, see the projectSave and load report from SQL Server database to Blazor pivot tablea GitHub.
Conclusion
Thank you for reading! In this blog we learned how to easily connect to a SQL Server database to store reports and load them intoSyncfusion Blazor turntable. Give the process a try and give your feedback in the comments section below!
Try out our Blazor components by downloading a30 day free trial periodto explore its features.
You can also contact us through oursupport forum,support portal, ofeedback portal. We are always happy to help you!
Related Blogs
- File Preview in Blazor File Manager: A Complete Guide
- Configuring real-time updates in a Blazor WebAssembly app using SignalR [Webinar Show Notes]
- Create a PDF digital signature web service with Azure Key Vault and the Syncfusion C# PDF Library
- Column Wrapping: The Efficient Way to Arrange Columns in the Blazor DataGrid