Array in WebService

Question

User-1568959541 posted

Hi, Im trying to develope an array as the result of a query, but the result is none when I add the web refence on the launched IE web page. I have over 109,000 rows and thats why I get Top 20 data in query. This is my code. How to get that array???... 

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// Para permitir que se llame a este servicio web desde un script, usando ASP.NET AJAX, quite la marca de comentario de la línea siguiente. 
// [System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService {

    public WebService () {

        //Eliminar la marca de comentario de la línea siguiente si utiliza los componentes diseñados 
        //InitializeComponent(); 
    }

    [WebMethod]
    public string[] Universo(string pcualNombre)  //(string prefixText, int count)
    {
      //ADO.Net
      SqlConnection cn = new SqlConnection();
      DataSet ds = new DataSet();
      DataTable dt = new DataTable();
      String strCn = "SERVER=111.22.333.444;UID=QQQQQQ;DATABASE=WWWWWWW; pwd=EEEEEEE";
      cn.ConnectionString = strCn;
      SqlCommand cmd = new SqlCommand();
      cmd.Connection = cn;
      cmd.CommandType = CommandType.Text;

      cmd.CommandText = "SELECT top 20 nombre FROM UNIVERSO WHERE NOMBRE LIKE @cualNombre ";
      cmd.Parameters.AddWithValue("@cualNombre", pcualNombre + "%");
      try
      {
        cn.Open();
        cmd.ExecuteNonQuery();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(ds);
      }
      catch (Exception ex)
      {
        string sMensaje = ex.Message.ToString();
      }
      finally
      {
        cn.Close();
      }
      dt = ds.Tables[0];

      //Then return List of string(txtItems) as result
      List<string> txtItems = new List<string>();
      String dbValues;

      foreach (DataRow row in dt.Rows)
      {
        //String From DataBase(dbValues)
        dbValues = row["nombre"].ToString();
        dbValues = dbValues.ToLower();
        txtItems.Add(dbValues);
      }
      return txtItems.ToArray();
    }
}

Answer 1

User-1469158370 posted

You can calling cmd.ExecuteNonQuery() which means that no data will be returned by the database server.

You are not using the SqlDataAdapter correctly.

Below is the right way of using it.

try
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(ds);

}

Answer 2

User-1568959541 posted

 I am so happy because the project is working fine !!! ajajaaaaaa..

Thanks a lot...