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

I would go with the second approach and "how I can Implement it" is up to you.You can definitely find some information on google. Try something and if any error occurs or stuck some where then post a question. Then the people might more eager to help you. — chamara, Jun 11 '13 at 03:44
when you say "import data" - what are you importing it to? a different database? — StanK, Jun 14 '13 at 02:35
@StanK Import data with the above context to approach 1 is to copy all data from Access database and bind it to the Model [ASP.NET MVC architecture] — Swapneel Kondgule, Jun 14 '13 at 05:16
You need a centralized database (maybe embed a SQL Compact Database?) Then you can bind your model to that, and run the import process from Access-to-CompactSQL. Then you would refresh your model and make use of it. — TheOptimusPrimus, Nov 14 '13 at 16:22

score 1 · Answer 1 · answered Dec 09 '13 at 03:53

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

score 1 · Answer 2 · answered Feb 01 '15 at 21:23

1

Use Entity Framework.Create ViewModel to map.

answered Feb 01 '15 at 21:23

user3450804

83
1
6

score 0 · Answer 3 · answered Jun 11 '13 at 09:39

What you should do is build your model according to your table. So the model class should have properties which correspond to your table fields. Then when you require the Model you would query againist the DB and populate the model's properties accordingly.

score 0 · Answer 4 · answered Jan 07 '15 at 16:44

I did not understood Approach 1.

Is it a requirement to use Access? I have seen there are lot of problems with file based database (such as Access) so better import all the data to SQL Server or some other database from Access and then use option 2.

As your database already created you can use Entity Framework database first approach to bind it.

score 0 · Answer 5 · answered Feb 14 '15 at 16:21

You need to add using System.Data.OleDb; in Header file And add these Provider=Microsoft.ACE.OLEDB.12.0;DataSource=|DataDirectory|\myAccessFile.mdb; Persist Security Info=False; line in connection string use connection string fetch
update ms-access database using OleDbCommand , OleDbConnection

and ms-access query just like sql query

Import Ms Access Data by programmming

5 Answers5