none
get a data on my textbox from database using 3 layer architecture.. RRS feed

  • Question

  • here is my code using 3 layer but it is not working.

    i have used these files in my code 1.BELayer,  2. DALayer,  3. BALayer and last 4. Main page

    BELayer:
    public string quantity { get; set; }
    public string prod_id { get; set; }
    public string party_id { get; set; }  




    DALayer page code:
    //command for auto fill data into quantity table;        public SqlDataReader autofillparty_id(BEL beobj) 
           {
                try
                {
                                    cmd = new SqlCommand("select * from product where prod_id='" + beobj.prod_id + "'", con); 
                   cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "spsearchpartyid";
                    cmd.Parameters.AddWithValue("@prod_id", beobj.prod_id);
                    con.Open();
                    SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);   
                 return sdr;
                     } 
               catch (Exception ex)
                {
                    throw ex; 
               }  
              finally 
                { 
                  con.Close();
                } 
           }




    BALayer code
    //command for autofill data into table quantity..
            public SqlDataReader autofillquantity(BEL beobj)
            {
                try  
              { 
                   return daobj.autofillparty_id(beobj);
                } 
               catch (Exception ex)
                {
                    throw ex;
                } 
           }
    


    Main Page code::
    private void button1_Click(object sender, EventArgs e) 
           {  
              if (baobj.autofillquantity(beobj).Read())  
              {
                    txtboxpartyidquant.Text = (beobj.party_id.ToString());
              } 
           }
    


     this code is not working i know problem is in my main page but i don't know how can i solve this problem please help me ...
    Friday, February 14, 2020 10:20 AM

All replies

  • I see at least one error un your DALayer:

    You are returning a SqlDataReader, but you have a "finally" that closes the connection. Upon doing the return, the finally block is first invoked before returning. Therefore the connection is closed, and the datareader becomes useless; you cannot read any data through it.

    Don't return a SqlDataReader from the DA layer. In fact, any of the objects that start with "Sql" should not be visible from any layers above the DA. These are data-access objects and only the DA should have knowledge of them. Use the Sql classes only in the DA to read data into an entity class (that's why you have the BE) and return these entities from the DA to the upper layers.

    Friday, February 14, 2020 1:16 PM
    Moderator
  • Your SQL command starts with a select but you set the command type as a stored procedure. Then you replace it with a sproc name. Just use the sproc.

    You should also never return a data reader from a method directly unless it is a private method being used by a higher level public method. 

    An example:

    public class MyData
    {
       //Use appropriate types
       public int Quantity { get; set; }
    
       public string ProductId { get; set; }
       public string PartyId { get; set; }
    }
    
    //Data layer?
    public MyData GetProduct ( string productId )
    {
       //You should really create the connection at the point you need it...
       var cmd = new SqlCommand("spsearchpartyid", con);
       cmd.Parameters.AddWithValue("@prod_id", productId);
    
       con.Open();
       using (var reader = cmd.ExecuteReader())
       {
          if (reader.Read())
          {
             //Completely making up column names, match to your sproc return value
             return new MyData()
             {
                ProductId = reader.GetString(reader.GetOrdinal("prod_id")),
                PartyId = reader.GetString(reader.GetOrdinal("party_id")),
                Quantity = reader.GetInt32(reader.GetOrdinal("quantity"))
             };
    
          };
       };
    
       return null;
    }
    
    //Higher level code
    var product = baObj.GetProduct("123");
    if (product != null)
       txtboxpartidquant.Text = product.PartyId;
    


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, February 14, 2020 2:55 PM
    Moderator
  • Hi kamlesh,

    Thank you for posting here.

    When I use ADO.NET, I generally use using {} so that I don't need to think about when to close it.

                using (SqlConnection conn = new SqlConnection(""))
                {
                    using (SqlCommand command = new SqlCommand("",conn))
                    {
                        //Do something...
                    }
                }

    I think this is a good habit.

    using statement (C# Reference)

    Best Regards,

    Timon


    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.


    Monday, February 17, 2020 5:47 AM
  • [...] I generally use using {} [...] I think this is a good habit.

    Yes, it is a good habit... but in this particular case, the OP would still suffer from the same problem even if he had been using using. The problem was that he was returning the data access object (a SqlDataReader) from his data layer to the upper layer, but his finally statement was closing the connection before returning so the returned SqlDataReader became invalid. If you were to write the same code with a using statement, you would still have the same problem, because the using would also close the connection upon exiting the block in the same way as the finally block, so the returned SqlDataReader would still be unusable.

    Monday, February 17, 2020 7:23 AM
    Moderator
  • A datareader shouldn't be traveling between layers. A DTO travels between layers.

    https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

    Each layer acts upon the DTO. 

    So the DAL method creates the DTO and maps result of the datareader to a DTO or DTO(s) in a collection and sends the DTO to the presentation layer. The BLL is just the middle man. If the BLL is anemic, has no business logic and is just acting as a pass-through, then why do you need it? Maybe, you use a ServiceLayer that sits between the PL and the DAL.

    The DTO(s) sit in their own classlib project call it Entities,  and all the projects that need to know about the DTO(s) have project reference to Entities. 

    https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

    The DAL could be using the DAO pattern.

    https://javarevisited.blogspot.com/2013/01/data-access-object-dao-design-pattern-java-tutorial-example.html

    https://www.tutorialspoint.com/design_pattern/data_access_object_pattern.htm

    If you're going to use the datareader, then you should look into to using column names in the database table schema to retrieve the data.

     https://www.akadia.com/services/dotnet_data_reader.html

    Example of DAL using the DAO and DTO patterns in a layered solution, which happens to be using EF.

    using System.Collections.Generic;
    using Entities;
    
    namespace DAL
    {
        public interface IDaoAuthor
        {
            List<DtoAuthor> GetAll();
            DtoAuthor Find(int id);
            void Add(DtoAuthor dto);
            void Update(DtoAuthor dto);
            void Delete(int id);
        }
    }
    ======================================================
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Transactions;
    using DAL.Models;
    using Entities;
    using Microsoft.EntityFrameworkCore;
    
    namespace DAL
    {
        public class DaoAuthor : IDaoAuthor
        {
            private PublishingCompanyContext pc;
    
            public DaoAuthor(PublishingCompanyContext dbcontext)
            {
                pc = dbcontext;
            }
    
            public List<DtoAuthor> GetAll()
            {
                var dtos = new List<DtoAuthor>();
    
                var authors =  pc.Author.AsNoTracking().ToList();
    
                dtos.AddRange(authors.Select(author => new DtoAuthor()
                {
                    AuthorId = author.AuthorId,
                    FirstName = author.FirstName,
                    LastName = author.LastName
                }).ToList());
    
                return dtos;
            }
    
            public DtoAuthor Find(int id)
            {
                var dto = new DtoAuthor();
    
                var author =  pc.Author.AsNoTracking().SingleOrDefault(a => a.AuthorId == id);
    
                if (author != null)
                {
                    dto.AuthorId = author.AuthorId;
                    dto.FirstName = author.FirstName;
                    dto.LastName = author.LastName;
                }
                else
                {
                    throw new Exception($"Author with ID = {id} was not found.");
                }
    
                return dto;
            }
           
            public void Add(DtoAuthor dto)
            {
                var author = new Author
                {
                    FirstName = dto.FirstName,
                    LastName = dto.LastName
                };
    
                pc.Author.Add(author);
                pc.SaveChanges();
            }
            public void Update(DtoAuthor dto)
            {
                var author = pc.Author.Single(a => a.AuthorId == dto.AuthorId);
    
                author.FirstName = dto.FirstName;
                author.LastName = dto.LastName;
                
                pc.SaveChanges();
            }
    
            public void Delete(int id)
            {
                var author = pc.Author.SingleOrDefault(a => a.AuthorId == id);
                    
                if (author != null)
                {
                    using (TransactionScope transScope = new TransactionScope())
                    {
                        var articles = pc.Article.Where(a => a.AuthorId.ToString().Contains(id.ToString())).ToList();
    
                        if (articles.Any())
                        {
                            foreach (var article in articles)
                            {
                                pc.Article.Remove(article);
                            }
    
                            pc.SaveChanges();
                        }
    
                        var payrolls = pc.Payroll.Where(a => a.AuthorId.ToString().Contains(id.ToString()));
    
                        if (payrolls.Any())
                        {
                            foreach (var payroll in payrolls)
                            {
                                pc.Payroll.Remove(payroll);
                            }
    
                            pc.SaveChanges();
                        }
    
                        pc.Author.Remove(author);
                        pc.SaveChanges();
    
                        transScope.Complete();
                    }
                }
            }
        }
    }
    
    

    Monday, February 17, 2020 8:10 AM
  • [...] I generally use using {} [...] I think this is a good habit.

    Yes, it is a good habit... but in this particular case, the OP would still suffer from the same problem even if he had been using using. The problem was that he was returning the data access object (a SqlDataReader) from his data layer to the upper layer, but his finally statement was closing the connection before returning so the returned SqlDataReader became invalid. If you were to write the same code with a using statement, you would still have the same problem, because the using would also close the connection upon exiting the block in the same way as the finally block, so the returned SqlDataReader would still be unusable.

    To clarify.

    Sorry I didn't make it clear, I know this, but I saw that you have explained this in detail, so I didn't mention it again in my reply.

    Best Regards,

    Timon


    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.

    Monday, February 17, 2020 10:16 AM
  • [...] I generally use using {} [...] I think this is a good habit.

    Yes, it is a good habit... but in this particular case, the OP would still suffer from the same problem even if he had been using using. The problem was that he was returning the data access object (a SqlDataReader) from his data layer to the upper layer, but his finally statement was closing the connection before returning so the returned SqlDataReader became invalid. If you were to write the same code with a using statement, you would still have the same problem, because the using would also close the connection upon exiting the block in the same way as the finally block, so the returned SqlDataReader would still be unusable.

    To clarify.

    Sorry I didn't make it clear, I know this, but I saw that you have explained this in detail, so I didn't mention it again in my reply.

    Best Regards,

    Timon


    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.

    Monday, February 17, 2020 10:18 AM