Using Access DB (.mdb) with ADODB in C# (ASP.MVC) like with Classic ASP

Question

Hi,

I used to work a lot with classic asp. I'm currently trying the ASP.NET MVC and like it so far, but I'm missing the simple usage of the ADODB Connection. I was searching the web, for any simple and clear solution like the code below, used on classic ASP, but everything I found was way more complicated and/or not with ADODB on C#. I want Recordsets, no "UPDATE.." SQLs, Column("Name") not Column[0], and so on... Is all of this gone?

Set db = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
db.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("data.mdb")
rs.Open "select * from test;", db, 1, 3 
rs.AddNew
rs("text") = "Hello World!"
rs.Update
rs.Close
db.Close
Set rs = Nothing   
Set db = Nothing

I know there are ObjectMappers, etc... But I would like to use the ADODB Connection with Recordsets the old fashion way. So please no anwsers, dont use it, its old or to slow etc. I'm aware of these things. Is it still possible and is there any simple working example with code.

Thanks.

Answer 1

No its all there, you'll just need to just use COM interop to get to it. See here for how

Answer 2

You can fill a DataSet and work with that. See the following OleDB tutorial for an explanation:

http://msdn.microsoft.com/en-us/library/aa288452(VS.71).aspx

Answer 3

I am not sure why you would want to use ADODB.Recordset, when things have moved into .net?

Having said that, you can wrap your operations into a VB based Activex DLL and with COM interop, make calls to your DLL.

e.g. The Update you mentioned can be wrapped into a method of your public class in VB6.
Make an activex DLL of it. Consume (Add Reference) to the Activex DLL from your .net code.

By doing this, COM specific things are handled in a boundary by itself and .net doesn't need to manage life of all COM class instances, that you create in your code.

Answer 4

Your life would be vastly simplified by picking up new technologies here--such as Linq2Sql. Really isn't much point in writing ADO.NET code anymore, nevermind ADO.old code.

Answer 5

What you are proposing to do is similar to walking into a Ferrari showroom and asking to buy a new car, and then promptly throwing the engine out and replacing it with an old heap from a 10 year old banger with 100,000 miles on the clock. This is (in my opinion) further exacerbated by the fact that you seem to prefer creating recordsets in classic ASP to perform a simple update. This practice was discouraged years ago even within classic ASP.

Working with Access in ASP.NET is actually quite easy if you forget about the RecordSet concept, and actually get the SQL to use "UPDATE MyTable SET x = etc...". Some clear examples of simple operations using ADO.NET and Access appear here: http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

ASP.NET MVC is a great choice for you, given your background. I urge you to put the little bit of extra effort in to learn ADO.NET and use that too instead of classic ADO. You've put your big toe in the new waters. Time to hold you breath and go in all the way ;o)