0

The following code:

public IEnumerable<AccountStructModel> Query(string query)
{
    List<AccountStructModel> output = new List<AccountStructModel>();

    using (SqlConnection connection = new SqlConnection("MyConnectionString"))
    {
        connection.Open();

        using (SqlCommand command = new SqlCommand())
        {
            command.CommandText = query;
            command.CommandType = CommandType.Text;
            command.Connection = connection;

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                for (int x = 0; x < reader.FieldCount; x++)
                {
                    output.Add(new AccountStructModel { Username = reader[1].ToString(), Password = reader[2].ToString() });
                }
            }

            command.Dispose();
        }

        connection.Close();
        connection.Dispose();
    }

    return output;
}

Gives the following output:

Username | Password
---------|---------
  Tim    | randomPassword

The problem starts here:

new AccountStructModel { Username = reader[1].ToString(), Password = reader[2].ToString() }

I don't seem to be able to find a way to implement a loop that would take care of the following: reader[i] instead of reader[1], reader[2], reader[3], reader[4], maybe the answer is obvious but I don't seem to be able to find it that easily.

coder
  • 8,346
  • 16
  • 39
  • 53
Dan
  • 67
  • 1
  • 5
  • 1
    It's really not clear what you're asking. Does this code work? If so, then what do you want us to help with? If not, what is the problem? – DavidG Jun 06 '18 at 09:28
  • Do you want a loop that gives the properties their values? If so, you can do this using [reflection](https://stackoverflow.com/questions/619767/set-object-property-using-reflection), but I wouldn't recommend it. That line looks fine as it is. – Arnaud VdP Jun 06 '18 at 09:30
  • 1
    It is not quite clear why you seem to want to iterate over all columns of one record or simply access specific columns by index. To me it looks like you just need to throw out the inner for-loop. – Filburt Jun 06 '18 at 09:31
  • 2
    Why not accessing the reader columns by name? like redaer["username"] ? – CodeNotFound Jun 06 '18 at 09:32
  • reader[0] is a record, reader[1] is the next record in the field and instead of writing reader[ + the number of the record ] can I just make a loop that would take care of that? – Dan Jun 06 '18 at 09:34
  • Wait? reader["username"] ? Let me try this – Dan Jun 06 '18 at 09:35
  • 2
    `reader[0]` is not a record, it's a field of the current record that `reader` is pointing to. – DavidG Jun 06 '18 at 09:35
  • Use a SQLDataAdapter instead of SQLDataReader. The SQLDataAdapter will automatically file a DataTable so you do not need to use a loop. Then you can use linq to read table into a class. Usually I find a DataTable can eliminate the need for a class. – jdweng Jun 06 '18 at 09:36
  • It doesn't take string as an argument, it returns "out of range" exception – Dan Jun 06 '18 at 09:36
  • Okay I'll try DataAdapter – Dan Jun 06 '18 at 09:37
  • You are adding a User for each _colum_ (not row) that you find. Doesn't look logical. – bommelding Jun 06 '18 at 09:39
  • Yea I just noticed, also reader["account-username"] actually worked, I wrote "username" instead of "account-username" that's why gave me an error – Dan Jun 06 '18 at 09:45
  • Anyway thanks I think I solved my issue – Dan Jun 06 '18 at 09:45
  • @Dan take a look at Dapper.Net. It will reduce a lot the code you wrote. – CodeNotFound Jun 06 '18 at 09:47

0 Answers0