how to create SQL Server Database with vb.net code

Question

I'm using VB 2012 and SQL Server 2012 Localdb

I want to create a database file (.mdf) in my folder C:\AppData

eg. db file name 0105.mdf & log file name 0105.ldf

Can anyone please tell me the procedure?

I can connect to SQL Server 2012 localdb using connection string

Data Source=(LocalDB)\v11.0;AttachDbFilename={0};Integrated Security=True;Connect Timeout=30

Steve Pettifer · Accepted Answer · 2014-04-11 08:10:12Z

2

Use SqlCommand.ExecuteNonQuery

Imports System.Data.SqlClient

Dim queryString As String = "CREATE DATABASE [0105] ON  PRIMARY (NAME = N'0105', FILENAME = N'C:\APPDATA\Pluto_Data.MDF' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) " & _
                                "LOG ON (NAME = N'0105_Log', FILENAME = N'C:\APPDATA\0105_Log.LDF' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )"

    Using connection As New SqlConnection(queryString)
        Dim command As New SqlCommand(queryString, connection)
        command.Connection.Open()
        command.ExecuteNonQuery()
    End Using

Just change the bits you need to change above like the filegroup, file growth and initial sizes etc. I think that'll work although I haven't got LocalDB to hand to try it so I may be a bit off course.

EDIT: I was off course and too free and easy with the T-SQL.

Try this instead, from the link I added in a comment below.

Public Function CreateDatabase(ByVal dbName As String, ByVal dbFileName As String) As Boolean

    Try

        Dim connectionString As String = String.Format("Data Source=(LocalDB)\v11.0;Initial Catalog=master;Integrated Security=True")
        Using connection As New SqlConnection(connectionString))

            connection.Open()
            Dim cmd As SqlCommand = connection.CreateCommand()


            DetachDatabase(dbName)

            cmd.CommandText = String.Format("CREATE DATABASE {0} ON (NAME = N'{0}', FILENAME = '{1}')", dbName, dbFileName)
            cmd.ExecuteNonQuery()
        End Using

        If (My.Computer.FileSystem.FileExists(dbFileName)) Then
            Return True
        Else
            Return False
        End If

    Catch
        Throw
    End Try
End Function

edited Apr 11, 2014 at 8:10

answered Apr 11, 2014 at 7:24

Steve Pettifer

2,0422 gold badges20 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Amit Saraf Over a year ago

err Keyword not supported: 'create database [0105] on primary (name'.

Steve Pettifer Over a year ago

ah there we go - the dangers of not having it to hand to test!

Steve Pettifer Over a year ago

There is a demo here. It is in C# but you could easily convert it to VB. It doesn't look that different to what I suggested - I think I got a bit free and easy with the options you use on full SQL Server.

Amit Saraf · Accepted Answer · 2014-04-11 08:05:47Z

0

Thanks Steve Pettifer

Here is final code which worked

        Dim Conn As New SqlConnection("Data Source=(LocalDB)\v11.0;Initial Catalog=master;Integrated Security=True")
        Using Conn
            Conn.Open()
            Dim cmd As SqlCommand = Conn.CreateCommand
            Dim str As String = "CREATE Database {0} ON (Name= N'{0}', FileName='{1}\{0}.mdf')"
            cmd.CommandText = String.Format(str, T2.Text, T1.Text)
            cmd.ExecuteNonQuery()
        End Using

T1.text : Folder Location T2.text : File Name without extention

answered Apr 11, 2014 at 8:05

Amit Saraf

1311 gold badge3 silver badges17 bronze badges

1 Comment

Steve Pettifer Over a year ago

Ah cool, you just beat me to my edit :) Glad you got it working.

Collectives™ on Stack Overflow

how to create SQL Server Database with vb.net code

2 Answers 2

EDIT: I was off course and too free and easy with the T-SQL.

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

EDIT: I was off course and too free and easy with the T-SQL.

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related