Here is the code for a webservice which returns a dataset:
<WebMethod()>
Public Function ReadCustomers(ByVal CustCode As String) As DataSet
Dim strSql As String
Dim objConn As New SQLConnection(ConfigurationSettings.AppSettings("MyDataDSN"))
strSql = "select * from CUSTOMER_TABLE WHERE CUSTOMER_CODE = '" & CustCode
Dim MyDataAdapter As New SQLDataAdapter(strSql, objConn)
Dim ds As New DataSet
MyDataAdapter.Fill(ds)
Return ds
End Function
Note the Data Connection information being referenced from the Web.config which would contain the following code :
<configuration>
..
..
<appSettings>
<add key="MyDataDSN" value="data source=SQLSERVER1;DATABASE=Retail;UID=myloginid;PWD=mypassword;"/>
</appSettings>
</configuration>
This code would normally be published as a separate project, in true "N-Tier" fashion. It could be in the same project as the retrieval routine, but there wouldn't be much point in it being a webservice!
In a separate Visual Studio Project, at the very top of the code-behind page, add the declaration:
Imports System.Web.Services
Then inside the function which will retrieve the data, declare the objects which will use the webservice:
Dim consumeWebService = New MyWebServices.DataRoutines
Dim MyDataSet As DataSet
Here the ReadCustomers Web Method is called, storing the returned data in a dataset:
Try
MyDataSet = consumeWebService.ReadCustomers(strUsername)
Catch e As Exception
'No records returned - put error handling code here
End Try
Now set the data source of a datagrid to point to the dataset, and bind it to fill the datagrid:
MyDatagrid.DataSource = MyDataSet
MyDatagrid.DataBind()
The data will fill the datagrid and be displayed on screen.