none
How to get data from database using wcf service? RRS feed

  • Question

  • In my table duplicated ID value is their,that's reason,
    
    Example data:Employee
    
    ID    Name   Job       DeptName    Loc
    
    1     a       Mgr      sales         USA
    
    1      a       xxx     ffff           UK
    
    
    I want to display data like below
    
    <Employees>
    
    <ID>1</ID>
    
    <NAME>a</NAME>
    
     <DEPTS>
    
      <Dept>
    
      <Job>mgr</Job>
    
      <DeptName>sales</DeptName>
    
      <Dept>
    
      <Dept>
    
      <Job>xxx</Job>
    
      <DeptName>fff</DeptName>
    
      <Dept>
    
     </DEPTS>
    
    <EMPLOYEES>
    
    I want this type of output,database values

    Can you give me the code for this,How can i start?

    anilbabu

    Monday, October 27, 2014 3:11 PM

Answers

  • You can write a SQL Query with self join, and use "FOR XML" to convert query result to xml

    http://msdn.microsoft.com/en-IN/library/ms178107.aspx


    Gaurav Khanna | Microsoft VB.NET MVP | Microsoft Community Contributor

    Monday, October 27, 2014 4:33 PM
  • I am writing this code in WCF project only.

    So? It doesn't mean that it's a WCF issue. WCF's job is to allow communcations between a WCF client and a WCF service. It has no control over how you have created objects that are to be sent/received.

    If you were doing things properly, you would have a DAL (Data Access Layer) that the WCF service has reference too. You would test the DAL and its methods separately without WCF even being invloved in it to ensure things were working the way you wanted before WCF is even invloved in it.

    Monday, October 27, 2014 8:39 PM

All replies

  • You can write a SQL Query with self join, and use "FOR XML" to convert query result to xml

    http://msdn.microsoft.com/en-IN/library/ms178107.aspx


    Gaurav Khanna | Microsoft VB.NET MVP | Microsoft Community Contributor

    Monday, October 27, 2014 4:33 PM
  • I am writing to get that out put following code
    [DataContract]
        public class Employee
        {
            [DataMember]
           public string ID {get;set;}
    [DataMember]
               public List<Dept> Depts;
    }
     public class Dept
        {      
    
            [DataMember]
            public string Job { get; set; }
            [DataMember]
            public string DeptName { get; set; }
    }
    Interface:
    [WebGet(ResponseFormat = WebMessageFormat.Xml)]
            [OperationContract]
            List<Employee> AllEmp();
    
    Implementation:
    
    public List<Empoyee> AllEmp()
            {           
    
                List<Empoyee> lists = new List<Empoyee>();
                Empoyee dto = new Empoyee();            
    
                SqlConnection con = new SqlConnection(@"data");
                con.Open();
                SqlCommand cmd = new SqlCommand("selectEmpoyee", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                adp.Fill(dt);           
                List<Dept> uplist = new List<dept>();
                Dept up;   
         intId = 0;
                dto.ID = Convert.ToInt32(dt.Rows[0]["ID"]);
                foreach (DataRow stoup in dt.Rows)
                {
                    if(Id!=0)
                        loadId = Convert.ToInt32(stoup["ID"]);
    
                    if (Id != dto.ID || (Id == 0))
                    {    
                        dto = new Dept();
                        dto.ID = Convert.ToInt32(stoup["ID"]);
                        dto.Name = stoup["Name"].ToString();
                        lists.Add(dto);                   
                    }               
                       
                    if ((Id == dto.ID) || (Id == 0))
                    {
                        up = new Dept();
                        up.Job = stoup["Job"].ToString();
                        up.DeptName = stoup["DeptName"].ToString();                   
                        
                        uplist.Add(up);                    
                                    
                    Id = Convert.ToInt32(stoup["ID"]);
                        }               
                    dto.Depts = uplist;
                }
                return lists;
            }
    but I am running this code getting following output
    ID    Name   Job       DeptName    Loc
    
    1     a       Mgr      sales         USA
    
    1      a       xxx     ffff           UK
    2     b       pro      or            USA
    
    2      b       Dev     pur          UK
    <Employees>
    <employee>
    <ID>1</ID>
    
    <NAME>a</NAME>
    
     <DEPTS>
    
      <Dept>
    
      <Job>mgr</Job>
    
      <DeptName>sales</DeptName>
    
      <Dept>
    
      <Dept>
    
      <Job>xxx</Job>
    
      <DeptName>fff</DeptName>
    
      <Dept>
    <Dept>
    
      <Job>Pro</Job>
    
      <DeptName>or</DeptName>
    
      <Dept>
    
      <Dept>
    
      <Job>dev</Job>
    
      <DeptName>pur</DeptName>
    
      <Dept>
    <employee>
    <Employee>
    <ID>2</ID>
    
    <NAME>b</NAME>
    
     <DEPTS>
    
      <Dept>
    
      <Job>mgr</Job>
    
      <DeptName>sales</DeptName>
    
      <Dept>
    
      <Dept>
    
      <Job>xxx</Job>
    
      <DeptName>fff</DeptName>
    
      <Dept>
    <Dept>
    
      <Job>Pro</Job>
    
      <DeptName>or</DeptName>
    
      <Dept>
    
      <Dept>
    
      <Job>dev</Job>
    
      <DeptName>pur</DeptName>
    
      <Dept>
    
    </Employee>
     </DEPTS>
    
    <EMPLOYEES>
    But i want to below output
    <Employees>
    <employee>
    
    <ID>1</ID>
    
    <NAME>a</NAME>
    
     <DEPTS>
    
      <Dept>
    
      <Job>mgr</Job>
    
      <DeptName>sales</DeptName>
    
      <Dept>
    
      <Dept>
    
      <Job>xxx</Job>
    
      <DeptName>fff</DeptName>
    
      <Dept>
    
    
    <employee>
    <Employee>
    <ID>2</ID>
    
    <NAME>b</NAME>
    
     <DEPTS>  
    
    <Dept>
    
      <Job>Pro</Job>
    
      <DeptName>or</DeptName>
    
      <Dept>
    
      <Dept>
    
      <Job>dev</Job>
    
      <DeptName>pur</DeptName>
    
      <Dept>
    
    </Employee>
     </DEPTS>
    
    <EMPLOYEES>
    At the time of debugging code executing correct but displaying xml output not getting correct
    What is the mistake in my code plz correct it


    anilbabu


    • Edited by anil33 Monday, October 27, 2014 4:48 PM
    Monday, October 27, 2014 4:48 PM
  • How is your problem a WCF issue? It's not a WCF issue. It seems to that maybe you need to post to the MSDN C# General forum to learn how to deal with SQL Server T-SQL queries and how to deal with objects.

    Monday, October 27, 2014 5:01 PM
  • I am writing this code in WCF project only,

    not getting output.


    anilbabu

    Monday, October 27, 2014 5:15 PM
  • I am writing this code in WCF project only.

    So? It doesn't mean that it's a WCF issue. WCF's job is to allow communcations between a WCF client and a WCF service. It has no control over how you have created objects that are to be sent/received.

    If you were doing things properly, you would have a DAL (Data Access Layer) that the WCF service has reference too. You would test the DAL and its methods separately without WCF even being invloved in it to ensure things were working the way you wanted before WCF is even invloved in it.

    Monday, October 27, 2014 8:39 PM