Code not working properly datareader from database in C#

Hi I got an InvalidCastException saying that "Specified cast is not valid". I don't know where is the problem. Does my code has an error? Wordlist column is text field.

This is my code:

 public static void Load_Processing_Words()
        {
            OleDbConnection con = new OleDbConnection();
            con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\USDB\USDB.accdb; Persist Security Info=False;";
            con.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = con;
            string query = "Select Wordlist from Words";
            cmd.CommandText = query;
            OleDbDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            //while (str != null)
            {
                string str = reader.GetString(0);
                char[] chArray;
                string[] strArray;
                string str2;
                if (str.Contains("Postfix_Exception"))
                {
                    str = reader.GetString(0);
                    chArray = new char[] { '\t', '\n', '\r' };
                    while (!str.Contains("Prefix_Exception"))
                    {
                        strArray = str.Split(chArray, StringSplitOptions.RemoveEmptyEntries);
                        if (strArray.Length != 0)
                        {
                            str2 = strArray[0];
                            if (!Postfix_Exception.Contains(str2))
                            {
                                Postfix_Exception.Add(str2, 1);
                            }
                        }
                        str = reader.GetString(0);
                    }
                }
              }
                con.Close();
        }
Jon Skeet
people
quotationmark

Basically, you're not using the reader properly. DbDataReader.Read() returns a Boolean value indicating whether or not it's managed to need another row of results. You're currently treating it as if it returns the next result, returning null if it's reach the end of the stream. That's simply not how DbDataReader works.

Once you've moved onto the next result, you then need to call GetString or the indexer to get the data yourself. For example, your loop should probably be something like:

while (reader.Read())
{
    string word = reader.GetString(0);
    // Use it
}

Now, that will read one result at a time - but it sounds like you actually really care about the order in which you read results, as if there's one line followed by a bunch of other related words. That sort of structure should be reflected in your database - you shouldn't just assume that "list of lines in text file == list of rows in database". Aside from anything else, you haven't specified any ordering in your query, so the database is free to return those rows in any order it wants, probably losing your implicit structure. More generally, one row in a table shouldn't depend on "the next row"... if you want relationships between rows, those should be expressed in the data.

You should also use using statements for the connection, command and reader.

people

See more on this question at Stackoverflow