Mohammad Zulfikar Mohammad Zulfikar - 5 months ago 17
Vb.net Question

How to refresh the Datagridview when the Database has been restored from .bak without restarting the app using Vb.Net?

I have created a built in Backup/ Restore application is which I am able to Backup & Restore successfully but when restored successfully I have to close the app and restart it again to view the changes but I don't want to close the app instead I want it to appear the changes immediately after successfully restored, so is it possible? If yes then how?

Screenshot of the form:
Screenshot of the form

Current Code:

Imports Microsoft.SqlServer.Server
Imports MySql.Data.MySqlClient
Imports System.Data.SqlClient

Public Class Test


Sub Backup()
With SaveFileDialog1
.FileName = ""
.Filter = "Backup File|*.bak"
SaveFileDialog1.ShowDialog()
If .FileName = "" Then
Exit Sub
Else
Dim BUDate As New TextBox
BUDate.Text = "Backup database BISDB To Disk='" + SaveFileDialog1.FileName + "'"
Dim con As New SqlClient.SqlConnection("data source=.\SQLEXPRESS;initial catalog=BISDB;Integrated Security=True")
Dim cmd As New SqlCommand()

Try
con.Open()
cmd.CommandType = CommandType.Text
cmd.CommandText = BUDate.Text
cmd.Connection = con
cmd.ExecuteNonQuery()
MsgBox("Backup successfull!", MsgBoxStyle.OkOnly, "Backup")
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End If
End With


End Sub

Private Sub Test_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.StudentsTableAdapter.Fill(Me.BIS.Students)

End Sub

Private Sub btnCalculate_Click(sender As Object, e As EventArgs) Handles btnCalculate.Click
Backup()
End Sub

Private Sub btnRestore_Click(sender As Object, e As EventArgs) Handles btnRestore.Click
With OpenFileDialog1
.FileName = ""
.Filter = "Backup File|*.bak"
OpenFileDialog1.ShowDialog()
If .FileName = "" Then
Exit Sub
Else
Dim FileName As String = OpenFileDialog1.FileName
Dim BUDate As New TextBox
BUDate.Text = "RESTORE DATABASE BISDB FROM Disk='" + FileName + "'"
Dim con2 As New SqlClient.SqlConnection("data source=.\SQLEXPRESS;initial catalog=master;Integrated Security=True")
Dim cmd = New SqlCommand(BUDate.Text, con2)

Try
con2.Open()
cmd.ExecuteNonQuery()
MsgBox("Restore Successfull!")
Me.StudentsTableAdapter.Fill(Me.BIS.Students) '//Edited According to Vignesh Kumar's Answer
Me.StudentsBindingSource.ResetBindings(False) '//Edited According to GuidoG's Answer
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
con2.Close()
con2.Dispose()

End Try
End If
End With
End Sub

Private Sub btnClose_Click(sender As Object, e As EventArgs) Handles btnClose.Click
'Me.BIS.Reset()
'Me.StudentsBindingSource.Dispose()
'Me.StudentsBindingSource.ResetAllowNew()
Me.StudentsBindingSource.ResetBindings(True)
Me.StudentsBindingSource.SuspendBinding()
Me.StudentsBindingSource.ResumeBinding()

Me.StudentsTableAdapter.Fill(Me.BIS.Students)
End Sub
End Class





Names of itmes:
Refresh Button - btnClose
Backup Button - btnCalculate
Restore Button - btnRestore
Datagridview - DataGridView1

Form - Test



I Start the application by clicking on Start Button :
enter image description here

I use bindingsource to fill the DataGridView and don't use any code to fill the DataGridView but
Me.StudentsTableAdapter.Fill(Me.BIS.Students)

While loading the form.

Answer

I guess that you have to refill your datagrid after a succesfull restore.
Look for the comment
// Refill the datagrid after succesfull restore
in my answer

Private Sub
    With OpenFileDialog1
        .FileName = ""
        .Filter = "Backup File|*.bak"
        OpenFileDialog1.ShowDialog()
        If .FileName = "" Then
            Exit Sub
        Else
            Dim FileName As String = OpenFileDialog1.FileName
            Dim BUDate As New TextBox
            BUDate.Text = "RESTORE DATABASE BISDB FROM Disk='" + FileName + "'"
            Dim con2 As New SqlClient.SqlConnection("data source=.\SQLEXPRESS;initial catalog=master;Integrated Security=True")
            Dim cmd = New SqlCommand(BUDate.Text, con2)

            Try
                con2.Open()
                cmd.ExecuteNonQuery()

                // Refill the datagrid after succesfull restore
                Me.StudentsTableAdapter.Fill(Me.BIS.Students)

                MsgBox("Restore Successfull!")

            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Finally
                con2.Close()
                con2.Dispose()

            End Try
        End If
    End With
    End Sub