ASP.. NET MVC应用程序从SQLite数据库读取数据

新手问题:

我有一个SQLite数据库与一些随机的电影信息，我正在读取数据，并将数据添加到视图。但是，现在它正在遍历表，只将最后一条记录添加到视图中。我如何添加每条记录作为SQLiteDataReader是通过表而不是表中的最后一条记录?

这是我当前的控制器代码:

public ActionResult dbView()
    {
        string cs = "Data Source=" + Environment.CurrentDirectory + "''example.db";
        using (SQLiteConnection con = new SQLiteConnection(cs))
        {
            con.Open();
            string stm = "SELECT * FROM Movie";
            using (SQLiteCommand cmd = new SQLiteCommand(stm, con))
            {
                using (SQLiteDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        ViewBag.movie = rdr["MovieId"];
                        ViewBag.title = rdr["title"];
                        ViewBag.rating = rdr["rating"];
                        ViewBag.image = rdr["image"];
                    }
                }
            }
            var image = "data:image/png;base64," + Convert.ToBase64String(ViewBag.image);
            ViewBag.image = image;

            con.Close();
        }

        return View();
    }

视图代码:

<div class="col-md-4">
<table style="width: 100%" border="1">
    <tr>
        <th>Title</th>
        <th>Rating</th>
    </tr>
    <tr>
        <td>@ViewBag.title</td>
        <td>@ViewBag.rating</td>
    </tr>
</table>
    <img src="@ViewBag.image" style="width: 300px"/>
</div>

更新:这是我目前在哪里:

    public List<Movie> Movies { get; set; }
    public ActionResult dbView()
    {
        string cs = "Data Source=" + Environment.CurrentDirectory + "''example.db";
        using (SQLiteConnection con = new SQLiteConnection(cs))
        {
            var listOfMovies = new List<Movie>();
            con.Open();
            string stm = "SELECT * FROM Movie";
            using (SQLiteCommand cmd = new SQLiteCommand(stm, con))
            {
                using (SQLiteDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        listOfMovies.Add(new Movies
                        {
                        MovieID= int.Parse(reader["EmployeeId"].ToString()),
                        Title= reader["Title"].ToString(),
                        Rating= reader["Rating"].ToString(),
                        });
                    }
                    rdr.Close();
                    Movies = listOfMovies;
                }
            }
            con.Close();
        }
        return View(Movies); // this says "Class name is not valid"
    }
}

和我的模型:

public class Movie
{
Public int MovieID {get;set;}
Public String Title {get;set;}
Public int rating {get;set;}
Public Byte Image {get;set;}
}

添加到视图:

    @foreach (var item in Movies) // this says "cannot resolve symbol Movies"
    {
        <tr>
            <td>@ViewBag.title</td>
            <td>@ViewBag.rating</td>
        </tr>
    }

public List<Movie> Movies{ get; set; }
public void Connect()
        {
         string cs = "Data Source=" + Environment.CurrentDirectory + "''example.db";
            using (var connection = new SQLiteConnection(cs))
            {
                var listOfMovies= new List<Movie>();
                var stm = "SELECT * FROM Movie";
                var command = new SQLiteCommand(stm , connection);
                try
                {
                    var reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        listOfMovies.Add(new Movie
                        {
                            MovieID= int.Parse(reader["EmployeeId"].ToString()),
                            Title= reader["Title"].ToString(),
                            Rating= reader["Rating"].ToString(),
                            //Do what ever you should be doing with the image.
                        });
                    }                      
                    reader.Close();                  
                    Movies = listOfMovies;
                }
                catch (Exception ex)
                {
                    throw;
                }
            }
        }
public class Movie
{
Public int MovieID {get;set;}
Public String Title {get;set;}
Public int rating {get;set;}
Public Byte Image {get;set;}
}

<div class="col-md-4">
<table style="width: 100%" border="1">
    <tr>
        <th>Title</th>
        <th>Rating</th>
    </tr>
@foreach(var item in model.Movies)
{
<tr>
        <td>item.title</td>
        <td>item.rating</td>
 //EDITED HERE FOR IMAGE
//This should work note that this code is untested.
 <td><img src="item.imageFilePath" width="300px;"/></td>
    </tr>
}
</table>
<img src="@ViewBag.image" style="width: 300px"/>

 using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand)){
    using (var dataSet = new DataSet())
    {
      sqlDataAdapter.Fill(dataSet);
      return dataSet.Tables[0].AsEnumerable().Select(row => new Movie
      {
        MovieID= row["MovieID"].ToString(),
        Title= row["Title"].ToString(),
        Rating= row["Rating"].ToString()
      });
    }
}