XML Web Services

Re: How do I get table rows returned by a web service

johnwsaunders3 — Mon, 06 Jul 2009 15:36:37 GMT

Great. Glad to hear it. I hope you enjoy learning about WCF.

Feef free to start a new thread with any WCF questions you may have.

Re: How do I get table rows returned by a web service

bofcarbon1 — Mon, 06 Jul 2009 14:43:31 GMT

I'm finally getting to read in depth about web services. I undestand the XML friendly arguments passed and returned now. I guess the trade-off is worth it. I am looking forward to using them between my .NET web apps and my Java web apps (coming soon). Turns out my final fix of my AJAX cascading drop down list was selecting a ParentID as a parameter in the child cascading drop down list which was overlooked in the example that AJAX provided. Plus without a good debugger it was a mystery as to where the real problem was in the web service method or in the controls interfacing with them.

Re: How do I get table rows returned by a web service

johnwsaunders3 — Sat, 04 Jul 2009 08:28:36 GMT

Note that nothing but .NET will be able to make sense out of that XML.

Re: How do I get table rows returned by a web service

TATWORTH — Sat, 04 Jul 2009 07:17:36 GMT

I find it preferable to write the data access as a separate layer:

    #region " ListAll "
    /// <summary>
    /// Select list of all on ChangeLog table
    /// </summary>
    /// <remarks>
    /// This code was autogenerated on 29Jun2009
    /// </remarks>
    /// <returns>dataset of ChangeLog </returns>
    public static DataSet ListAll()
    {
      return CommonData.GetDataSet("usp_ChangeLogListAll");
    }
    #endregion

Re: How do I get table rows returned by a web service

TATWORTH — Sat, 04 Jul 2009 07:15:26 GMT

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>

Re: How do I get table rows returned by a web service

johnwsaunders3 — Sat, 04 Jul 2009 04:03:13 GMT

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.

Re: How do I get table rows returned by a web service

bofcarbon1 — Sat, 04 Jul 2009 03:41:29 GMT

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();

}

Re: How do I get table rows returned by a web service

johnwsaunders3 — Tue, 17 Mar 2009 18:49:06 GMT

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.

Re: How do I get table rows returned by a web service

CSharpUniversity.com — Mon, 16 Mar 2009 17:21:25 GMT

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

Re: How do I get table rows returned by a web service

johnwsaunders3 — Mon, 16 Mar 2009 17:03:34 GMT

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.

Re: How do I get table rows returned by a web service

CSharpUniversity.com — Mon, 16 Mar 2009 06:08:56 GMT

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.

Re: How do I get table rows returned by a web service

Robert Barnes — Fri, 13 Mar 2009 20:13:44 GMT

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.

Re: How do I get table rows returned by a web service

TATWORTH — Fri, 13 Mar 2009 17:00:06 GMT

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.

Re: How do I get table rows returned by a web service

TATWORTH — Fri, 13 Mar 2009 16:57:53 GMT

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

Re: How do I get table rows returned by a web service

Suprotim Agarwal — Thu, 12 Mar 2009 03:35:32 GMT

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