Import Ms Access Data by programmming

Question

I am in search of an good approach to import data from ms access and bind it to any Model of an MVC pattern

---

Here is the approach which we are thinking to following

Approach 1 :

• Open Ms Access file
• Open database
• Open all tables
• Import data of all tables and bind them to an model
• Close all tables
• Close database
• Close file

Approach 2 :

• Connect Ms Access Database in Asp.Net MVC
• Open the database
• pass an query
• fetch data and bind it to model
• close database

Which approach is better and how I can Implement it?

UPDATE: I have implemented Approach 2 and its works fine , does anyone know how to implement Approach 1

Answer 1

Both approaches will require you to connect to your database and map the contents into your model. I'm assuming Approach 1 is 'when the web app starts connect and copy all the database contents into memory and access if from there' and Approach 2 is 'when I need to display some data, connect the the database and copy the specific contents to my model'.

If this is the case, then Approach 2 is recommended (and you've stated you have done this so all is good).

Approach 1 may work ok-ish for smaller sized databases but:

• You loose all the [acid][1]-y goodness that your database provides
• Your stuck with global collection variables - not the most loved concept in web apps
• You have an entire database unnecessarily in memory. Your slow point in web apps is usually the network, a few milliseconds to load data when needed is nothing compared with the time it takes for your html to reach the browser

If you were to try approach one (not recommended, do not do, a kitten is harmed each time this code is run) , then the easiest way would be to have something like this in your global.asax.cs file:

public class MvcApplication : System.Web.HttpApplication {

public static List<MyTable1> globalTable1;
public static List<MyTable2> globalTable2;

protected void Application_Start() {
  AreaRegistration.RegisterAllAreas();
  WebApiConfig.Register(GlobalConfiguration.Configuration);
  FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
  RouteConfig.RegisterRoutes(RouteTable.Routes);
  BundleConfig.RegisterBundles(BundleTable.Bundles);

  var DatabaseMagic = new DatabaseAccessClass("a:\path\to\database.mdb");
  globalTable1 = DatabaseMagic.getDataForTableOne();   //However you do your loading and mapping
  globalTable2 = DatabaseMagic.getDataForTableTwo();   //ditto
}

Then in your controllers:

    public ActionResult Index()
    {
        return View(MvcApplication.globalTable1);
    }

And your view:

@model List<MvcApplication1.MvcApplication.MyTable1>
@{
    ViewBag.Title = "Index";
}
<h2>Blah</h2>
<ul>
@foreach (var i in Model) {
    <li>@i.idField  - @i.contentField </li>
}
</ul>

(Did I mention don't do this?)