Jake Sankey Jake Sankey -4 years ago 139
C# Question

C# sqlite query results to list<string>

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download