Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# .NET
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:
    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#:
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
<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">
Posted 11-Apr-13 3:53am
Alexrf361
Edited 11-Apr-13 5:03am
CHill6067K
v2
Comments
ryanb31 at 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 at 11-Apr-13 9:14am
   
Please tell me, how it write on c # correct?
try
{
SqlDataReader reader = comm.ExecuteReader();
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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[^]
  Permalink  
Comments
Alexrf at 11-Apr-13 9:36am
   
i need, that web-service do XML from database result.
CHill60 at 11-Apr-13 9:42am
   
Are you saying that you want to construct an XML document, in your C# code, using data returned in a SqlDtaReader column by column?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

CHill60 , i need some thing like this
<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">
  Permalink  
Comments
ThePhantomUpvoter at 11-Apr-13 10:13am
   
So what?
Alexrf at 11-Apr-13 10:20am
   
All good:)
I'm using dataset
[WebMethod(Description = "Просмотр заявки")]
public DataSet Select(string Reques_id)
{
SqlConnection myConn = new SqlConnection(constring);
SqlDataAdapter myData = new SqlDataAdapter("select_request", myConn);
myData.SelectCommand.CommandType = CommandType.StoredProcedure;
myData.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Char, 5));
myData.SelectCommand.Parameters["@id"].Value = Reques_id;
 
DataSet ds = new DataSet();
myData.Fill(ds);
return ds;
}
CHill60 at 11-Apr-13 10:41am
   
I thought your requirement was to return some XML as a string? All you have now is a DataSet.
 
Try putting something like this instead of return ds;StringWriter sw = new StringWriter();
 
ds.WriteXml(sw);
 
return sw.ToString();
Alexrf at 12-Apr-13 3:41am
   
It getting error in VS 2010
Error Cannot implicitly convert type 'string' to 'System.Data.DataSet'
Sergey Alexandrovich Kryukov at 15-May-13 10:55am
   
You should not post such content as "Solution", this is the abuse. Use "Improve question".
—SA

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

  Print Answers RSS
0 OriginalGriff 7,853
1 Sergey Alexandrovich Kryukov 7,107
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,790


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 11 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100