LINQ and MS Access

[Neil B]

I've JUST move to Vista, VS 2008, C# from XP, VS.net 2002, VB & C++ so I'm on
a steep learning curve.

I'm currently developing a C# Web App project in VS 2008. I need to query an
existing MS Access data base. I thought LINQ would be the way to go.

Can you suggest a good example of C#, LINQ, code that will get me started
with accessing a MS Access data base??

Thanks, Neil

 

[Peter Bromberg [C# MVP]]

if you are on such a "steep learning curve" from all your migrations to newer
this and newer that, it might be a good idea to lessen the steepness for a
bit and just concentrate on how to query MS Access with plain old ADO.NET
using the OleDb provider. Once you've got that under your belt, you can add
LINQ into the mix.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short Urls & more: http://ittyurl.net

 

[Neil B]

I'll keep that in mind. However, I'm still interested in looking at a LINQ
solution.
Can you point me to a good example that will get me connected to an Access
DB??

Thanks, Neil

 

[Miha Markic]

Hi Neil,

You have two options from MS "out of the box" and neither is a good one for
you:
1. LINQ to SQL ... works only with SQL servers
2. Entity Framework ... in beta 3 phase, might support Access.

So, both options are not feasible for you.

Perhaps you might take a look at a better, commercial, ORM like LLBLGenPro,
which supports MS Access out of the box (LINQ provider is currently in beta,
it should be relased soon).
http://www.llblgen.com/

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

 

[Neil B]

That's interesting...

So why would Microsoft release their new query solution without support for
one of their most popular databases???

If I was starting from scratch and were to use LINQ for my database queries
what would be the most natural place for the data base support? Or must you
use some flavor of "LINQ to xxxx"?

Thanks, Neil

Hi Neil,

You have two options from MS "out of the box" and neither is a good one for
you:
1. LINQ to SQL ... works only with SQL servers
2. Entity Framework ... in beta 3 phase, might support Access.

So, both options are not feasible for you.

Perhaps you might take a look at a better, commercial, ORM like LLBLGenPro,
which supports MS Access out of the box (LINQ provider is currently in beta,
it should be relased soon).
http://www.llblgen.com/

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

I'll keep that in mind. However, I'm still interested in looking at a LINQ
solution.
Can you point me to a good example that will get me connected to an Access
DB??

Thanks, Neil

 

[Marc Gravell]

So why would Microsoft release their new query solution without support for

one of their most popular databases???

Popular with who? Probably not the same set of people who might be
using LINQ. It just about works for single user (OK, maybe a handful
of users) in a destkop app, but you aren't really going to use LINQ
inside such. And if you don't need the desktop app side of things, SQL
Express makes a good choice... with far fewer of Access's
limitations...

But this is one of those polarizing things. Personally I wouldn't
recommend access, but that might just be me.

Marc

 

[Marc Gravell]

(note that I'm only using SQL Express here as a comparison to entry-
level tools like Access; obviously SQL Server / Oracle kick in at some
point...)

 

[Miha Markic]

Right, Access should be dumped asap.
Besides SQL Express, which requires installation, there is another very
appealing database engine: SQL Compact Edition - subset of SQL Server, no
install whatsoever required - besides copying (I think) three assemblies. It
isn't multiuser, but for a single application should do just fine.

 

[Neil B]

Maybe you can help me convert my data access code from VB to C#.

In VB I have the following code snippets:

Set Con = Server.CreatObject("ADODB.Connection");
Con.Open "MyDataBase.mdb"
SET RecSet = Con.Execute(someSqlString)
If RecSet.BOF Or RecSet.EOF Then ....
RecSet.Close

Can you tell me where to find the C# equivalents?
A good example would be very helpful.

Also, Since I'm developing in VS 2008 using an ASP.NET Web Application
Project how do you address the database file so that the code will working
when debugging as well as when its published to the web site? Is
"~/MyDataBase.mdb" appropriate.

Thanks for any help you can render,
Neil

Right, Access should be dumped asap.
Besides SQL Express, which requires installation, there is another very
appealing database engine: SQL Compact Edition - subset of SQL Server, no
install whatsoever required - besides copying (I think) three assemblies. It
isn't multiuser, but for a single application should do just fine.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Popular with who? Probably not the same set of people who might be
using LINQ. It just about works for single user (OK, maybe a handful
of users) in a destkop app, but you aren't really going to use LINQ
inside such. And if you don't need the desktop app side of things, SQL
Express makes a good choice... with far fewer of Access's
limitations...

But this is one of those polarizing things. Personally I wouldn't
recommend access, but that might just be me.

Marc

 

[Miha Markic]

Sorry, take a look at datasets and adapters. I am sure there is plenty of
info out there. You might even start by reading .net help files.

As per websites and access files: please, please, do use SQL Server
(Express) instead. Access is just not meant to be used in web scenarios
(although it can, if you really want to).

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Maybe you can help me convert my data access code from VB to C#.

In VB I have the following code snippets:

Set Con = Server.CreatObject("ADODB.Connection");
Con.Open "MyDataBase.mdb"
SET RecSet = Con.Execute(someSqlString)
If RecSet.BOF Or RecSet.EOF Then ....
RecSet.Close

Can you tell me where to find the C# equivalents?
A good example would be very helpful.

Also, Since I'm developing in VS 2008 using an ASP.NET Web Application
Project how do you address the database file so that the code will working
when debugging as well as when its published to the web site? Is
"~/MyDataBase.mdb" appropriate.

Thanks for any help you can render,
Neil

Right, Access should be dumped asap.
Besides SQL Express, which requires installation, there is another very
appealing database engine: SQL Compact Edition - subset of SQL Server, no
install whatsoever required - besides copying (I think) three assemblies.
It
isn't multiuser, but for a single application should do just fine.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

So why would Microsoft release their new query solution without
support
for
one of their most popular databases???

Popular with who? Probably not the same set of people who might be
using LINQ. It just about works for single user (OK, maybe a handful
of users) in a destkop app, but you aren't really going to use LINQ
inside such. And if you don't need the desktop app side of things, SQL
Express makes a good choice... with far fewer of Access's
limitations...

But this is one of those polarizing things. Personally I wouldn't
recommend access, but that might just be me.

Marc

 

[Frans Bouma [C# MVP]]

Popular with who? Probably not the same set of people who might be
using LINQ. It just about works for single user (OK, maybe a handful
of users) in a destkop app, but you aren't really going to use LINQ
inside such. And if you don't need the desktop app side of things, SQL
Express makes a good choice... with far fewer of Access's
limitations...

But this is one of those polarizing things. Personally I wouldn't
recommend access, but that might just be me.

I don't see ms access as an enterprise multi-user database, but it's
really suitable for a desktop app's local db. For example, it has more
features than the horrible crap called SqlServer CE Desktop.

FB


--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Marc Gravell]

I'll readily defer to your judgement on most database things.

But for the record I wasn't comparing it to that *particular* beast...
I guess it depends on whether you can get away with installing SQL Server
Express Edition - obviously the jet driver itself is smaller and more
portable. If the app is for full-time usage, then you probably can. If it is
just a quick standalone smart-client, then probably not.

As always, then, the answer probably starts with "it depends"...

;-p

 

[Martin Duhalde]

visit www.blsys.net, there is a small open source projet where you can
download Linq 2 Access. This soft is preliminar but can help with simple
developments