-2

I am trying to read all the records from a local sqlite file. But for some reason, the code that I am using isn't reading all records. It is reading only the second row, that too only few columns, not all columns as well from that row.

Below are the records that my sample sqlite file has.

1|ABC - Student1|1E|TX|01122017|BLK|120|9210001010|20121|sample1
1|DEF - Student2|2E|AZ|01112017|BLK|110|9210002020|20122|sample2
1|GHI - Student3|3E|TX|01102017|BLK|130|9210003030|20123|sample3
1|JKL - Student4|4E|AZ|01092017|BLK|100|9210004040|20124|sample4
1|MNO - Student5|5E|TX|01082017|BLK|140|9210005050|20125|sample5

When I open my sample file in notepad, below is how I see the data:

1ABC - Student11ETX01122017BLK120921000101020121sample1K
1DEF - Student22EAZ01112017BLK110921000202020122sample2Q
1GHI - Student33ETX01102017BLK130921000303020123sample3P
1JKL - Student44EAZ01092017BLK100921000404020124sample4Y
1MNO - Student55ETX01082017BLK140921000505020125sample5

When I try to read all the records from code, it is fetching only the 2nd record, that too not all columns. It is fetching only 5 columns. Below is the code that I am using to read the file:

public List<string> ReadStuent()
        {
            var conn = new SQLiteConnection(@"Data Source=C:\Sqlite\Students.sqlite");
            SQLiteDataReader datareader;
            
            int count = 0;
            var StudentData= new List<string>();
            conn.Open();
            
            SQLiteCommand cmd = new SQLiteCommand(conn);
            cmd.CommandText = "SELECT * FROM Students";
            

            datareader = cmd.ExecuteReader();
            while (datareader.Read())
            {
                StudentData.Add(datareader.GetString(count));
                count++;
            }
            conn.Close();
            return StudentData;
        }

Can someone help me what wrong I am doing here?

user243724
  • 13
  • 1
  • 2
    Based on your code I don't think you are getting the second record. It looks like you are getting a different column from each record, at the end the single record you see is actually a mix of the first 5 columns from all records. The first time through your `while` loop you get record 1, column 1; the second time through your loop you get record row 2, column 2, the third time through your loop you get record 3, column 3... for the fourth and fifth loop you are getting record 4, column 4, and record 5, column 5. – quaabaam Feb 21 '23 at 21:17
  • The reason you only see 5 columns in your output is because you are confusing the rows and columns in your loop. Your loop is looping the five records and using the count based on record rows to get the columns will limit your column output to the number of rows, in this case 5 rows = 5 columns. – quaabaam Feb 21 '23 at 21:19
  • Understood. Could you also please tell me what should I do instead of the above code that I am using? – user243724 Feb 21 '23 at 21:25
  • 1
    Hey, use the built in debugging (breakpoints and step through) features of your IDE to get more detailed information about why it isn't working how you expect – Richard Bamford Feb 21 '23 at 21:28
  • You're trying to put multiple columns into a single string. That's not going to work. Before we can give you an answer we need to know which columns you want, and what you're going to do with them. Also, telling us the schema of your `Students` table would help. – Corvus Feb 21 '23 at 21:42

2 Answers2

0
public List<string> ReadStuent()
        {
            var conn = new SQLiteConnection(@"Data Source=C:\Sqlite\Students.sqlite");
            SQLiteDataReader datareader;
            
          
            var StudentData= new List<string>();
            conn.Open();
            
            SQLiteCommand cmd = new SQLiteCommand(conn);
            cmd.CommandText = "SELECT * FROM Students";
            

            datareader = cmd.ExecuteReader();
            while (datareader.Read())
            {
                StudentData.Add(datareader["nameoffieldhere"].ToString());
             
            }
            conn.Close();
            return StudentData;
        }
Justin Kirk
  • 129
  • 6
  • Oh, I can't edit it, but it should be the name of the column, not the name of the field. Also if you want multiple columns of data put into your list of strings you need to concatenate it like "datareader["nameofcolumn1"].ToString() + datareader["nameofcolumn2"].ToString() etc – Justin Kirk Feb 21 '23 at 21:39
0

Below is a basic example to to get you going forward. There are other problems with your approach but I will leave those details out and focus on your code example.

// The datareader.Read() below sets the current row and will increment
// the current row with each loop iteration
while (datareader.Read())
{
    // iterate all columns on the datareader to get values
    for(var i = 0;i < datareader.FieldCount; i++)
    {
        // get current column from current row
        studentData.Add(datareader.GetString(i));
    }
}
quaabaam
  • 1,808
  • 1
  • 7
  • 15