[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

mySQL vs Access with VB.NET

I'm currently developing a system with VB.NET, I've been thinking of using either mySQL or Access. However I'm still not sure which database can gives the best performance with VB.NET.

I also found that if I use mySQL I will have to use object such as OdbcCommand which is totally different from the standard object provide such as sqlDataAdapter, sqlCommandBuilder (please tell me if I'm wrong)

please see: http://dev.mysql.com/doc/mysql/en/ODBC.NET(VB).html

Is that meant if I change database one day, I also have to change the code as well. for example: from mySQL to MS SQL Server??  

Please tell me which database I should use !! THANKS


Joson

0
jpytse
Asked:
jpytse
1 Solution
 
RonaldBiemansCommented:
Hi jpytse,

Well MySQL is a very powerfull database, if your program uses a lot of records or you uses a lot of difficult queryd you will get a better performance using MySQL (stored procedures).
On the other hand MySQL has more overhead (uses more memory) and is more difficult to install than an Access database.

And if you switch databases then you indeed have to change your code (or build it in beforehand).

I personally use Access only in small apps. and use MSDE or SQL Server for larger apps.





0
 
rdrunnerCommented:
Try to encapsulate all DB access in its own layer and only access that layer and not the DB itself. This way you can "just" rewrite the DAL and you dont need to redo the "whole" work.

Also if you are using ODBC you can connect to many datasources but you will loose some performance since there is an extra layer. (Opposed to using the SQL Server classes)

For more information see my question here....

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/Q_21191259.html

There are some nice articles here
0
 
Ignacio Soler GarciaCommented:
You can also use the OLEDB classes that are just like the SQL ones but for OLEDB. That's what I use with Oracle, for example:

sqlDataAdapter, sqlCommandBuilder  = oleDbDataAdapter, oleDbCommandBuilder

On the other side, just like Ronald:

Small and SOHO apps --> Acces || Big or industrial apps --> MySQL (or if you can, of course, SQL Server)

SoMoS
0
Technology Partners: 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!

 
Éric MoreauSenior .Net ConsultantCommented:
>>I also found that if I use mySQL I will have to use object such as OdbcCommand

You can now have native provider: http://www.able-consulting.com/dotnet/adonet/Data_Providers.htm#MySQLDirectNETDataProvider

>>which is totally different from the standard object provide such as sqlDataAdapter, sqlCommandBuilder (please tell me if I'm wrong)

That's true for all provider (SQL, Oracle, OLEDB, ODBC, MySQL, ...). The purposes of the command and dataadapter objects are to link to database and eash database has its way.
0
 
wguerramCommented:
I Personally don't recommend you to use ms access, when the database grows to 600 MB or greater it becomes slow and the traffic on the network too.

I have seen two of this cases.

rdrunner took a good point on separating the data access in a layer, so if someday you change DB, you will only have to change the db layer.
0
 
jpytseAuthor Commented:
Guys, thanks for your help !! Do you mind if you can explain more in this --> encapsulate all DB access in its own layer and only access that layer and not the DB itself. Do you have any example ?


Joson
0
 
Ignacio Soler GarciaCommented:
Of course!!! That's what OLEDB is for. You can use the same program when you change the database, you only need to change the connectionstring. It's the only thing needed to change from one to another.

Almost all good databases have OLEDB and this is the middle layer that they're talking about but done by the developers from the database company.

SoMoS
0
 
Éric MoreauSenior .Net ConsultantCommented:
>>encapsulate all DB access in its own layer and only access that layer and not the DB itself

Create a set of class (and put them into a DLL if you want a physical layer) that will contain your connection and all the queries to the database. All other classes/forms of your code will go through this layer to talk to the database. No other classes/forms should contain SQL queries.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now