DataBase Programming

[Guest]

I am totally confused between Access, SQL Express, and SQL Server and MSDE
and OLEDB vs SQL in .net. Please someone tell me if I"m correct in the
following:

With MSDE installed, I can program using OLEDB in ADO.Net for Access then
convert later to an SQL Express or SQL Server database and my program still
work if I change the connection string.

With MSDE installed, I can program ADO.Net for SQL and it will work for SQL
express or SQL server databases but not for Access databases.

MSDE comes with Windows XP and will also come with Windows Vista
automatically when the operating systems are installed.

The Access Jst Engine comes with Windows XP and will come with Windows Vista
automatically when the operating systems are installed.

If I program with ADO.Net for SQL using a SQL Express database, I can later
convert to SQL Server database and my program will work with out
modificaitons.

Please, would someone tell me if the above is True or False and if False,
what is the real story.

 

[David Browne]

I am totally confused between Access, SQL Express, and SQL Server and MSDE
and OLEDB vs SQL in .net. Please someone tell me if I"m correct in the
following:

With MSDE installed, I can program using OLEDB in ADO.Net for Access then
convert later to an SQL Express or SQL Server database and my program
still
work if I change the connection string.

False. The engines are different, and the SQL dialicts are different.

With MSDE installed, I can program ADO.Net for SQL and it will work for
SQL
express or SQL server databases but not for Access databases.

True.

MSDE comes with Windows XP and will also come with Windows Vista
automatically when the operating systems are installed.

False. MSDE and it's replacement SQL Server 2005 Express Edition are
availible for free download, and can be redistributed with custom
applications.

The Access Jst Engine comes with Windows XP and will come with Windows
Vista
automatically when the operating systems are installed.

False, I think. The Jet Engine used to be included in MDAC, which is part
of the OS now. But it was removed. So unless some other
installed-by-default product installs Jet, it may not be there.

If I program with ADO.Net for SQL using a SQL Express database, I can
later
convert to SQL Server database and my program will work with out
modificaitons.

True. SQL Express _is_ SQL Server.

David

 

[Cor Ligthert [MVP]]

Dennis,

Almost everything you wrote seems for me a wrong perception, therefore this
text.

Access is in fact the MS-Office Access system that has used in past forever
the Jet engine.
That Jet engine is usable using the MDAC parts which are delivered now
standard with XP SP2.

Those parts are just some DLL's with what you can access a Jet "MDB" file on
your disk(s) using SQL commands. You can create that file yourself using
ADODB.

SQLServer is the Database server from Microsoft. It is as well a file,
however only accessible by a windowservice.

The SQLServer has been downsized and given for free. In past this were two
versions named MSDE. One was a kind of Office add on, while the later was
downloadable and fit inside SQL server pack 3. Both were a hell to install
and to manage (while it is not almost clear for which version the
documentation is on MSDN).

Those two MSDE versions are replaced by SQLExpress. The install part is
better but the managing has still to be done by a beta management tool (if
you don't have a full SQL package). I did not try it but that beta tool
seems to be fine.

The MSDE and SQLExpress versions are downsized and therefore limited in use.
This limiting is by instance about the total size of a database (you can
have more databases on one server) and the concurrent connections.

The windowservice to access those databases is very extended and reachable
from very much places as long as you have the correct connection string. In
past the connection string did not tell anything about the place where the
actual Data was stored, but because for stand alone installations is this
changed for SQLExpress.

MS-Office (including Access) can use the Jet engine and the SQL server as a
database.

I hope this gives an idea,

Cor

 

[Guest]

Thanks for your reply...I do understand the difference between access and SQL
Server but wasn't clear on what I could do and what I need to supply when I
distributed an application. When I wrote an application with an access
database, I could rely on the user's machine to have the Jet engine if he had
almost any windows operating system but from one reply I got, it seems that
the Jet engine won't be installed automatically with "Vista".

Maybe you could clear up a couple of more questions I have:

If I write an application using an SQL database, then any user wanting to
run my application will have to have SQL Express or SQL server installed..is
this correct?

Can I use ADO.Net with the OLEDB Class for SQL databases?

Thanks Cor for your help.

 

[Guest]

Thanks for answers...just a couple of clarifiying questions:

I thought that you could use the ADO.Net OLEDB class to program access to
SQL databases...is this not correct?

If the jet engine won't be installed by Vista Operating system, then any
application that I write using either an Access or SQL database that will be
installed on another user's computer must also install either the Jet Engine
or SQL on the users machine if he is running Vista (unless he has already
installed same)? Is this correct

 

[Cor Ligthert [MVP]]

Dennis,

Inline the answers,
....

If I write an application using an SQL database, then any user wanting to
run my application will have to have SQL Express or SQL server
installed..is
this correct?

No only on one computer working as Server, the users needs of course to
have rights to connect to that SQLServer either by integrated windows
authority or special SQLServer authority. And they share of course the data,
therefore the concurrency routines should be correct. (You can of course as
well install on every computer an SQLExpress database for standalone
purpose. But you wrote will have to have.)

Can I use ADO.Net with the OLEDB Class for SQL databases?

You can use OLEDB, however that gives a loss on performance while probably
the only thing you have to change in your program is everywhere the prefix
OleDB by SQL (And than the right imports). The only difference I know are
the parameters, OleDB has no named parameters while SQLClient needs them. If
you have used parameters in OleDB as @Dennis, than you have in fact used
SQLClient parameters. This is a flaw in the MSDN documentation but can be in
your advance. The other difference I know is the way an update sets the
autokeynumbers in the dataset. That is done by SQLClient and not by OleDB.

I hope this gives an idea,

Cor