ASP.NET MVC application reading data from SQLite database

Question

Novice question:

I have an SQLite db with some randomg movie info that I'm reading data from and adding said data to a view. However, right now it's going through the table and only adding the last record to the view. How do I add each record as the SQLiteDataReader is through the table instead of just the last record in the table?

This is my current controller code:

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();
    }

My view code:

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

UPDATE: Here's where I'm currently at:

    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"
    }
}

And my model:

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

Added to the view:

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

Answer 1

I have not had experience using SQL lite but it should serve the same purpose as MS SQl Server

Using some of my code as an example modified for your purpose.

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;}
}

Simply return the 'Movies List' back to the View ( In a Model ) try not to use Viewbag properties for this type of thing.

and in your cshtml :

<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"/>

Answer 2

Like TinMan7757 wrote but using dataset adapter is better approach:

 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()
      });
    }
}