How to add custom MSSQL datasource to show all stored procedures from database

[muhammadhunainnasir]

Hi.

I want to add datasource by code which I am able to do and it shows like below. But its not showing any tables or any stored proc from the database.

I am using below code to add datasource from code:

Code: Select all

public void LoadSubMenuDataSource(StiReport report)
{
    if (report.Dictionary.Databases.Contains("MoosaDatabase")) return;

    var sqlDatabase = new StiSqlDatabase("MoosaDatabase", _connectionString);
    report.Dictionary.Databases.Add(sqlDatabase);

    var dataSource = new StiSqlSource(
        "MoosaDatabase",
        "SubMenuDataSource", 
        "SubMenu", 
        "SELECT * FROM SubMenu" 
    );

    report.Dictionary.DataSources.Add(dataSource);
}

If I write query then it only shows that but I have 100's of tables and I can't write queries for each table. Is there a way to include all tables and/or stored procedures to the dictionary.

[muhammadhunainnasir]

My primary target to show stored procedures from database that starts with rpt_% and only show those stored procedures and no tables shown in designer datasource.

[Max Shamanov]

Hello,

You can try to use the following code:

Code: Select all

 var sqlDatabase = new StiSqlDatabase("MoosaDatabase", _connectionString);
    sqlDatabase.Synchronize(report);
    report.Dictionary.Databases.Add(sqlDatabase);
    

Thank you.

[muhammadhunainnasir]

Can I only show stored procedures from mssql database and not tables for user to pick and use in reports ?

[Max Shamanov]

Hello,

Please try to use the following code:

Code: Select all

 var dataSource = new StiSqlDatabase("Test", "SubMenuDataSource", "test");

 var test = dataSource.GetDatabaseInformation(report);
 test.Tables.Clear();
 test.Views.Clear();
 test.Relations.Clear();

 dataSource.ApplyDatabaseInformation(zxc, report);

 report.Dictionary.Databases.Add(dataSource);

Thank you.