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.
sql-server database visual-studio rdlc reportviewer asked Nov 14 '18 at 19:16
PuggyLongLegs
1612212
add a comment |
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.
sql-server database visual-studio rdlc reportviewer asked Nov 14 '18 at 19:16
PuggyLongLegs
1612212
add a comment |
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.
sql-server database visual-studio rdlc reportviewer asked Nov 14 '18 at 19:16
PuggyLongLegs
1612212
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.
sql-server database visual-studio rdlc reportviewer sql-server database visual-studio rdlc reportviewer asked Nov 14 '18 at 19:16
PuggyLongLegs
1612212
asked Nov 14 '18 at 19:16
PuggyLongLegs
1612212
edited Nov 15 '18 at 19:12
asked Nov 14 '18 at 19:16
PuggyLongLegs
1612212
asked Nov 14 '18 at 19:16
PuggyLongLegs
1612212
asked Nov 14 '18 at 19:16
PuggyLongLegs
1612212
1612212
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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
answered Nov 15 '18 at 0:12
dbamex
514
Thank you. I really appreciate your response. Would you be able to add more details? For example, what do you mean by pointing the datasource to my master database?
– PuggyLongLegs
Nov 15 '18 at 19:00
1
I don't have VS installed currently, so will go by memory. First create a parameter, there's an option to get values from a query, on it create the query as "select dbname from sysdatabases" Then for your datasource point to your server and use master database, In the query you should specify "Select table from @parameter_name.dbo.table" Hope this helps.
– dbamex
Nov 15 '18 at 19:10
Sorry, would you be able to explain what the parameter is? Like, what do you mean by "create a parameter and ask for it before running the 'actual' report?" What exactly do I ask for?
– PuggyLongLegs
Nov 16 '18 at 14:58
add a comment |
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|ResourcesSSA.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.
answered Dec 31 '18 at 20:44
PuggyLongLegs
1612212
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53307314%2fvisual-studio-using-reportviewer-with-a-user-generated-database-file-datasourc%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Nov 15 '18 at 0:12
dbamex
514
Thank you. I really appreciate your response. Would you be able to add more details? For example, what do you mean by pointing the datasource to my master database?
– PuggyLongLegs
Nov 15 '18 at 19:00
1
I don't have VS installed currently, so will go by memory. First create a parameter, there's an option to get values from a query, on it create the query as "select dbname from sysdatabases" Then for your datasource point to your server and use master database, In the query you should specify "Select table from @parameter_name.dbo.table" Hope this helps.
– dbamex
Nov 15 '18 at 19:10
Sorry, would you be able to explain what the parameter is? Like, what do you mean by "create a parameter and ask for it before running the 'actual' report?" What exactly do I ask for?
– PuggyLongLegs
Nov 16 '18 at 14:58
add a comment |
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
answered Nov 15 '18 at 0:12
dbamex
514
Thank you. I really appreciate your response. Would you be able to add more details? For example, what do you mean by pointing the datasource to my master database?
– PuggyLongLegs
Nov 15 '18 at 19:00
1
I don't have VS installed currently, so will go by memory. First create a parameter, there's an option to get values from a query, on it create the query as "select dbname from sysdatabases" Then for your datasource point to your server and use master database, In the query you should specify "Select table from @parameter_name.dbo.table" Hope this helps.
– dbamex
Nov 15 '18 at 19:10
Sorry, would you be able to explain what the parameter is? Like, what do you mean by "create a parameter and ask for it before running the 'actual' report?" What exactly do I ask for?
– PuggyLongLegs
Nov 16 '18 at 14:58
add a comment |
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
answered Nov 15 '18 at 0:12
dbamex
514
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
answered Nov 15 '18 at 0:12
dbamex
514
answered Nov 15 '18 at 0:12
dbamex
514
answered Nov 15 '18 at 0:12
dbamex
514
answered Nov 15 '18 at 0:12
dbamex
514
514
Thank you. I really appreciate your response. Would you be able to add more details? For example, what do you mean by pointing the datasource to my master database?
– PuggyLongLegs
Nov 15 '18 at 19:00
1
I don't have VS installed currently, so will go by memory. First create a parameter, there's an option to get values from a query, on it create the query as "select dbname from sysdatabases" Then for your datasource point to your server and use master database, In the query you should specify "Select table from @parameter_name.dbo.table" Hope this helps.
– dbamex
Nov 15 '18 at 19:10
Sorry, would you be able to explain what the parameter is? Like, what do you mean by "create a parameter and ask for it before running the 'actual' report?" What exactly do I ask for?
– PuggyLongLegs
Nov 16 '18 at 14:58
add a comment |
Thank you. I really appreciate your response. Would you be able to add more details? For example, what do you mean by pointing the datasource to my master database?
– PuggyLongLegs
Nov 15 '18 at 19:00
1
I don't have VS installed currently, so will go by memory. First create a parameter, there's an option to get values from a query, on it create the query as "select dbname from sysdatabases" Then for your datasource point to your server and use master database, In the query you should specify "Select table from @parameter_name.dbo.table" Hope this helps.
– dbamex
Nov 15 '18 at 19:10
Sorry, would you be able to explain what the parameter is? Like, what do you mean by "create a parameter and ask for it before running the 'actual' report?" What exactly do I ask for?
– PuggyLongLegs
Nov 16 '18 at 14:58
Thank you. I really appreciate your response. Would you be able to add more details? For example, what do you mean by pointing the datasource to my master database?
– PuggyLongLegs
Nov 15 '18 at 19:00
Thank you. I really appreciate your response. Would you be able to add more details? For example, what do you mean by pointing the datasource to my master database?
– PuggyLongLegs
Nov 15 '18 at 19:00
1
1
I don't have VS installed currently, so will go by memory. First create a parameter, there's an option to get values from a query, on it create the query as "select dbname from sysdatabases" Then for your datasource point to your server and use master database, In the query you should specify "Select table from @parameter_name.dbo.table" Hope this helps.
– dbamex
Nov 15 '18 at 19:10
I don't have VS installed currently, so will go by memory. First create a parameter, there's an option to get values from a query, on it create the query as "select dbname from sysdatabases" Then for your datasource point to your server and use master database, In the query you should specify "Select table from @parameter_name.dbo.table" Hope this helps.
– dbamex
Nov 15 '18 at 19:10
Sorry, would you be able to explain what the parameter is? Like, what do you mean by "create a parameter and ask for it before running the 'actual' report?" What exactly do I ask for?
– PuggyLongLegs
Nov 16 '18 at 14:58
Sorry, would you be able to explain what the parameter is? Like, what do you mean by "create a parameter and ask for it before running the 'actual' report?" What exactly do I ask for?
– PuggyLongLegs
Nov 16 '18 at 14:58
add a comment |
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|ResourcesSSA.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.
answered Dec 31 '18 at 20:44
PuggyLongLegs
1612212
add a comment |
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|ResourcesSSA.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.
answered Dec 31 '18 at 20:44
PuggyLongLegs
1612212
add a comment |
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|ResourcesSSA.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.
answered Dec 31 '18 at 20:44
PuggyLongLegs
1612212
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|ResourcesSSA.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.
answered Dec 31 '18 at 20:44
PuggyLongLegs
1612212
answered Dec 31 '18 at 20:44
PuggyLongLegs
1612212
answered Dec 31 '18 at 20:44
PuggyLongLegs
1612212
answered Dec 31 '18 at 20:44
PuggyLongLegs
1612212
1612212
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53307314%2fvisual-studio-using-reportviewer-with-a-user-generated-database-file-datasourc%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
