Read all record in sql table using SqlDataReader-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

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

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

CodePudding user response：

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