Advice Needed For New VB.NET 2005 + MySQL Application

Hi Guys,

I am after some advice and examples of code or relevant links so I can do some research before I start writing my new application in VB 2005.

Firstly this application will need to be both single user and multiple user.  However when i say multiple user i mean users need to be able to use the application and share the data across the internet.

I need to know what is the best way to achieve this and ideally with free tools.

My initial idea was that on installation the user is given 2 choices:
Single User - which uses a local database like an mdb on the local machine or even a local copy of MySQL if thats not to difficult to distribute.
Multiple Users - which uses a MySQL database out on the internet or local network and asks the user for database details to connect to or where to create the tables.

How difficult is this to do?

If the user chooses to store the data in a mysql database out on the www then is it possible to synchronize the database back to a local pc version in the event the user is not always connected to the internet.  I understand that this could possibly cause errors if multiple users have edited the same records at different times so if there is no connection to the internet i would want the application to work in "read only" mode.

Is all this possible? I'm not sure even if 2005 can talk to mysql on the www yet?

Advice please?  Points awarded for the most userful information!
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Shakti109Connect With a Mentor Commented:

One of the Murach books has a very good chapter on web-services :

An article from fawcette has a compelte example, a fully working web-service made with, and a client application that consumes (uses) it. The client application they made with vb6 to show that virtually anything can consume a web-service, it is platform independant.

Addison wesley has a good book for web-services also:

As to the synchronization question :

If you have many tables (say 15) and you decide that the "remote" database is the authoritative source, and that all of the records in the local databases should simply be mirrors of the remote, then rather than try to loop through every record of every table, and compare to the records, it would be more direct/efficient to simply delete everything from the local tables and insert from the remote.

The situation will change if the remote database is a central store of "core" records but each local copy will have unique records that may not necessarily be part of the remote database. If this is the case, then you will need to loop and compare records.

For inserting "new" records from the remote into the local, this could be as simple as something like (in SQL pseudo-code) :

from [remote_database]
where Bookname not in (Select bookname from [local_database])

From this record set you then insert these records into the local database.

For "updating" the records it will be a little more complex :

from [remote_database]
as Remote_dataset

from [Local_database]
as Local_dataset

Then loop through the two, and when the datasets "differ" you update the local dataset to match the remote.

Another consideration is size.  If the databases/tables are huge, then I would compare in chunks (first 500 records, next 500, next 500 etc.).

MySQL is one of the most popular database implementations for use by internet applications/sites. Connecting to a MySQL database is as simple as any other.

Some examples of the connection strings :

"Provider=MySQLProv;Data Source=mydb;User Id=UserName;Password=asdasd;"

MyODBC(3.51) :
"DRIVER={MySQL ODBC 3.51 Driver};;PORT=3306;DATABASE=myDatabase; USER=myUsername;PASSWORD=myPassword;OPTION=3;"

There are freware type "drivers" for MySQL connectivity :

As to the feasability of "synchronizing" the databases, this is a simple set of select (from remote) and update/insert (to local) database statements.

You can do this with connected data objects (like data-adapters), or via direct SQL statements.

There are also several approaches, do you want to do "updates" to the records in the database, or do you want to simply truncate the local database and "copy" the remote database. There are benefits and drawbacks to both approaches. The following would connect to a database, select everything from the BookStatus table into a dataset, and close the connection (using an adapater, and a command-text).

      Dim RmtSqlConn As New SqlConnection
      Dim SelectSqlCmd As New SqlCommand
      Dim RmtDataAdapter As New SqlDataAdapter
      Dim ds As New DataSet

      SelectSqlCmd.CommandText = "Select * from BookStatus"
      SelectSqlCmd.Connection = RmtSqlConn
      RmtDataAdapter.SelectCommand = SelectSqlCmd
      RmtSqlConn.ConnectionString = "Provider=MySQLProv;Data Source=mydb;User Id=UserName;Password=asdasd;"
      catch ex as exception
        ' Trap error, report, etc
      End Try

After you get the dataset back, you can delete everything from it and then insert all the records from the dataset you just read.

There are probably 10-15 ways to do this. Connected objects where you let the wizard create the statements (insert, select, etc) for you, ADO.NET connection, Disconnected (using a dataset like above) which means it reads the data then closes the connection, and others.

ALSO you could use a web-service you create and connect to that would live on the server that held the MySQL database, this is my favored approach. You encapsulate all of the functionality you want to expose in a web-service, only define the methods you want the 'public' to be able to use and then simply add a reference to your code for this web service and call it's methods. This has nearly countless benefits including :

Security -  You only allow connections from the web-service through, and lock out everything else.

Scalability - Want to allow people to connect to your database from say an .ASP page? Simply have them reference your web-service and call it's methods.

Re-Use - Need to provide similar functionality on another project? Take the web-service, modify it with the new connection parameters, re-name it and publish it, you're done.

And many many others.

This is as simple as (this assumes you have added the reference to the web-service) :
Dim oWS as new WebService-01-Production
oWS.GetDataSet("MyRmtDatabase", "select * from bookstatus")

The above method returns a dataset.

You don't have to open any connections, create any adapters, close any connections, or virtually anything else. You simply call your web-services methods and act on what comes back, the service handles everything else.

I have seen projects where people have created literally hundreds of data-adapters. To me this is an administrative nightmare.

What if you need to change databases (moved to a different server/etc), time to go modify your client-application code. If you use a web-service, you simply modify the service (the connection properties) and your client-code dosent have to change, it still calls the magic "black-box" of the web-service and gets it's data from it.

The time you save in code simplification alone is worth learning about web-services. There many great resources (both on the web, and in print) about web services, how to write them with, etc, so rather than write out a novel here, I would reccomend you search out documentation/etc on them.

As a summary of one possible approach :

1) Create a web-service that exposes the methods you will need (select, insert, update) to your MySQL database. This has the following benefits :
A) You don't have to distribute/pay-for/use any third-party components as far as MySQL database access, it is central to the web-service. In your code you simply reference your web-service, call it's methods and you're done.
B) You don't have many data-adapters and the associated overhead/code to maintain.
C) You create an n-tier structure by separating the data-access components from the code. Need to change databases? No need to update the application, simply make the change in the web-service and the clients never know anything changed.

2) Depending on the complexity of the database, decide if you want to update your local tables to match the remote database, or delete everything from the local and insert everything from the remote.

Nothing you are wanting to do is very complex at all, and this is entirely possible. There are many approaches, with some saving you significant time and effort.

Sailo100Author Commented:
Thanks for your feedback and suggestions.

I wasn't aware of web services but that sounds like the best logcial solution.

Can you provide me with any decent references for reading up on them?

In regards to database synchronization, i'm not really sure which is the most efficient method for me to use. Do you have a recommendation for this?


Sailo100Author Commented:
Thanks for your time and information!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.