Reputation: 977
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
Upvotes: 4
Views: 18700
Reputation: 1039598
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()
Upvotes: 13