ASP.NET MVC Database access

asp.net-mvc, c#, database, view

Solution

I'd recommend you using view models and get rid of DataSets and Tables. Let's forget about those data types. They are legacy.

For example you could have a method (which you would eventually externalize into a repositorhy to avoid polluting your controller code with database access):

public IEnumerable<Test> GetTests()
{
    using (var conn = new SqlConnection(connectionString))
    using (var cmd = conn.CreateCommand())
    {
        conn.Open();
        cmd.CommandText = "SELECT ID, name FROM Test";
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                yield return new Test
                {
                    ID = reader.GetInt32(reader.GetOrdinal("ID")),
                    name = reader.GetString(reader.GetOrdinal("name")),
                }
            };
        }
    }

}

and then your controller action:

public ActionResult Index()
{
    IList<Test> tests = GetTests().ToList();
    return View(tests);
}

So far so good. Finally the corresponding strongly typed view to display the results:

@model IList<Test>
<table>
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
        </tr>
    </thead>
    <tbody>
        @for (var i = 0; i < Model.Count; i++)
        {
            <tr>
                <td>@Html.DisplayFor(x => x[i].ID)</td>
                <td>@Html.DisplayFor(x => x[i].name)</td>
            </tr>
        }
    </tbody>
</table>

or use the `WebGrid` helper if you prefer:

@model IList<Test>
@{
    var grid = new WebGrid(Model);
}
@grid.GetHtml()

Problem

I've been trying to use a preexisting database in my ASP.NET MVC project. I create a "Data Connection" to my (Microsoft SQL)Database (".\SQLEXPRESS.Databases"), which has the table "Test". This table has two columns, "ID (int)" and "name (nvarchar(MAX))". In the "Models" folder I put this new class: ``` using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data.Entity; namespace MvcMovie.Models { public class Test { public int ID { get; set; } public string name { get; set; } } } ``` Then (in "Controllers") I have a new Controller, "HelloWorldController". In this I have this function: ``` public ActionResult Index() { DataSet data; DataTable table; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand select = new SqlCommand("SELECT * FROM Test", connection); SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = select; data = new DataSet(); adapter.Fill(data, "Test"); table = data.Tables[0]; //is the [0] really correct? } return View(/* HOW DO I GET THE TABLE TO THE VIEW? */); } ``` Now I would like to display the rows of the table in my "Index.cshtml" file (in "Views"). How do I get the table from the Controller to the view? What I found was with "@model" and "IEnumerable", but that does not work, since I cannot convert my table. ``` @model IEnumerable<MyProject.Models.Test> @{ ViewBag.Title = "Index"; } <h2>Index</h2> @foreach (var item in Model) { <p>blaaaah</p> } ``` Now there are three questions: 1. Am I doing something completely wrong? (Method of DB-Access,..) 2. Is there an easier way to do this? (Without all adapter,.. stuff) 3. If !1&&!2, How do I get it to work? (Get the view file to "know" my table) Thanks for help, Regards, Caba

Original source