LINQ and MS Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Neil B - 25 Mar 2008 19:12 GMT

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

Reply to this Message

Peter Bromberg [C# MVP] - 25 Mar 2008 19:36 GMT

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

> I've JUST move to Vista, VS 2008, C# from XP, VS.net 2002, VB & C++ so I'm on
> a steep learning curve.
[quoted text clipped - 6 lines]
>
> Thanks, Neil

Reply to this Message

Neil B - 25 Mar 2008 22:19 GMT

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

> 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
[quoted text clipped - 16 lines]
> >
> > Thanks, Neil

Reply to this Message

Miha Markic - 26 Mar 2008 09:42 GMT

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/

Signature

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.
[quoted text clipped - 29 lines]
>> >
>> > Thanks, Neil

Reply to this Message

Neil B - 26 Mar 2008 10:51 GMT

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,
>
[quoted text clipped - 43 lines]
> >> >
> >> > Thanks, Neil

Reply to this Message

Marc Gravell - 26 Mar 2008 12:41 GMT

> 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

Reply to this Message

Marc Gravell - 26 Mar 2008 12:42 GMT

(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...)

Reply to this Message

Miha Markic - 26 Mar 2008 13:02 GMT

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.

Signature

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
[quoted text clipped - 11 lines]
>
> Marc

Reply to this Message

Neil B - 26 Mar 2008 19:53 GMT

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
[quoted text clipped - 17 lines]
> >
> > Marc

Reply to this Message

Miha Markic - 27 Mar 2008 08:52 GMT

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

Signature

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#.
>
[quoted text clipped - 40 lines]
>> >
>> > Marc

Reply to this Message

Frans Bouma [C# MVP] - 27 Mar 2008 09:39 GMT

> > So why would Microsoft release their new query solution without
> > support for one of their most popular databases???
[quoted text clipped - 8 lines]
> 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

Signature

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

Reply to this Message

Marc Gravell - 27 Mar 2008 10:36 GMT

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

Reply to this Message

LINQ and MS Access

Free Magazines