1

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Amit Saraf
  • 101
  • 1
  • 3
  • 15

2 Answers2

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
Steve Pettifer
  • 1,975
  • 1
  • 19
  • 34
  • err **Keyword not supported: 'create database [0105] on primary (name'.** – Amit Saraf Apr 11 '14 at 07:39
  • ah there we go - the dangers of not having it to hand to test! – Steve Pettifer Apr 11 '14 at 07:44
  • 1
    There is a demo [here](http://social.msdn.microsoft.com/Forums/en-US/268c3411-102a-4272-b305-b14e29604313/localdb-create-connect-to-database-programmatically-?forum=sqlsetupandupgrade). 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. – Steve Pettifer Apr 11 '14 at 07:47
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

Amit Saraf
  • 101
  • 1
  • 3
  • 15