Solved

Create Database Programatically in asp.net

Posted on 2009-06-28
3
434 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
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…

730 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