How do I Use VB.NET web service to return a sql record set?

I am somewhat new to the web services/WSDL thing so i need a little help. I have created a web service using Visual studio. The web service is obviously in a .asmx page which tells .NET that it is a WS and then .net generates the WSDL so when you go to the .asmx?WSDL page it shows you the schema, etc.  i understand (sort of) how to create the service to return a simple value. I want to know how to create the WS to generate the WSDL as a complex type that displays a SQL record set of data something like this:

<s:element name="MyWSResponse">
- <s:complexType>
- <s:sequence>
  <s:element minOccurs="0" maxOccurs="1" name="MyWSResult" type="tns:RecordSet" />
  </s:sequence>
  </s:complexType>
  </s:element>
- <s:complexType name="RecordSet">
- <s:sequence>
  <s:element minOccurs="0" maxOccurs="1" name="IDcolumn" type="s:string" />
  <s:element minOccurs="0" maxOccurs="1" name="DataColumn" type="s:string" />
  </s:sequence>
- <s:sequence>
  <s:element minOccurs="0" maxOccurs="1" name="IDcolumn" type="s:string" />
  <s:element minOccurs="0" maxOccurs="1" name="DataColumn" type="s:string" />
  </s:sequence>
  </s:complexType>

I am currently using the SqlDataReader to get the data from the database, but i could us a dataset if necessary. I have tried a dataset with a getXML call but that only returns the xml as a string inside the WSDL response.

Please help.

Thanks,

acekz
acekzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PlatoConsultantCommented:
Web Service uses Dataset  for returning complete recordset values

http://www.west-wind.com/presentations/dotnetwebservices/DotNetWebServices.asp

check this link it has a detailed tutorial how u return dataset

 
[WebMethod]
 
public System.Data.DataSet GetAuthorData(string lcID)   {
 
 
 
if (lcID == "" )
 
  lcID = "%";
 
 
 
DataSet ds = new DataSet();
 
 
 
string cConnection = "server=(local);provider=SQLOleDb;database=pubs;uid=sa;pwd=";
 
OleDbConnection   oConn = new OleDbConnection(cConnection);
 
                 
 
OleDbDataAdapter oAdapter = new OleDbDataAdapter();
 
      oAdapter.SelectCommand =
 
  new OleDbCommand("select * from Authors where au_id like '" + lcID + "%'",oConn);
 
                 
 
try
 
{
 
  oConn.Open();
 
}
 
catch(Exception e)
 
{
 
  return null;
 
}
 
 
 
oAdapter.Fill(ds,"Authors");
 
     
 
oConn.Close();
 
return ds;       }

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.