Using Access Databases with razor mvc4

Question

I have a Microsoft Access Database (.accdb) full of values that I wish to display in a table on my .cshtml razor page. I need to open the connection and then make the table with the values.

I think that the table would be something like this:

<table>
        <thead>
            <tr>
                <th>Name</th>
                <th>Address</th>
                <th>Address 2</th>
                <th>Field of Work</th>
            </tr>
        </thead>

       <tbody>
            @foreach (var row in _____.Query(____))
            {
                <tr>
                    <td>@row.name</td>
                    <td>@row.address</td>
                    <td>@row.address2</td>
                    <td>@row.field</td>
                </tr>
            }
        </tbody>
  </table>

, but I have not been able to open the connection correctly.

Advice please?

Mike Brind · Accepted Answer · 2013-10-06 07:26:11Z

3

If you are using Access, you should put the database file in App_Data and specify a connection string in your web.config file:

<connectionStrings>
    <add name="MyConnection" 
      connectionString="Provider="Microsoft.ACE.OleDb.12.0;Data Source=|DataDirectory|MyDatabase.accdb;" 
      providerName="System.Data.OleDb" />
</connectionStrings>

|DataDirectory| is a special short-cut that resolves to the location of the App_Data folder.

When you use the Database helper, you pass in the name of the connection string:

var db = Database.Open("MyConnection");
var data = db.Query("SELECT * FROM MyTable");
...
...
<tbody>
    @foreach (var row in data){
        <tr>
            <td>@row.name</td>
            <td>@row.address</td>
            <td>@row.address2</td>
            <td>@row.field</td>
        </tr>
    }
</tbody>

However, if you are using MVC, the database opening and querying should not happen in the view. You should do that in the controller, or better yet, in a data layer. Alternatively, you can look at ASP.NET Web Pages where the data access code does belong in the view: http://asp.net/web-pages.

answered Oct 6, 2013 at 7:26

Mike Brind

30.3k6 gold badges65 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Harrison Gibbs Over a year ago

it is giving me a "the name 'database' does not exist in the current context". I assume I am missing something. Also, could you explain why the database opening and querying should happen in another layer as opposed to the view?

Harrison Gibbs Over a year ago

I got the name to resolve. All I would like to know is what are the benefits of having it in a different layer? Thanks for all the help!

Mike Brind Over a year ago

The whole point of using the MVC framework is to leverage the fact that Views are separate from Controllers and both are separate from the Model, which is where data access takes place. This makes code management and testing much easier. These are fairly advanced features, so if you don't need them, I recommend the Web Pages framework. It's a lot simpler to work with.

Collectives™ on Stack Overflow

Using Access Databases with razor mvc4

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related