Public Module BackupRestoreModule

    ' save all db
    Public Function SaveDB(ByVal FileName As String) As Boolean
        Try
            ' create the stream writer object
            Dim FS As New System.IO.StreamWriter(FileName)

            ' open DB connection and start a transaction
            Dim Conn As New OleDb.OleDbConnection
            Dim Tmp As New LMS_DSTableAdapters.BookTableAdapter
            Conn.ConnectionString = Tmp.Connection.ConnectionString
            Conn.Open()
            Dim Trans As OleDb.OleDbTransaction
            Trans = Conn.BeginTransaction


            ' save the tables
            If Not SaveTableToDisk(Conn, Trans, "Login", FS) Then
                Conn.Close()
                FS.Close()
                Return False
            End If
            If Not SaveTableToDisk(Conn, Trans, "Members", FS) Then
                Conn.Close()
                FS.Close()
                Return False
            End If
            If Not SaveTableToDisk(Conn, Trans, "Book", FS) Then
                Conn.Close()
                FS.Close()
                Return False
            End If
            If Not SaveTableToDisk(Conn, Trans, "BookBorrowed", FS) Then
                Conn.Close()
                FS.Close()
                Return False
            End If
            If Not SaveTableToDisk(Conn, Trans, "MemberFine", FS) Then
                Conn.Close()
                FS.Close()
                Return False
            End If
            FS.Close()
            Conn.Close()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function


    ' this function will save a table into disk
    Public Function SaveTableToDisk(ByVal Conn As OleDb.OleDbConnection, ByVal Trans As OleDb.OleDbTransaction, ByVal TableName As String, ByVal FS As System.IO.StreamWriter) As Boolean
        Try
            ' get the number of rows
            Dim CMD As New OleDb.OleDbCommand
            Dim SQL As String = "select count (*) from [" & TableName & "]"
            CMD.CommandText = SQL
            CMD.Connection = Conn
            CMD.Transaction = Trans


            Dim RowCount As Long = CMD.ExecuteScalar


            CMD.Dispose()

            ' get all the values
            SQL = "select * from [" & TableName & "]"

            CMD = New OleDb.OleDbCommand
            CMD.CommandText = SQL
            CMD.Connection = Conn
            CMD.Transaction = Trans

            Dim R = CMD.ExecuteReader

            Dim ColumnCount As Long = R.FieldCount

            ' write the info
            FS.WriteLine(TableName)
            FS.WriteLine(ColumnCount)
            FS.WriteLine(RowCount)
            Dim I As Integer
            For I = 0 To RowCount - 1
                R.Read()
                If Not WriteRowInfoToFile(R, FS) Then
                    Return False
                End If
            Next
            R.Close()
            CMD.Dispose()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function


    ' write the information of row into file
    Public Function WriteRowInfoToFile(ByVal R As OleDb.OleDbDataReader, ByVal FS As System.IO.StreamWriter)
        Try
            Dim I As Integer
            For I = 0 To R.FieldCount - 1
                Dim V As String = R.Item(I) & ""
                FS.WriteLine(V)
            Next
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function


    ' read a number of rows from file
    Public Function ReadFullRowInformationFromDisk(ByVal Fs As System.IO.StreamReader, ByVal ColumnCount As Integer) As List(Of String)
        Try
            Dim I As Integer
            Dim CV As New List(Of String)
            For I = 0 To ColumnCount - 1
                CV.Add(Fs.ReadLine)
            Next
            Return CV
        Catch ex As Exception
            Return Nothing
        End Try
    End Function
    ' load a table from backup
    Public Function LoadTableFromBackup(ByVal Conn As OleDb.OleDbConnection, ByVal Trans As OleDb.OleDbTransaction, ByVal FS As System.IO.StreamReader, ByVal TableName As String) As Boolean
        Try
            ' load basic information
            Dim TName As String = FS.ReadLine
            Dim ColumnCount As Long = FS.ReadLine
            Dim RCount As Long = FS.ReadLine

            If TableName <> TName Then
                Return False
            End If

            Dim InsertSQL As String = "insert into [" & TableName & "] Values ("
            Dim I As Integer
            For I = 0 To ColumnCount - 1
                InsertSQL = InsertSQL & ":" & I
                If I <> ColumnCount - 1 Then
                    InsertSQL = InsertSQL & ","
                End If
            Next
            InsertSQL = InsertSQL & ")"

            For I = 0 To RCount - 1
                Dim Values = ReadFullRowInformationFromDisk(FS, ColumnCount)
                If Values Is Nothing Then
                    Return False
                End If
                Dim CMD As New OleDb.OleDbCommand
                CMD.CommandText = InsertSQL
                CMD.Connection = Conn
                CMD.Transaction = Trans

                Dim J As Integer
                For J = 0 To Values.Count - 1
                    CMD.Parameters.AddWithValue(":" & J, Values(J))
                Next

                CMD.ExecuteNonQuery()
                CMD.Dispose()
            Next

            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function


    ' load all db
    Public Function LoadDB(ByVal FileName As String) As Boolean
        Dim Conn As New OleDb.OleDbConnection
        Dim Trans As OleDb.OleDbTransaction

        Try
            ' create the stream writer object
            Dim FS As New System.IO.StreamReader(FileName)

            ' open DB connection and start a transaction
            Dim Tmp As New LMS_DSTableAdapters.BookTableAdapter
            Conn.ConnectionString = Tmp.Connection.ConnectionString
            Conn.Open()
            Trans = Conn.BeginTransaction

            ' clear all tables
            Dim CMD As New OleDb.OleDbCommand
            CMD.CommandText = " delete from MemberFine"
            CMD.Connection = Conn
            CMD.Transaction = Trans
            CMD.ExecuteNonQuery()
            CMD.Dispose()

            CMD = New OleDb.OleDbCommand
            CMD.CommandText = " delete from BookBorrowed"
            CMD.Connection = Conn
            CMD.Transaction = Trans
            CMD.ExecuteNonQuery()
            CMD.Dispose()

            CMD = New OleDb.OleDbCommand
            CMD.CommandText = " delete from Book"
            CMD.Connection = Conn
            CMD.Transaction = Trans
            CMD.ExecuteNonQuery()
            CMD.Dispose()

            CMD = New OleDb.OleDbCommand
            CMD.CommandText = " delete from Members"
            CMD.Connection = Conn
            CMD.Transaction = Trans
            CMD.ExecuteNonQuery()
            CMD.Dispose()

            CMD = New OleDb.OleDbCommand
            CMD.CommandText = " delete from Login"
            CMD.Connection = Conn
            CMD.Transaction = Trans
            CMD.ExecuteNonQuery()
            CMD.Dispose()

            ' load the tables
            If Not LoadTableFromBackup(Conn, Trans, FS, "Login") Then
                Trans.Rollback()
                Conn.Close()
                FS.Close()
                Return False
            End If
            If Not LoadTableFromBackup(Conn, Trans, FS, "Members") Then
                Trans.Rollback()
                Conn.Close()
                FS.Close()
                Return False
            End If
            If Not LoadTableFromBackup(Conn, Trans, FS, "Book") Then
                Trans.Rollback()
                Conn.Close()
                FS.Close()
                Return False
            End If
            If Not LoadTableFromBackup(Conn, Trans, FS, "BookBorrowed") Then
                Trans.Rollback()
                Conn.Close()
                FS.Close()
                Return False
            End If
            If Not LoadTableFromBackup(Conn, Trans, FS, "MemberFine") Then
                Trans.Rollback()
                Conn.Close()
                FS.Close()
                Return False
            End If

            FS.Close()
            Trans.Commit()
            Conn.Close()
            Return True
        Catch ex As Exception
            Trans.Rollback()
            Conn.Close()
            Return False
        End Try
    End Function

End Module

-------------------------------------
i have five table in my database
i really dont know what is the problem..
in my previous project i use there is no problem
my previous project database have four table

Edited 3 Years Ago by Lle26: Change column to table

To take the back up in Text format on disk

Public Sub WritingTo_Textfiles(ByVal TableName As String)
          Try
               'Open connection here

               Dim line As String

               Dim mysqlCommand As SqlCommand
               mysqlCommand = New SqlCommand("SELECT * FROM " & TableName & " ", myconn)
               Dim myReader As SqlDataReader = mysqlCommand.ExecuteReader

               Dim fileName As String = "D:\" & TableName & ".txt"

               'create a stream object which can write text to a file
               Dim outputStream As StreamWriter = New StreamWriter(fileName)

               'Get all values from the current item on the reader as long as Read() returns true...
               Do While myReader.Read
                    'make an array the length of the available fields
                    Dim values(myReader.FieldCount - 1) As Object
                    'get all the field values
                    myReader.GetValues(values)
                    'write the text version of each value to a comma seperated string
                    line = String.Join("^", values)

                    'write the txt line to the file
                    outputStream.WriteLine(line)
               Loop

               myReader.Close()
               outputStream.Close()

               'close DB connection
               dbstatus = modMSSQL_DB.Close_DB_Connection(myconn)
          Catch ex As Exception
               MsgBox(ex.Message)
          End Try
     End Sub

When u need to take the backup call the function with table name

    WritingTo_Textfiles("Table name")
This article has been dead for over six months. Start a new discussion instead.