I posted a query about this as http://forums.asp.net/t/1369710.aspx, but this seems to have died, so I'm rewording and posting the query again.

I have previously successfully written a web service to return "Hello World", and another web service that, when given a username and password, will get several scalar fields from the database, so I can successfully use a web service to access my Web-site database and return data to my Windows program.  However I've been unable to write a web service to which I pass some search arguments, and which then returns zero or more rows of data from my database. I' ve tried a number of alternatives.  Some compile, some don't, but none of the ones that compiled actually worked. Can somebody please show me a working example.  

Thank you, Robert.

Take a look over here: http://aspnet.4guysfromrolla.com/articles/062602-1.aspx

 Thanks Suprotim, this looks good.  I'll check it out, and I'll either be back with further questions or I'll click [mark as answer].  It may take me a couple of days.

 Cheers, Robert.

This article returns the data as a dataset, having retrieved it from the database with SQLCommand.  After reading articles that said that a much better approach was to use tableadaptors, so for the last couple of years all the data access in my projects is via code such as
    Dim taGDBSearch As New GDBChartDataTableAdapters.GDBSearchTableAdapter
    Dim tbGDBSearch As GDBChartData.GDBSearchDataTable
    Dim trGDBSearch As GDBChartData.GDBSearchRow
    ...
    tbGDBSearch = taGDBSearch.GetData(Family, Given, YofBirth1, YofBirth2, YofDeath1, YofDeath2, Owner)

This works perfecly within the web service (as shown by debugging), but I was having trouble returning the datatable through the web service (see previous post for details).

The 4guysfromrolla article returns the data as a dataset, not a datatable, and it presumably works where my version returning a datatable does not.  Is there a similar article to the one cited that uses tableadaptors and related typed objects rather than SQLCommand?

Regards, Robert

I will restrain myself from getting into the DataSets vs TableAdaptors Vs BusinessObject discussion. There's plenty of info on the net.

Some links to look at:

http://aspalliance.com/914_Building_a_DAL_using_Strongly_Typed_TableAdapters_and_DataTables_in_VS_2005_and_ASPNET_20.9

http://blogs.msdn.com/smartclientdata/archive/2006/02/21/SeperatingTypedDataSetsFromTableAdapters.aspx

Robert Barnes:

Can somebody please show me a working example.  

I just wrote 

    /// <summary>
    /// Gets string value from parameter table.
    /// </summary>
    /// <returns>String value</returns>
    [WebMethod]
    public string GetStringValue(ParamStringEnum id)
    {
      return ParamString.GetValue(id);
    }

and  it returned 
<string>ABCD</string>

The data layer function that was called was:
    #region " GetValue "
    /// <summary>
    /// Get Value from ParamString
    /// </summary>
    /// <param name="iId">Id of record to fetch</param>
    /// <returns>Value from parameter table row</returns>
    public static string GetValue(ParamStringEnum iId)
    {
      var connect = CommonData.ConnectionString;
      var sqlConnection = new SqlConnection(connect);
      var sqlCommand = new SqlCommand("usp_ParamStringGetValue", sqlConnection);
      try
      {
        sqlCommand.CommandType = CommandType.StoredProcedure;
        sqlCommand.Parameters.Add("@Id", SqlDbType.Int);
        sqlCommand.Parameters["@Id"].Value = iId;
        sqlCommand.Parameters.Add("@ParamValue", SqlDbType.NVarChar, -1);
        sqlCommand.Parameters["@ParamValue"].Direction = ParameterDirection.Output;
        sqlCommand.Connection.Open();
        sqlCommand.ExecuteNonQuery();
        return CommonData.NullToString(sqlCommand.Parameters["@ParamValue"].Value);
      }
      catch (Exception ex)
      {
        const string Message = "Failure calling usp_ParamStringGetValue";
        CommonData.WriteEventError(ex, Message);
        throw new DalGeneralException(Message, ex);
      }
      finally
      {
        if (sqlCommand.Connection != null)
        {
          sqlCommand.Connection.Close();
        }
        sqlCommand.Dispose();
        sqlConnection.Dispose();
      }
    }
    #endregion

 The code I wrote as a demonstration will be available in the next release of the CommonData project at http://www.CodePlex.Com/CommonData

I choose a single data value return function rather than a dataset as this can be demonstrated within your browser.

Tatworth, thank you for taking the trouble to post a response to my question, but (as I pointed out in my first post) I can already return scalar values from a web service. My first web service returned "Hello World", the second a few items from the database (given userid and password, return email and subscription-paid-to), now I am trying to write a search that will return zero or more table rows. 

I had coded a tableadapter like this: -

<System.Web.Services.WebService(Namespace:="http://www.nzgdb.co.nz/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class GDBService
    Inherits System.Web.Services.WebService
  [snip]
    Dim taGDBSearch As New GDBChartDataTableAdapters.GDBSearchTableAdapter
    Dim tbGDBSearch As GDBChartData.GDBSearchDataTable
    Dim trGDBSearch As GDBChartData.GDBSearchRow

    <WebMethod()> _
    Public Function GDBSearch(ByVal Family As String, ByVal Given As String, ByVal YofBirth1 As Integer, _
                              ByVal YofBirth2 As Integer, ByVal YofDeath1 As Integer, ByVal YofDeath2 As Integer, _
                              ByVal Owner As String) As GDBChartData.GDBSearchDataTable
        tbGDBSearch = taGDBSearch.GetData(Family, Given, YofBirth1, YofBirth2, YofDeath1, YofDeath2, Owner)
        Return tbGDBSearch
    End Function
End Class

While the correct results are returned to tbGDBSearch within the service (as shown by debugging), I couldn't figure out how to get the results back to the program.  I am currently working through the notes from Suprotim's posts.  I think that his second post looks quite promising, but I've now been diverted on to an unrelated problem and it may be a few more days before I can get back to this issue.

Please try a simple way by using the DataSet's GetXml() method and return that as a string from your web service method.  The client side code can easily transform it back into a valid DataSet object.  Reference articles:

http://www.csharpuniversity.com/2009/03/13/how-to-create-an-aspnet-c-web-service-that-queries-a-database-table-and-returns-a-list-of-data/

http://www.csharpuniversity.com/2009/03/14/how-to-call-an-aspnet-c-web-service-that-queries-a-database-table-and-show-the-results-in-a-gridview/

*Also, just a side note, to make life easier for maintenance in the future, you may want to consider passing the search parameters as an object rather than individually.  That way you don't have to change the method signature every time.

This should work in Robert's case, as both his client and server are .NET. It will not, of course, work cross-platform. I also wonder if it will always work for every combination of .NET client version and .NET server version.

What specifically will not work cross-platform?  Please clarify.

The use of GetXml will produce a chunk of XML that will need to be parsed by any client except for a .NET client, which will be able to load it into a DataTable.

I tried the following but received an obscure JavaScript error. i'm not loving AJAX with web services right now. I have yet been able to get data from a method and place that data in a control. I thought this was about advancing but the examples I've seen look like they revert back to more primative methods of getting data from a database to a control. This code would have been nice had it worked. It is so easy to bind data in C#.NET code behind. When I look at AJAX examples out there I feel this sense of voodo at work. A dropdownlist with a reference to a service method that will get the web service method started. I can see my data for example if I use SQL reader with a simple get/set class.  The problem is returning data from these methods. I am puzzled that there is no BIND of some kind within the targeted dropdownlist. I don't feel that being forced to use a TableAdapter or XML is an acceptable limit. It doesn't seem to fit in with the idea of seperate data components. 

[WebMethod]

public string GetReportCategories()

{

SqlDataAdapter dbadapter = null;

DataSet returnDS = new DataSet();

SqlConnection con = new SqlConnection( WebConfigurationManager.ConnectionStrings["QSTrain1"].ConnectionString);

SqlCommand cmd = new SqlCommand();

string sqlQuery = "SELECT Report_Category_ID, Report_Category_Name FROM Report_Category";

cmd.CommandText = sqlQuery;

cmd.Connection = con;

dbadapter = new SqlDataAdapter(cmd);

dbadapter.Fill(returnDS);

return returnDS.GetXml();

}

Even if you're converting it to XML, you are still allowing your JavaScript client to know about and to be dependent on, the implementation details of your service. Do not pass DataTable, DataSet, or anything like that from a web service.

bofcarbon1:

[WebMethod]
public string GetReportCategories()
{

Your return type should be DataSet and not string

Here is an example that I have just added to the CommonData solution:

/// <summary>
/// Gets the change log list all.
/// </summary>
/// <returns>Dataset of all change log entries</returns>
[WebMethod(Description = "Gets the change log list all")]
public DataSet GetChangeLogListAll()
{
    return ChangeLog.ListAll();
}

This returned:

<DataSet>
<xs:schema id="NewDataSet">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:Locale="">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="Id" type="xs:int" minOccurs="0"/>
<xs:element name="ChangeFilename" type="xs:string" minOccurs="0"/>
<xs:element name="DateAdded" type="xs:dateTime" minOccurs="0"/>
<xs:element name="Purpose" type="xs:string" minOccurs="0"/>
<xs:element name="Rerunnable" type="xs:boolean" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram>
<NewDataSet>
<Table diffgr:id="Table1" msdata:rowOrder="0">
<Id>1</Id>
<ChangeFilename>CommonData02Y.SQL</ChangeFilename>
<DateAdded>2009-06-29T11:09:13.03+01:00</DateAdded>
<Purpose>Initial Load 0.2Y</Purpose>
<Rerunnable>false</Rerunnable>
</Table>
<Table diffgr:id="Table2" msdata:rowOrder="1">
<Id>2</Id>
<ChangeFilename>TestData02Y.SQL</ChangeFilename>
<DateAdded>2009-06-29T11:09:13.03+01:00</DateAdded>
<Purpose>Initial Data 0.2Y</Purpose>
<Rerunnable>false</Rerunnable>
</Table>
</NewDataSet>
</diffgr:diffgram>
</DataSet>