CODEWITHSUNDEEP
c#sqlite
Jake Sankey
Jake Sankey

Reputation: 5137

C# sqlite query results to list<string>

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

Upvotes: 15

Views: 81164

Answers (4)

Bablo
Bablo

Reputation: 916

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.

Upvotes: 34

Nikos Steiakakis
Nikos Steiakakis

Reputation: 5745

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

Upvotes: 3

JustABill
JustABill

Reputation: 1559

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

Upvotes: 0

lc.
lc.

Reputation: 116498

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

Upvotes: 0

Related Questions