CODEWITHSUNDEEP
sql-serverdatabasevisual-studiordlcreportviewer
Wabbage
Wabbage

Reputation: 437

Visual Studio: Using ReportViewer with a user-generated database file (datasource)

My program allows users to create local SQL Server database files (.mdf) to store huge amount of generated data. Now, I want to create reports which lets the user select a database that the program generated for them. This means that I can't attach a datasource to my application to create RDLC's. However, all the databases have the same schema coming from some "master" database I created for the program.

How can I create reports using ReportViewer with dynamically generated database files?

I'm thinking of just copying the data from the generated database to my "master" database using queries, generating a report, and truncating the master database after, so that I can create RDLC's using the master database. However, that seems inefficient and messy. Is there a better way to do this?

Thanks.

Upvotes: 0

Views: 119

Answers (2)

Wabbage
Wabbage

Reputation: 437

This is what worked for me:

First, update the connection string from the "master" database's to point to the database you want to access like what dbamex suggested. For example, if you have this connection string from your app.config:

<add name="MyConnectionString" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Resources\SSA.mdf;Integrated Security=True;Connect Timeout=30"

Then you can add something like this in your code that you can call when you want to access the user-generated database:

Dim config As Configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
config.ConnectionStrings.ConnectionStrings("MyConnectionString").ConnectionString = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=" & new_db_path & ";Integrated Security=True;Connect Timeout=30"
config.Save(ConfigurationSaveMode.Modified)

So any dataset in your app that uses the connection string will be updated to point to the new one. Hope this helps.

Upvotes: 0

dbamex
dbamex

Reputation: 211

Create the datasource to point directly to your master database, then create a parameter in the report, and ask for it before running the "actual" report. This parameter could be a combo or checkbox which will have as values the data generated from a query where you get the database names.

Finally on your report use the parameter as variable and then select from the DB that you really need, like :

Select table from mydatabase.schema.table

Upvotes: 1

Related Questions