Members

» Login
» Register (Free)
» Member Benefits
» Submit Resource

Technology Zones

» .NET Framework
(Quickstart)
» ASP
» ASP.NET
» C / C++ / MFC
» C# Programming
» Java
» JavaScript
» PHP
» Databases & SQL
» Visual Basic
» VB.NET
» XML
» More...

IBM Learning Center

» Best practices for software analysis
» Writing Good Software Systems Development Use Cases.
» How to use the Scrum project management method with IBM Rational Team Concert and the Jazz platform.
» Test management with Jazz and IBM Rational Team Concert.

Articles

» The .NET Framework & Protecting your Code
» Number Systems
» Uploading Files with ASP
» Visual Studio Next Generation: Language Enhancements
» How to NNTP in C#

Hosted By

Info

» Partners
» Advertise
» About Us
» Link To Us
» Privacy Policy
» Contact Us

This resource has not currently been approved, and is not currently linked to from our directory of resources. It is being displayed here for preview by the author and moderators only.

Rated
Read 2,670 times

0 comments

Simple SQL Server Schema Comparison in VB.Net - DB Comparison Form

Authorkhari6579

DB Comparison Form

Public Class frmDBComparison
    Inherits System.Windows.Forms.Form
    Dim sqlConn1 As SqlClient.SqlConnection
    Dim sqlConn2 As SqlClient.SqlConnection

    Dim strConn1 As String
    Dim strConn2 As String

    Dim dTable1 As DataTable
    Dim dTable2 As DataTable

    Dim dRow1 As DataRow

    Dim sqlAdapt As SqlClient.SqlDataAdapter

    Dim dSet1 As DataSet
    Dim dSet2 As DataSet

    Dim pstrSQL As String

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.
    'Do not modify it using the code editor.
    Friend WithEvents lblServer1 As System.Windows.Forms.Label
    Friend WithEvents txtServer1 As System.Windows.Forms.TextBox
    Friend WithEvents lblPassword1 As System.Windows.Forms.Label
    Friend WithEvents txtPassword1 As System.Windows.Forms.TextBox
    Friend WithEvents lblUser1 As System.Windows.Forms.Label
    Friend WithEvents txtUserID1 As System.Windows.Forms.TextBox
    Friend WithEvents lblDBName1 As System.Windows.Forms.Label
    Friend WithEvents txtDBName1 As System.Windows.Forms.TextBox
    Friend WithEvents lblServer2 As System.Windows.Forms.Label
    Friend WithEvents txtServer2 As System.Windows.Forms.TextBox
    Friend WithEvents lblPassword2 As System.Windows.Forms.Label
    Friend WithEvents txtPassword2 As System.Windows.Forms.TextBox
    Friend WithEvents lblUserID2 As System.Windows.Forms.Label
    Friend WithEvents txtUserID2 As System.Windows.Forms.TextBox
    Friend WithEvents lblDbName2 As System.Windows.Forms.Label
    Friend WithEvents txtDBName2 As System.Windows.Forms.TextBox
    Friend WithEvents dgrdMissing1 As System.Windows.Forms.DataGrid
    Friend WithEvents dgrdMissing2 As System.Windows.Forms.DataGrid
    Friend WithEvents btnCompare As System.Windows.Forms.Button
    Friend WithEvents grpSource As System.Windows.Forms.GroupBox
    Friend WithEvents grpTarget As System.Windows.Forms.GroupBox
    Friend WithEvents txtScripts As System.Windows.Forms.TextBox
    Friend WithEvents lblScripts As System.Windows.Forms.Label
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.grpSource = New System.Windows.Forms.GroupBox
        Me.lblServer1 = New System.Windows.Forms.Label
        Me.txtServer1 = New System.Windows.Forms.TextBox
        Me.lblPassword1 = New System.Windows.Forms.Label
        Me.txtPassword1 = New System.Windows.Forms.TextBox
        Me.lblUser1 = New System.Windows.Forms.Label
        Me.txtUserID1 = New System.Windows.Forms.TextBox
        Me.lblDBName1 = New System.Windows.Forms.Label
        Me.txtDBName1 = New System.Windows.Forms.TextBox
        Me.grpTarget = New System.Windows.Forms.GroupBox
        Me.lblServer2 = New System.Windows.Forms.Label
        Me.txtServer2 = New System.Windows.Forms.TextBox
        Me.lblPassword2 = New System.Windows.Forms.Label
        Me.txtPassword2 = New System.Windows.Forms.TextBox
        Me.lblUserID2 = New System.Windows.Forms.Label
        Me.txtUserID2 = New System.Windows.Forms.TextBox
        Me.lblDbName2 = New System.Windows.Forms.Label
        Me.txtDBName2 = New System.Windows.Forms.TextBox
        Me.dgrdMissing1 = New System.Windows.Forms.DataGrid
        Me.dgrdMissing2 = New System.Windows.Forms.DataGrid
        Me.btnCompare = New System.Windows.Forms.Button
        Me.txtScripts = New System.Windows.Forms.TextBox
        Me.lblScripts = New System.Windows.Forms.Label
        Me.grpSource.SuspendLayout()
        Me.grpTarget.SuspendLayout()
        CType(Me.dgrdMissing1, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.dgrdMissing2, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'grpSource
        '
        Me.grpSource.Controls.Add(Me.lblServer1)
        Me.grpSource.Controls.Add(Me.txtServer1)
        Me.grpSource.Controls.Add(Me.lblPassword1)
        Me.grpSource.Controls.Add(Me.txtPassword1)
        Me.grpSource.Controls.Add(Me.lblUser1)
        Me.grpSource.Controls.Add(Me.txtUserID1)
        Me.grpSource.Controls.Add(Me.lblDBName1)
        Me.grpSource.Controls.Add(Me.txtDBName1)
        Me.grpSource.Font = New System.Drawing.Font("Verdana", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.grpSource.ForeColor = System.Drawing.SystemColors.ActiveCaptionText
        Me.grpSource.Location = New System.Drawing.Point(24, 8)
        Me.grpSource.Name = "grpSource"
        Me.grpSource.Size = New System.Drawing.Size(272, 176)
        Me.grpSource.TabIndex = 20
        Me.grpSource.TabStop = False
        Me.grpSource.Text = "Source"
        '
        'lblServer1
        '
        Me.lblServer1.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblServer1.ForeColor = System.Drawing.SystemColors.ActiveCaptionText
        Me.lblServer1.Location = New System.Drawing.Point(24, 38)
        Me.lblServer1.Name = "lblServer1"
        Me.lblServer1.Size = New System.Drawing.Size(55, 16)
        Me.lblServer1.TabIndex = 27
        Me.lblServer1.Text = "Server"
        '
        'txtServer1
        '
        Me.txtServer1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.txtServer1.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.txtServer1.Location = New System.Drawing.Point(96, 36)
        Me.txtServer1.Name = "txtServer1"
        Me.txtServer1.Size = New System.Drawing.Size(104, 20)
        Me.txtServer1.TabIndex = 20
        Me.txtServer1.Text = ""
        '
        'lblPassword1
        '
        Me.lblPassword1.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblPassword1.ForeColor = System.Drawing.SystemColors.ActiveCaptionText
        Me.lblPassword1.Location = New System.Drawing.Point(24, 128)
        Me.lblPassword1.Name = "lblPassword1"
        Me.lblPassword1.Size = New System.Drawing.Size(56, 16)
        Me.lblPassword1.TabIndex = 26
        Me.lblPassword1.Text = "Password"
        '
        'txtPassword1
        '
        Me.txtPassword1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.txtPassword1.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.txtPassword1.Location = New System.Drawing.Point(96, 126)
        Me.txtPassword1.Name = "txtPassword1"
        Me.txtPassword1.PasswordChar = Microsoft.VisualBasic.ChrW(42)
        Me.txtPassword1.Size = New System.Drawing.Size(152, 20)
        Me.txtPassword1.TabIndex = 24
        Me.txtPassword1.Text = ""
        '
        'lblUser1
        '
        Me.lblUser1.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblUser1.ForeColor = System.Drawing.SystemColors.ActiveCaptionText
        Me.lblUser1.Location = New System.Drawing.Point(24, 98)
        Me.lblUser1.Name = "lblUser1"
        Me.lblUser1.Size = New System.Drawing.Size(55, 16)
        Me.lblUser1.TabIndex = 25
        Me.lblUser1.Text = "User ID"
        '
        'txtUserID1
        '
        Me.txtUserID1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.txtUserID1.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.txtUserID1.Location = New System.Drawing.Point(96, 96)
        Me.txtUserID1.Name = "txtUserID1"
        Me.txtUserID1.Size = New System.Drawing.Size(152, 20)
        Me.txtUserID1.TabIndex = 22
        Me.txtUserID1.Text = ""
        '
        'lblDBName1
        '
        Me.lblDBName1.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblDBName1.ForeColor = System.Drawing.SystemColors.ActiveCaptionText
        Me.lblDBName1.Location = New System.Drawing.Point(24, 67)
        Me.lblDBName1.Name = "lblDBName1"
        Me.lblDBName1.Size = New System.Drawing.Size(55, 16)
        Me.lblDBName1.TabIndex = 23
        Me.lblDBName1.Text = "DB Name"
        '
        'txtDBName1
        '
        Me.txtDBName1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.txtDBName1.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.txtDBName1.Location = New System.Drawing.Point(96, 66)
        Me.txtDBName1.Name = "txtDBName1"
        Me.txtDBName1.Size = New System.Drawing.Size(104, 20)
        Me.txtDBName1.TabIndex = 21
        Me.txtDBName1.Text = ""
        '
        'grpTarget
        '
        Me.grpTarget.Controls.Add(Me.lblServer2)
        Me.grpTarget.Controls.Add(Me.txtServer2)
        Me.grpTarget.Controls.Add(Me.lblPassword2)
        Me.grpTarget.Controls.Add(Me.txtPassword2)
        Me.grpTarget.Controls.Add(Me.lblUserID2)
        Me.grpTarget.Controls.Add(Me.txtUserID2)
        Me.grpTarget.Controls.Add(Me.lblDbName2)
        Me.grpTarget.Controls.Add(Me.txtDBName2)
        Me.grpTarget.Font = New System.Drawing.Font("Verdana", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.grpTarget.ForeColor = System.Drawing.SystemColors.ActiveCaptionText
        Me.grpTarget.Location = New System.Drawing.Point(432, 8)
        Me.grpTarget.Name = "grpTarget"
        Me.grpTarget.Size = New System.Drawing.Size(272, 176)
        Me.grpTarget.TabIndex = 28
        Me.grpTarget.TabStop = False
        Me.grpTarget.Text = "Target"
        '
        'lblServer2
        '
        Me.lblServer2.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblServer2.ForeColor = System.Drawing.SystemColors.ActiveCaptionText
        Me.lblServer2.Location = New System.Drawing.Point(24, 38)
        Me.lblServer2.Name = "lblServer2"
        Me.lblServer2.Size = New System.Drawing.Size(55, 16)
        Me.lblServer2.TabIndex = 27
        Me.lblServer2.Text = "Server"
        '
        'txtServer2
        '
        Me.txtServer2.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.txtServer2.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.txtServer2.Location = New System.Drawing.Point(96, 36)
        Me.txtServer2.Name = "txtServer2"
        Me.txtServer2.Size = New System.Drawing.Size(104, 20)
        Me.txtServer2.TabIndex = 20
        Me.txtServer2.Text = ""
        '
        'lblPassword2
        '
        Me.lblPassword2.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblPassword2.ForeColor = System.Drawing.SystemColors.ActiveCaptionText
        Me.lblPassword2.Location = New System.Drawing.Point(24, 128)
        Me.lblPassword2.Name = "lblPassword2"
        Me.lblPassword2.Size = New System.Drawing.Size(56, 16)
        Me.lblPassword2.TabIndex = 26
        Me.lblPassword2.Text = "Password"
        '
        'txtPassword2
        '
        Me.txtPassword2.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.txtPassword2.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.txtPassword2.Location = New System.Drawing.Point(96, 126)
        Me.txtPassword2.Name = "txtPassword2"
        Me.txtPassword2.PasswordChar = Microsoft.VisualBasic.ChrW(42)
        Me.txtPassword2.Size = New System.Drawing.Size(152, 20)
        Me.txtPassword2.TabIndex = 24
        Me.txtPassword2.Text = ""
        '
        'lblUserID2
        '
        Me.lblUserID2.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblUserID2.ForeColor = System.Drawing.SystemColors.ActiveCaptionText
        Me.lblUserID2.Location = New System.Drawing.Point(24, 98)
        Me.lblUserID2.Name = "lblUserID2"
        Me.lblUserID2.Size = New System.Drawing.Size(55, 16)
        Me.lblUserID2.TabIndex = 25
        Me.lblUserID2.Text = "User ID"
        '
        'txtUserID2
        '
        Me.txtUserID2.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.txtUserID2.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.txtUserID2.Location = New System.Drawing.Point(96, 96)
        Me.txtUserID2.Name = "txtUserID2"
        Me.txtUserID2.Size = New System.Drawing.Size(152, 20)
        Me.txtUserID2.TabIndex = 22
        Me.txtUserID2.Text = ""
        '
        'lblDbName2
        '
        Me.lblDbName2.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblDbName2.ForeColor = System.Drawing.SystemColors.ActiveCaptionText
        Me.lblDbName2.Location = New System.Drawing.Point(24, 67)
        Me.lblDbName2.Name = "lblDbName2"
        Me.lblDbName2.Size = New System.Drawing.Size(55, 16)
        Me.lblDbName2.TabIndex = 23
        Me.lblDbName2.Text = "DB Name"
        '
        'txtDBName2
        '
        Me.txtDBName2.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.txtDBName2.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.txtDBName2.Location = New System.Drawing.Point(96, 66)
        Me.txtDBName2.Name = "txtDBName2"
        Me.txtDBName2.Size = New System.Drawing.Size(104, 20)
        Me.txtDBName2.TabIndex = 21
        Me.txtDBName2.Text = ""
        '
        'dgrdMissing1
        '
        Me.dgrdMissing1.DataMember = ""
        Me.dgrdMissing1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.dgrdMissing1.Location = New System.Drawing.Point(24, 224)
        Me.dgrdMissing1.Name = "dgrdMissing1"
        Me.dgrdMissing1.Size = New System.Drawing.Size(680, 136)
        Me.dgrdMissing1.TabIndex = 29
        '
        'dgrdMissing2
        '
        Me.dgrdMissing2.DataMember = ""
        Me.dgrdMissing2.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.dgrdMissing2.Location = New System.Drawing.Point(336, 104)
        Me.dgrdMissing2.Name = "dgrdMissing2"
        Me.dgrdMissing2.Size = New System.Drawing.Size(64, 24)
        Me.dgrdMissing2.TabIndex = 30
        Me.dgrdMissing2.Visible = False
        '
        'btnCompare
        '
        Me.btnCompare.Font = New System.Drawing.Font("Verdana", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.btnCompare.ForeColor = System.Drawing.Color.White
        Me.btnCompare.Location = New System.Drawing.Point(24, 192)
        Me.btnCompare.Name = "btnCompare"
        Me.btnCompare.Size = New System.Drawing.Size(680, 24)
        Me.btnCompare.TabIndex = 31
        Me.btnCompare.Text = "Compare"
        '
        'txtScripts
        '
        Me.txtScripts.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.txtScripts.Location = New System.Drawing.Point(80, 376)
        Me.txtScripts.Multiline = True
        Me.txtScripts.Name = "txtScripts"
        Me.txtScripts.Size = New System.Drawing.Size(624, 128)
        Me.txtScripts.TabIndex = 32
        Me.txtScripts.Text = ""
        '
        'lblScripts
        '
        Me.lblScripts.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblScripts.ForeColor = System.Drawing.SystemColors.ActiveCaptionText
        Me.lblScripts.Location = New System.Drawing.Point(24, 384)
        Me.lblScripts.Name = "lblScripts"
        Me.lblScripts.Size = New System.Drawing.Size(56, 16)
        Me.lblScripts.TabIndex = 33
        Me.lblScripts.Text = "Scripts"
        '
        'frmDBComparison
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.BackColor = System.Drawing.SystemColors.Desktop
        Me.ClientSize = New System.Drawing.Size(728, 525)
        Me.Controls.Add(Me.lblScripts)
        Me.Controls.Add(Me.txtScripts)
        Me.Controls.Add(Me.btnCompare)
        Me.Controls.Add(Me.dgrdMissing2)
        Me.Controls.Add(Me.dgrdMissing1)
        Me.Controls.Add(Me.grpSource)
        Me.Controls.Add(Me.grpTarget)
        Me.Name = "frmDBComparison"
        Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
        Me.Text = "DB Comparison"
        Me.grpSource.ResumeLayout(False)
        Me.grpTarget.ResumeLayout(False)
        CType(Me.dgrdMissing1, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.dgrdMissing2, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub btnCompare_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCompare.Click
        If fncControlsValidation() = True Then
            Me.Cursor = Cursors.WaitCursor
            Try
                pstrSQL = "Select TABLE_CATALOG [DB NAME],TABLE_NAME [TABLE NAME],COLUMN_NAME [COLUMN NAME],DATA_TYPE [DATA TYPE]," & _
                    " COLUMN_DEFAULT [DEFAULT],IS_NULLABLE [NULLABLE],CHARACTER_OCTET_LENGTH [LENGTH]" & _
                    " From " & CStr(txtDBName1.Text) & ".information_schema.columns"

                dSet1 = New DataSet
                sqlAdapt = New SqlClient.SqlDataAdapter(pstrSQL, sqlConn1)
                sqlAdapt.Fill(dSet1)
                sqlAdapt.Dispose()

                pstrSQL = "Select TABLE_CATALOG [DB NAME],TABLE_NAME [TABLE NAME],COLUMN_NAME [COLUMN NAME],DATA_TYPE [DATA TYPE]," & _
                                    " COLUMN_DEFAULT [DEFAULT],IS_NULLABLE [NULLABLE],CHARACTER_OCTET_LENGTH [LENGTH]" & _
                                    " From " & CStr(txtDBName2.Text) & ".information_schema.columns"

                dSet2 = New DataSet
                sqlAdapt = New SqlClient.SqlDataAdapter(pstrSQL, sqlConn2)
                sqlAdapt.Fill(dSet2)
                sqlAdapt.Dispose()

                If (dSet1.Tables(0).Rows.Count > 0 And dSet2.Tables(0).Rows.Count > 0) Then
                    dTable1 = New DataTable
                    fncCompareDatabase(dTable1, dgrdMissing1)
                    dgrdMissing1.DataSource = dTable1
                End If
                dSet1.Dispose() : dSet2.Dispose()

                pstrSQL = "Select TABLE_CATALOG [DB NAME],TABLE_NAME [TABLE NAME],COLUMN_NAME [COLUMN NAME],DATA_TYPE [DATA TYPE]," & _
                    " COLUMN_DEFAULT [DEFAULT],IS_NULLABLE [NULLABLE],CHARACTER_OCTET_LENGTH [LENGTH]" & _
                    " From " & CStr(txtDBName2.Text) & ".information_schema.columns"

                dSet1 = New DataSet
                sqlAdapt = New SqlClient.SqlDataAdapter(pstrSQL, sqlConn2)
                sqlAdapt.Fill(dSet1)
                sqlAdapt.Dispose()

                pstrSQL = "Select TABLE_CATALOG [DB NAME],TABLE_NAME [TABLE NAME],COLUMN_NAME [COLUMN NAME],DATA_TYPE [DATA TYPE]," & _
                                    " COLUMN_DEFAULT [DEFAULT],IS_NULLABLE [NULLABLE],CHARACTER_OCTET_LENGTH [LENGTH]" & _
                                    " From " & CStr(txtDBName1.Text) & ".information_schema.columns"

                dSet2 = New DataSet
                sqlAdapt = New SqlClient.SqlDataAdapter(pstrSQL, sqlConn1)
                sqlAdapt.Fill(dSet2)
                sqlAdapt.Dispose()

                If (dSet1.Tables(0).Rows.Count > 0 And dSet2.Tables(0).Rows.Count > 0) Then
                    dTable2 = New DataTable
                    fncCompareDatabase(dTable2, dgrdMissing2)
                    dgrdMissing2.DataSource = dTable2
                End If

                dSet1.Dispose() : dSet2.Dispose()
            Catch ex As Exception
            Finally
                If sqlConn1.State = ConnectionState.Open Then sqlConn1.Close()
                If sqlConn2.State = ConnectionState.Open Then sqlConn2.Close()
                Me.Cursor = Cursors.Default
            End Try
        End If
    End Sub
    Private Function fncControlsValidation() As Boolean
        fncControlsValidation = True
        'Database - 1
        If Len(txtServer1.Text) = 0 Then
            MsgBox("Database -1 Server name should not be empty!", MsgBoxStyle.Information)
            txtServer1.Focus()
            fncControlsValidation = False
            Exit Function
        End If

        If Len(txtDBName1.Text) = 0 Then
            MsgBox("Database -1 name should not be empty!", MsgBoxStyle.Information)
            txtDBName1.Focus()
            fncControlsValidation = False
            Exit Function
        End If

        If Len(txtUserID1.Text) = 0 Then
            MsgBox("Database -1 User ID should not be empty!", MsgBoxStyle.Information)
            txtUserID1.Focus()
            fncControlsValidation = False
            Exit Function
        End If

        If Len(txtPassword1.Text) = 0 Then
            MsgBox("Database -1 Password should not be empty!", MsgBoxStyle.Information)
            txtPassword1.Focus()
            fncControlsValidation = False
            Exit Function
        End If

        'Database - 2

        If Len(txtServer2.Text) = 0 Then
            MsgBox("Database -2 Server name should not be empty!", MsgBoxStyle.Information)
            txtServer2.Focus()
            fncControlsValidation = False
            Exit Function
        End If

        If Len(txtDBName2.Text) = 0 Then
            MsgBox("Database -2 name should not be empty!", MsgBoxStyle.Information)
            txtDBName2.Focus()
            fncControlsValidation = False
            Exit Function
        End If

        If Len(txtUserID2.Text) = 0 Then
            MsgBox("Database -2 User ID should not be empty!", MsgBoxStyle.Information)
            txtUserID2.Focus()
            fncControlsValidation = False
            Exit Function
        End If

        If Len(txtPassword2.Text) = 0 Then
            MsgBox("Database -2 Password should not be empty!", MsgBoxStyle.Information)
            txtPassword2.Focus()
            fncControlsValidation = False
            Exit Function
        End If

        strConn1 = "Persist Security Info=False;User ID=" & Replace(CStr(txtUserID1.Text), "'", "''") & ";pwd=" & Replace(CStr(txtPassword1.Text), "'", "''") & "; Initial Catalog=" & Replace(CStr(txtDBName1.Text), "'", "''") & ";Data Source=" & Replace(CStr(txtServer1.Text), "'", "''")
        strConn2 = "Persist Security Info=False;User ID=" & Replace(CStr(txtUserID2.Text), "'", "''") & ";pwd=" & Replace(CStr(txtPassword2.Text), "'", "''") & "; Initial Catalog=" & Replace(CStr(txtDBName2.Text), "'", "''") & ";Data Source=" & Replace(CStr(txtServer2.Text), "'", "''")

        If fncVerifyConnection1(strConn1) = False Then
            fncControlsValidation = False
            MsgBox("Database - 1 Connection failed!", MsgBoxStyle.Information)
            Exit Function
        End If

        If fncVerifyConnection2(strConn2) = False Then
            fncControlsValidation = False
            MsgBox("Database - 2 Connection failed!", MsgBoxStyle.Information)
            Exit Function
        End If

    End Function
    Private Function fncVerifyConnection1(ByVal strConnection) As Boolean
        fncVerifyConnection1 = True
        Try
            sqlConn1 = New SqlClient.SqlConnection(strConnection)
            sqlConn1.Open()
        Catch ex As Exception
            fncVerifyConnection1 = False
        End Try
    End Function
    Private Function fncVerifyConnection2(ByVal strConnection) As Boolean
        fncVerifyConnection2 = True
        Try
            sqlConn2 = New SqlClient.SqlConnection(strConnection)
            sqlConn2.Open()
        Catch ex As Exception
            fncVerifyConnection2 = False
        End Try
    End Function
    Private Sub txtDBName1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtDBName1.KeyPress
        If e.KeyChar = "'" Then
            e.Handled = True
        End If
    End Sub

    Private Sub txtDBName2_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtDBName2.KeyPress
        If e.KeyChar = "'" Then
            e.Handled = True
        End If
    End Sub

    Private Sub txtPassword2_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtPassword2.KeyPress
        If e.KeyChar = "'" Then
            e.Handled = True
        End If
    End Sub

    Private Sub txtPassword1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtPassword1.KeyPress
        If e.KeyChar = "'" Then
            e.Handled = True
        End If
    End Sub

    Private Sub txtServer1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtServer1.KeyPress
        If e.KeyChar = "'" Then
            e.Handled = True
        End If
    End Sub

    Private Sub txtServer2_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtServer2.KeyPress
        If e.KeyChar = "'" Then
            e.Handled = True
        End If
    End Sub

    Private Sub txtUserID1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtUserID1.KeyPress
        If e.KeyChar = "'" Then
            e.Handled = True
        End If
    End Sub

    Private Sub txtUserID2_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtUserID2.KeyPress
        If e.KeyChar = "'" Then
            e.Handled = True
        End If
    End Sub
    Private Sub fncCompareDatabase(ByVal dT As DataTable, ByVal refGrid As DataGrid)
        Dim iCount1 As Integer
        Dim iCount2 As Integer
        Dim blnExist As Boolean
        'Dim A1, B1, C1, D1, E1, F1 As String
        'Dim A2, B2, C2, D2, E2, F2 As String

        'Initialise dTable1
        'dT = New DataTable
        dT.Columns.Add("DB NAME")
        dT.Columns.Add("TABLE NAME")
        dT.Columns.Add("COLUMN NAME")
        dT.Columns.Add("DATA TYPE")
        dT.Columns.Add("DEFAULT")
        dT.Columns.Add("NULLABLE")
        dT.Columns.Add("LENGTH")

        fncInitialiseGridStyle(refGrid)

        For iCount1 = 0 To dSet1.Tables(0).Rows.Count - 1
            If IsModified(dSet1.Tables(0).Rows(iCount1), dSet2.Tables(0)) Then
                'blnExist = True
            Else
                dRow1 = dT.NewRow
                dRow1("DB NAME") = CStr(dSet1.Tables(0).Rows(iCount1).Item(0))
                dRow1("TABLE NAME") = CStr(dSet1.Tables(0).Rows(iCount1).Item(1))
                dRow1("COLUMN NAME") = CStr(dSet1.Tables(0).Rows(iCount1).Item(2))
                dRow1("DATA TYPE") = CStr(dSet1.Tables(0).Rows(iCount1).Item(3))
                dRow1("DEFAULT") = CStr(IIf(IsDBNull(dSet1.Tables(0).Rows(iCount1).Item(4)) = True, "", dSet1.Tables(0).Rows(iCount1).Item(4)))
                dRow1("NULLABLE") = CStr(dSet1.Tables(0).Rows(iCount1).Item(5))
                dRow1("LENGTH") = CStr(IIf(IsDBNull(dSet1.Tables(0).Rows(iCount1).Item(6)) = True, "", dSet1.Tables(0).Rows(iCount1).Item(6)))
                dT.Rows.Add(dRow1)
            End If
        Next
    End Sub
    Private Sub fncInitialiseGridStyle(ByVal grid As DataGrid)
        Dim tableStyle As DataGridTableStyle
        Dim colStyle As New DataGridBoolColumn

        tableStyle = New DataGridTableStyle
        tableStyle.SelectionBackColor = Color.Yellow
        tableStyle.SelectionForeColor = Color.Red
        tableStyle.PreferredRowHeight = 25

        Dim colTableName As New DataGridTextBoxColumn
        With colTableName
            .MappingName = "TABLE NAME"
            .HeaderText = "TABLE NAME"
            .Width = 120
        End With
        tableStyle.GridColumnStyles.Add(colTableName)

        Dim colColumnName As New DataGridTextBoxColumn
        With colColumnName
            .MappingName = "COLUMN NAME"
            .HeaderText = "COLUMN NAME"
            .Width = 100
        End With
        tableStyle.GridColumnStyles.Add(colColumnName)

        Dim colDataType As New DataGridTextBoxColumn
        With colDataType
            .MappingName = "DATA TYPE"
            .HeaderText = "DATA TYPE"
            .Width = 70
        End With
        tableStyle.GridColumnStyles.Add(colDataType)

        Dim colDefault As New DataGridTextBoxColumn
        With colDefault
            .MappingName = "DEFAULT"
            .HeaderText = "DEFAULT"
            .Width = 70
        End With
        tableStyle.GridColumnStyles.Add(colDefault)

        With colStyle
            .MappingName = "NULLABLE"
            .HeaderText = "NULLABLE"
            .Alignment = HorizontalAlignment.Center
            .Width = 70
            .NullValue = "No"
            .TrueValue = "Yes"
            .FalseValue = "No"
        End With
        tableStyle.GridColumnStyles.Add(colStyle)

        Dim colLength As New DataGridTextBoxColumn
        With colDefault
            .MappingName = "LENGTH"
            .HeaderText = "LENGTH"
            .Width = 70
        End With
        tableStyle.GridColumnStyles.Add(colLength)

        grid.TableStyles.Add(tableStyle)
        grid.ReadOnly = True
    End Sub
    Private Function IsModified(ByVal oDRPrevious As DataRow, ByVal oDRCurrent As DataTable) As Boolean
        'need code to compare columns in each row
        IsModified = False
        Dim i, iCount As Integer
        For iCount = 0 To oDRCurrent.Rows.Count - 1
            For i = 1 To oDRPrevious.ItemArray.Length - 1
                If IIf(IsDBNull(oDRPrevious.Item(i)) = True, "", oDRPrevious.Item(i)) = IIf(IsDBNull(oDRCurrent.Rows(iCount).Item(i)) = True, "", oDRCurrent.Rows(iCount).Item(i)) Then
                    IsModified = True
                Else
                    IsModified = False
                    Exit For
                End If
            Next
            If IsModified = True Then
                Return True
            End If
        Next
    End Function

    Private Sub dgrdMissing1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgrdMissing1.DoubleClick


    End Sub
End Class

I am Hari, SSE in a Pvt Firm. I have knowledge in Dot Net, VB6, Visual Foxpro, Foxpro, SQL Server, Oracle, Access, DBase and Crystal Report.

Comments

Read Comments | Post Comment

No one has posted a comment yet. Be the first.

Search

Code Samples

» Optional Arguments in JScript
» Loading Huge Files
» Boot RegKey API
» Messenger Client Coding
» Read text files into ADO recordsets

New Members

» vimlendu kumar in India
» MK in Israel
» CK in Belgium
» Harold Brown in United States
» arockia in India
» chinnaraju in India