Category / Section
How to use Visual Basic to render Pivot components from a SQL database?
1 min read
This KB document explains how to render Pivot component 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
//….
