Advice Needed For New VB.NET 2005 + MySQL Application

Posted on 2006-07-23
Medium Priority
Last Modified: 2010-04-23
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 vb.net 2005 can talk to mysql on the www yet?

Advice please?  Points awarded for the most userful information!
Question by:Sailo100
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2

Expert Comment

ID: 17164028

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};SERVER=data.domain.com;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).

      imports system.data.sqlclient
      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 vb.net(2003/2005), 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.


Author Comment

ID: 17175236
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?



Accepted Solution

Shakti109 earned 2000 total points
ID: 17176969

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 vb.net, 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.).

Author Comment

ID: 17198993
Thanks for your time and information!

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question