Joel #

Codigo para crear una base de datos, una tabla y stored procedures 

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