storing data locally?

[Cip]

Hi,

in a nutshell, is there a quick and efficient way of storing data from
a database on each user's machine so that a WinForm application can
query/analyze/do whatever it wishes to this local data?

the winform app will never need to interact with the original database
except the first time it is run - to create the local data files on
each user's computer. From then on, each time a user runs the
application, the app would be using the local data.

I thought of saving the data as XML files... but I am concerned about
efficiency. As well, I do not need the files to be viewed through
other mediums (web, excel, etc.).

Actually, I would prefer it if the local files are NOT viewable in
other mediums... only the WinForm application would use the files.

If I am able to query data in these local files using SQL (like
"select myCol From file1.dat") that would be also be a huge plus....


I also thought of loading a DataSet object then serializing it to a
file... any links/advice/help on this? I couldnt really find any
specific examples/white papers on serializing data, but I am still
looking.


Any other suggestions? Thank you.

 

[Marc Scheuner [MVP ADSI]]

in a nutshell, is there a quick and efficient way of storing data from

a database on each user's machine so that a WinForm application can
query/analyze/do whatever it wishes to this local data?

Yes, sure, serialize your DataSet onto a local file, either by means
of the BinaryFormatter or the SOAPFormatter (XML).

Check out the MSDN Docs on ISerializable interface or the two
formatters.

Marc
================================================================
Marc Scheuner May The Source Be With You!
Bern, Switzerland m.scheuner(at)inova.ch

 

[Miha Markic [MVP C#]]

Hi Cip,

Beside the ways Marc suggested there are also DataSet.WriteXml and ReadXml
methods you might find useful.
BTW there is no significant difference between binary and xml serialization
in terms of space.

 

[Cip]

what about performance of binary vs xml serialization vs
dataset.write/readXML ?

I am not concerned in terms of space or performance in serializing...
my only concern is performance in deserializing (ie loading) the
DataSet.

I have read the article "Serializing Objects" in the .NET Guide, my
initial suspicion is that Binary serialization may be a bit faster
since I do not require any portability associated with XML... i dunno
it just seems that XML serializing or dataset.write/read XML *should*
carry some overhead vs binary serialization.

am i right/wrong? any other last comments would be appreciated.

I guess the only way to be sure is run tests myself, but from both of
your replies it seems that performance differences will be
nonexistant. If that is the case I will still use the binary
formatter since my winForm app will be completely isolated on each
user's machine - data portability across mediums is not an issue here.

thanks again for your time.

Hi Cip,

Beside the ways Marc suggested there are also DataSet.WriteXml and ReadXml
methods you might find useful.
BTW there is no significant difference between binary and xml serialization
in terms of space.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Hi,

in a nutshell, is there a quick and efficient way of storing data from
a database on each user's machine so that a WinForm application can
query/analyze/do whatever it wishes to this local data?

the winform app will never need to interact with the original database
except the first time it is run - to create the local data files on
each user's computer. From then on, each time a user runs the
application, the app would be using the local data.

I thought of saving the data as XML files... but I am concerned about
efficiency. As well, I do not need the files to be viewed through
other mediums (web, excel, etc.).

Actually, I would prefer it if the local files are NOT viewable in
other mediums... only the WinForm application would use the files.

If I am able to query data in these local files using SQL (like
"select myCol From file1.dat") that would be also be a huge plus....


I also thought of loading a DataSet object then serializing it to a
file... any links/advice/help on this? I couldnt really find any
specific examples/white papers on serializing data, but I am still
looking.


Any other suggestions? Thank you.

 

[Cip]

actually i just found an amazing article at

http://msdn.microsoft.com/msdnmag/issues/02/12/cuttingedge/default.aspx?

it pretty much explains the architectural differences between all the
options we have brought up in the thread.

The binary formatter when used with DataSet and DataTable objects
actually does output XML so any significant reduction in space
normally attributed to binary formatting is wasted... this might be a
'feature' of .NET maybe?

my solution - since portability is not an issue - i will just
implement my own Serializable Class which serializes dataTable
objects. The article discusses this option as well.

That way I will achieve TRUE binary serialization, and hopefully have
faster deserialization times. (my only concern is performance
deserializing... as i mentioned before serializing performance and
space used is no concern)

any other thoughts?

Hi Cip,

Beside the ways Marc suggested there are also DataSet.WriteXml and ReadXml
methods you might find useful.
BTW there is no significant difference between binary and xml serialization
in terms of space.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Hi,

in a nutshell, is there a quick and efficient way of storing data from
a database on each user's machine so that a WinForm application can
query/analyze/do whatever it wishes to this local data?

the winform app will never need to interact with the original database
except the first time it is run - to create the local data files on
each user's computer. From then on, each time a user runs the
application, the app would be using the local data.

I thought of saving the data as XML files... but I am concerned about
efficiency. As well, I do not need the files to be viewed through
other mediums (web, excel, etc.).

Actually, I would prefer it if the local files are NOT viewable in
other mediums... only the WinForm application would use the files.

If I am able to query data in these local files using SQL (like
"select myCol From file1.dat") that would be also be a huge plus....


I also thought of loading a DataSet object then serializing it to a
file... any links/advice/help on this? I couldnt really find any
specific examples/white papers on serializing data, but I am still
looking.


Any other suggestions? Thank you.

 

[Miha Markic [MVP C#]]

Hi Cip,

actually i just found an amazing article at

http://msdn.microsoft.com/msdnmag/issues/02/12/cuttingedge/default.aspx?

it pretty much explains the architectural differences between all the
options we have brought up in the thread.

The binary formatter when used with DataSet and DataTable objects
actually does output XML so any significant reduction in space
normally attributed to binary formatting is wasted... this might be a
'feature' of .NET maybe?

my solution - since portability is not an issue - i will just
implement my own Serializable Class which serializes dataTable
objects. The article discusses this option as well.

That way I will achieve TRUE binary serialization, and hopefully have
faster deserialization times. (my only concern is performance
deserializing... as i mentioned before serializing performance and
space used is no concern)

any other thoughts?

You've got it.
A while ago I saw a greate binary serialization for datasets, however I
don't have a link at this time.
If I find it, I'll post it.

 

[Guest]

Hi Miha

I hope you'll find it, we've been looking for something like this for a few days. I guess the main problem with Deserialization of datasets is the TIME it takes! We're trying to pass datasets with the .NET remoting architecture and the Deserialization kills the performance


----- Miha Markic [MVP C#] wrote: ----

<message cut
You've got it
A while ago I saw a greate binary serialization for datasets, however
don't have a link at this time
If I find it, I'll post it

 

[Ravi[MSFT]]

We are aware of the performance issue of remoting dataset in V1.x. We will
be fixing it for the next version. In the mean time, to alleviate the
performance issue associated with dataset remoting in V1.x, we've created an
interim surrogate class [sort of a wrapper around DataSet]. You basically
remote this surrogate object over the wire and convert it back into DataSet
after the surrogate object gets deserialized. You can see the MSDN KB
article on the DataSetSurrogate article at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;829740

Thanks,
Ravi

Hi Miha,

I hope you'll find it, we've been looking for something like this for a

few days. I guess the main problem with Deserialization of datasets is the
TIME it takes! We're trying to pass datasets with the .NET remoting
architecture and the Deserialization kills the performance.

----- Miha Markic [MVP C#] wrote: -----

<message cut>
You've got it.
A while ago I saw a greate binary serialization for datasets, however I
don't have a link at this time.
If I find it, I'll post it.

 

[Cip]

Hi, I have successfully created my own wrapper classes for truly
binary serializing DataTable objects.

Deserializing a bin file to a DataTable with 80,000 rows takes about 5
seconds which is great performance (for my use)

However, deserializing a bin file with 175,000 rows takes about 3
minutes. I have tried several times and these results are consistent.

~80K rows: ~5 seconds
~170K rows: ~3 minutes

what is going on? does it have something to do with Page File usage or
something?

Does anyone have any other ideas concerning ways to "store data
locally", other than binary files?

A friend of mine suggested looking into installing a local database
server on each user's machine and keeping all the local data in the
database. He suggested looking into a free openSource db, called
FireBird. I am currently reading up on this, and seeing if it can
solve my problem. Apparently I can find .NET providers for FireBird
as well.

any other possible solutions? has anyone heard of/used FireBird?

thanks again.

 

[Cip]

concerning my last post,

I should mention that I would REALLY REALLY prefer using binary files
rather than resorting to an Embedded DB server (like FireBird, MSDE,
etc)

In my opinion, using binary files would result in a LOT less
headaches.

Are binary files w/ 170,000 rows of data pushing it? Do I have no
other choice?

 

[Miha Markic [MVP C#]]

Hi everybody,

Here is the link:
http://www.eggheadcafe.com/articles/20031219.asp

True Binary Serialization and
Compression of DataSets
By Peter A. Bromberg, Ph.D.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Hi Miha,

I hope you'll find it, we've been looking for something like this for a

few days. I guess the main problem with Deserialization of datasets is the
TIME it takes! We're trying to pass datasets with the .NET remoting
architecture and the Deserialization kills the performance.

----- Miha Markic [MVP C#] wrote: -----

<message cut>
You've got it.
A while ago I saw a greate binary serialization for datasets, however I
don't have a link at this time.
If I find it, I'll post it.

 

[Alex Kourkoumelis]

Could I see that wrapper class?

 

[Cip]

Ok, the Class is at the end of the msg.

BTW, I have decided to scrap the whole idea of serializing DataTables.
I am actually using an Embedded Database server, SQLite.

As I mentioned before, I just need a way to store data and then select
it. Deserializing DataTables takes waaay too long, and using MSDE was
overkill for my use since I do not need any DB functionality.

SQLite serves its purpose well. I found ADO.NET providers for SQLite
as well... works like a charm! Just a very small DLL, no client
install... everything is embedded and runs VERY fast.

Well, here is the class I WAS using, it is courtesy of the article
referenced in one of my previous posts (modified to VB)

Thanks!

Imports System
Imports System.Collections
Imports System.Data

<Serializable()> _
Public Class DataTableSerializer
Protected colNames As ArrayList
Protected colTypes As ArrayList
Protected dataRows As ArrayList

Public Sub New()
colNames = New ArrayList
colTypes = New ArrayList
dataRows = New ArrayList
End Sub

Public Sub Load(ByVal dt As DataTable)
For Each col As DataColumn In dt.Columns
colNames.Add(col.ColumnName)
colTypes.Add(col.DataType.FullName)
Next col

For Each row As DataRow In dt.Rows
dataRows.Add(row.ItemArray)
Next row
End Sub

Public Function Save() As DataTable
Dim dt As New DataTable
Dim col As DataColumn
Dim row As DataRow

For i As Integer = 0 To colNames.Count - 1
col = New DataColumn(colNames(i).ToString(), _
Type.GetType(colTypes(i).ToString()))
dt.Columns.Add(col)
Next i

For i As Integer = 0 To dataRows.Count - 1
row = dt.NewRow()
row.ItemArray = CType(dataRows(i), Object())
dt.Rows.Add(row)
Next i

dt.AcceptChanges()
Return dt
End Function

End Class

'and methods using the Class:
Imports System.IO
Imports System.Runtime.Serialization.Formatters.Binary

Private Function BinaryDeserialize(ByVal sourceFile As String) As
DataTable
Dim bf As New BinaryFormatter
Dim reader As New StreamReader(sourceFile)
Dim dtSerializer As New DataTableSerializer

dtSerializer = CType(bf.Deserialize(reader.BaseStream),
DataTableSerializer)
reader.Close()

Return dtSerializer.Save()
End Function


Private Function BinarySerialize(ByVal dt As DataTable, ByVal
outputFile As String)
Dim bin As New BinaryFormatter
Dim dat As New StreamWriter(outputFile)

Dim dtSerializer As New DataTableSerializer
dtSerializer.Load(dt)


bin.Serialize(dat.BaseStream, dtSerializer)
dat.Close()
End Function

 

[Cip]

wow very impressive stuff. I have tested it out and am very pleased.

thanks a lot for this link, I am gonna show this to more ppl for sure