asked on

C#: Return a list of objects from a method and SQL

Hi

I'm using an SQLite database which doesn't play nice with Entity Framework

I've been enhancing a previous question here

in SQL to use LIKE you need to use the '%' wild card eg

Select * from movie where title like '%Big%'

Open in new window

In the code bellow I'm building the query thus

string fName = "%" + Path.GetFileNameWithoutExtension( fi.Name) + "%";

Open in new window

and sending that as a Parameter
Is that the best to do it?

The main part of my question is how do I return a list of movies instead of the last one wins?

using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

using System.Configuration;
using System.Data.SQLite;

using System.Diagnostics;

namespace UpdateMoviePath
{
    class Program
    {
        public class MovieObj
        {
            public int Id { get; set; }
            public string Title { get; set; }
            public int Year { get; set; }
            public int FileId { get; set; }
            public string FilePath { get; set; }

        }
        static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");
            string Films = @"E:\Media\TestFilms";

            if (Directory.Exists(Films))
            {
                string[] Files = Directory.GetFiles(Films, "*.mp4");
                foreach (string file in Files)
                {
                    Console.WriteLine("File " + file);
                    FileInfo fi = new FileInfo(file);
                    string Ext = fi.Extension;
                    string fName = "%" + Path.GetFileNameWithoutExtension( fi.Name) + "%";
                    fName = fName.Replace("_", ":");

                    MovieObj movie = RunQuery(fName);
                    if (movie != null)
                    {
                        Console.WriteLine("id: {0} Title: {1} Year: {2}", movie.Id, movie.Title, movie.Year);
                    }
                    else
                    {
                        Console.WriteLine("Can't Find: {0}", fi.Name);
                    }
     
                }
            }

            Console.ReadLine();
        }

        private static MovieObj RunQuery(string fName)
        {
            SQLiteConnection dbConnection;
            dbConnection = new SQLiteConnection("Data Source=E:\\Media\\database\\MovieDatabase.db3;Version=3;");
            SQLiteCommand command = new SQLiteCommand("SELECT m.id, m.title, m.year, f.id,f.path FROM movielinkfile k INNER JOIN movie m ON k.movieId = m.id INNER JOIN file f ON k.fileId = f.id WHERE(m.title LIKE @fName)", dbConnection);
            SQLiteDataReader sQLiteDataReader = null;
            MovieObj movie = new MovieObj();
            try
            {
                dbConnection.Open();
                command.CommandType = CommandType.Text;
                command.Parameters.Add(new SQLiteParameter("@fName", fName));
                sQLiteDataReader = command.ExecuteReader();
                while (sQLiteDataReader.Read())
                {
                    int id = sQLiteDataReader.GetInt32(0);
                    string title = sQLiteDataReader.GetString(1);
                    int year = sQLiteDataReader.GetInt32(2);
                    int FileID = sQLiteDataReader.GetInt32(3);
                    string FilePath = sQLiteDataReader.GetString(4);

                    Console.WriteLine("Title: {0}", title);

                    movie.Id = id;
                    movie.Title = title;
                    movie.Year = year;
                    movie.FileId = FileID;
                    movie.FilePath = FilePath;
                }
                sQLiteDataReader.Close();
                dbConnection.Close();
                command.Dispose();
            }
            catch (Exception err)
            {
                Console.WriteLine(err.ToString());
            }
            finally
            {
                if (sQLiteDataReader != null) sQLiteDataReader.Close();
                dbConnection.Close();
                command.Dispose();
            }

            if(movie.Id > 0)
            {

            return movie;
            }
            else
            {
                return null;
            }

        }
    }
}

Open in new window

ASKER CERTIFIED SOLUTION

kaufmed

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

trevor1940

ASKER

This?

Call method

     \\  E1
   List<MovieObj> movies = RunQuery(fName);

Open in new window

        private static MovieObj RunQuery(string fName)
        {
            SQLiteConnection dbConnection;
            dbConnection = new SQLiteConnection("Data Source=E:\\Media\\database\\MovieDatabase.db3;Version=3;");
            SQLiteCommand command = new SQLiteCommand("SELECT m.id, m.title, m.year, f.id,f.path FROM movielinkfile k INNER JOIN movie m ON k.movieId = m.id INNER JOIN file f ON k.fileId = f.id WHERE(m.title LIKE @fName)", dbConnection);
            SQLiteDataReader sQLiteDataReader = null;


            List<MovieObj> Movies = new List<MovieObj>();
            try
            {
                dbConnection.Open();
                command.CommandType = CommandType.Text;
                command.Parameters.Add(new SQLiteParameter("@fName", fName));
                sQLiteDataReader = command.ExecuteReader();
                while (sQLiteDataReader.Read())
                {
                    MovieObj movie = new MovieObj();
                    int id = sQLiteDataReader.GetInt32(0);
                    string title = sQLiteDataReader.GetString(1);
                    int year = sQLiteDataReader.GetInt32(2);
                    int FileID = sQLiteDataReader.GetInt32(3);
                    string FilePath = sQLiteDataReader.GetString(4);

                    Console.WriteLine("Title: {0}", title);

                    movie.Id = id;
                    movie.Title = title;
                    movie.Year = year;
                    movie.FileId = FileID;
                    movie.FilePath = FilePath;
                    Movies.Add(movie);
                }
                sQLiteDataReader.Close();
                dbConnection.Close();
                command.Dispose();
            }
            catch (Exception err)
            {
                Console.WriteLine(err.ToString());
            }
            finally
            {
                if (sQLiteDataReader != null) sQLiteDataReader.Close();
                dbConnection.Close();
                command.Dispose();
            }

            if(Movies.Count() >= 1)
            {
////        E2
            return Movies;
            }
            else
            {
                return null;
            }

        }
    }

Open in new window

Errors

E1
Cannot implicitly convert type 'UpdateMoviePath.Program.MovieObj' to 'System.Collections.Generic.List<UpdateMoviePath.Program.MovieObj>'

E2
Error	CS0029	Cannot implicitly convert type 'System.Collections.Generic.List<UpdateMoviePath.Program.MovieObj>'

Open in new window

kaufmed

You have to update the return type of your method as well:

private static List<MovieObj> RunQuery(string fName)

trevor1940

ASKER

That caused multiple errors

kaufmed

I suspect you have a missing or extra brace somewhere.

trevor1940

ASKER

Found that but I'm still getting the error on the return line "E2" above

kaufmed

The variable where you store the result of the call to RunQuery also needs to be a List:

MovieObj movie = RunQuery(fName);

Open in new window

List<MovieObj> movies = RunQuery(fName);

Open in new window

Adjust the remaining code accordingly to deal with a list instead of a single Movie.

trevor1940

ASKER

I'm Calling the method thus

List<MovieObj> movies = RunQuery(fName);

Open in new window

Gives Error

Severity	Code	Description	Project	File	Line	Suppression State
Error	CS0029	Cannot implicitly convert type 'UpdateMoviePath.Program.MovieObj' to 'System.Collections.Generic.List<UpdateMoviePath.Program.MovieObj>'	UpdateMoviePath

Open in new window

The method

What should the method be returning?

private static MovieObj RunQuery(string fName)
{
             List<MovieObj> Movies = new List<MovieObj>();
            try
            {
..............
                 // do stuff to create the List of Movies
            }
            if(Movies.Count() >= 1)
            {

            return Movies;
// Above line creates this Error
// Severity	Code	Description	Project	File	Line	Suppression State
/// Error	CS0029	Cannot implicitly convert type 'System.Collections.Generic.List<UpdateMoviePath.Program.MovieObj>' to 'UpdateMoviePath.Program.MovieObj'	UpdateMoviePath	

            }
            else
            {
                return null;
            }
}

Open in new window

Not Sure what I'm doing Wrong

I created a dotnetfiddle so you can see exactly what I'm doing (Ignore errors relating the SQLite )

kaufmed

You have the same problem: Your method (currently) expects a single MovieObject, but you're returning a List<MovieObje>. Change the return type of the method.

trevor1940

ASKER

Change the return type of the method

Sorry I don't understand how to do that

I'm assuming you mean change the line "return Movies;" to something else??

it_saige

Kaufmed means this:

private static List<MovieObj> RunQuery(string fName)
{
    List<MovieObj> Movies = new List<MovieObj>();
    try
    {
        // do stuff to create the List of Movies
    }

    if(Movies.Count() >= 1)
    {
        return Movies;
    }
    else
    {
        return null;
    }
}

Open in new window

-saige-

trevor1940

ASKER

I hadn't realized the return type in bold was different from the Actual return

private static List<MovieObj> RunQuery(string fName)

I thought this was how you call the method until IT_sage showed me

Thanx for your help