[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

C# Webservice SQL and how to return a result

Posted on 2008-11-08
5
Medium Priority
?
1,206 Views
Last Modified: 2012-05-05
Hi everyone and thanks in advance for your help.

I am trying to create a web service that uses an input parameter and outputs a string

Example = input parameter = 'Hi'
Result  = 'Hi thanks for stopping  by my web service"

I have created and test the Proc and the proc reads like this
select output1 from table where input1 = @input1
ouput1 is a nvarchar (255)

Now my webservice is working but it is returning a 0 for the results
code below

        [WebMethod]
        public int GetTextResponse(string filter)
        {
            string connString = "Server= xxx ;Initial Catalog=TIRES;Persist Security Info=True;User ID=sa; Password=xxxx;";
            using (SqlConnection sc = new SqlConnection(connString))
            {
                sc.Open();
                using (SqlCommand cmd = new SqlCommand ("usp_GetTextResponse",sc))
                {  
                    cmd.CommandType = CommandType.StoredProcedure;
                   // cmd.Parameters.Add("Output1",SqlDbType.NVarChar,255);
                    //cmd.Parameters["OutPut1"].Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add("@Input1",SqlDbType.NVarChar,50);
                    cmd.Parameters["@Input1"].Direction =ParameterDirection.Input;
                    cmd.Parameters["@Input1"].Value = filter;
                    cmd.ExecuteNonQuery();
                   return (int)cmd.Parameters["Output1"].Value;

What am I doing wrong ?

I know it is in this line of code

           return (int)cmd.Parameters["Output1"].Value;

but am not sure what I need to do to fix

I know I could create a data set and return the output in the dataset ....but sense there is on one output and it is a nvarchar(255) ...I thought they would be some way just to return a string...Is a dataset the best way to go?

Thanks in advance for your help
0
Comment
Question by:BBQMemphis
  • 2
  • 2
5 Comments
 
LVL 2

Expert Comment

by:ssflynn
ID: 22913501
It's looks to be returning an integer becuase your method has (int) as the return type and this line

return (int)cmd.Parameters["Output1"].Value;

should read

return cmd.Parameters["Output1"].Value.ToString()

Also change

  [WebMethod]
        public int GetTextResponse(string filter)

to

  [WebMethod]
        public string GetTextResponse(string filter)
0
 

Author Comment

by:BBQMemphis
ID: 22913589
Thank my code now reads like this

  [WebMethod]
        public string GetTextResponse(string filter)
        {
            string connString = "Server= xxx ;Initial Catalog=TIRES;Persist Security Info=True;User ID=sa; Password=xxx;";
            using (SqlConnection sc = new SqlConnection(connString))
            {
                sc.Open();
                using (SqlCommand cmd = new SqlCommand ("usp_GetTextResponse",sc))
                {  
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("Output1",SqlDbType.NVarChar,255);
                    cmd.Parameters["OutPut1"].Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add("@Input1",SqlDbType.NVarChar,50);
                    cmd.Parameters["@Input1"].Direction =ParameterDirection.Input;
                    cmd.Parameters["@Input1"].Value = filter;
                    cmd.ExecuteNonQuery();
                    return cmd.Parameters["Output1"].Value.ToString();

It is still only outputting a 0 (zero) when I test the web service....I have used SQL profiler and am sure that the proc is running and not error out

Could it be I am really trying to insert a parameter and just print the result set as output .....What am I doing wrong..

Thanks Again
0
 
LVL 2

Accepted Solution

by:
ssflynn earned 1600 total points
ID: 22914408
Try the following code.  First is my stored procedure,  then my C# code.

CREATE PROCEDURE uspTest      
      @InputString NVARCHAR(50)
AS
BEGIN
      DECLARE @StringToAdd NVARCHAR(MAX)
      SET @StringToAdd = 'Add this text'
      SELECT @InputString + ' ' + @StringToAdd
END
GO
        public static string GetTextResponse(string filter)
        {
            string connString = "Server=localhost;Initial Catalog=XXX;Persist Security Info=True;User ID=sa; Password=XXX;";
            using (SqlConnection sc = new SqlConnection(connString))
            {
                sc.Open();
                using (SqlCommand cmd = new SqlCommand("uspTest", sc))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@InputString", SqlDbType.NVarChar, 50);
                    cmd.Parameters["@InputString"].Value = filter;
                    return cmd.ExecuteScalar().ToString() ;
                }
            }
        }

Open in new window

0
 
LVL 26

Assisted Solution

by:Anurag Thakur
Anurag Thakur earned 400 total points
ID: 22915049
i think the culprit is your stored procedure - may be you are not assigining the value to the @Output1 variable

modify your stored procedure like this
Declare Output1 NvarChar(255)
select @output1 = output1 from table where input1 = @input1


or if you let your sp's statement like this
select output1 from table where input1 = @input1

then you can simply use the ExecuteScalar as shown below
cmd.ExecuteScalar().ToString() ;
0
 

Author Closing Comment

by:BBQMemphis
ID: 31514706
Thanks for your help.....
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question