programmatically creating a sql database in c#

Question

0.00/5 (No votes)

See more:

Hello!

I am trying to create a database programmatically with the following code:

C#

@Select = @Select + sql;
String str;
SqlConnection myConn = new SqlConnection(<a valid="" connection="" string="">);

str = "CREATE DATABASE " + sql + " ON PRIMARY " +
      "(NAME = " + sql + ", " +
      "FILENAME = '" + @Select + ".mdf', " +
      "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
      "LOG ON (NAME = " + sql + "_Log, " +
      "FILENAME = '" + @Select + ".ldf', " +
      "SIZE = 1MB, " +
      "MAXSIZE = 5MB, " +
      "FILEGROWTH = 10%)";

where the variable sql is a valid database name ("ChaosSpaceMarines").
@Select is a valid path ("E:\\databases\\")

This code works fine once. But if I delete the database ChaosSpaceMarines (and all associated files) I get the SQL error "Database ChaosSpaceMarines" already exists. Even if I create the new database on a different drive. (Also tried a different directory).

Can anybody tell me why I can only create the named database only once? The database contains 22 tables and currently I am trying to debug the SQL statements that create the tables. It is a pain to have to use a different table name each time I restart the program.

Posted 26-May-14 5:58am

Member 10599190

Updated 26-May-14 6:32am

DamithSL

v2

Add a Solution

Comments

[no name] 26-May-14 12:11pm

Are you deleting the database correctly or are you just deleting the files? SQL Server would still "know" about the database if you just delete the files.

Member 10599190 30-May-14 12:40pm

Excellent point. That must have been the problem.

[no name] 26-May-14 12:11pm

It seems you do not use DROP DATABASE to delete the db

Member 10599190 30-May-14 12:44pm

Indeed not. And as I just discovered, that was the problem. I just deleted the files on the drive without dropping the database from the server as somebody already pointed out.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 26-May-14 13:34pm

Show how you exactly delete the Database.

anup.bhunia 26-May-14 15:01pm

I am also interested to see how you deleting the DB

2 solutions

Solution 2

try
{
@Select = @Select + txtDatabase.Text;
str = "USE MASTER";
myCommand = new SqlCommand(str, myConn);
myConn.Open();
myCommand.ExecuteNonQuery();
str = "DROP DATABASE " + sql;
myCommand = new SqlCommand(str, myConn);
myCommand.ExecuteNonQuery();
myConn.Close();
}
catch (Exception) { myConn.Close(); }

With this try block (and subsequent catch, of course) I am able to "drop" the named database from the Master table and recreate the database. This is not a very elegant solution, but hey, it works.

Thank you one and all for your excellent suggestions.

Posted 30-May-14 6:46am

Member 10599190

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-05-26T09:13:00

You can't add another database with same name even if its path is differ.
Have a look here: CREATE DATABASE[^]

More details: Delete a Database[^]

MSDN wrote:
Follow Up: After deleting a database

Back up the master database. If master must be restored, any database that has been deleted since the last backup of master will still have references in the system catalog views and may cause error messages to be raised.