Not all code paths return a value for Array

I'm getting the following error:

Not all code paths return a value for Array

Here is my code:

public Product[] LoadAllDatas()
    {
        SqlConnection con = new SqlConnection("Server=####; Integrated Security=true;Database=Store");

        SqlCommand cmd = new SqlCommand("usp_LoadTestData");
        cmd.Connection = con;
        con.Open();
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataReader dr = cmd.ExecuteReader();

        while (dr.Read())
        {
            return new Product[]
            {
                new Product() { ProductId = Convert.ToInt32(dr["Id"]), Name = dr["Name"].ToString(), AdminContent = dr["AdminComment"].ToString(), ProductTemplate = dr["ProductTemplateId"].ToString(), CreatedOnUtc = Convert.ToDateTime(dr["CreatedOnUtc"]) }
            };
        }

        con.Close();
        con.Dispose();
    }
Jon Skeet
people
quotationmark

Well, you basically need to think about what happens if dr.Read() returns false - i.e. if there are no results. You may well want to throw an exception in that case... or possibly return an empty array.

Additionally, you should use using statements for your reader, command and your connection, to close them - currently if you do return a result, you don't close the connection.

Finally, you're always going to return an array which only contains a single Product. Is that really what you want? I would expect that you'd want to return an array with all the results.

Here's some sample code addressing all of these issues, returning an empty array in the case of no results:

public Product[] LoadAllDatas()
{
    using (SqlConnection con = new SqlConnection(...))
    {
        con.Open();
        using (SqlCommand cmd = new SqlCommand("usp_LoadTestData", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
                List<Product> results = new List<Product>();
                while (dr.Read())
                {
                    results.Add(new Product { 
                         ProductId = Convert.ToInt32(dr["Id"]),
                         Name = dr["Name"].ToString(),
                         AdminContent = dr["AdminComment"].ToString(),
                         ProductTemplate = dr["ProductTemplateId"].ToString(),
                         CreatedOnUtc = Convert.ToDateTime(dr["CreatedOnUtc"])
                    });
                }
                return results.ToArray();
            }
        }
    }
}

One last note - if the values in your reader are of an appropriate type, I'd expect you to be able to simplify your Product instantiation to:

new Product
{ 
     ProductId = (int) dr["Id"],
     Name = (string) dr["Name"],
     AdminContent = (string) dr["AdminComment"],
     ProductTemplate = (string) dr["ProductTemplateId"],
     CreatedOnUtc = (DateTime) dr["CreatedOnUtc"]
}

people

See more on this question at Stackoverflow