VB-Tips

SQL Server: Create database

You can create an SQL server database by executing sql commands. Here is an example.
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()
conn.Close()
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

---