How to retrieve data from SQl

Sorry i couldn't get you what exactly you need. Since the code which you have written is a well structured. May be you could place the class SearchInformation,GetDataResult in a separate file for future enhancement.


Please feel free to ask. I am always there to help you.

Regards,
raaj
 

Thanks, can you send the code formated?
 
Thanks again

Hi,

  I tried modifying your code and it was compiling successfully. Just have a look at this code.

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Web;
using System.Web.Services;
using System.Collections.Generic;
namespace APPRECWS
{
    ///

    /// Summary description for Service1.
    ///

    public class AplicationRecordWS : System.Web.Services.WebService
    {
        public AplicationRecordWS()
        {
            //CODEGEN: This call is required by the ASP.NET Web Services Designer
            InitializeComponent();
        }
        #region Component Designer generated code

        //Required by the Web Services Designer
        private IContainer components = null;

        ///

        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        ///

        private void InitializeComponent()
        {
        }
        ///

        /// Clean up any resources being used.
        ///

        protected override void Dispose(bool disposing)
        {
            if (disposing && components != null)
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #endregion
        // WEB SERVICE EXAMPLE
        // The HelloWorld() example service returns the string Hello World
        // To build, uncomment the following lines then save and build the project
        // To test this web service, press F5
        //  [WebMethod]
        //  public string HelloWorld()
        //  {
        //   return "Hello World";
        //  }
        [WebMethod]
        public IEnumerable GetData(SearchInformation InputData)
        {
            List result = new List();
            SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);
            SqlCommand cmd = new SqlCommand("spFAWebServiceWC", conn);
            try
            {
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@BusinessSeg", InputData.BusinessSeg);
                cmd.Parameters.AddWithValue("@BusinessBan", InputData.BusinessBan);
                cmd.Parameters.AddWithValue("@BusinessType", InputData.BusinessType);
                cmd.Parameters.AddWithValue("@ApplicatioName", InputData.ApplicatioName);
                cmd.Parameters.AddWithValue("@ApplicationShortName", InputData.ApplicationShortName);
                cmd.Parameters.AddWithValue("@RBS", InputData.RBS);
                SqlDataReader dados = cmd.ExecuteReader();
                while (dados.Read())
                {
                    result.Add(new GetDataResult()
                    {
                        ApplicationID = (int)dados["ApplicationID"],
                        BusinessSeg = (int)dados["BusinessSeg"],
                        BusinessBan = (int)dados["BusinessBan"],
                        BusinessType = (string)dados["BusinessType"],
                        ApplicationName = (string)dados["ApplicationName"],
                        ApplicationShortName = (string)dados["ApplicationShortName"],
                        RBS = (string)dados["RBS"],
                        SystemType = (string)dados["SystemType"],
                        Status = (string)dados["Status"]
                    });
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }
        public class SearchInformation
        {
            public string BusinessSeg;
            public string BusinessBan;
            public string BusinessType;
            public string ApplicatioName;
            public string ApplicationShortName;
            public string RBS;
        }
        public class GetDataResult
        {
            public int ApplicationID;
            public int BusinessSeg;
            public int BusinessBan;
            public string BusinessType;
            public string ApplicationName;
            public string ApplicationShortName;
            public string RBS;
            public string SystemType;
            public string Status;


        }
    }
}

Regards,
raaj

Hi Raaj
 
Here it goes
 
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Web;
using System.Web.Services;
namespace APPRECWS
{
 ///

 /// Summary description for Service1.
 ///

 public class AplicationRecordWS : System.Web.Services.WebService
 {
  public AplicationRecordWS()
  {
   //CODEGEN: This call is required by the ASP.NET Web Services Designer
   InitializeComponent();
  }
  #region Component Designer generated code
  
  //Required by the Web Services Designer
  private IContainer components = null;
    
  ///

  /// Required method for Designer support - do not modify
  /// the contents of this method with the code editor.
  ///

  private void InitializeComponent()
  {
  }
  ///

  /// Clean up any resources being used.
  ///

  protected override void Dispose( bool disposing )
  {
   if(disposing && components != null)
   {
    components.Dispose();
   }
   base.Dispose(disposing);  
  }
  
  #endregion
  // WEB SERVICE EXAMPLE
  // The HelloWorld() example service returns the string Hello World
  // To build, uncomment the following lines then save and build the project
  // To test this web service, press F5
  //  [WebMethod]
  //  public string HelloWorld()
  //  {
  //   return "Hello World";
  //  }
  [WebMethod]
 
  public GetDataResult[] GetData(SearchInformation InputData)
  {
   SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);
   SqlCommand cmd = new SqlCommand("spFAWebServiceWC",conn);
   conn.Open();
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Parameters.Add("@BusinessSeg",InputData.BusinessSeg);
   cmd.Parameters.Add("@BusinessBan",InputData.BusinessBan);
   cmd.Parameters.Add("@BusinessType",InputData.BusinessType);
   cmd.Parameters.Add("@ApplicatioName",InputData.ApplicatioName);
   cmd.Parameters.Add("@ApplicationShortName",InputData.ApplicationShortName);
   cmd.Parameters.Add("@RBS",InputData.RBS);
   
   
   
   
   SqlDataReader dados = cmd.ExecuteReader();
//   Object [] values = new Object[dados.FieldCount];
//   int fieldCount = dados.GetValues(values);
//   ArrayList al = new ArrayList();
//   al.Add(values);
   
   
   conn.Close();
   while (dados.Read())
   {
   int ApplicationID=(int) dados["ApplicationID"];
   int BusinessSeg=(int) dados["BusinessSeg"];;
   int BusinessBan=(int) dados["BusinessBan"];;
   string BusinessType=(string) dados["BusinessType"];;
   string ApplicationName=(string) dados["ApplicationName"];;
   string ApplicationShortName=(string) dados["ApplicationShortName"];;
   string RBS=(string) dados["RBS"];;
   string SystemType=(string) dados["SystemType"];;
   string Status=(string) dados["Status"];;
   
   
   
   }
   
//   GetDataResult[] resultado = new GetDataResult[fieldCount];
   //   
   //   for(int x=0;x< dados;x++)
   //   
   //   {
   //    resultado[x] = new GetDataResult();
   //    resultado[x].ApplicationID = (dados["ApplicationID"].ToString());
   //    resultado[x].Business = dados.Rows[x]["Business"].ToString();
   //    resultado[x].BusinessType = dados.Rows[x]["BusinessType"].ToString();
   //    resultado[x].ApplicationName = dados.Rows[x]["ApplicationName"].ToString();
   //    resultado[x].ApplicationShortName = dados.Rows[x]["ApplicationShortName"].ToString();
   //    resultado[x].RBS = dados.Rows[x]["RBS"].ToString();
   //    resultado[x].SystemType = dados.Rows[x]["SystemType"].ToString();
   //    resultado[x].Status = dados.Rows[x]["Status"].ToString();
   //       }
   //
   //    return resultado;
   //  }
   
  }
  public class SearchInformation
  {
   public string BusinessSeg;
   public string BusinessBan;
   public string BusinessType;
   public string ApplicatioName;
   public string ApplicationShortName;
   public string RBS;
  }
  public class GetDataResult
  {
   public int ApplicationID;
   public int BusinessSeg;
   public int BusinessBan;
   public string BusinessType;
   public string ApplicationName;
   public string ApplicationShortName;
   public string RBS;
   public string SystemType;
   public string Status;

  
  }
 }
}

It seems some where you haven't mentioned the type name.

Could you please post the entire class such that i could tell you the solution

Hi Raaj
Thanks in advance for your help, but in these lines
 
public IEnumerable GetData(SearchInformation InputData)
        {

            List result = new List();
 
I have the message invalid token '<' in class, struct or interface member declaration.
 
Can you help me?
 
Thanks

Hi,

  Try this code,

         [WebMethod]
        public IEnumerable GetData(SearchInformation InputData)
        {

            List result = new List();
            SqlConnection conn = new                     SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);
            SqlCommand cmd = new SqlCommand("spFAWebServiceWC", conn);
            conn.Open();
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@BusinessSeg", InputData.BusinessSeg);
            cmd.Parameters.AddWithValue("@BusinessBan", InputData.BusinessBan);
            cmd.Parameters.AddWithValue("@BusinessType", InputData.BusinessType);
            cmd.Parameters.AddWithValue("@ApplicatioName", InputData.ApplicatioName);
            cmd.Parameters.AddWithValue("@ApplicationShortName", InputData.ApplicationShortName);
            cmd.Parameters.AddWithValue("@RBS", InputData.RBS);
            SqlDataReader dados = cmd.ExecuteReader();
            conn.Close();
            while (dados.Read())
            {
                result.Add(new GetDataResult()
                {
                    ApplicationID = (int)dados["ApplicationID"],
                    BusinessSeg = (int)dados["BusinessSeg"],
                    BusinessBan = (int)dados["BusinessBan"],
                    BusinessType = (string)dados["BusinessType"],
                    ApplicationName = (string)dados["ApplicationName"],
                    ApplicationShortName = (string)dados["ApplicationShortName"],
                    RBS = (string)dados["RBS"],
                    SystemType = (string)dados["SystemType"],
                    Status = (string)dados["Status"]
                });
            }
            return result;
        }


Since this will return a collection of object so that you can easily enumerate into that using foreach statement

Regards,
raaj