Solved

Advice Needed For New VB.NET 2005 + MySQL Application

Posted on 2006-07-23
4
197 Views
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!
0
Comment
Question by:Sailo100
  • 2
  • 2
4 Comments
 
LVL 8

Expert Comment

by:Shakti109
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 :

OLEDb:
"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 :
http://dev.mysql.com/downloads/connector/net/1.0.html

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;"
      Try
         RmtSqlConn.Open()
         RmtDataAdapter.Fill(ds)
      catch ex as exception
        ' Trap error, report, etc
      Finally
         RmtSqlConn.Close()
      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.

0
 

Author Comment

by:Sailo100
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?

Cheers

Chris
0
 
LVL 8

Accepted Solution

by:
Shakti109 earned 500 total points
ID: 17176969

One of the Murach books has a very good chapter on web-services :
http://www.murach.com/books/bvbn/ch20ex1.htm

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.
http://www.fawcette.com/vsm/2002_07/magazine/columns/gettingstarted/

Addison wesley has a good book for web-services also:
http://safari.adobepress.com/0672321564

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) :

Select
   BookName,
   Author
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 :

Select
  BookName,
  Author
from [remote_database]
as Remote_dataset

Select
  BookName,
  Author
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.).
0
 

Author Comment

by:Sailo100
ID: 17198993
Thanks for your time and information!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now