Ticket T622683

Visible to All Users

Query Builder - Limit the list of available Stored Procedures, Tables, or Views

Ansarada TriLine GRC

created 7 years ago

Hello

We have given our customers the ability to create and modify their own Dashboards. Our data structure is quite complex and the customer can set who can see individual records . To overcome this we have created a number of Stored Procedures for use by the customers where we set the user credentials as the page is loaded. We have, with your assistance, removed the option for the customers to use the Query Builder feature when working with Data Sources. All of the necessary Stored Procedures are prefixed with 'Dashboard_'.

Is it possible to limit the list of available Stored Procedures in the Dashboard Data Source Wizard window?

Cheers
James

stored pro...edures.png

Answers approved by DevExpress Support

Sergi (DevExpress)

created 7 years ago (modified 7 years ago)

Hello James,

To achieve your goal, create a custom DB schema provider by implementing the IDBSchemaProviderEx interface and return only required stored procedures by overriding the GetProcedures method. Refer to the How to: Customize a Data Store Schema for SQL Data Sources example for more details. To assign your custom DB schema provider to Web Dashboard, call the ASPxDashboard.SetDBSchemaProvider method.

Update:
If you want only to hide certain stored procedures, you can inherit a custom provider from the base DBSchemaProviderEx class. Then, override the GetProcedures function and call the base function to get the default list of stored procedures. You will be able to filter out unnecessary stored procedures from the list.

C#
public class CustomDBSchemaProvider : DBSchemaProviderEx  
{  
    public override DBStoredProcedure[] GetProcedures(SqlDataConnection connection, params string[] procedureList)  
    {  
        var procedures = base.GetProcedures(connection, procedureList);  
        if (procedureList.Length == 0)  
            // procedureList is empty if schema is loaded by the query builder  
            return procedures.Take(3).ToArray();  
        else  
            // before data loading dashboard requests schema only for the used procedures  
            return procedures;  
    }  
}

Show previous comments (3)

Ansarada TriLine GRC 7 years ago

Thanks for that.

I implemented the class (without any changes) just to get it working:

Visual Basic
Private Class CustomDBSchemaProvider  
    Implements IDBSchemaProviderEx  

    Public Sub LoadColumns(connection As SqlDataConnection, ParamArray tables() As DBTable) Implements IDBSchemaProviderEx.LoadColumns  
        Throw New NotImplementedException()  
    End Sub  

    Public Function GetTables(connection As SqlDataConnection, ParamArray tableList() As String) As DBTable() Implements IDBSchemaProviderEx.GetTables  
        Throw New NotImplementedException()  
    End Function  

    Public Function GetViews(connection As SqlDataConnection, ParamArray viewList() As String) As DBTable() Implements IDBSchemaProviderEx.GetViews  
        Throw New NotImplementedException()  
    End Function  

    Public Function GetProcedures(connection As SqlDataConnection, ParamArray procedureList() As String) As DBStoredProcedure() Implements IDBSchemaProviderEx.GetProcedures  
        Dim storedProcedures(-1) As DBStoredProcedure  
        Return storedProcedures  
    End Function  
End Class

Then added this in the Load routine:

Visual Basic
cdb.SetDBSchemaProvider(New CustomDBSchemaProvider)

When the class is executed the connection parameter is blank and the procedurelist parameter only includes the Stored Procedure for the 'current' Dashboard. When the page is displayed, all of the elements show data errors.

How do I set the connection parameter and the complete list of required Stored Procedures?

Cheers
James

customDashboard.zip

DevExpress Support Team 7 years ago

Try to inherit your custom provider from the default one as shown below:

Visual Basic
Public Class CustomDBSchemaProvider  
    Inherits DBSchemaProviderEx  

    Public Overrides Function GetProcedures(ByVal connection As SqlDataConnection, ParamArray ByVal procedureList() As String) As DBStoredProcedure()  
        Dim procedures = MyBase.GetProcedures(connection, procedureList)  
        If procedureList.Length = 0 Then  
            'procedureList is empty if schema is loaded by the query builder  
            Return procedures.Take(3).ToArray()  
        Else  
            'before data loading dashboard requests schema only for the used procedures  
            Return procedures  
        End If  
    End Function  
End Class

Ansarada TriLine GRC 7 years ago

Thanks for that.

I need to be able to select all the Stored Procedures that are prefixed with 'Dashboard_". Some customers may have more than others so a fixed number will not work for us (while we have a one version policy for the application we want to be able to handle a variable number of Stored Procedures).

How can I select by Stored Procedure name? I see there is a 'Where' option (instead of Take) but I can't find any examples of how that works.

Cheers
James

stored pro...edures.png

Sergi (DevExpress) 7 years ago

Hello James,

The Take and Where methods are the standard LINQ methods that can be applied to a collection of objects. For instance, I suggest that you review the How do I append a 'where' clause using VB.NET and LINQ? article to learn how to use the Where method in VB.NET.

Ansarada TriLine GRC 7 years ago

Thanks for that. The LINQ perspective was not clear from the error message.
I modified the example as such:

Visual Basic
Private Class CustomDBSchemaProvider  

    Inherits DBSchemaProviderEx  

    Public Overrides Function GetProcedures(ByVal connection As SqlDataConnection, ParamArray ByVal procedureList() As String) As DBStoredProcedure()  
        Dim procedures = MyBase.GetProcedures(connection, procedureList)  
        If procedureList.Length = 0 Then  
            'procedureList is empty if schema is loaded by the query builder  
            Return procedures.Where(Function(d) d.Name.StartsWith("Dashboard_")).ToArray()  
        Else  
            'before data loading dashboard requests schema only for the used procedures  
            Return procedures  
        End If  
    End Function  
End Class

Cheers
James

Sergi (DevExpress) 7 years ago

I am happy to hear that the problem has been resolved. Thank you for letting us know of your progress.
Please feel free to contact us if you have any difficulties. We will be happy to assist you.

Created: April 4, 2018 11:30 PM

Modified: September 5, 2018 2:10 PM

Platform: Business Intelligence

Product: Dashboard for Web Forms

Build: 17.2.7

Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Query Builder - Limit the list of available Stored Procedures, Tables, or Views

Answers approved by DevExpress Support

Recently viewed tickets