Vicki's Tech Blog

After getting tired up with the all installer and "installer experts", my client asked me to write some code to check and attach the SQL Express db on Application startup.
For this, I had a very vague idea – but i had heard something called SMO (SQL Server Management Objects), so i tried google which as usual put me on the right track.

Below is the code to attach SQL mdf file programmatically. VocabEx is the name of database user want to attach. Reference to the assemblies required are:

Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SqlEnum.dll
Microsoft.SqlServer.SmoEnum.dll
Microsoft.SqlServer.ConnectionInfo.dll

These assemblies can be found in: <installation directory>:Program FilesMicrosoft SQL Server90SDKAssemblies folder

Dim sqlServer As New Server(".SQLExpress")
Dim sqlServerConn As ServerConnection = sqlServer.ConnectionContext

‘TRUE for using integrated security mode otherwise use sqlServerConn.Login and sqlServerConn.Password
sqlServerConn.LoginSecure = True

sqlServerConn.DatabaseName = "master"

‘Check to see if database already exists
If Not sqlServer.Databases("VocabEx") Is Nothing Then
          MsgBox("DB Already Exists")
          Return
End If

Dim db As Database = New Database(sqlServer, "VocabEx")
Dim fg As FileGroup = New FileGroup(db, "PRIMARY")

db.FileGroups.Add(fg)

Dim dbFile As DataFile = New DataFile(fg, "VocabEx")
fg.Files.Add(dbFile)

dbFile.FileName = "c:dataVocabEx.mdf"

Dim dbLogFile As LogFile = New LogFile(db, "VocabEx_log")
db.LogFiles.Add(dbLogFile)
dbLogFile.FileName = "c:dataVocabEx_log.ldf"

Dim dbColl As New System.Collections.Specialized.StringCollection
dbColl.Add(dbFile.FileName)
dbColl.Add(dbLogFile.FileName)

sqlServer.AttachDatabase("VocabEx", dbColl)