Creating Simple desktop database application

views:

answers:

Creating Simple desktop database application

Hi guys,

I am here to write a small database application that will be running in desktop (offline mode).

I am using MSAccess 2007 as my database file and trying to write code in vb.net.

I used to write the code vb6 an usually had global variables for storing database connection and executing every query from that.

I am trying to upgrade myself from vb6 to vb.net.

do i need to read some more simple starter books also?

Store the connection string in the config file (in the solution explorer, open the My Project folder and doubleclick on Settings.settings).

I'd suggest that you create one or more classes to contain your database code and let those classes convert between the database data and your application objects, most VB6 projects I saw had the GUI hard linked to the DB which can make future maintenance or new features very difficult and limits the possiblity of code reuse.

If you've got VB6 experience I'd thought that you could probably start trying to create the application right away but you should definitely read either a good book or good articles about it at the same time so that you pick up things like that you need to Dispose of your database objects after user etc.

ho1 2010-05-31 12:37:27

does it allow me to change the database servername and other parameters before creating connetction. ie. if i want to pass servername, username, password n other is it possible?

KoolKabin 2010-05-31 12:45:28

Your comment about VB6 applications having limited possibility of code reuse seems like a variant of the old joke: "The food here is terrible!" "Yes, and such small portions!"

MusiGenesis 2010-05-31 12:49:34

I dun want to reuse code but i want to use same code with multiple settings

KoolKabin 2010-05-31 13:08:30

@KoolKabin - you could just store multiple connectionstrings in the config file, or if you prefer you could store just a connectstring with placeholders instead of the actual values for the values that you want to change during runtime and then just do a `String.Replace` with the correct values.

ho1 2010-05-31 13:37:19

+1 for placeholder in connection string

KoolKabin 2010-06-01 07:16:00

It's probably a good idea to get a book, a lot has changed since VB6.

Also consider using a more robust db, like SQL compact or SQLite. It will allow you you use the Entity Framework which will make writing your app a whole lot easier.

Doobi 2010-05-31 12:39:33

can u suggest some good books for beginers?

KoolKabin 2010-05-31 12:46:57

In what ways are SQL Server CE and SQLite "more robust" than Jet/ACE for a single-user desktop app?

David-W-Fenton 2010-05-31 19:04:15

Perhaps the newer versions of Access are better, but in my experience (2000-2003) we had issues once the number of rows started to increase (talking 10-100s of thousands). We've used SQLite for some pretty huge tables (near gigs) without issue. In farness that may not be an issue for a desktop app. The big win for me is using EF and LINQ. If there's a way to do that with Access that'd be awesome.

Doobi 2010-06-01 04:28:14

I don't know what EF and LINQ are, and perhaps they aren't needed by the person asking the question? As to having problems with 10s/100s of thousands of records, I've never seen that myself. I had an app that I wanted to upsize to SQL Server that went 5 years with over 300K records in 2 of the 3 main tables, and over 600K in a third table, and 10-15 simultaneous users, and no problems with reliability or performance. Of course, I know what I'm doing in regard to designing an efficient multi-user Access app, and it is not what the defaults in Access suggest (though it's not difficult).

David-W-Fenton 2010-06-03 01:14:07

In .NET, talking to a database is handled with ADO.NET, which uses something called "connection pooling". The connection pool is basically a collection of open connections to your database that ADO.NET manages for you. In your code, when you create and open a Connection object, ADO.NET first looks in the connection pool to see if it already has an open connection to your data source, and if it finds one it uses that (instead of actually creating and opening a new connection). When you close your connection, ADO.NET does not really close it, but instead returns it to the connection pool.

Therefore, you do not need (and in face do not want) to maintain open connection objects inside your application (in a global variable or anywhere). The correct approach with data access in ADO.NET is to create and Open a Connection object, do whatever you need to do with the database, and then Close and Dispose your Connection.

MusiGenesis 2010-05-31 12:45:49

so is the connection object same for access and sql

KoolKabin 2010-05-31 13:00:08

Yes, connection pooling applies no matter what kind of database you're talking to (as long as you're using ADO.NET), so the basic approach should always be the same: create and Open a Connection object immediately before you need to use it, then Close and Dispose the object as soon as you're done with it. You should not store a Connection object in a global (or even class level) variable, and you should definitely not store it in an Open state.

MusiGenesis 2010-05-31 13:08:23

can it be justified with some reasons?

KoolKabin 2010-05-31 13:09:45

Do you mean: "why does ADO.NET use connection pooling?"

MusiGenesis 2010-05-31 13:12:15

nope... why shouldn't we store a connection object in a global variable

KoolKabin 2010-05-31 13:13:50

Because that would mean your application is "selfishly" monopolizing an open connection (which is a relatively expensive resource) even when it doesn't need it. Programmers used to do this frequently to avoid the startup time involved in creating a new connection (sometimes as much as a few seconds or even more). Connection pooling allows your application (and everybody else's, too) to avoid the connection startup times (thus being as fast as possible) while not monopolizing the connections. If you keep an open Connection in a global variable, you will not benefit yourself at all ...

MusiGenesis 2010-05-31 13:28:56

... and you will hurt everybody else. Also, it's more work on your part to keep track of your open connections, so why do it?

MusiGenesis 2010-05-31 13:29:36

With a Jet/ACE back end, everything works better if you use a single connection and keep it open. This is contradictory of everything we're (properly) taught about client/server apps, but an app with a Jet/ACE back end is not a client/server app to begin with.

David-W-Fenton 2010-05-31 19:03:37

ansaurus

tags:

views:

answers:

Creating Simple desktop database application

related questions