Creating web-service C# HELP!

Question

0.00/5 (No votes)

See more:

Hello, guys.
Please help me.
I write a C # web-service, and i want to receive data in XML from database.
I have stored procedure in MS SQL, it is:

SQL

CREATE PROCEDURE select_request
@id int,
@name nvarchar(50) output,
@count int output,
@date nvarchar(50) output,
@fio nvarchar(50) output

AS
BEGIN
SET NOCOUNT ON;
  select @name=name, @count=count,@fio=fio,@date=date from View_4
    WHERE id_req=@id

END

and i want to execute this stored procedure, and return data in XML.

I wrote this code on C#:

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;

namespace usingparameterwebservice
{
    /// <summary>
    /// Summary description for mywebservice
    /// </summary>
    [WebService(Namespace = "create_request")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
    // [System.Web.Script.Services.ScriptService]
    public class Service1 : System.Web.Services.WebService
    {
        string constring = "Database=details;server=learnad02;user=alexrf;password=111111";
        SqlConnection conn;
        SqlCommand comm;

        [WebMethod(Description = "Создание заявки")]
        public string data(int id, string nameproc)
        {
            conn = new SqlConnection(constring);
            conn.Open();

            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = System.Data.CommandType.StoredProcedure;
            comm.CommandText = nameproc;           
            comm.Parameters.AddWithValue("@id", id);
            
            try
            {
                comm.ExecuteNonQuery();
                return "ALL DATA IS SELECTED";
            }
            catch (Exception)
            {
                return "FAILED";
            }


            finally
            {
                conn.Close();
            }

I know, that ExecuteNonQuery() uses only when insert,update operation, but how i can return data in XML.
HEEELP!
i need some thing like this

XML

<dataset xmlns="http://www.aspnetmania.com/webservices">
    <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
        <xs:element name="NewDataSet" msdata:isdataset="true" msdata:locale="uk-UA" xmlns:msdata="#unknown">
            <xs:complextype>
                <xs:choice maxoccurs="unbounded">
                    <xs:element name="Orders">
                        <xs:complextype>
                            <xs:sequence>
                                <xs:element name="OrderID" type="xs:int" minoccurs="0" />
                                <xs:element name="OrderDate" type="xs:dateTime" minoccurs="0" />
                                <xs:element name="RequiredDate" type="xs:dateTime" minoccurs="0" />
                                <xs:element name="ShippedDate" type="xs:dateTime" minoccurs="0" />
                            </xs:sequence>
                        </xs:complextype>
                    </xs:element>
                    <xs:element name="OrderDetails">
                    …
                    </xs:element>
                </xs:choice>
            </xs:complextype>
            <xs:unique name="Constraint1">
                <xs:selector xpath=".//Orders" />
                <xs:field xpath="OrderID" />
            </xs:unique>
            <xs:keyref name="Relation1" refer="Constraint1">
                <xs:selector xpath=".//OrderDetails" />
                <xs:field xpath="OrderID" />
            </xs:keyref>
        </xs:element>
    </xs:schema>
    <diffgr:diffgram xmlns:diffgr="#unknown"></diffgr:diffgram></dataset>

Posted 11-Apr-13 2:53am

Alexrf

Updated 11-Apr-13 4:03am

CHill60

v2

Add a Solution

Comments

ZurdoDev 11-Apr-13 8:56am

In your SQL instead of using output parameters just use a SELECT statement to return the data and then in C# use a SQLDataReader.

Alexrf 11-Apr-13 9:14am

Please tell me, how it write on c # correct?
try
{
SqlDataReader reader = comm.ExecuteReader();

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2013-04-11T03:27:00

Return the data from your stored procedure as XML in the first place ... something like ...

CREATE PROCEDURE select_request
    @id int
AS
    BEGIN
    SET NOCOUNT ON;
      select @name=name, @count=count,@fio=fio,@date=date from View_4
        WHERE id_req=@id  for xml auto, elements 
    END

Then in your code use something like

XmlReader xmlResults = cmd.ExecuteXmlReader();

Couple of caveats
- I haven't been able to test this properly so there may be typos
-the SQL may need to change depending on your verson of SQL Server
- the XML returned is a fragment not a full XML document - see this link for a solution
http://www.sqlxml.org/faqs.aspx?faq=16[^]

Alexrf · Answer 2 · 2013-04-11T03:50:00

CHill60 , i need some thing like this

XML

<dataset xmlns="http://www.aspnetmania.com/webservices">
    <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
        <xs:element name="NewDataSet" msdata:isdataset="true" msdata:locale="uk-UA" xmlns:msdata="#unknown">
            <xs:complextype>
                <xs:choice maxoccurs="unbounded">
                    <xs:element name="Orders">
                        <xs:complextype>
                            <xs:sequence>
                                <xs:element name="OrderID" type="xs:int" minoccurs="0" />
                                <xs:element name="OrderDate" type="xs:dateTime" minoccurs="0" />
                                <xs:element name="RequiredDate" type="xs:dateTime" minoccurs="0" />
                                <xs:element name="ShippedDate" type="xs:dateTime" minoccurs="0" />
                            </xs:sequence>
                        </xs:complextype>
                    </xs:element>
                    <xs:element name="OrderDetails">
                    …
                    </xs:element>
                </xs:choice>
            </xs:complextype>
            <xs:unique name="Constraint1">
                <xs:selector xpath=".//Orders" />
                <xs:field xpath="OrderID" />
            </xs:unique>
            <xs:keyref name="Relation1" refer="Constraint1">
                <xs:selector xpath=".//OrderDetails" />
                <xs:field xpath="OrderID" />
            </xs:keyref>
        </xs:element>
    </xs:schema>
    <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="#unknown">
    xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
        <newdataset xmlns="">
            <orders diffgr:id="Orders1" msdata:roworder="0">
                <orderid>10643</orderid>
                <orderdate>1997-08-25T00:00:00.0000000+03:00</orderdate>
                <requireddate>1997-09-22T00:00:00.0000000+03:00</requireddate>
                <shippeddate>1997-09-02T00:00:00.0000000+03:00</shippeddate>
            </orders>
            <orders diffgr:id="Orders2" msdata:roworder="1">
                <orderid>10692</orderid>
                <orderdate>1997-10-03T00:00:00.0000000+03:00</orderdate>
                <requireddate>1997-10-31T00:00:00.0000000+02:00</requireddate>
                <shippeddate>1997-10-13T00:00:00.0000000+03:00</shippeddate>
            </orders>
            …
            <orderdetails diffgr:id="OrderDetails1" msdata:roworder="0">
                <productname>Rössle Sauerkraut</productname>
                <unitprice>45.6</unitprice>
                <quantity>15</quantity>
                <discount>0.25</discount>
                <orderid>10643</orderid>
            </orderdetails>
            <orderdetails diffgr:id="OrderDetails2" msdata:roworder="1">
                <productname>Chartreuse verte</productname>
                <unitprice>18</unitprice>
                <quantity>21</quantity>
                <discount>0.25</discount>
                <orderid>10643</orderid>
            </orderdetails>
            …
        </newdataset>
    </diffgr:diffgram>
</dataset>

Creating web-service C# HELP!

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0