Styjun

I am making a web service get data from sql server. I need to get many fields from the sql server, but I can only get one field, which is the Currancy Name

namespace WebApplication2

 public class DataHelper
 
 public static string GetCurrency(string currencyCode)
 
 string currencyName = "";
 SqlConnection con = new SqlConnection(@"Data Source=WEB3SHAREPOINT;Initial Catalog=WSS_Search_WEB3;Integrated Security=True");
 SqlCommand cmd = new SqlCommand("select PO_NUMBER,PO_STATUS from View_1 where PO_HEADER_ID ='" + currencyCode.ToUpper() + "'", con);
 con.Open();
 SqlDataReader dr = cmd.ExecuteReader();
 while (dr.Read())
 
 currencyName = dr["PO_NUMBER"].ToString(); 
 
 dr.Close();
 con.Close();
 return currencyName;
 
 

I need to get the PO_Number & PO Status from the Query

As I understand you need to return not only PO_NUMBER, but also PO_STATUS, and as I understand you want to return both values.

I suggest you make model that represent what you want to return.

So for that we make a model class call it for instance POModel:

public class POModel

 public string currencyName get; set; // PO_Number
 public string statusName get; set; // PO_Status

Than fetch the values from SQL as you did and return object in stead of string.

Here would you final code looks like, of course naming and all the stuff you can change the way if fits best:

public class DataHelper

 public static POModel GetCurrency(string currencyCode)
 
 //string currencyName = "";
 var poModel = new POModel();
 SqlConnection con = new SqlConnection(@"Data Source=WEB3SHAREPOINT;Initial Catalog=WSS_Search_WEB3;Integrated Security=True");
 SqlCommand cmd = new SqlCommand("select PO_NUMBER,PO_STATUS from View_1 where PO_HEADER_ID ='" + currencyCode.ToUpper() + "'", con);
 con.Open();
 SqlDataReader dr = cmd.ExecuteReader();
 while (dr.Read())
 
 poModel.currencyName = dr["PO_NUMBER"].ToString();
 poModel.statusName = dr["PO_STATUS"].ToString();
 
 dr.Close();
 con.Close();
 //return currencyName;
 return poModel;
 


public class POModel

 public string currencyName get; set; 
 public string statusName get; set; 

One option is to return an array that contains the two values. Notice string[]:

public static string[] GetCurrency(string currencyCode)

Similar to how you declared string currencyName = "";, instead make an array variable:

string[] poData = new string[2];

Since this looks like it should return a single row, I would not loop. Just do a Read():

dr.Read();
poData[0] = dr["PO_NUMBER"].ToString(); //poData[] will have to be declared in your method
poData[1] = dr["PO_STATUS"].ToString();
....
return poData;