Creating Relational class objects

Question

I have table named Products and Categories, one of Products table's row include data like that:

ProductId,ProductName,CategoryId

And Categories include

CategoryId,CategoryInfo

So I m getting rows with using ExecudeReader() using ado.net like

List<ProductEntity> lst = new List<ProductEntity>();
using (SqlConnection connection = new SqlConnection(constr))
        {
            SqlCommand command = connection.CreateCommand();
            command.CommandText = "select ProductId,ProductName,CategoryId from Products";
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    lst.Add(new ProductEntity()
                    {
                        ProductId = int.Parse(reader[0].ToString()),
                        ProductName= reader[1].ToString(),
                        CategoryId=int.Parse(reader[2].ToString())
                    });
                }
            }
        }

But I want to get CategoryInfo from Categories table too without using another ExecuteReader(). Can I create one entity class for both Product and Category if yes How I do that? Or another best practice exist?

What value cmdText has when used as command text?

MarcinJuraszek
– MarcinJuraszek

2013-03-13 07:20:22 +00:00
Commented Mar 13, 2013 at 7:20 — MarcinJuraszek
– MarcinJuraszek, Commented Mar 13, 2013 at 7:20
use joins if it is possible

TalentTuner
– TalentTuner

2013-03-13 07:21:48 +00:00
Commented Mar 13, 2013 at 7:21 — TalentTuner
– TalentTuner, Commented Mar 13, 2013 at 7:21

MarcinJuraszek · Accepted Answer · 2013-03-13 07:24:38Z

1

Change your query to:

SELECT p.ProductId, p.ProductName, p.CategoryId, c.CategoryInfo
FROM Products p
JOIN Categories c ON c.CategoryId = p.CategoryId

And get CategoryInfo as a fourth element returned by query:

CategoryInfo= reader[3].ToString()

To make it clear - you have to have CategoryInfo property within ProductEntity class.

answered Mar 13, 2013 at 7:24

MarcinJuraszek

126k15 gold badges200 silver badges267 bronze badges

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

Collectives™ on Stack Overflow

Creating Relational class objects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related