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

TinMan7757 · Accepted Answer · 2015-09-01 13:15:12Z

3

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

edited Sep 1, 2015 at 13:15

answered Sep 1, 2015 at 10:48

TinMan7757

1482 silver badges10 bronze badges

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

7 Comments

Left4Cookies Over a year ago

This brought me a lot closer but when I try and get my model from the view, I get a "Class name is not valid at this point".

TinMan7757 Over a year ago

In your action result you should be returning : return View(MovieModel); At which point is it failing ?

Left4Cookies Over a year ago

Both the return View(Movie); and foreach I've updated the OP with my current code.

TinMan7757 Over a year ago

oh right, in that case in your view it should be @foreach(var item in Model){//dostuff} and it should be <td>@item.Title</td> Also make sure you have the Model declared at the top of the page (Note the lowecase 'm') @model Namespace.To.Model.In.Folder

TinMan7757 Over a year ago

Image should be just as easy to do, however it depends on how you are storing it and where. If you are saving the file path to the DB it should be as simple as adding it into the Foreach statement as an image. with the file path as the source. If however it is stored as a 'blob' or data in the databse its a little more tricky.. Glad this helped.

|

Jure Potocnik · Accepted Answer · 2015-09-01 11:50:44Z

0

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

answered Sep 1, 2015 at 11:50

Jure Potocnik

4897 silver badges22 bronze badges

1 Comment

infocyde Over a year ago

DataReaders are faster than DataAdapters. Probably a moot point unless your site is getting hammered, but relevant.

Collectives™ on Stack Overflow

ASP.NET MVC application reading data from SQLite database

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related