CODEWITHSUNDEEP
c#sql-server-2008ado.net
Michael Riva
Michael Riva

Reputation: 541

Creating Relational class objects

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?

Upvotes: 0

Views: 106

Answers (1)

MarcinJuraszek
MarcinJuraszek

Reputation: 125630

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.

Upvotes: 1

Related Questions