Solved

Create Database Programatically in asp.net

Posted on 2009-06-28
3
400 Views
Last Modified: 2012-05-07
I need to be able to code a page for an asp.net website (using vb.net) that can a:) Create a database based on user input (name of db only) and b:) create it in my sites "App_Data" folder. I've been able to create a database using the code below, but it places it in the default SQL Server folder, rather than the App_Data folder. I am using VS 2008 and SQL Server 2005. So how can I arrange for the created database to be in my sites "App_Data" folder and attach it to the project.
dim c as sqlconnection

dim com as sqlcommand

dim w as string = "Create Database " & "textbox1.text
 

com = new sqlcommand(w,c)

c.open

com.executenonquery

c.close

Open in new window

0
Comment
Question by:SwingSt
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
Solar_Flare earned 500 total points
ID: 24733446
if you use the "CREATE DATABASE" sql command then you can specify the filename and path, see http://msdn.microsoft.com/en-us/library/aa258257(SQL.80).aspx


if you are using SQLExpress another approach you could take is to use system.io to make a copy of an mdf file that you have prepared earlier and put it in the App_Data folder - you can then use a sqlconnection with a connectionstring that specifies you are attaching a user instance, something like this:

Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=|DataDirectory|\mydb.mdf;User Instance=true;
0
 

Author Comment

by:SwingSt
ID: 24733640
No, these db's are to be original. The link to MSDN would be great, but when I use any of the examples other than
     dim w as string = "create database " & fileName.text
I start getting errors like the following:

Incorrect syntax near 'FILENAME'

So, could I get an example of the proper syntax one would use in this situation?
0
 

Author Comment

by:SwingSt
ID: 24733710
Nevermind, I finally worked out the syntax, thanks for the help - pushed me in the right direction. For anyone looking for a similar solution - I have posted the final code below. It creates a brand new Db in the folder specified, you have to then create and your needed tables but it is right there.
dim c as sqlconnection = YOUR CONNECTION

dim com as sqlcommand

Dim w As String

        w = "CREATE DATABASE " & nameBox.Text & " " _

        & "ON " _

        & "( NAME = " & nameBox.Text & "," _

        & " FILENAME = 'C:\Users\Thomas\Desktop\WebSite1\App_Data\" & nameBox.Text & ".mdf')" _

        & " LOG ON" _

        & " ( NAME = '" & nameBox.Text & "_log'," _

        & " FILENAME = 'C:\Users\Thomas\Desktop\WebSite1\App_Data\" & nameBox.Text & ".ldf')"
 

com = new sqlcommand(w,c)

c.open

com.executenonquery

c.close

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now