Web Service code not returning array of String

Question

I want to return an array of Strings in the form "abc#xyz#ghi#tru" (where # is delimiter) from my web service method . However i m not able to do it . Here is my current web service code :

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Data.SqlClient;
namespace WebService10
{
 /// <summary>
 /// Summary description for Service1
 /// </summary>
 [WebService(Namespace = "http://tempuri.org/")]
 [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
 [ToolboxItem(false)]
 // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment
 the following line. 
 // [System.Web.Script.Services.ScriptService]
 public class Service1 : System.Web.Services.WebService
 {
 String[] result=new String[40];
 String[] result2 = new String[40];
 [WebMethod]
 public String[] getData()
 {
 SqlConnection myConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=student;User ID=sa;Password=123");
 try
 {
 myConnection.Open();
 SqlCommand myCommand = new SqlCommand();
 myCommand.Connection = myConnection;
 myCommand.CommandText = "select count(*) from names where name =@name";
 SqlDataReader myReader = myCommand.ExecuteReader();
 //while
 for(int i=0;i<40;i++)
 {
 if (myReader.Read())
 {
 result[i]= myReader["name"].ToString();
 result2[i] = result[i] + "#";
 }
 }
 }
 catch (Exception ex)
 {
 Console.WriteLine(ex.Message);
 }
 finally
 {
 myConnection.Close();
 }
 return result2;
 }
 }
}

Can anyone tell me what's wrong with my code ?

• Brian Hoover: That's a lot of code. What is it doing? What happens if you change getData() to just return a static value such as "returned string?"{
• Parth Doshi: hey sorry i will explain . I have created a database in SQL Server 2008 having a single column "name" in which i have inserted sample values..I just want to retreive those values in the form i have mentioned in my question along with a delimiter. is there any way to do it ?
• kleinohad: do you want to get all the names from you single column?
• Parth Doshi: @kleinohad : yes i have single column "name" with values that I have already inserted . I just need to retreive them now using the method of my web service in the manner i hv mentioned in my post
• Ash Eldritch: Is there a strong reason for delimiting with a "#" rather than the standard comma? (Because rather than doing string concatenation you can just rely on the web service to serialize your array for you)

Answer 1

Try this:

I changed the query (BTW: the query still doesn't make much sense but I do not know what you really want), the resulting type and the loop.

You forgot to pass the parameter to the query too.

Also: change the exception handling; writing to the console on the server side is not a good idea.

public class Service1 : System.Web.Services.WebService
{
 [WebMethod]
 public String getData(string nameFilter)
 {
 String result = "";
 SqlConnection myConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=student;User ID=sa;Password=123");
 try
 {
 myConnection.Open();
 SqlCommand myCommand = new SqlCommand();
 myCommand.Connection = myConnection;
 myCommand.CommandText = "select name from names where name =@name";
 myCommand.Parameters.AddWithValue("@name", nameFilter);
 SqlDataReader myReader = myCommand.ExecuteReader();
 while(myReader.Read())
 {
 if(result.Length > 0)
 {
 result += "#";
 }
 result += myReader["name"].ToString();
 }
 }
 catch (Exception ex)
 {
 Console.WriteLine(ex.Message);
 }
 finally
 {
 myConnection.Close();
 }
 return result;
 }
}

EDIT

I'd prefer a different approach:

public class Service1 : System.Web.Services.WebService
{
 [WebMethod]
 public String[] getData(string nameFilter)
 {
 var names = new List<string>();
 SqlConnection myConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=student;User ID=sa;Password=123");
 try
 {
 myConnection.Open();
 SqlCommand myCommand = new SqlCommand();
 myCommand.Connection = myConnection;
 myCommand.CommandText = "select name from names where name = @name";
 myCommand.Parameters.AddWithValue("@name", nameFilter);
 SqlDataReader myReader = myCommand.ExecuteReader();
 while(myReader.Read())
 {
 names.Add(myReader["name"].ToString());
 }
 }
 catch (Exception ex)
 {
 //Console.WriteLine(ex.Message);
 }
 finally
 {
 myConnection.Close();
 }
 return names.ToArray();
 }
}

• Parth Doshi: @Emo : thanks a lot for the edits and the code. I tried it..However when I click on invoke button in browser i get this [<string xmlns="tempuri.org/"/>] ..There is no string array displayed ..
• Parth Doshi: @Emo : Yes ur recent edited code runs fine..but i just want a delimiter to be inserted after it fetches each record from my database ...eg: john#ann#joe
• Emond: @Parth_90: I added the delimiter. If you expect a lot of names it might be best to switch back to an array of strings or to a StringBuilder while appending the names.
• Parth Doshi: Hey thanks !! Actually i dnt have a lot of names to fetch..so will use this only. Thanks for all the help !! :-)
• Emond: @Parth_90: No problem, glad to help. One bit of advise: switch back (if possible) to returning an array of strings. That is much easier and less error prone (what if a name contains a #)

Answer 2

 [WebMethod]
 public string getData()//changed to return string
 {
 SqlConnection myConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=student;User ID=sa;Password=123");
 try
 {
 myConnection.Open();
 SqlCommand myCommand = new SqlCommand();
 myCommand.Connection = myConnection;
 myCommand.CommandText = "select name from names";//you can make it select distinct
 SqlDataReader myReader = myCommand.ExecuteReader();
 string toReturn = "";
 while(myReader.Read())
 {
 if (myReader.Read())
 {
 toReturn += myReader["name"].ToString() + "#";
 }
 }
 }
 catch (Exception ex)
 {
 Console.WriteLine(ex.Message);
 }
 finally
 {
 myConnection.Close();
 }
 return toReturn; //# as delimiter
 }

Answer 3

First, your query returns only number instead of set of rows. So you should use sql like this:

select name from names

Second, to return array you could use List instead of pre-defined arrays: I would be use the following approach:

var result = new List<string>();
while(myReader.Read())
{
 result.Add(reader["name"].ToString() + "#");
}
return result.ToArray();

Or if you want to return a string:

return string.Join("#", result.ToArray())

• Parth Doshi: Yes I follow your approach and yea it's a better way of doing it than mine. Since I am new to C# especially, I would take some time. But as long as people like u help me understand things better, I feel I will learn things much quickly :-)