Programatically creating a SQL database if it doesn't exist.

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

I need to be able to test an already started MSDE/SQL server for a
specific database. If that database doesn't exist, I want to create
it.

I have the following statement someone provided on site, but if I
can't connect to the database, how can I execute the sql command?


Dim sql As String = "CREATE DATABASE mydb ON PRIMARY" +
"(Name=test_data, filename = 'C:\mysql\mydb_data.mdf', size=3," +
"maxsize=5, filegrowth=10%)log on" + "(name=mydbb_log,
filename='C:\mysql\mydb_log.ldf',size=3," + "maxsize=20,filegrowth=1)"

If I try to connect with the following string, it fails and I can't
execute the sql string above, of course.


ConnectionString = "UID=sa;PWD=pass;" + "Initial Catalog=mydb;" +
"Data Source=localhost;"



Thanks much for any help you can offer.
 
Hi Matt,

First of all, I would like to confirm my understanding of your issue.
From your description, I understand that you wants to judge if one dabase
exists in the SQL server, if no, you will create it and access it.
Have I fully understood you? If there is anything I misunderstood, please
feel free to let me know.

I think you may write your code as below.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Me.SqlConnection1.ConnectionString = "workstation
id=""SHA-VPHUANG-XP"";packet size=4096;integrated security=SSPI;data
source=""sha-vphuang-03"";persist security info=False"
Me.SqlConnection1.Open()
Dim cmd As New SqlClient.SqlCommand
cmd.Connection = Me.SqlConnection1
cmd.CommandText = "if not exists (select * from
master..sysdatabases where name = 'mydb') CREATE DATABASE mydb ON PRIMARY
(Name=test_data, filename = 'C:\mydb_data.mdf', size=3,maxsize=5,
filegrowth=10%) log on
(name=mydbb_log,filename='C:\mydb_log.ldf',size=3,maxsize=20,filegrowth=1)"
Dim i As Integer = cmd.ExecuteNonQuery()
cmd.CommandText = "use mydb"
i = cmd.ExecuteNonQuery()
MsgBox(Me.SqlConnection1.Database.ToString())
Me.SqlConnection1.Close()
End Sub

Please apply my suggestion above and let me know if it helps resolve your
problem.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Matt,
In addition to the other comments.

I would not expect the connect to work as you do not have the database yet
(chicken & egg problem).

I would try to simply connect to a known database first "master","model",
"tempdb" come to mind.

Then execute your statement.

After executing your statement you can then change to your database.

Something like:

Dim connectionString As String = "UID=sa;PWD=pass;" & _
"Initial Catalog=master;Data Source=localhost;"

Dim sql As String = "CREATE DATABASE mydb ON PRIMARY" & _
"(Name=test_data, filename = 'C:\mydb_data.mdf', size=3," & _
"maxsize=5, filegrowth=10%)log on" & _
"(name=mydbb_log, filename='C:\mydb_log.ldf',size=3," & _
"maxsize=20,filegrowth=1)"

Dim connection As New SqlClient.SqlConnection(connectionString)
connection.Open()
Dim cmd As New SqlClient.SqlCommand(sql, connection)
cmd.ExecuteNonQuery()
connection.ChangeDatabase("mydb")

connection.Close()

Of course I would probably have two connection strings or modify a single
connection string, one to create the database and a second to actually
connect & use it.

Note: the SqlConnection.ChangeDatabase does a "USE mydb", so the connection
above is left using the new database after it is created.

Hope this helps
Jay
 
That's a great idea, I don't know why I didn't think of that. :)

I'll take this idea and look at Peter's code and give it go. Sure
it'll work now.

Thanks much to everyone that offered some help (I'll also look up the
adonet group, didn't see when I looked last time)

M
 
Matt,

I think your problem is that in your connection string you are pointing to
the database name you are trying to create. I think you need to point to the
master db, create the 'mydb' database and then switch the connection to the
newly created database.

Francisco
 
Back
Top