How to install and setup a database with .NET?

Question

I am currently working on a project that include the use of SQLServer. I would like to know what strategy I should use when I install the software to build the database? I need to set up the tables, the stored procedures and the users.

Does my software need to make a check on start up to see if the database exist and then if it doesn't, create it up? Is there any way that I could automate this when I install SQLServer?

Thank you.

EDIT Ok right now I have plenty of nice solution, but I am really looking for a solution (free or open source would be awesome) that would allow me to deploy a new application that needs SQLServer to be freshly installed and setuped to the needs of the software.

Answer 1

You do all of that with the SQL scripts. And then your installation program runs them against the customer's SQL Server.

Answer 2

You can write a T-SQL script that only creates objects when they do not exist. For a database:

if db_id('dbname') is null
    create database dbname

For a stored procedure (see MSDN for a list of object types):

if object_id('spname', 'P') is null
    exec ('create procedure dbo.spname as select 1')
go
alter procedure dbo.spname
as
<procedure definition>

The good thing about such scripts is that running them multiple times doesn't create a problem- all the objects will already exist, so the script won't do anything a second time.

Answer 3

RedGate software offers SQL Packager which gives you option to produce a script/.exe to deploy whole db including everything (stored procedures, tables etc) from one single .exe. If you can afford it and want to have easy way to do it (without having to do it yourself) it's the way to go ;-)

• Easy roll-out of database updates across your client base
• Script and compress your schema and data accurately and quickly
• Package any pre-existing SQL script as a .exe, or launch as a C# project
• Simplify deployments and updates for SQL Server 2000, 2005 and 2008

Answer 4

You could use migration framework like Migrator.Net, then you could run the migrations every time your application starts. The good thing about this approach is that you can update your database when you release a new version of your software.

Go take a look at their Getting started page. This might clear up the concept.

I have succesfully used this approach to solve the problem you are confronted with.

Answer 5

Setting up the server is pretty straight forward if you're using MS SQL Server. As for creating the database and tables, you generally only do this once. The whole point of a database is that the data is persistent, so if there's a chance that the database won't exist you've either got a) major stability problems, or b) no need for an actual database.

Designing the database, tables, and procedures is an entire component of the software development process. When I do this I usually keep all of my creation scripts in source control. After creation you will write the program in such a way that it assumes the database already exists - checking for connectivity is one thing, but the program should never think that there is no database at all.

Answer 6

you can make a script from all of objects that exist in your db. after that you can run this script from your code.

when you create your db script with script wizard in sql server, in "choose script options" section, set "Include If Not Exist" to yes. with this work done only if db not exists.