serialize datatable to a binary file , then add new rows to file

[waleed.makarem]

Dear Experts,

Problem :

I need to save a datatable to a binary file , in binary format , in order to make the process fast because the datatable may contain up ten millions rows. So , XML is not favorable because it makes the file large sized , and the process will be slow.
I managed to save the datatable to a binary file , and it works fine , but the problem when I try to add new rows to the existing binary file >> it does not work. Is it possible to serialize datarows and add it the binary file ?

My Code:

Function GetTable() As DataTable

        Dim table As New DataTable     ' Create new DataTable instance.

        table.Columns.Add("Dosage", GetType(Integer))  ' Create four typed columns in the DataTable.
        table.Columns.Add("Drug", GetType(String))
        table.Columns.Add("Patient", GetType(String))
        table.Columns.Add("Date", GetType(DateTime))
        ' Add five rows with those columns filled in the DataTable.
        table.Rows.Add(25, "Indocin", "David", DateTime.Now)
        table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now)
        table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now)
        table.Rows.Add(21, "Combivent", "Janet", DateTime.Now)
        table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now)
        Return table
    End Function

     Private Sub SaveDataTabletoBinary()
        dt = GetTable()

        Dim format As New Binary.BinaryFormatter
        Dim ds As New DataSet
        ' ds = DataGridView1.DataSource

        Using fs As New FileStream("c:\sar1.txt", FileMode.Append)
            dt.RemotingFormat = SerializationFormat.Binary

            'Other option is SerilaizationFormat.XML 
            format.Serialize(fs, ds)
        End Using
    End Sub

Thanks

[Reverend Jim]

Instead of asking how to implement a particular solution, you may want to ask youself if the solution you have picked is appropriate for the problem you are trying to solve. Perhaps if you tell us the underlying goal we may be able to offer a better approach.

[waleed.makarem]

Dear Jim,

Thanks for your feedback , I appreciate it so much.

Regarding your inquiry , I need to save a datatable to a binary table , then update the binary file . I have made it by using stream and binarywriter. I thought there were a simple solution for it.

Thanks for your feedback.

[adam_k]

May I ask why go with a file in the first place? Why invent the wheel all over, when there are dbs you can use?
Handling 10 million records for mySQL or MS SQL (even for Express) will be a walk in the park and you get all the goodies that come with such a solution as backups, transactions, automatic management of stuff like memory, among others.

[ret801]

Im sorry I can't answer this myself, but man isn't it so hard to get a straight answers with these nerds online these days?

[Reverend Jim]

My point is that instead of trying to pickle (pyrthon term) a datatable, you might be better off exporting the data directly from the database to a file.

@ret801 - save your replies for constructive suggestions or corrections when someone has posted something incorrect or misleading. We "nerds" are attempting to offer help. Help does not necessarily mean helping the OP to implement a bad solution. It also consists of suggesting better approaches to a problem. The first step in offering that assistance is determining exactly what problem the OP is trying to solve. The answer to the question "how do I install drywall with a hammer" is not a step-by-step procudure on how to do just that. The answer is "instead of a hammer you should be using ...". This help is offered by people who are volunteering their expertise for free and in their own spare time. We try to give the best advice that we are able.