What's a good database for a really simple app?

[dgk]

I'm planning on writing a fairly small app (VB2005) to be distributed
via shareware that needs a single user database. I don't even think
that SQL Server Express edition is needed since I think that it is
essentially a true DB that runs all the time.

I really need something like Access and was thinking about using it
but there is so little documentation available on using Access from
DotNet that I have to feel that it is not the answer. Cor had an
example from two years back and that is about it.

So, perhaps the answer is just to create an in-memory dataset and use
it as needed, then serialize it when done and read it back in as
needed? What are the liabilities of an approach like this or some
other alternatives?

 

[Guest]

I'm planning on writing a fairly small app (VB2005) to be distributed
via shareware that needs a single user database. I don't even think
that SQL Server Express edition is needed since I think that it is
essentially a true DB that runs all the time.

I really need something like Access and was thinking about using it
but there is so little documentation available on using Access from
DotNet that I have to feel that it is not the answer. Cor had an
example from two years back and that is about it.

So, perhaps the answer is just to create an in-memory dataset and use
it as needed, then serialize it when done and read it back in as
needed? What are the liabilities of an approach like this or some
other alternatives?

There is a ton of stuff about using Access from DotNet. The main
difference is just the connection string and you using a different
class. If you feel comfortable using SQL from DotNet, then using Access
will be a piece of cake.

Chris

 

[Russ Green]

I've done quite a lot with small apps and access databases and think its
probably a really good solution. There are plenty of examples out on the
net for use access from VB.NET.

 

[dgk]

There is a ton of stuff about using Access from DotNet. The main
difference is just the connection string and you using a different
class. If you feel comfortable using SQL from DotNet, then using Access
will be a piece of cake.

Chris

Actually the sticking point was CREATING an Access database from
DotNet - that's what there isn't much documentation about. I don't
want to ship an empty database; if it doesn't exist then I want to
create it on the fly. I did it using VB6 a while back. For DotNet,
Cor's example requires a reference to ADOX ext 2.0 and:

Dim catNewDB as New ADOX.Catalog
catNetDB.Create("Provider=Microsoft ...

I guess I try it and if it still works (the example is from two years
ago) then I guess I can do it. I hope the Install Wizard and ClickOnce
is up to the challenge.

The idea of just creating a DB in memory and serializing it is still
tempting though.

 

[Cor Ligthert [MVP]]

dgk,

A search in this newsgroup will surely give you this link, as well has Paul
Clement written a lot about this.

http://www.vb-tips.com/default.aspx?ID=e76b8450-4c8a-4662-8f41-d6dda3c888c8

The same kind of samples are on our website for SQLServer and MySQL

I hope this helps,

Cor

 

[dgk]

dgk,

A search in this newsgroup will surely give you this link, as well has Paul
Clement written a lot about this.

http://www.vb-tips.com/default.aspx?ID=e76b8450-4c8a-4662-8f41-d6dda3c888c8

The same kind of samples are on our website for SQLServer and MySQL

I hope this helps,

Cor

Yes, that's the one. You had posted it two years ago. Still, what's
the downside of creating a dataset in memory? My thought is to check
if it's on disk at startup, create it if not, and serialize it as the
app is shut down. I think that there's even an option to serialize it
in a binary format now but XML would be sufficient. All I need are a
few tables and if there are even a hundred rows for any of them I will
be (happily) surprised.

It's kind of like having a database without any database software at
all. No versioning problems, no deployment issues. It won't work for
anyone needing even a two user app, but for a single user app it
should work.

Of course, I can't use Access to look at it in a pinch. But maybe I
can. Access can probably import a database from XML, and if not, it
should be able to.

 

[m.posseth]

well if it is a small amount of data

you could also create the stuff as a object database and just serialize
and deserialize it to disk on startup and shutdown of your app

or just throw everything in a dataset and retrieve and store it to XML

regards

Michel Posseth [MCP]

 

[Juliano]

Hi,

I think you should take a look at
http://bbooprevalence.sourceforge.net.

I've been using it for some projects and I'm very happy with the
results. It's pretty simple, efficient, and you won't have all the
problems with Object-Relational mapping.

Juliano

 

[Cor Ligthert [MVP]]

DGK,

Of course, I can't use Access to look at it in a pinch. But maybe I
can. Access can probably import a database from XML, and if not, it
should be able to.

For the single user database as you want probably nothing.

Be aware that you keep good track that you only delete an XML file when you
are sure that it is good written. (Not overwrite it, however by instance
first rename it, write it and after that delete the renamed file)

If it becomes really huge, than it will probably will need to much reading
and writing time.

In the samples on our website is probably one that fits. By instance the one
about the images, that covers without that safe storing I talk about above
probably your question.

http://www.vb-tips.com/default.aspx?ID=0bf3f72d-b722-459d-8a46-38b5a2f7fdf0

Cor

 

[dgk]

DGK,


For the single user database as you want probably nothing.

Be aware that you keep good track that you only delete an XML file when you
are sure that it is good written. (Not overwrite it, however by instance
first rename it, write it and after that delete the renamed file)

If it becomes really huge, than it will probably will need to much reading
and writing time.

In the samples on our website is probably one that fits. By instance the one
about the images, that covers without that safe storing I talk about above
probably your question.

http://www.vb-tips.com/default.aspx?ID=0bf3f72d-b722-459d-8a46-38b5a2f7fdf0

Cor

It won't get that huge.

I knew I did this before. Here's creating the DB:

Dim RSdb As DataSet
RSdb = New DataSet("RSData")
' Manufacturers Table
Dim RSTB_Man As DataTable = RSdb.Tables.Add("Manufacturers")
Dim ManCol As DataColumn = RSTB_Man.Columns.Add("PriKey",
Type.GetType("System.Int32"))
RSTB_Man.PrimaryKey = New DataColumn() {ManCol}
RSTB_Man.Columns("PriKey").AutoIncrement = True
RSTB_Man.Columns.Add("Name", Type.GetType("System.String"))
RSTB_Man.Columns("Name").MaxLength = 40
RSTB_Man.Columns.Add("ContactURL",
Type.GetType("System.String"))
RSTB_Man.Columns("ContactURL").MaxLength = 60
RSTB_Man.Columns.Add("ContactPhone",
Type.GetType("System.String"))
RSTB_Man.Columns("ContactPhone").MaxLength = 20
' Vendors Table
Dim RSTB_Vendors As DataTable = RSdb.Tables.Add("Vendors")
Dim VenCol As DataColumn = RSTB_Vendors.Columns.Add("PriKey",
Type.GetType("System.Int32"))
RSTB_Vendors.PrimaryKey = New DataColumn() {VenCol}
RSTB_Vendors.Columns("PriKey").AutoIncrement = True
RSTB_Vendors.Columns.Add("Name",
Type.GetType("System.String"))
RSTB_Vendors.Columns("Name").MaxLength = 40
RSTB_Vendors.Columns.Add("ContactURL",
Type.GetType("System.String"))
RSTB_Vendors.Columns("ContactURL").MaxLength = 60
RSTB_Vendors.Columns.Add("ContactPhone",
Type.GetType("System.String"))
RSTB_Vendors.Columns("ContactPhone").MaxLength = 20

Add a little data:

Dim MyMan As System.Data.DataTable =
RSdb.Tables("Manufacturers")
Dim ManRow As System.Data.DataRow = MyMan.NewRow
ManRow("Name") = "Linksys"
MyMan.Rows.Add(ManRow)
ManRow = MyMan.NewRow
ManRow("Name") = "Belkin"
MyMan.Rows.Add(ManRow)

Save it to disk when done - but do save previous version first!:

Try
Dim MyPath As String = AppPath
If MyPath.Length > 0 And Not MyPath.EndsWith("\") Then
MyPath += "\"
End If
db.WriteXml(MyPath & "RSData.xml",
XmlWriteMode.WriteSchema)
Catch Badthing As Exception
MessageBox.Show("Failed to save dataset: " &
Badthing.Message, "Save Error", MessageBoxButtons.OK,
MessageBoxIcon.Warning)
End Try

And of course, read it in next time:

RSdb = New DataSet ' better instantiate it first
RSdb.ReadXml(MyPath & "RSData.xml", XmlReadMode.ReadSchema)

 

[dgk]

Hi,

I think you should take a look at
http://bbooprevalence.sourceforge.net.

I've been using it for some projects and I'm very happy with the
results. It's pretty simple, efficient, and you won't have all the
problems with Object-Relational mapping.

Juliano

It requires a Java runtime. I remember folks used to laugh at VB
because it required a runtime. Now all the good languages are wearing
one. However, I would like to avoid having my customers install Java
just for my benefit.

Also, compared to even something like Access, Prevalence is WAY
overkill for my project. I don't want to use SQL Express because I
don't want to have their machines running SQL all the time just to use
my app for fifteen minutes a week. I sure don't see a need to have the
data in memory all the time that their machine is up. RAM may be cheap
but most folks don't have enough as it is and anything that sits in
memory is chewing up cpu cycles as well.

This is a very interesting product and way of thinking about data, but
it doesn't meet my requirements. Also, there is a cuteness factor that
strikes me the wrong way, and I really don't take too much very
seriously. The whole site is filled with cute and/or sarcastic
language and it gets tiresome quickly. It isn't just me; in one of the
FAQs someone else complained about that and got, naturally, a
sarcastic answer. I think that it's counterproductive because it is an
interesting idea.

I think I go with plain old XML or Access for my app but thanks for
the pointer and I'll keep it in mind for something more appropriate.

 

[Greg Burns]

I'm planning on writing a fairly small app (VB2005) to be distributed
via shareware that needs a single user database. I don't even think
that SQL Server Express edition is needed since I think that it is
essentially a true DB that runs all the time.

I was under the impression that the new SQL Server Express did NOT run all
time. Isn't this one of it's major new features? It can load the database
into memory only when the app calls for it. And similarly releases it when
done.

Trying to find some links about this...

Greg

 

[Homer J Simpson]

I was under the impression that the new SQL Server Express did NOT run all
time. Isn't this one of it's major new features? It can load the database
into memory only when the app calls for it. And similarly releases it
when done.

So it seems.