Solved

Create Database Programatically in asp.net

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…

791 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