Read all record in sql table using SqlDataReader

Question

I want to read all records from "product" table and create objects from each records. it only gets one records from the database, any ideas might help ?

public IReadOnlyList<Product> Search(string name)
{
    var result = new List<Product>();

    using (var conn = new SqlConnection(connectionString))
    {
        if (name == null)
        {
            var command = new SqlCommand("SELECT * FROM Product ", conn);
            conn.Open();

            using var reader = command.ExecuteReader();
            {
                    
            while (reader.Read())
            {
                var prod = new Product((int)reader["ID"], (string)reader["Name"],
                    (double)reader["Price"], (int)reader["Stock"], (int)reader["VATID"],
                    (string)reader["Description"]);

                result.Add(prod);
                reader.NextResult();

            }
            reader.Close();
            conn.Close();
            return result;
        };
    }
}

Why needed reader.NextResult();? You have while loop on reader.Read(). — Auditive
– Auditive, Commented Oct 21, 2021 at 14:56
You needn't it. reader.Read() switches to next record and returns true if next record exists. Otherwise false, which means there no more records to read. — Auditive
– Auditive, Commented Oct 21, 2021 at 14:58
If I get rid of it, this error occur : Unable to cast object of type 'System.DBNull' to type 'System.String. — Yassine Mrabet
– Yassine Mrabet, Commented Oct 21, 2021 at 14:59

Dmitrii Bychenko · Accepted Answer · 2021-10-21 15:17:52Z

1

If you have several result sets, you should loop over them, i.e. you should put one more outer loop, e.g.

using var reader = command.ExecuteReader();

do {
  while (reader.Read()) {
    var prod = new Product(
      Convert.ToInt32(reader["ID"]), 
      Convert.ToString(reader["Name"]),
      Convert.ToDouble(reader["Price"]), // decimal will be better for money
      Convert.ToInt32(reader["Stock"]), 
      Convert.ToInt32(reader["VATID"]),
      Convert.ToString(reader["Description"])
    );

    result.Add(prod); 
  }
}
while (reader.NextResult());

Note outer do .. while loop since we always have at least one result set.

edited Oct 21, 2021 at 15:17

answered Oct 21, 2021 at 15:12

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yassine Mrabet Over a year ago

YES, thank you this works perfectly !

Tim Schmelter · Accepted Answer · 2021-10-21 15:15:57Z

You use NextResult which advances the reader to the next result set. This makes sense if you have multiple sql queries and you'd use it after the while-loop. Here it's just unnecessary and wrong.

You are already advancing the reader to the next record with Read.

If I get rid of it, this error occur : Unable to cast object of type 'System.DBNull' to type 'System.String.

You can use IsDBNull:

int nameIndex = reader.GetOrdinal("Name");
string name = reader.IsDBNull(nameIndex) ? null : reader.GetString(nameIndex);
int descIndex = reader.GetOrdinal("Description");
string description = reader.IsDBNull(descIndex) ? null : reader.GetString(descIndex);

var prod = new Product((int)reader["ID"], 
                       name, 
                       (double)reader["Price"], 
                       (int)reader["Stock"], 
                       (int)reader["VATID"], 
                       description);

Use it for every nullable column, for the numeric columns you could use nullable types like int?.

loopedcode · Accepted Answer · 2021-10-21 14:58:38Z

0

You have an error in your code: Remove the line reader.NextResult();

NextResult is used for moving to next result set not next record.

answered Oct 21, 2021 at 14:58

loopedcode

4,9131 gold badge24 silver badges22 bronze badges

Comments

Joel Coehoorn · Accepted Answer · 2021-10-21 15:24:48Z

0

Definitely remove the NextResult(). That does NOT move between individual records in the same query. Read() does this for you already. Rather, NextResult() allows you to include multiple queries in the same CommandText and run them all in one trip to the database.

Try this:

public IEnumerable<Product> Search(string name)
{
    using (var conn = new SqlConnection(connectionString))
    using (var command = new SqlCommand("SELECT * FROM Product ", conn))
    {
        if (!string.IsNullOrEmpty(name) )
        {
           command.CommandText += " WHERE Name LIKE @Name + '%'";
           command.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = name;
        }

        conn.Open();
        using var reader = command.ExecuteReader();
        {                    
            while (reader.Read())
            {
                var prod = new Product((int)reader["ID"], reader["Name"].ToString(),
                    (double)reader["Price"], (int)reader["Stock"], (int)reader["VATID"],
                    reader["Description"].ToString());

                yield return prod;
            }
        }
    }
}

edited Oct 21, 2021 at 15:24

answered Oct 21, 2021 at 15:00

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

2 Comments

Yassine Mrabet Over a year ago

okey, I removed it but still "Unable to cast object of type 'System.DBNull' to type 'System.String" error occurs.

Joel Coehoorn Over a year ago

Then you need to add code to the new Product() section to look for those NULL values. Also, the Price value should be decimal rather than double. NEVER use double for money.

Collectives™ on Stack Overflow

Read all record in sql table using SqlDataReader

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related