?
Solved

Create Database Programatically in asp.net

Posted on 2009-06-28
3
Medium Priority
?
444 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
Solar_Flare earned 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

762 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