Opret SQL-database i runtime

Hvis du kan eksekvere SQL sætninger, så kan du vel også lave CREATE DATABASE
og CREATE TABLE sætninger.

Jeg kunne godt tænke mig et lidt mere detaljeret eksempel. Eks. kildekode

Følgende kode virker:

Dim conn As SqlConnection

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim strConn As String

strConn = "Server = " & Environment.MachineName

strConn += "\VSdotNET; Database = ; Integrated Security = SSPI;"

conn = New SqlConnection(strConn)

conn.Open()

CreateDataBase()

CreateClientsTable()

End Sub

Private Sub CreateDataBase()

Dim strSQL As String

strSQL = "if Exists (Select * From master..sysdatabases Where Name = 'VET')"

strSQL += "DROP DATABASE VET" & vbCrLf & " CREATE DATABASE VET"

Dim cmd As New SqlCommand(strSQL, conn)

cmd.CommandType = CommandType.Text

Try

cmd.ExecuteNonQuery()

Catch

MessageBox.Show("Error Creating DB")

Finally

cmd.Dispose()

End Try

End Sub

Private Sub CreateClientsTable()

Me.Text = "Creating Clients Table..."

Dim strSQL As String = _

"USE VET" & vbCrLf & _

"IF EXISTS (" & _

"SELECT * " & _

"FROM VET.dbo.sysobjects " & _

"WHERE Name = 'Clients' " & _

"AND TYPE = 'u')" & vbCrLf & _

"BEGIN" & vbCrLf & _

"DROP TABLE VET.dbo.Clients" & vbCrLf & _

"END" & vbCrLf & _

"CREATE TABLE Clients (" & _

"ID Int NOT NULL," & _

"LastName NVarChar(20) NOT NULL," & _

"FirstName NVarChar(20) NOT NULL," & _

"Address NVarChar(150) NOT NULL," & _

"City NVarChar(20) NOT NULL," & _

"ZipCode NVarChar(5) NOT NULL," & _

"PhoneNumber NVarChar(20) NOT NULL," & _

"WorkNumber NVarChar(20)," & _

"CellNumber NVarChar(20)," & _

"Email NVarChar(50) NOT NULL," & _

"Balance Money NOT NULL," & _

"BalanceDate DateTime NOT NULL," & _

"CONSTRAINT [ID] PRIMARY KEY CLUSTERED" & _

"(ID))"

Dim cmd As New SqlCommand(strSQL, conn)

cmd.CommandType = CommandType.Text

Try

cmd.ExecuteNonQuery()

Catch ex As SqlException

MessageBox.Show(ex.ToString, "Clients")

Finally

cmd.Dispose()

End Try

End Sub

Private Sub MakeClientStoredProcedure()

Dim strSQL As String = _

"USE VET" & vbCrLf & _

"IF EXISTS (" & _

"SELECT * " & _

"FROM VET.dbo.sysobjects " & _

"WHERE Name = 'ClientInfo' " & _

"AND TYPE = 'p')" & vbCrLf & _

"BEGIN" & vbCrLf & _

"DROP PROCEDURE ClientInfo" & vbCrLf & _

"END"

Dim cmd As New SqlCommand(strSQL, conn)

cmd.CommandType = CommandType.Text

Try

cmd.ExecuteNonQuery()

cmd.CommandText = "Create Procedure ClientInfo" & vbCrLf & _

"@ClientID int " & vbCrLf & _

"AS Select * " & vbCrLf & _

"FROM VET.dbo.Clients Where ID = @ClientID"

cmd.ExecuteNonQuery()

Catch ex As SqlException

MessageBox.Show(ex.ToString, "Error Creating Stored Procedure")

Finally

cmd.Dispose()

End Try

End Sub

Koden ovenfor virker uden tvivl. Men SQL server har jo sit helt eget objekt hieraki som du kan få fat i via SQL-DMO. Eksempel.

Først set reference til SQL-DMO.

Dim oSQLServer As New SQLDMO.SQLServer
Dim bConnected As Boolean
Dim oDatabase As New SQLDMO.Database
Dim oDBFileData As New SQLDMO.DBFile
Dim oLogFile As New SQLDMO.LogFile
Dim tableCategories As New SQLDMO.Table
Dim colCategoryID As New SQLDMO.Column
Dim colCategoryName As New SQLDMO.Column
Dim colDescription As New SQLDMO.Column
Dim colPicture As New SQLDMO.Column

bConnected = False

oSQLServer.LoginTimeout = 30
oSQLServer.LoginSecure = True
oSQLServer.Connect "(localhost)","sa",""
oDatabase.Name = "Northwind"

' Define the PRIMARY data file.
oDBFileData.Name = "NorthData1"
oDBFileData.PhysicalName = "c:\program files\microsoft sql server\mssql\data\northwnd.mdf"
oDBFileData.PrimaryFile = True

' Specify file growth in chunks of fixed size for all data files.
oDBFileData.FileGrowthType = SQLDMOGrowth_MB
oDBFileData.FileGrowth = 1

oDatabase.FileGroups("PRIMARY").DBFiles.Add oDBFileData

' Define the database transaction log.
oLogFile.Name = "NorthLog1"
oLogFile.PhysicalName = "c:\program files\microsoft sql server\mssql\data\northwnd.ldf"
oDatabase.TransactionLog.LogFiles.Add oLogFile


' Get the Northwind database. Note: Create and connect of SQLServer
' object used is not illustrated in this example.
Set oDatabase = oSQLServer.Databases("Northwind")

' Populate the Column objects to define the table columns.
colCategoryID.Name = "CategoryID"
colCategoryID.Datatype = "int"
colCategoryID.Identity = True
colCategoryID.IdentityIncrement = 1
colCategoryID.IdentitySeed = 1
colCategoryID.AllowNulls = False

colCategoryName.Name = "CategoryName"
colCategoryName.Datatype = "varchar"
colCategoryName.Length = 15
colCategoryName.AllowNulls = False

colDescription.Name = "Description"
colDescription.Datatype = "text"
colDescription.AllowNulls = True

colPicture.Name = "Picture"
colPicture.Datatype = "image"
colPicture.AllowNulls = True

' Name the table, then set desired properties to control eventual table
' construction.
tableCategories.Name = "Categories"
tableCategories.FileGroup = "PRIMARY"
tableCategories.TextFileGroup = "fgNorthwindTxtImg"

' Add populated Column objects to the Columns collection of the
' Table object.
tableCategories.Columns.Add colCategoryID
tableCategories.Columns.Add colCategoryName
tableCategories.Columns.Add colDescription
tableCategories.Columns.Add colPicture

' Create the table by adding the Table object to its containing
' collection.
oDatabase.Tables.Add tableCategories

oSQLServer.Databases.Add oDatabase
oSQLServer.DisConnect

Hej der ude.
Lidt tricky med SQL-DMO referencen og installationen heraf.
Men jeg kunne bruge dit forslag i vid udstrækning og points går derfor til dig.

-