How to render Pivot component from SQL Database using VB in ASP.NET MVC?
This KB document explains how to render Pivot component in ASP.NET MVC from SQL Database using VB.
Solution:
You can render Pivot components (in relational server mode) in VB by using DataTable as DataSource. You can also perform the operations supported by respective Pivot controls. The following code samples demonstrate this.
Step 1
In Relational WebAPI controller, add the following class. Using the GetSQLResult method, you can get the data from local database.
''' Local Database Connection Public Class DbaseQuery Public Function GetSQLResult() Dim conSTR As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=" & AppDomain.CurrentDomain.BaseDirectory & "App_Data\Database1.mdf;Integrated Security=True" Dim xquery As String = "SELECT * FROM table1" Dim sqlConn As SqlConnection = New SqlConnection(conSTR) sqlConn.Open() Dim cmd As SqlCommand = New SqlCommand(xquery, sqlConn) Dim da As SqlDataAdapter = New SqlDataAdapter(cmd) Dim dt As DataTable = New DataTable() da.Fill(dt) For i = 0 To dt.Rows.Count - 1 For j = 0 To dt.Rows(i).ItemArray.Length - 1 dt.Rows(i).ItemArray(j) = dt.Rows(i).ItemArray(j).ToString().Trim() Next Next Return dt End Function End Class
Step 2
Add the following namespace.
Imports System.Data.SqlClient
Step 3
Pass the above DataTable as parameter, which is returned from the GetSQLResult method, in every action like InitializeClient, FetchMembers, nodeDropped, etc., of respective Pivot component.
Public Class RelationalGridController
Inherits ApiController
Dim dbquery As DbaseQuery = New DbaseQuery()
<System.Web.Http.ActionName("InitializeGrid")> _
<System.Web.Http.HttpPost> _
Public Function InitializeGrid(jsonResult As Dictionary(Of String, Object)) As Dictionary(Of String, Object)
//…
dict = htmlHelper.GetJsonData(jsonResult("action").ToString(), dbquery.GetSQLResult())
Return dict
End Function
<System.Web.Http.ActionName("FetchMembers")> _
<System.Web.Http.HttpPost> _
Public Function FetchMembers(jsonResult As Dictionary(Of String, Object)) As Dictionary(Of String, Object)
//…
dict = htmlHelper.GetJsonData(jsonResult("action").ToString(), dbquery.GetSQLResult(), jsonResult("headerTag").ToString(), jsonResult("sortedHeaders").ToString())
Return dict
End Function
//….
I hope you enjoyed learning about how to render Pivot component from SQL Database using VB in MVC.
You can refer to our ASP.NET MVC Pivot Table's feature tour page to know about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. You can also explore our Pivot Table example to understand how to create and manipulate data.
For current customers, you can check out our ASP.NET MVC Components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls.
If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!
