Report with Direct Database Connection

[Sandy Pham]

Most of my users will be using DataSets for their reports. However, I also want to allow them to set up a direct database connection and create a report from that. I thought I did everything I needed to do, but my DataSource does not appear in the Dictionary tree.

Here's my code:

private void Test(ReportDef defReport)
{
SqlConnection conn = null;

try
{
conn = new SqlConnection();
conn.ConnectionString = "Data Source=SANDYPHAM\\SQLSVR2005;Initial Catalog=Chris;Integrated Security=True;";
conn.Open();

StiReport report = new StiReport();
report.RegData("MyDatasource", conn);
report.Dictionary.Synchronize();
report.Design();
}
finally
{
if (conn != null)
conn.Close();
}
}

I checked in the debugger and I see report.Dictionary.DataStore.Items[0].Data set to my SqlConnection.

What do I need to do to get my database tables to appear in the Dictionary tree view?

Thanks,

Sandy

[Brendan]

Hi Sandy,

The following snippet of code should let your Register an SqlConnection to a Report:

Code: Select all

Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport();
Stimulsoft.Report.Dictionary.StiSqlDatabase myDB = new Stimulsoft.Report.Dictionary.StiSqlDatabase("MyDatabase", @"Data Source=SANDYPHAM\SQLSVR2005;Initial Catalog=Chris;Integrated Security=True;");

report.Dictionary.Databases.Add(myDB);
report.Design();

[Sandy Pham]

Hi Brendan,

That worked (thank you!!!). Now, I'm wondering how I can get a list of tables or even fields to appear. I tried right clicking and adding a SQL query, thinking that a field list would appear under my database. But, it didn't do anything.

I would like all of the tables in the database to be displayed, but I think I remember asking that question before and you can't do that.

So, I guess I would like to be able to set a SQL select statement and have a field list appear.

Any ideas,

Sandy

[jing]

You need to add datasource to your database to get tables.
You also may need to write a method to retrieve fields from a table manually. (If you only have sources and databases, you will only see tables, but no fields)

hope this helps - it took me a long time to figure out how to do this

[Edward]

To retrieve columns from the StiDataSource please do the following:

Code: Select all

StiReport report = new StiReport();
StiSqlDatabase mySqlDatabase = new StiSqlDatabase("MyStiSqlDatabase","Data Source=EDWARD;Initial Catalog=Northwind;Integrated Security=True");
report.Dictionary.Databases.Add(mySqlDatabase);
StiSqlSource mySQLDataSource = new StiSqlSource("MyStiSqlDatabase", "MySQLSource", "MySQLSource", "SELECT * FROM Categories");
report.Dictionary.DataSources.Add(mySQLDataSource);
report.Dictionary.Synchronize();
mySQLDataSource.SynchronizeColumns();

Now we do not have the ability of retrieving all DataTables from the SQL and OleDB databases in the automatic mode. But we have added this to our to-do list.

We will inform you in this topic when the solution will be ready.

Thank you.

[Sandy Pham]

I have exactly what I want now. Thank you!!

For those that prefer not to see both the Databases and Datasources tree nodes in the Dictionary window, I discovered that you can use report.RegData("name", IDbConnection) instead of report.Dictionary.Databases.Add(StiSqlDatabase). Then, you only see the DataSources node with your tables underneath.

Here's the code:

StiReport report = new StiReport();
report.RegData("MyStiSqlDatabase", dbConn);
StiSqlSource mySQLDataSource = new StiSqlSource("MyStiSqlDatabase", "MySQLSource", "MySQLSource", "SELECT * FROM Categories");
report.Dictionary.DataSources.Add(mySQLDataSource);
report.Dictionary.Synchronize();
mySQLDataSource.SynchronizeColumns();

[jing]

To retrieve columns from the StiDataSource please do the following:

Code: Select all

report.Dictionary.Synchronize();
mySQLDataSource.SynchronizeColumns();

This only works if the SQL query does not contain a variable.... I had

Code: Select all

Dim sqlCommand As String = "SELECT stockcode, description, bincode, barcode1, barcode2, barcode3, " & sellPriceString & ", '" & gstMsg & "' as GST_Name FROM stock_items WHERE stockcode IN ({StockCode}) ORDER BY description"

Dim source As New Dictionary.StiSqlSource("Exonet", "StockItems", "StockItems", sqlCommand, False, False)

and when I use SynchronizeColumns, it says it has incorrect syntax near }.... works fine with a simple SQL query though

thanks

[Vital]

That correct because report engine don't know what it must do with {StockCode}. In your case you use variable, but you can write and
something like {StockCode1 + "123" + StockCode2}.

Thank you.