none
Get Master Details From a Database with Datareader RRS feed

  • Question

  • Greetings to the community, I have a question, I have 3 tables (Project, Phases and Comments), a master and two details

    the relationship is one to many between Project and Phases, and one to one between Project and Comments

    The problem is that I don't know how to return the data using ADO.NET, this is what I have so far, the classes

        public class EntidadProyecto
        {
    	public EntidadProyecto(){
    	this.Fase = new List<BEFaseProyecto>();
    	this.Comentario = new List<BEComentario>();}
    	public int IDProyecto { get; set; }
    	public string Nombre { get; set; }
    	public string Descripcion { get; set; }
    	public List<BEFaseProyecto> Fase { get; set; }
    	public List<BEComentario> Comentario { get; set; }
    	}
    	
    	public class BEFaseProyecto
        {
    	  public string FechaInicio { get; set; }
    	  public string FechaFin { get; set; }
    	  public decimal? Monto { get; set; }
    	}
    	
         public class BEComentario
    	{
    	  public int IDComentario { get; set; }
    	  public string Descripcion { get; set; }
    	}

    This is how I return the data I return it in a query

    And this is my code so far

    public Lista<EntidadProyecto> GetProyectoObtener(Int32 IDProyecto){
               
    	List<EntidadProyecto> lstLista = new List<EntidadProyecto>();
       
    	try
    	{
    	OpenConnection();
    	using (SqlCommand myComm = GetConnection().CreateCommand())
    	{
    		myComm.CommandType = CommandType.StoredProcedure;
    		myComm.CommandText = "MiProcedimiento";
    		myComm.Parameters.AddWithValue("@ID", IDProyecto);
    
    		SqlDataReader myReader = myComm.ExecuteReader();
    		while (myReader.HasRows)
    		{
    		EProyecto eProyecto = new EProyecto();
    		eProyecto.IDProyecto = Convert.ToInt32(myReader["IDProyecto"]);
    		eProyecto.Nombre = Convert.ToString(myReader["Nombre"]);
    		eProyecto.Descripcion = Convert.ToString(myReader["Descripcion"]);
    
    
    		EFaseProyecto fase = new EFaseProyecto();
    		fase.FechaInicio= Convert.ToString(myReader["FechaInicio"]);
    		fase.FechaInicio = Convert.ToString(myReader["FechaInicio"]);
    		fase.Monto= Convert.ToDecimal(myReader["Monto"]);
    
    		eProyecto.Fase.Add(fase);
    		........
    			





    • Edited by Augusto C Sunday, September 15, 2019 11:42 PM
    Wednesday, September 11, 2019 4:26 PM

Answers

  • Hi Karen, thanks for your reply.
    The problem of repetition or double population of data occurs here.

        while (myReader.Read()){
        BEProyecto eProyecto = new BEProyecto();
        eProyecto.IDProyecto = Convert.ToInt32(myReader[0]);
        eProyecto.Nombre = Convert.ToString(myReader[1]);
        eProyecto.Descripcion = Convert.ToString(myReader[2]);

    when I go to the database to bring the data and fill the object with the readers

    On the other hand your code is very interesting I will use it for other purposes

    I will use it by filling the object as many times as it is in the reader since I can't find any other solution, anyway it puts the same data

    You can find a few code samples as per what I posted along with using IEqualityComparer interface and IEquatable Interface that may prove helpful in this repository.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Augusto C Wednesday, September 18, 2019 3:41 PM
    Tuesday, September 17, 2019 9:08 PM
    Moderator

All replies

  • To give suggestions please indicate if the master-detail you want are for a single master record and child tables or is the master going to be fully populated? What is the context for this e.g. a grid, input controls, both etc. ?

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, September 11, 2019 4:38 PM
    Moderator
  • Hi Karen, thank you very much for your response,

    lines above I show how the data is returned from sql, there the master columns and details columns  are combined, what I need is for the Project(proyecto in spanish) entity to be completely populated with the data of this entity and those of his two details,
    Later I must populate controls, but for now I just need to fill it

    this is my query

    CREATE PROCEDURE uspGetProject
    @ID int
    )
    AS
    SELECT P.IDProyecto,
    		 P.Nombre,
    		 P.Descripcion,
    		 FP.FechaInicio,
    		 FP.FechaFin,
    		 FP.Monto,
    		 c.Descripcion
    FROM Project P
    LEFT JOIN Fase FP ON P.IDProyecto=FP.IDProyecto
    LEFT JOIN Coments C  ON FP.IDProyecto=C.IDReferenciaPadre
    WHERE P.IDProyecto=@ID

    Is something like this only I have two details

    Reading multiple child objects from SQL Server

    Wednesday, September 11, 2019 5:03 PM
  • IMO, you have to read for each table individually

     The easiest way is to do the reads using a stored procedure. You do s select statement for the patent tecod that returns s resultset. You do select statement for each child table returning each resultset.

    You will have a multiple resultset returned to the datareader by the stored procedure and the datareader reads the parent resultset, does a datareader nextresult for each child result and reads the resultset.

    You can do the same thing and not use a stored procedure and doing the individual read of the parent and child tables in C# code using a datareader for each select statement.

    The other thing you can do is learn how to use the ADO.NET Entity Framework that makes the whole thing you are trying to do a simple thing to do in C# code.

    http://csharp.net-informations.com/data-providers/csharp-multiple-resultsets.htm

     


    • Edited by DA924x Thursday, September 12, 2019 6:53 AM more information
    Thursday, September 12, 2019 6:40 AM
  • Hi Augusto C,

    Thank you for posting here.

    First, I want to mention that your stored procedure has some wrong code. Please change this to the following code:

         myComm.CommandText = "uspGetProject"; // here is stored procedure name
         myComm.Parameters.AddWithValue("@ID", IDProyecto);// here is parameter 

    Second, I think we  should return List<BEProyecto> instead of List<EntidadProyecto>.

    Last, you could try the following code to get the list.

      private void button1_Click(object sender, EventArgs e)
            {
                var list = GetProyectoObtener(1001);
    
            }
            public List<BEProyecto> GetProyectoObtener(Int32 IDProyecto)
            {
    
                List<BEProyecto> lstLista = new List<BEProyecto>();
    
                try
                {
                    string con = @"connectionstring";
                    SqlConnection connection = new SqlConnection(con);
                    connection.Open();
    
                    using (SqlCommand myComm = new SqlCommand())
                    {
                        myComm.Connection = connection;
                        myComm.CommandType = CommandType.StoredProcedure;
                        myComm.CommandText = "uspGetProject";
                        myComm.Parameters.AddWithValue("@ID", IDProyecto);
    
                        SqlDataReader myReader = myComm.ExecuteReader();
                        while (myReader.Read())
                        {
                            BEProyecto eProyecto = new BEProyecto();
                            eProyecto.IDProyecto = Convert.ToInt32(myReader[0]);
                            eProyecto.Nombre = Convert.ToString(myReader[1]);
                            eProyecto.Descripcion = Convert.ToString(myReader[2]);
    
    
                            BEFaseProyecto fase = new BEFaseProyecto();
                            fase.FechaInicio = Convert.ToString(myReader[3]);
                            fase.FechaInicio = Convert.ToString(myReader[4]);
                            fase.Monto = Convert.ToDecimal(myReader[5]);
                            BEComentario comentario = new BEComentario();
                            comentario.Descripcion = Convert.ToString(myReader[6]);
                            eProyecto.Fase.Add(fase);
                            eProyecto.Comentario.Add(comentario);
                            lstLista.Add(eProyecto);
                        }
                    }
    
    
                }
                catch(Exception E)
                {
                    MessageBox.Show(E.Message);
                }
    
                return lstLista;
            }

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 12, 2019 8:15 AM
    Moderator
  • Hello,

    I recommend considering Entity Framework Core or Entity Framework classic (version 6) as DA924x mentioned also.

    Basics steps

    • Ensure the relationships are sound in the database as the steps below by scaffolding or wizard mode will generate relations for each class that has related data.
    • Scaffold your tables via Entity Framework Core tools. For Entity Framework Classic add a new item to the project under data tab, select ADO.NET Entity Data Model, select "Code First from database" and follow through the wizard.

      If you feel comfortable moving the classes create a Model folder and move the classes which represent tables into the model folder then either stop there or change the namespace of each class to base namespace.models.
    • Write LINQ statements to get your data into classes like or similar to what you have now.
    • If you need to traverse data in a DataGridView consider the following custom BindingListView.

    The first time writing Entity Framework code may take a little time getting use to but it's more the worth the time it takes to learn. When looking a LEFT JOIN see the following simple example. For Group joins.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, September 12, 2019 11:48 AM
    Moderator
  • Hi Jack

    "First, I want to mention that your stored procedure has some wrong code....."

    you're right those mistakes are for the code transcript, I already corrected them

    The approach you propose I had already thought about, the problem is that the project entity is filled three times with the same data due to the join, I wanted to know if that could be avoided

    Sunday, September 15, 2019 11:49 PM
  • Hi DA924X

    I would prefer to bring the data in block, instead of a select for each table. I do not use E.F.
    I use ADO.NET

    P.D.  WHAT IS IMO?

    Sunday, September 15, 2019 11:52 PM
  • Hi Kareninstructor 

    Thanks for your answer.

    I can see that you are one of the heavyweights of this forum,
    I must tell you that I do not use E.F in any of its versions for this project, I use ADO.NET, switch to E.F would be very dramatic at this point.

    I would prefer if it existed,
    a recommendation focused on ADO.NET

    Regards


    Sunday, September 15, 2019 11:59 PM
  • You want  to do a read for one block. Well that would be fine if you were not trying to make a parent object and two child collection of objects associated to the parent object.

    In My Opinion aka IMO, the optimal approach is to do the Select for the parent, second select is for a set of children and the third select is for the last set of children.

    They are all returned in one block, a single result with multiple resultsets. One datareader reading each resultset in the single result of the 3 resultsets returned to the datareader.

     You should know the read of the first resultset is for the parent and you build the parent object. You move to the next resultset in the datareader knowing that any records read in the resultset is for the the collection of child objects. You move to the next resultset in the datareader and make the child objects for that collection of objects

    This is how you do it that I was taught how to do it many years ago that is still used to this day in using straight up ADO.NET, a sproc that is reading parent and children records  using a datareader and making the parent object and any collection of child objects.


    • Edited by DA924x Monday, September 16, 2019 1:23 AM
    Monday, September 16, 2019 1:21 AM
  • Hi Jack

    "First, I want to mention that your stored procedure has some wrong code....."

    you're right those mistakes are for the code transcript, I already corrected them

    The approach you propose I had already thought about, the problem is that the project entity is filled three times with the same data due to the join, I wanted to know if that could be avoided

    If items are added three times means a check must be done when adding. One way to do this is to a) implement an Interface b) create a generic extension method to work against the interface and a property generic to one or more classes.

    For example, we want to add unique person items to a list of person

    The original person class

    public class Person
    {
        public int Identifier { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

    Create an Interface for an alias to the primary key Identifier as Id

    public interface IBase
    {
        int Id { get; }
    }

    Implement in the class

    public class Person : IBase
    {
        public int Identifier { get; set; }
        public int Id => Identifier;
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

    Create an extension method with a constraint that the caller implements IBase

    public static class Extensions 
    {
        public static void AddUnique<T>(this ICollection<T> self, T item) where T : IBase
        {
            if (self.FirstOrDefault(x => x.Id == item.Id) == null)
            {
                self.Add(item);
            }
        }
    }

    Useage where in this case there are two instances of person with the Identifier of 1, AddUnique will only add the first and last person.

    var people = new List<Person>();
    
    var person1 = new Person() {Identifier = 1, FirstName = "Karen", LastName = "Payne"};
    var person2 = new Person() { Identifier = 1, FirstName = "Karen", LastName = "Payne" };
    var person3 = new Person() { Identifier = 2, FirstName = "Mary", LastName = "Jones"};
    
    
    people.AddUnique(person1);
    people.AddUnique(person2);
    people.AddUnique(person3);

    Since the extension method is generic we can use it on another class as per below as it implements IBase.

    public class Category : IBase
    {
        public int CategoryIdentifier { get; set; }
        public string Name { get; set; }
        public int Id => CategoryIdentifier;
    }

    Usage, three out unique so three are added.

    var categories = new List<Category>();
    var category1 = new Category() { CategoryIdentifier = 1, Name = "Seafood"};
    var category2 = new Category() { CategoryIdentifier = 2, Name = "Bread" };
    var category3 = new Category() { CategoryIdentifier = 3, Name = "Fruit" };
    var category4 = new Category() { CategoryIdentifier = 1, Name = "Seafood" };
    
    categories.AddUnique(category1);
    categories.AddUnique(category2);
    categories.AddUnique(category3);
    categories.AddUnique(category4);
    Hopefully this makes sense.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, September 16, 2019 2:00 AM
    Moderator
  • Hi Karen, thanks for your reply.
    The problem of repetition or double population of data occurs here.

        while (myReader.Read()){
        BEProyecto eProyecto = new BEProyecto();
        eProyecto.IDProyecto = Convert.ToInt32(myReader[0]);
        eProyecto.Nombre = Convert.ToString(myReader[1]);
        eProyecto.Descripcion = Convert.ToString(myReader[2]);

    when I go to the database to bring the data and fill the object with the readers

    On the other hand your code is very interesting I will use it for other purposes

    I will use it by filling the object as many times as it is in the reader since I can't find any other solution, anyway it puts the same data

    Tuesday, September 17, 2019 8:45 PM
  • You want  to do a read for one block. Well that would be fine if you were not trying to make a parent object and two child collection of objects associated to the parent object.

    In My Opinion aka IMO, the optimal approach is to do the Select for the parent, second select is for a set of children and the third select is for the last set of children.

    They are all returned in one block, a single result with multiple resultsets. One datareader reading each resultset in the single result of the 3 resultsets returned to the datareader.

     You should know the read of the first resultset is for the parent and you build the parent object. You move to the next resultset in the datareader knowing that any records read in the resultset is for the the collection of child objects. You move to the next resultset in the datareader and make the child objects for that collection of objects

    This is how you do it that I was taught how to do it many years ago that is still used to this day in using straight up ADO.NET, a sproc that is reading parent and children records  using a datareader and making the parent object and any collection of child objects.


    Hi DA92x

    "In My Opinion aka IMO, the optimal approach is to do the Select for the parent, second select is for a set of children and the third select is for the last set of children.

    They are all returned in one block, a single result with multiple resultsets. One datareader reading each resultset in the single result of the 3 resultsets returned to the datareader......."

    I thought that way too, but I need it all together for the definition of my entities

    public class EntidadProyecto
        {
    	public EntidadProyecto(){
    	this.Fase = new List<BEFaseProyecto>();
    	this.Comentario = new List<BEComentario>();}

    Thanks for the IMO clarification

    Tuesday, September 17, 2019 8:56 PM
  • Hi Karen, thanks for your reply.
    The problem of repetition or double population of data occurs here.

        while (myReader.Read()){
        BEProyecto eProyecto = new BEProyecto();
        eProyecto.IDProyecto = Convert.ToInt32(myReader[0]);
        eProyecto.Nombre = Convert.ToString(myReader[1]);
        eProyecto.Descripcion = Convert.ToString(myReader[2]);

    when I go to the database to bring the data and fill the object with the readers

    On the other hand your code is very interesting I will use it for other purposes

    I will use it by filling the object as many times as it is in the reader since I can't find any other solution, anyway it puts the same data

    You can find a few code samples as per what I posted along with using IEqualityComparer interface and IEquatable Interface that may prove helpful in this repository.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Augusto C Wednesday, September 18, 2019 3:41 PM
    Tuesday, September 17, 2019 9:08 PM
    Moderator
  • What are you talking about you need it altogether in a result returned to the datareader? The problem here is that you do not know how to stich the objects together. What is in the result the 3 resultsets have nothing to to with architecture of the object model and the manual creation of each object manually to be loaded into the collection within the parent. This is OOP 101 and the developer's understanding of how to create and build objects regardless of the data source.
    • Edited by DA924x Tuesday, September 17, 2019 10:44 PM
    Tuesday, September 17, 2019 10:15 PM
  • What are you talking about you need it altogether in a result returned to the datareader? The problem here is that you do not know how to stich the objects together. What is in the result the 3 resultsets have nothing to to with architecture of the object model and the manual creation of each object manually to be loaded into the collection within the parent. This is OOP 101 and the developer's understanding of how to create and build objects regardless of the data source.
    So you think that bringing it separately is the best way? I thought it was better to bring all the data together.
    Wednesday, September 18, 2019 3:41 PM
  • No, I wouldn't be using joins. There would be a select statement for the parent and a select statementl for each child table in the one stored procedure resulting in a multiple resultset being returned to the datareader. From there, each resultset would be read by the reader and I would make the appropriate object or objects as needed.

    • Edited by DA924x Thursday, September 19, 2019 4:32 AM
    Wednesday, September 18, 2019 4:24 PM