Member Avatar for blueshiftdani

I want to deploy a database application in C#. I should write a script for creating a database and its tables.
The script should work only at install time and setup the database automatically. Or tell me if there is another better way to do that.
Actually I wanted to deploy this application on another PC. but connection string and login permissions to the database are causing problem.
What I have tried is:
1. Created a database using SQL management studio.
2. copied database file to Client PC.
But that did not work.
Then I tried adding a new database project to my solution. That also did not work.
The application runs fine on my own PC where I have developed this project.

Regards

Edited by blueshiftdani because: I wanted to add more details
  • 2 Contributors
  • 1 Reply
  • 424 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by djjeavons
Member Avatar for djjeavons

Hi

If this is SQL Server then there are a number of options. One would be to generate a script from SQL Server Management Studio. You would have to do this in a number of steps:

  1. Script to create the actual database. Right click on the database and select "Script Database As" > "Create To" and then select a file or clipboard.
  2. Then you need to script each of your tables. The easiest way is to ensure that you have the Object Explorer Details view open (accessible via the View menu) and then select all tables and right click and select "Script Table As" and again select "Create To" file or clipboard.
  3. Then repeat 2 for all required stored procedures and views.
  4. Finally, all of this should be placed into one single file and tested to ensure that you have the order of creation in the right places as views and stored procedures might have dependencies that need to be created first.
  5. Once you have the script ready, you can execute it against your target SQL Server database. But you will need to get the user to provide the necessary user name and password or use Windows Authentication when building the connection string.

An alternative would be to use SMO (SQL Server Management Objects) which is a powerful library that allows you to pretty much fully manage SQL Server. With this, you could either execute scripts or more aptly, restore a backup of your database (that you might include in your installation package) to the target SQL Server. In fact, this might be the easiest option. There is an example of backing up and restoring using SMO on MSDN.

Or tell me if there is another better way to do that.

If you go with the SMO route, you could have some code that runs on startup of your application that determines if you have a connection string (i.e. the process has already been run) and if not, present the user with a dialog whereby they can choose the SQL Server instance (available via SMO) and provide credentials. Upon successful connnection, save these details to a configuration file for later use. Then run the script or restore the database. The next time the program is run, you will have a connection string and therefore this setup piece will not need to be executed.

HTH

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.