3-tiers application using ASP.NET and Linq 2 Sql with multiple tables

Question

hello everyone i have a problem with my 3-tiers application i don't know how to get data from multiple tables using linq2sql in 3 tiers architecture application here is each layers code

GestionProjetCommon Project

Client Class :

public class Client
{
    private int _ID;
    public int ID
    {
        get { return _ID; }
        set { _ID = value; }
    }
    private string _Name;
    public string Name
    {
        get { return _Name; }
        set { _Name = value; }
    }

}

Project Class :

public class Projet
{
    private int _ID;
    public int ID
    {
        get { return _ID; }
        set { _ID = value; }
    }
    private string _Title;
    public string Title        {

        get { return _Title; }
        set { _Title= value; }

    }

   private int _IDClient;
    public int IDClient
    {
        get { return _IDClient; }
        set { _IDClient = value; }
    }
}

GestionProjetDAL Project

GestionProjetDA Class :

public class GestionProjetDA
{
    private GestionProjetDADataContext db = new GestionProjetDADataContext();
    public List<GestionProjet.Client> GetClients() //This Works Fine No Problem !
    {
        var req = from clt in db.Clients select clt;

        List<GestionProjet.Client> clientList = new List<GestionProjet.Client>();
        foreach (Clients item in req)
        {
            clientList.Add(new GestionProjet.Client() { ID = item.ID, Nom = item.Nom });
        }
        return clientList;
    }

public List<GestionProjet.Client> GetProjectClient()
    {
        var req = from prj in db.Projets
                  from clt in db.Clients
                  where clt.ID == prj.IDClient
                                  select new
                                  {
                                      Name=clt.Name,
                                      Projet = prj.Title,
                                  };
        List<GestionProjet.Client> clientProjectList = new List<GestionProjet.Client>();
      foreach (var clt in req)
        {
//I Don't know what to do in here and get the Data From both of the Tables
        }

    }
 }

GestionProjetBusiness Project

GestionProjetB Class :

 public class GestionProjetB
{
    private GestionProjetDAL.GestionProjetDA GPDA = new GestionProjetDAL.GestionProjetDA();

    public List<Client> GetClients()
    {
        return GPDA.GetClients();
    }

  //Here i Should put the 2nd Method

}

Well as you can see i have no problem with getting data from one table but the only problem is getting it from multiple tables.

i've been look for a solution the whole night but i didn't find it please help me thanks

shouldn't your method `GetProjectClient` get id of project as input, i.e. return the clients of a particular project? — Maziar Taheri, Jun 02 '11 at 12:16
no i have to join all the clients names and their projects something like SELECT * FROM Clients C,Project P WHERE C.ID = P.IDClient — Houssam, Jun 02 '11 at 12:19

Maziar Taheri · Accepted Answer · 2011-06-02T13:41:54.300

3

Create a DTO class, something like:

public class ClientDTO
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string ProjectName { get; set; }
    }

now write a good linq expression to fill the DTO class:

 public List<GestionProjet.Client> GetProjectClient()
    {
        return (from prj in db.Projets
                  join clt in db.Clients on prj.IDClient equals clt.ID
                                  select new ClientDTO
                                  {
                                      Name = clt.Name,
                                      ProjetName = prj.Title,
                                      ID = clt.ID
                                  }).ToList();

    }

I hope I understood your problem right, and pardon me for not testing the code before posting.

edited Jun 02 '11 at 13:41

answered Jun 02 '11 at 12:29

Maziar Taheri

2,288
22
27

thanks Maziar for your help but i already thought about this, it's not practical imagine i have about 6 tables and these tables have more than 15 relations so is it right to create a DTO class for each relation ? – Houssam Jun 02 '11 at 12:39
this works fine @Maziar Taheri thanks a lot but tell me what if i had a lot of tables with multiple relations ? – Houssam Jun 02 '11 at 12:56
you're welcome, actually DTO objects must be defined as rich as the context wants them to, for example your DTO class might contain a list of another DTO class. but for making it efficient, this List might be be filled with most of your DAL methods and remain empty. one particular method would return DTO objects with that list filled. – Maziar Taheri Jun 02 '11 at 13:03
After all, you might know that LINQ table classes have relation to related tables, but using those relations might in some cases be inefficient, and on the other side not a good 3-tier model. – Maziar Taheri Jun 02 '11 at 13:05
"for example your DTO class might contain a list of another DTO class. but for making it efficient, this List might be be filled with most of your DAL methods and remain empty" Example please !? code snippet ! – Houssam Jun 02 '11 at 13:12
class FirstDTO { List SecondDTOs {get { ... } } } List Method1 () // returns FirstDTO list in which SecondDTOs are not initialized List Method2 () // returns FirstDTO list in which SecondDTOs ARE initialized i.e. in a foreach loop – Maziar Taheri Jun 02 '11 at 13:20
Alright ! thanks dude and good luck for your career ! i will implement this in my school project btw ! thank you again – Houssam Jun 02 '11 at 13:31
try to Close the public List GetProjectClient() Method in your answer ! thanks again – Houssam Jun 02 '11 at 13:33

3-tiers application using ASP.NET and Linq 2 Sql with multiple tables

1 Answers1