C# sqlite query results to list<string>

Question

Guys, I'm struggling. I have query against my db that returns a single column of data and I need to set it as List. Here is what I am working with and I am getting an error about converting void to string.

public static void GetImportedFileList()
    {
        using (SQLiteConnection connect = new SQLiteConnection(@"Data Source=C:\Documents and Settings\js91162\Desktop\CMMData.db3"))
        {
            connect.Open();
            using (SQLiteCommand fmd = connect.CreateCommand())
            {
                SQLiteCommand sqlComm = new SQLiteCommand(@"SELECT DISTINCT FileName FROM Import");
                SQLiteDataReader r = sqlComm.ExecuteReader();
                while (r.Read()) 
                {
                    string FileNames = (string)r["FileName"];

                    List<string> ImportedFiles = new List<string>();                        
                }                    

                connect.Close();
        }
    }
}

THEN LATER IN THE APPLICATION

List<string> ImportedFiles = GetImportedFileList() // Method that gets the list of files from the db 
foreach (string file in files.Where(fl => !ImportedFiles.Contains(fl))) 

Answer 1

The error is about the return type of your method. You are returning void (public staticvoid) but then use it later as if it were returning a List<string>.

I think you intended the following method signature:

public static List<string> GetImportedFileList()

Answer 2

You already have a way to get the individual results, so to me it looks like you just need to:

Move the List<string> ImportedFiles = new List<string>(); outside the while loop.

Call ImportedFiles.Add(FileNames); inside the loop (after that string gets assigned).

return ImportedFiles at the end.

Change the return type to List<string>.

Answer 3

First of all your return type is void. You need to return a List. Another problem is that you initialize the list inside the loop, so in each pass of the loop you have a new list, and whatsmore, you do not add the string in the list. Your code should probably be more like:

public static List<string> GetImportedFileList()
    {
        List<string> ImportedFiles = new List<string>();                        
        using (SQLiteConnection connect = new SQLiteConnection(@"Data Source=C:\Documents and Settings\js91162\Desktop\CMMData.db3"))
        {
            connect.Open();
            using (SQLiteCommand fmd = connect.CreateCommand())
            {
                fmd.CommandText = @"SELECT DISTINCT FileName FROM Import";
                SQLiteDataReader r = fmd.ExecuteReader();
                while (r.Read()) 
                {
                    string FileNames = (string)r["FileName"];

                    ImportedFiles.Add(FileNames);
                }                    

                connect.Close();
        }
    }
    return ImportedFiles;
}

Answer 4

public static List<string> GetImportedFileList()
{
List<string> ImportedFiles = new List<string>();
    using (SQLiteConnection connect = new SQLiteConnection(@"Data Source=C:\Documents and Settings\js91162\Desktop\CMMData.db3"))
    {
        connect.Open();
        using (SQLiteCommand fmd = connect.CreateCommand())
        {
            fmd.CommandText = @"SELECT DISTINCT FileName FROM Import";
fmd.CommandType = CommandType.Text;
            SQLiteDataReader r = fmd.ExecuteReader();
            while (r.Read()) 
            {
                ImportedFiles.Add(Convert.ToString(r["FileName"]));

            }
    }
}
return ImportedFiles;
    }

Things i've amended in your code:

• Put ImportedFiles in scope of the entire method.
• No need to call connect.Close(); since the connection object is wrapped in a using block.
• Use Convert.ToString rather then (String) as the former will handle all datatype conversions to string. I came across this Here

Edit:

You were creating a new command object sqlComm instead of using fmd that was created by the connection object.