none
How to fill combobox with database values using n tier architecture

    Question

  • hi there, i write the code to fill combobox with database value. my code is working correctly if this code written on presentation layer. But i want to written this code on data layer. i have 3 project in my solution (UI, BAL, DLL). i want this code in DLL and then call in UI with the help of BAL. how to do that. please help me. here is my code.

    using (SQLiteConnection conn = new SQLiteConnection(DatabaseConnection.ConnectionString()))
                {
                    string CommandText = "SELECT Name FROM User";
                    using (SQLiteCommand cmd = new SQLiteCommand(CommandText, conn))
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        DataTable dt = new DataTable();
                        SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                        da.Fill(dt);
                        foreach (DataRow dr in dt.Rows)
                        {
                            namecombobox.Items.Add(dr["Name"].ToString());
                        }
                    }
                }

    Friday, January 11, 2019 4:25 PM

Answers

  • Since you are using datatable, you can use VO (value object ) o or DTO (Data Transfer Object) and either one  you can use. The VO is being used in the example where the DAL is usinf the DAO pattern.

    https://en.wikipedia.org/wiki/Data_access_object

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

    https://www.codeproject.com/Articles/36847/Three-Layer-Architecture-in-C-NET

    Below, I am using the DAO and DTO pattern in the DAL using Entity Framework Core

    Also, you're not using n-tier you are using layered, which is similar to n-tier.

    https://docs.microsoft.com/en-us/previous-versions/msp-n-p/ee658117(v=pandp.10)

    namespace DAL
    {
        public interface IDaoCache
        {
            DtoCache GetCache();
        }
    }
    
    ====================================
    
    using System.Collections.Generic;
    using System.Linq;
    using DAL.Models.DB;
    using Entities;
    using Microsoft.Extensions.Logging;
    using Microsoft.Extensions.Options;
    
    namespace DAL
    {
        public class DaoCache : IDaoCache
        {
            private readonly IOptions<ConnectionStrings> _options;
          
            public DaoCache(IOptions<ConnectionStrings> options)
            {
                _options = options;
            }
    
            public DtoCache GetCache()
            {
                var dtocache = new DtoCache
                {
                    ProjectTypes = new List<DtoProjectType>(),
                    Statuses = new List<DtoStatus>(),
                    Resources = new List<DtoResource>(),
                    Durations = new List<DtoDuration>()
                };
    
                using (var context = new ProjectManagementContext(_options))
                {
                    var projectypes = (from a in context.ProjectTypes select a).ToList();
                    CreateProjectTypes(dtocache, projectypes);
    
                    var statuses = (from a in context.Statuses select a).ToList();
                    CreateStatuses(dtocache, statuses);
    
                    var resources = (from a in context.Resources select a).ToList();
                    CreateResources(dtocache, resources);
    
                    var durations = (from a in context.Durations select a).ToList();
                    CreateDurations(dtocache, durations);
                }
    
                return dtocache;
            }
    
            private static void CreateProjectTypes(DtoCache dtocache, List<ProjectTypes> projectypes)
            {
                foreach (var pt in projectypes)
                {
                    var dto = new DtoProjectType
                    {
                        ProjectTypeId = pt.ProjectTypeId,
                        Text = pt.Text,
                        Value = pt.Value
                    };
    
                    dtocache.ProjectTypes.Add(dto);
                }
            }
    
            private static void CreateStatuses(DtoCache dtocache, List<Statuses> statuses)
            {
                foreach (var st in statuses)
                {
                    var dto = new DtoStatus()
                    {
                        StatusId = st.StatusId,
                        Text = st.Text,
                        Value = st.Value
                    };
    
                    dtocache.Statuses.Add(dto);
                }
            }
    
            private static void CreateResources(DtoCache dtocache, List<Resources> resources)
            {
    
                foreach (var rc in resources)
                {
                    var dto = new DtoResource()
                    {
                        ResourceId = rc.ResourceId,
                        Text = rc.Text,
                        Value = rc.Value
                    };
    
                    dtocache.Resources.Add(dto);
                }
            }
    
            private static void CreateDurations(DtoCache dtocache, List<Durations> durations)
            {
    
                foreach (var du in durations)
                {
                    var dto = new DtoDuration()
                    {
                        DurationId = du.DurationId,
                        Text = du.Text,
                        Value = du.Value
                    };
    
                    dtocache.Durations.Add(dto);
                }
            }
        }
    }
    

    • Marked as answer by John6272 Saturday, January 12, 2019 5:49 PM
    Friday, January 11, 2019 7:29 PM
  • Hello,

    The following uses SQL-Server data provider, you need to change to SqlLite data provider. The class would reside in your data layer class project. Call this then set te namecombobox us namecombobox.DataSource = items where items is the result back from the code below or iterate the list returned and add them as in your current code.

    using System.Collections.Generic;
    using System.Data.SqlClient;
    
    namespace DataOperations
    {
        public class Operations
        {
            public string ConnectionString => "Your connection string goes here";
    
            public List<string> ReadData()
            {
                var nameList = new List<string>();
    
                var selectStatement = "SELECT Name FROM User";
                using (SqlConnection cn = new SqlConnection() {ConnectionString = ConnectionString})
                {
                    using (SqlCommand cmd = new SqlCommand() {Connection = cn})
                    {
                        cmd.CommandText = selectStatement;
                        cn.Open();
                        var reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            nameList.Add(reader.GetString(0));
                        }
                    }
                }
    
                return nameList;
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by John6272 Saturday, January 12, 2019 5:49 PM
    Saturday, January 12, 2019 1:39 AM
    Moderator

All replies

  • I have some examples in my github from another topic. You can see it.

    UI is created by MVP (model, view, presenter) pattern. You can see ModelRepository is used as data layer.

    https://github.com/petrberek/MSDNForum/tree/master/4f44716a-ad48-46f5-9a43-3bedb16c5192

    It should be way how to do it.

    In another example I have using of business layer for business rules. In your case business layer is not necessary.

    https://github.com/petrberek/MSDNForum/tree/master/81f98c94-1327-406f-b6eb-1f0d424814ca

    Friday, January 11, 2019 5:50 PM
  • Since you are using datatable, you can use VO (value object ) o or DTO (Data Transfer Object) and either one  you can use. The VO is being used in the example where the DAL is usinf the DAO pattern.

    https://en.wikipedia.org/wiki/Data_access_object

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

    https://www.codeproject.com/Articles/36847/Three-Layer-Architecture-in-C-NET

    Below, I am using the DAO and DTO pattern in the DAL using Entity Framework Core

    Also, you're not using n-tier you are using layered, which is similar to n-tier.

    https://docs.microsoft.com/en-us/previous-versions/msp-n-p/ee658117(v=pandp.10)

    namespace DAL
    {
        public interface IDaoCache
        {
            DtoCache GetCache();
        }
    }
    
    ====================================
    
    using System.Collections.Generic;
    using System.Linq;
    using DAL.Models.DB;
    using Entities;
    using Microsoft.Extensions.Logging;
    using Microsoft.Extensions.Options;
    
    namespace DAL
    {
        public class DaoCache : IDaoCache
        {
            private readonly IOptions<ConnectionStrings> _options;
          
            public DaoCache(IOptions<ConnectionStrings> options)
            {
                _options = options;
            }
    
            public DtoCache GetCache()
            {
                var dtocache = new DtoCache
                {
                    ProjectTypes = new List<DtoProjectType>(),
                    Statuses = new List<DtoStatus>(),
                    Resources = new List<DtoResource>(),
                    Durations = new List<DtoDuration>()
                };
    
                using (var context = new ProjectManagementContext(_options))
                {
                    var projectypes = (from a in context.ProjectTypes select a).ToList();
                    CreateProjectTypes(dtocache, projectypes);
    
                    var statuses = (from a in context.Statuses select a).ToList();
                    CreateStatuses(dtocache, statuses);
    
                    var resources = (from a in context.Resources select a).ToList();
                    CreateResources(dtocache, resources);
    
                    var durations = (from a in context.Durations select a).ToList();
                    CreateDurations(dtocache, durations);
                }
    
                return dtocache;
            }
    
            private static void CreateProjectTypes(DtoCache dtocache, List<ProjectTypes> projectypes)
            {
                foreach (var pt in projectypes)
                {
                    var dto = new DtoProjectType
                    {
                        ProjectTypeId = pt.ProjectTypeId,
                        Text = pt.Text,
                        Value = pt.Value
                    };
    
                    dtocache.ProjectTypes.Add(dto);
                }
            }
    
            private static void CreateStatuses(DtoCache dtocache, List<Statuses> statuses)
            {
                foreach (var st in statuses)
                {
                    var dto = new DtoStatus()
                    {
                        StatusId = st.StatusId,
                        Text = st.Text,
                        Value = st.Value
                    };
    
                    dtocache.Statuses.Add(dto);
                }
            }
    
            private static void CreateResources(DtoCache dtocache, List<Resources> resources)
            {
    
                foreach (var rc in resources)
                {
                    var dto = new DtoResource()
                    {
                        ResourceId = rc.ResourceId,
                        Text = rc.Text,
                        Value = rc.Value
                    };
    
                    dtocache.Resources.Add(dto);
                }
            }
    
            private static void CreateDurations(DtoCache dtocache, List<Durations> durations)
            {
    
                foreach (var du in durations)
                {
                    var dto = new DtoDuration()
                    {
                        DurationId = du.DurationId,
                        Text = du.Text,
                        Value = du.Value
                    };
    
                    dtocache.Durations.Add(dto);
                }
            }
        }
    }
    

    • Marked as answer by John6272 Saturday, January 12, 2019 5:49 PM
    Friday, January 11, 2019 7:29 PM
  • public List<UserVO> getUsers()
            {
                var users = new List<UserVO>();
    
                DataTable dataTable = new DataTable();
                dataTable = _userDAO.getUsers(;
    
                foreach (DataRow dr in dataTable.Rows)
                {
                    UserVO userVO = new UserVO();
                    userVO.idUser = Int32.Parse(dr["t01_id"].ToString());
                    userVO.firstname = dr["t01_firstname"].ToString();
                    userVO.lastname = dr["t01_lastname"].ToString();
                    userVO.email = dr["t01_email"].ToString();
                    users.Add(userVO);
                }
     
                return users
            }

    var users = GetUsers();
    
    foreach (var user in users)
    {
      namecombobox.Items.Add(user.firstmame);
    }

    I thought I would give  you a little push with an example.

    However, you would create another classlib project called Entities or whatever you want to call it where you will keep all the VOs in one place. You set project refernce to Entities project for all projects that need to know about the VO. That way the VO(s) are in one spot and VO duplication doesn't have to be done in each layer.

    The object should be called a DTO, becuase developers know what the DTO is about that travels between, layers, tiers or processes.

    https://en.wikipedia.org/wiki/Data_transfer_object

    https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/classes-and-structs/auto-implemented-properties

    http://www.tutorialsteacher.com/csharp/csharp-list

    https://www.c-sharpcorner.com/UploadFile/mahesh/create-a-list-of-objects-in-C-Sharp/

    Saturday, January 12, 2019 12:02 AM
  • Hello,

    The following uses SQL-Server data provider, you need to change to SqlLite data provider. The class would reside in your data layer class project. Call this then set te namecombobox us namecombobox.DataSource = items where items is the result back from the code below or iterate the list returned and add them as in your current code.

    using System.Collections.Generic;
    using System.Data.SqlClient;
    
    namespace DataOperations
    {
        public class Operations
        {
            public string ConnectionString => "Your connection string goes here";
    
            public List<string> ReadData()
            {
                var nameList = new List<string>();
    
                var selectStatement = "SELECT Name FROM User";
                using (SqlConnection cn = new SqlConnection() {ConnectionString = ConnectionString})
                {
                    using (SqlCommand cmd = new SqlCommand() {Connection = cn})
                    {
                        cmd.CommandText = selectStatement;
                        cn.Open();
                        var reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            nameList.Add(reader.GetString(0));
                        }
                    }
                }
    
                return nameList;
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by John6272 Saturday, January 12, 2019 5:49 PM
    Saturday, January 12, 2019 1:39 AM
    Moderator