iulianchira
asked on
How to create SQL Server database programmatically, including stored procedures.
Part of the deployment scenario of my application (using an installer) I want to programmatically create the database. I'm a beginner at this, so I have several issues ...
First of all, should I put all the code related to the database in an SQL Server Project and reference the resulting DLL in my setup project, or should I simply put the code in a separate class?
Secondly I use stored procedures for any database operation and the project is quite complex so I have around 40 stored procedures. How can I generate them on the new database? I know how i can programmatically create a stored procedure ... but is there a faster, simpler way to do it for my scenario. I'm not that enthusiastic creating them one by one in the classic SMO way, not to mention that some have many parameters.
I'm using SQL Server 2005 and .Net.
First of all, should I put all the code related to the database in an SQL Server Project and reference the resulting DLL in my setup project, or should I simply put the code in a separate class?
Secondly I use stored procedures for any database operation and the project is quite complex so I have around 40 stored procedures. How can I generate them on the new database? I know how i can programmatically create a stored procedure ... but is there a faster, simpler way to do it for my scenario. I'm not that enthusiastic creating them one by one in the classic SMO way, not to mention that some have many parameters.
I'm using SQL Server 2005 and .Net.
bashka_abdyli
Script your database object thru the SQL Management Studio. It will produce scripts that will create all the objects.
In SQL Studio Management, right click a database go to TASKS - > Generate Scripts.
ASKER
I figured it out.
Can you close this question please?
Ok, sorry!
ASKER
I have scripted the database and used the Scrip database create option so when I run the script everything is ok. I have one more question though ... how could I pass the name of the database as a parameter?
You can create database using following script, name of database in this case is [BA] so you can change it, and after creating the database you can the execute the script that contains info to create tables, sp, views ect.
Hope this helps,
Hope this helps,
CREATE DATABASE [BA] ON PRIMARY
( NAME = N'BA', FILENAME = N'D:\Projektet\Bank Accounting .NET\Database\BA.mdf' , SIZE = 14336KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'BA_log', FILENAME = N'D:\Projektet\Bank Accounting .NET\Database\BA_log.ldf' , SIZE = 3456KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
ASKER
I know to do it in this way. Now i have just one script that I embed as a resource in my assembly. I read it using a stream reader then i execute it as a non-query. I'm looking for a more convenient way. I want to have a single script and be able to pass to the script the name as a parameter.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.