form1.vb

Beginning VB.NET DatabasesAll_Code.rar

        'btnProjects
        '
        Me.btnProjects.Location = New System.Drawing.Point(8, 56)
        Me.btnProjects.Name = "btnProjects"
        Me.btnProjects.Size = New System.Drawing.Size(128, 23)
        Me.btnProjects.TabIndex = 1
        Me.btnProjects.Text = "Projects"
        '
        'btnGroups
        '
        Me.btnGroups.Location = New System.Drawing.Point(8, 24)
        Me.btnGroups.Name = "btnGroups"
        Me.btnGroups.Size = New System.Drawing.Size(128, 23)
        Me.btnGroups.TabIndex = 0
        Me.btnGroups.Text = "Groups"
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(414, 263)
        Me.Controls.Add(Me.GroupBox2)
        Me.Controls.Add(Me.GroupBox1)
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle
        Me.MaximizeBox = False
        Me.MinimizeBox = False
        Me.Name = "Form1"
        Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
        Me.Text = "DB Migration Utility"
        Me.GroupBox1.ResumeLayout(False)
        Me.GroupBox2.ResumeLayout(False)
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private strProvider As String = "SQL Server"

    Private objData As DALBase
    Private objAccessDB As DALBase

    Private Sub optSQLServer_CheckedChanged(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles optSQLServer.CheckedChanged
        'Enable Labels and TextBoxes for SQL Server
        strProvider = "SQL Server"
        lblServer.Text = "Server"
        lblDatabase.Enabled = True
        txtDatabase.Enabled = True
        txtServer.Focus()
    End Sub

    Private Sub optOracle_CheckedChanged(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles optOracle.CheckedChanged
        'Disable Labels and TextBoxes for Oracle
        strProvider = "Oracle"
        lblServer.Text = "SID"
        lblDatabase.Enabled = False
        txtDatabase.Enabled = False
        txtServer.Focus()
    End Sub

    Private Sub btnOpenConnection_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles btnOpenConnection.Click
        'Initialize a new instance of the data access base class
        objData = New DALBase(strProvider, txtServer.Text, _
            txtDatabase.Text, txtLoginName.Text, txtPassword.Text)

        Try
            'Open the database connection
            objData.OpenConnection()
            'Set the status message
            lblStatus.Text = "Database opened"
        Catch ExceptionErr As Exception
            'Display the error
            MessageBox.Show(ExceptionErr.Message)
        End Try
    End Sub

    Private Sub btnCloseConnection_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles btnCloseConnection.Click
        'Close the database connection
        objData.CloseConnection()
        'Set the status message
        lblStatus.Text = "Database closed"
        'Cleanup
        objData.Dispose()
        objData = Nothing
    End Sub

    Private Sub btnGroups_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles btnGroups.Click
        'Initialize a new instance of the data access base class
        objAccessDB = New DALBase

        Try
            'Get all Groups in a DataReader object
            objAccessDB.SQL = "usp_SelectGroups"
            objAccessDB.InitializeCommand()
            objAccessDB.OpenConnection()
            objAccessDB.DataReader = objAccessDB.Command.ExecuteReader

            'See if any data exists before continuing
            If objAccessDB.DataReader.HasRows Then
                'Build an INSERT SQL statement
                objData.SQL = "INSERT INTO Groups " & _
                    "(GroupID, GroupName, GroupDescription, LastUpdateDate)" & _
                    "VALUES(?, ?, ?, ?)"
                'Initialize the Command object
                objData.InitializeCommand()
                'Add empty parameters to the Parameters collection
                objData.AddParameter("GroupID", _
                    OleDb.OleDbType.Char, 36, Nothing)
                objData.AddParameter("GroupName", _
                    OleDb.OleDbType.VarChar, 50, Nothing)
                objData.AddParameter("GroupDescription", _
                    OleDb.OleDbType.LongVarChar, 1000, Nothing)
                objData.AddParameter("LastUpdateDate", _
                    OleDb.OleDbType.Date, 8, Nothing)

                'Process all rows
                While objAccessDB.DataReader.Read()
                    'Set the parameter values
                    objData.Command.Parameters("GroupID").Value = _
                        objAccessDB.DataReader.Item("GroupID").ToString
                    objData.Command.Parameters("GroupName").Value = _
                        objAccessDB.DataReader.Item("GroupName")
                    objData.Command.Parameters("GroupDescription").Size = _
                        objAccessDB.DataReader.Item( _
                        "GroupDescription").ToString.Length
                    objData.Command.Parameters("GroupDescription").Value = _
                        objAccessDB.DataReader.Item("GroupDescription")
                    objData.Command.Parameters("LastUpdateDate").Value = _
                        objAccessDB.DataReader.Item("LastUpdateDate")
                    'Execute the INSERT statement
                    objData.Command.ExecuteNonQuery()
                End While
            End If

            'Close the DataReader
            objAccessDB.DataReader.Close()

            'Close the database connection
            objAccessDB.CloseConnection()

            'Set the status message
            lblGroups.Text = "Data successfully migrated"
        Catch ExceptionErr As Exception
            MessageBox.Show(ExceptionErr.Message)
        Finally
            'Cleanup
            objAccessDB.Dispose()
            objAccessDB = Nothing
            objData.Command.Dispose()
            objData.Command = Nothing
        End Try
    End Sub

    Private Sub btnProjects_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles btnProjects.Click
        'Initialize a new instance of the data access base class
        objAccessDB = New DALBase

        Try
            'Get all Projects in a DataReader object
            objAccessDB.SQL = "usp_SelectProjects"
            objAccessDB.InitializeCommand()
            objAccessDB.OpenConnection()
            objAccessDB.DataReader = objAccessDB.Command.ExecuteReader

            'See if any data exists before continuing
            If objAccessDB.DataReader.HasRows Then
                'Build an INSERT SQL statement
                objData.SQL = "INSERT INTO Projects " & _
                    "(ProjectID, ProjectName, ProjectDescription, " & _
                    "SequenceNumber, LastUpdateDate) " & _
                    "VALUES(?, ?, ?, ?, ?)"
                'Initialize the Command object
                objData.InitializeCommand()
                'Add empty parameters to the Parameters collection
                objData.AddParameter("ProjectID", _
                    OleDb.OleDbType.Char, 36, Nothing)
                objData.AddParameter("ProjectName", _
                    OleDb.OleDbType.VarChar, 50, Nothing)
                objData.AddParameter("ProjectDescription", _
                    OleDb.OleDbType.LongVarChar, 1000, Nothing)
                objData.AddParameter("SequenceNumber", _
                    OleDb.OleDbType.UnsignedTinyInt, 1, Nothing)
                objData.AddParameter("LastUpdateDate", _
                    OleDb.OleDbType.Date, 8, Nothing)

                'Process all rows
                While objAccessDB.DataReader.Read()
                    'Set the parameter values
                    objData.Command.Parameters("ProjectID").Value = _
                        objAccessDB.DataReader.Item("ProjectID").ToString
                    objData.Command.Parameters("ProjectName").Value = _
                        objAccessDB.DataReader.Item("ProjectName")
                    objData.Command.Parameters("ProjectDescription").Size = _
                        objAccessDB.DataReader.Item( _
                        "ProjectDescription").ToString.Length
                    objData.Command.Parameters("ProjectDescription").Value = _
                        objAccessDB.DataReader.Item("ProjectDescription")
                    objData.Command.Parameters("SequenceNumber").Value = _
                        objAccessDB.DataReader.Item("SequenceNumber")
                    objData.Command.Parameters("LastUpdateDate").Value = _
                        objAccessDB.DataReader.Item("LastUpdateDate")
                    'Execute the INSERT statement
                    objData.Command.ExecuteNonQuery()
                End While
            End If

            'Close the DataReader
            objAccessDB.DataReader.Close()

            'Close the database connection
            objAccessDB.CloseConnection()

            'Set the status message
            lblProjects.Text = "Data successfully migrated"
        Catch ExceptionErr As Exception
            MessageBox.Show(ExceptionErr.Message)
        Finally
            'Cleanup
            objAccessDB.Dispose()
            objAccessDB = Nothing
            objData.Command.Dispose()
            objData.Command = Nothing
        End Try
    End Sub
End Class