6 Replies - 6519 Views - Last Post: 20 March 2012 - 05:14 AM Rate Topic: -----

#1 ririnsfabrie   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 16-March 12

make the inner join with asp.net

Posted 19 March 2012 - 08:06 AM

how to make the inner join with asp.net web service? and how to display values ​​based on the foreign key?
Is This A Good Question/Topic? 0
  • +

Replies To: make the inner join with asp.net

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14097
  • View blog
  • Posts: 56,497
  • Joined: 12-June 08

Re: make the inner join with asp.net

Posted 19 March 2012 - 08:14 AM

You do inner joins with your SQL statements, in the code behind, and they operate pretty much like a desktop app's sql statements.
Was This Post Helpful? 0
  • +
  • -

#3 Nakor   User is offline

  • Professional Lurker
  • member icon

Reputation: 448
  • View blog
  • Posts: 1,504
  • Joined: 28-April 09

Re: make the inner join with asp.net

Posted 19 March 2012 - 07:40 PM

Basic SQL inner join syntax

SELECT a.column1, a.column2, b.column1, b.column2
FROM Table1 AS a
INNER JOIN Table2 AS b
ON a.ForeignKeyColumn = b.PrimaryKeyColumn



For anything else you're going to need to be a lot more specific with your question as well as providing the code you're having problems with.

This post has been edited by Nakor: 19 March 2012 - 07:41 PM

Was This Post Helpful? 0
  • +
  • -

#4 ririnsfabrie   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 16-March 12

Re: make the inner join with asp.net

Posted 19 March 2012 - 07:58 PM

then what if I use the syntax list <>?
example:
[WebMethod]
    public List<Request> getListRequestJoin()
    {
        WMS.Handler.FilterRequest fc = new WMS.Handler.FilterRequest();
        List<Request> lc = new List<Request>();
        List<WMS.Handler.RequestData> lcd = WMS.Handler.RequestHandler.GetListRequest(fc);
        
        WMS.Handler.FilterStatusUser ft = new WMS.Handler.FilterStatusUser();
        List<WMS.Handler.StatusUserData> ad = WMS.Handler.StatusUserHandler.GetListStatusUser(ft);
        List<StatusUser> yu = new List<StatusUser>();
        
        foreach (var d in lcd.Where( o=> o.UserID != null).ToList())
        {
            Request c = new Request();
            StatusUser a = new StatusUser();
            if (a.UserID==c.UserID){
            c.RequestID = d.RequestID;
            c.UserID = d.UserID;
            c.GF = d.GF;
            c.Indoor = d.Indoor;
            a.Name = d.Name;
            lc.Add(c);
        }}
        return lc;
        
        }

what should be changed?

View Postmodi123_1, on 19 March 2012 - 08:14 AM, said:

You do inner joins with your SQL statements, in the code behind, and they operate pretty much like a desktop app's sql statements.



then what if I use the syntax list <>?
example: see below!

what should be changed?

View PostNakor, on 19 March 2012 - 07:40 PM, said:

Basic SQL inner join syntax

SELECT a.column1, a.column2, b.column1, b.column2
FROM Table1 AS a
INNER JOIN Table2 AS b
ON a.ForeignKeyColumn = b.PrimaryKeyColumn



For anything else you're going to need to be a lot more specific with your question as well as providing the code you're having problems with.


then what if I use the syntax list <>?
example: see the code that I wrote above!

what should be changed?
Was This Post Helpful? 0
  • +
  • -

#5 Nakor   User is offline

  • Professional Lurker
  • member icon

Reputation: 448
  • View blog
  • Posts: 1,504
  • Joined: 28-April 09

Re: make the inner join with asp.net

Posted 19 March 2012 - 08:07 PM

I don't see where you're trying to do a join in that code.
Was This Post Helpful? 0
  • +
  • -

#6 Nakor   User is offline

  • Professional Lurker
  • member icon

Reputation: 448
  • View blog
  • Posts: 1,504
  • Joined: 28-April 09

Re: make the inner join with asp.net

Posted 19 March 2012 - 08:14 PM

A couple things I did notice...

        foreach (var d in lcd.Where( o=> o.UserID != null).ToList())
        {
            Request c = new Request();
            StatusUser a = new StatusUser();
            if (a.UserID==c.UserID){
            c.RequestID = d.RequestID;
            c.UserID = d.UserID;
            c.GF = d.GF;
            c.Indoor = d.Indoor;
            a.Name = d.Name;
            lc.Add(c);
        }}



The if (a.UserID==c.UserID) statement in that foreach block is pointless. You're comparing two objects that have just been created, there values are going to be whatever the default value is for that field. Also, there seems to be absolutely no point in creating the a object. All you do with it is set it's name to the same name as d but then you do nothing with it.
Was This Post Helpful? 0
  • +
  • -

#7 ririnsfabrie   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 16-March 12

Re: make the inner join with asp.net

Posted 20 March 2012 - 05:14 AM

I want to take the value (name) on the Request by UserID in StatusUser. Initially, the script that I made ​​last RequestData.cs FilterRequest.cs (for linq and the decision by category) and then Request.cs RequestHandler.cs (for object method). Here's his script code:

RequestData.cs
using System;
using System.Data;

namespace WMS.Handler
{
    public class RequestData
    {
        public RequestData()
        {
            RequestID = null;
            UserID = null;
            GF = null;
            Indoor = null;
            Name = string.Empty;
        }

        public Nullable<int> RequestID
        {
            get;
            set;
        }
        public Nullable<int> UserID
        {
            get;
            set;
        }
        public Nullable<double> GF
        {
            get;
            set;
        }
        public Nullable<double> Indoor
        {
            get;
            set;
        }

        public string Name
        {
            get;
            set;
        }
    }
}


FilterRequest.cs
using System;
using System.Data;

namespace WMS.Handler
{
    public class FilterRequest
    {
        public FilterRequest()
        {
            FilterRequestID = null;
            FilterUserID = null;
            FilterGF = null;
            FilterIndoor = null;
            FilterName = string.Empty;
        }

        public Nullable<int> FilterRequestID
        {
            get;
            set;
        }
        public Nullable<int> FilterUserID
        {
            get;
            set;
        }
        public Nullable<int> FilterGF
        {
            get;
            set;
        }
        public Nullable<int> FilterIndoor
        {
            get;
            set;
        }
        public string FilterName
        {
            get;
            set;
        }
    }
}


Request.cs
using System;
using System.Data;

[Serializable]
public class Request
{
    public Request()
    {
        RequestID = null;
        UserID = null;
        GF = null;
        Indoor = null;
        Name = string.Empty;
    }

    public Nullable<int> RequestID
    {
        get;
        set;
    }
    public Nullable<int> UserID
    {
        get;
        set;
    }
    public Nullable<double> GF
    {
        get;
        set;
    }
    public Nullable<double> Indoor
    {
        get;
        set;
    }
    public string Name
    {
        get;
        set;
    }
}


RequestHandler.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace WMS.Handler
{
    public class RequestHandler
    {public static List<RequestData> GetListRequestJoin(FilterRequest filter)
        {
            using (WMS.DAL.WarehouseLinqDataContext dc = new WMS.DAL.WarehouseLinqDataContext())
            {var data = from d in dc.Requests
                           from e in dc.StatusUsers
                           where d.userID == e.userID
                           select new { d.userID, e.name, d.GF, d.Ind, d.requestID };
                
                if (filter.FilterName != string.Empty && filter.FilterName != null)
                {
                    data = from e in data
                           where e.name.ToLower().Contains(filter.FilterName.ToLower())
                           select e;
                }
                if (filter.FilterRequestID != null)
                {
                    data = from d in data
                           where d.requestID == filter.FilterRequestID
                           select d;
                }
                if (filter.FilterUserID != null)
                {
                    data = from d in data
                           where d.userID == filter.FilterUserID
                           select d;
                }
                if (filter.FilterGF != null)
                {
                    data = from d in data
                           where d.GF == filter.FilterGF
                           select d;
                }
                if (filter.FilterIndoor != null)
                {
                    data = from d in data
                           where d.Ind == filter.FilterIndoor
                           select d;
                }

                List<RequestData> listRequest = new List<RequestData>();
                foreach (var d in data)
                {
                    RequestData RequestData = new RequestData();
                    RequestData.RequestID = d.requestID;
                    RequestData.UserID = d.userID;
                    RequestData.GF = (double)d.GF;
                    RequestData.Indoor = (double)d.Ind;
                    listRequest.Add(RequestData);
                }
                return listRequest;
            }
        }}}



WMSDService.cs
 #region Request
    [WebMethod]public List<Request> getListRequestJoin()
    {
        WMS.Handler.FilterRequest fc = new WMS.Handler.FilterRequest();
        List<Request> lc = new List<Request>();
        List<WMS.Handler.RequestData> lcd = WMS.Handler.RequestHandler.GetListRequest(fc);
        
        WMS.Handler.FilterStatusUser ft = new WMS.Handler.FilterStatusUser();
        List<WMS.Handler.StatusUserData> ad = WMS.Handler.StatusUserHandler.GetListStatusUser(ft);
        List<StatusUser> yu = new List<StatusUser>();
        
        foreach (var d in lcd.Where( o=> o.UserID != null).ToList())
        {
            Request c = new Request();
            StatusUser a = new StatusUser();
            if (a.UserID==c.UserID){
            c.RequestID = d.RequestID;
            c.UserID = d.UserID;
            c.GF = d.GF;
            c.Indoor = d.Indoor;
            a.Name = d.Name;
            lc.Add(c);
        }}
        return lc;
        
        }
  #endregion


I'm confused on WMSDService application, I use a restful web service. Thank you very much

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1