How to create a SQL Server database programmatical

Question

I want to create a program, that will use SQL Server 2008 database. During the first start, I would like to open a window, which let the user create a database. It will be simply, textboxes with name and ip of the database computer to set and button "Go".

Program will be in WPF .NET4. And database will be in local network.

Could you suggest me a good solution? Is it a good programming practice, to do that? Or maybe I should just attached a sql script?

I do some research, I found that article: http://www.codeproject.com/KB/tips/CreateSQLDV.aspx

But, first issue, in SQL Server 2008, there is no Microsoft.SqlServer.SmoEnum.dll. So, when I do similar "data create" window, but for SQL Server 2008 (using maybe different dlls) - It will not work for SQL Server 2005. And maybe will not work with other versions of SQL Server 2008 to? I don't know.. Example from codeproject looks good, but I'm not sure.

I would like to do a flexible program.

Answer 1

I would avoid SMO.

It depends a LOT on your audience and the control you have over the expected environment, but attaching pre-made databases, while a convenient option, can sometimes have issues - to start with it's a binary under source control, so you don't get diffs for free in your source control system. In addition, you're attaching a database with certain options and things which might not be appropriate for the specific target environment - SQL Server 2005, SQL Server 2008, SQL Server 2008 R2? Other than all that, it's a valid approach similar to the way one might deploy Access applications in the past.

In a less controlled environment, I would go with either generating a SQL script containing all the DDL (and DML for lookup tables) or providing a script, offering to run it automatically and also giving them the option of running it themselves with their own tools (if they have a DBA).

Now your script (or at least the template for the script or the code that generates the script) is under source control and can satisfy a DBA who wants to inspect it.

Answer 2

I would recommend not to actually programmatically create the database. As you mentioned - with different versions of SMO, this becomes a bit of a nightmare.

My approach would be this:

• with your installation, ship a "default" empty database that has your base structure (all your tables and everything) and possibly also some basic lookup data in certain tables

• when the user indicates he doesn't have an existing database for your application, copy the MDF/LDF/and possibly NDF files to the SQL Server data location

• attach those database files programmatically to the SQL Server instance

That seems a nice cleaner and more flexible approach.

Answer 3

The database creation may not need to be a part of your code per se. Especially, if you only need to create the database once. I suggest an approach on which you create an installer either by using Windows Installer or Inno Setup (I prefer Inno Setup). With an installer you can prompt the user for their SQL server name and the login credentials for their administrative user. Then you can use those to run a SQL script containing your CREATE DATABASE and CREATE TABLE statements, etcetera. Hope this helps.