How I make web service with multiple output parameters?

Question

0.00/5 (No votes)

See more:

I have tried but it returns null.

What I have tried:

My stored Procedure is

USE [webservice]
GO
/****** Object:  StoredProcedure [dbo].[SP_VALIDATE_VIN]    Script Date: 6/30/2017 10:11:32 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[SP_VALIDATE_VIN]
 @p_vin  varchar(1000),
 @Pmc int,
 @VIN                       varchar(1000) output,
@VEHICLE_MODEL             varchar(1000) output,
@MANUFACTURING_MONTH_YEAR  varchar(1000) output,
@INVOICE_NUMBER            varchar(1000) output,
@INVOICE_DATE              varchar(1000) output,
@INVOICE_TYPE              varchar(1000) output,
@INVOICE_DLR_FOR_CD        varchar(1000) output,
@CUST_NAME                 varchar(1000) output,
@CUST_ADDRESS1             varchar(1000) output,
@CUST_ADDRESS2             varchar(1000) output,
@CUST_ADDRESS3             varchar(1000) output,
@CUST_CITY                 varchar(1000) output,
@CUST_STATE                varchar(1000) output,
@CUST_PINCODE              varchar(1000) output,
@CUST_MOBILE               varchar(1000) output,
@CUST_EMAIL                varchar(1000) output,
@ERR_CD                    int output,
@ERR_MSG                   varchar(1000) output
 as begin
 select @VIN=VIN,@VEHICLE_MODEL=VEHICLE_MODEL,@MANUFACTURING_MONTH_YEAR=MANUFACTURING_MONTH_YEAR,@INVOICE_NUMBER=INVOICE_NUMBER,@INVOICE_DATE=INVOICE_DATE,@INVOICE_TYPE=INVOICE_TYPE,@INVOICE_DLR_FOR_CD=INVOICE_DLR_FOR_CD,@CUST_NAME=CUST_NAME,@CUST_ADDRESS1=CUST_ADDRESS1,@CUST_ADDRESS2=CUST_ADDRESS2,@CUST_ADDRESS3=CUST_ADDRESS3,
 @CUST_CITY=CUST_CITY,@CUST_STATE=@CUST_STATE,@CUST_PINCODE=@CUST_PINCODE,@CUST_MOBILE=@CUST_MOBILE,@CUST_EMAIL=CUST_EMAIL,@ERR_CD=ERR_CD,@ERR_MSG=ERR_MSG  from Validate_vin where P_VIN=@p_vin and PMC= @Pmc
 
 end

and my webservice code is

public string validate_vin(string _p_vin, int _PMC)
{
    List<Validate_vin> Vinvalidationlist = new List<Validate_vin>();
    try
    {

        // string sql = "select VIN,VEHICLE_MODEL,MANUFACTURING_MONTH_YEAR,INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,INVOICE_DLR_FOR_CD,CUST_NAME,CUST_ADDRESS1,CUST_ADDRESS2,CUST_ADDRESS3,CUST_CITY,CUST_STATE,CUST_PINCODE,CUST_MOBILE,CUST_EMAIL,ERR_CD,ERR_MSG from Validate_vin where P_VIN = '" + _p_vin + "' and PMC = '" + _PMC + "'";
        using (SqlConnection con = new SqlConnection(conn))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_VALIDATE_VIN";
            cmd.Parameters.AddWithValue("@p_vin", _p_vin);
            cmd.Parameters.AddWithValue("@Pmc", _PMC);
            cmd.Parameters.Add("@VIN", SqlDbType.VarChar,1000);
            cmd.Parameters["@VIN"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@VEHICLE_MODEL", SqlDbType.VarChar,1000);
            cmd.Parameters["@VEHICLE_MODEL"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@MANUFACTURING_MONTH_YEAR", SqlDbType.VarChar,1000);
            cmd.Parameters["@MANUFACTURING_MONTH_YEAR"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@INVOICE_NUMBER", SqlDbType.VarChar,1000);
            cmd.Parameters["@INVOICE_NUMBER"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@INVOICE_DATE", SqlDbType.VarChar,1000);
            cmd.Parameters["@INVOICE_DATE"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@INVOICE_TYPE", SqlDbType.VarChar,1000);
            cmd.Parameters["@INVOICE_TYPE"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@INVOICE_DLR_FOR_CD", SqlDbType.VarChar,1000);
            cmd.Parameters["@INVOICE_DLR_FOR_CD"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@CUST_NAME", SqlDbType.VarChar,1000);
            cmd.Parameters["@CUST_NAME"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@CUST_ADDRESS1", SqlDbType.VarChar,1000);
            cmd.Parameters["@CUST_ADDRESS1"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@CUST_ADDRESS2", SqlDbType.VarChar,1000);
            cmd.Parameters["@CUST_ADDRESS2"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@CUST_ADDRESS3", SqlDbType.VarChar,1000);
            cmd.Parameters["@CUST_ADDRESS3"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@CUST_CITY", SqlDbType.VarChar,1000);
            cmd.Parameters["@CUST_CITY"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@CUST_STATE", SqlDbType.VarChar,1000);
            cmd.Parameters["@CUST_STATE"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@CUST_PINCODE", SqlDbType.VarChar,1000);
            cmd.Parameters["@CUST_PINCODE"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@CUST_MOBILE", SqlDbType.VarChar,1000);
            cmd.Parameters["@CUST_MOBILE"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@CUST_EMAIL", SqlDbType.VarChar,1000);
            cmd.Parameters["@CUST_EMAIL"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@ERR_CD", SqlDbType.Int);
            cmd.Parameters["@ERR_CD"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@ERR_MSG", SqlDbType.VarChar,1000);
            cmd.Parameters["@ERR_MSG"].Direction = ParameterDirection.Output;
            SqlDataReader dr= cmd.ExecuteReader();
            //SqlDataAdapter da = new SqlDataAdapter("SP_VALIDATE_VIN", conn);
            //DataSet ds = new DataSet();
            //da.Fill(ds);
            while (dr.Read())
            {
                Validate_vin Vin_validation = new Validate_vin();
                Vin_validation.VIN = Convert.ToString(dr["VIN"].ToString());
                Vin_validation.VEHICLE_MODEL= Convert.ToString(dr["VEHICLE_MODEL"].ToString());
                Vin_validation.MANUFACTURING_MONTH_YEAR= Convert.ToString(dr["MANUFACTURING_MONTH_YEAR"].ToString());
                Vin_validation.INVOICE_NUMBER= Convert.ToString(dr["INVOICE_NUMBER"].ToString());
                Vin_validation.INVOICE_DATE= Convert.ToString(dr["INVOICE_DATE"].ToString());
                Vin_validation.INVOICE_TYPE= Convert.ToString(dr["INVOICE_TYPE"].ToString());
                Vin_validation.INVOICE_DLR_FOR_CD= Convert.ToString(dr["INVOICE_DLR_FOR_CD"].ToString());
                Vin_validation.CUST_NAME= Convert.ToString(dr["CUST_NAME"].ToString());
                Vin_validation.CUST_ADDRESS1= Convert.ToString(dr["CUST_ADDRESS1"].ToString());
                Vin_validation.CUST_ADDRESS2= Convert.ToString(dr["CUST_ADDRESS2"].ToString());
                Vin_validation.CUST_ADDRESS3= Convert.ToString(dr["CUST_ADDRESS3"].ToString());
                Vin_validation.CUST_CITY= Convert.ToString(dr["CUST_CITY"].ToString());
                Vin_validation.CUST_STATE= Convert.ToString(dr["CUST_STATE"].ToString());
                Vin_validation.CUST_PINCODE= Convert.ToString(dr["CUST_PINCODE"].ToString());
                Vin_validation.CUST_MOBILE= Convert.ToString(dr["CUST_MOBILE"].ToString());
                Vin_validation.CUST_EMAIL= Convert.ToString(dr["CUST_EMAIL"].ToString());
                Vin_validation.ERR_CD= Convert.ToString(dr["ERR_CD"].ToString());
                Vin_validation.ERR_MSG= Convert.ToString(dr["ERR_MSG"].ToString());
                Vinvalidationlist.Add(Vin_validation);
            }
            con.Close();
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {

    }
    return JsonConvert.SerializeObject(Vinvalidationlist, Newtonsoft.Json.Formatting.Indented);
}

Posted 29-Jun-17 23:40pm

Member 12962919

Updated 30-Jun-17 10:47am

Add a Solution

Comments

F-ES Sitecore 30-Jun-17 5:44am

What's wrong with the code you posted? (Note - "It doesn't work" is not an answer that contains enough information for people to help you)

Member 12962919 30-Jun-17 5:50am

after executing its return 0 count in dr.

F-ES Sitecore 30-Jun-17 6:09am

Then SP_VALIDATE_VIN isn't returning data given the inputs you're supplying. We can't access your data and we don't know what your inputs are so we can't tell you why that is, you'll need to look at the database and the values you're giving for your parameters to try and work it out. Maybe use something like SQL Profiler as that will show you exactly what is being executed against the database.

Member 12962919 30-Jun-17 6:11am

can you check the Procedure or code is completely true or anything wrong in it

F-ES Sitecore 30-Jun-17 6:19am

Looking at it further it looks like you are selecting all data into your output parameters, but your code is accessing the data as if it was a result set. Your SP doesn't generate any results, it simply sets the value of output params, so with no results ExecuteReader returns nothing. You might need to use ExecuteNonQuery rather than ExecuteReader, then examine the output params (google "ado.net output parameters" to see how to properly use output params).

Alternatively ditch all of the output params and just do a normal "SELECT VIN, Vehicle_Model...." and that will generate a result set that you can use your existing code to process.

1 solution

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)

sunithaNag · Answer 1 · 2017-06-30T10:47:00

you should return the data from SP instead of using out put paramters.
like below

-- =============================================

SQL

ALTER PROCEDURE [dbo].[my_test]	
	(
	@template_id varchar(50)
	
	
	)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT 
      [app_id]
    , [business_domain_id]
     , [business_line]
    ,  [language_cd]
    , [email_type]
    , [email_from]
     , [email_subject]
     [email_body]
  FROM [dbo].[EmailTemplate]
  where @template_id=template_id
END

in .NET

C#

public static string getData(string tempid)
        {
            List<MyTemplate> mylist = new List<MyTemplate>();
            SqlDataAdapter da = new SqlDataAdapter();
            System.Data.DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionStr"].ToString()))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                con.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.[my_test]";
                cmd.Parameters.AddWithValue("@template_id", tempid);
                da.SelectCommand = cmd;
                da.Fill(ds);

            }
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0] != null)
            {
                foreach (DataRow Dr in ds.Tables[0].Rows)
                {
                    MyTemplate mTemp = new MyTemplate();
                    mTemp.appId = Dr["app_id"].ToString();
                    mTemp.bDomainId = Dr["business_domain_id"].ToString();
                    mTemp.Bline = Dr["business_line"].ToString();
                    mTemp.emlBody = Dr["email_body"].ToString();
                    mTemp.emlFrom = Dr["email_from"].ToString();
                  //  mTemp.emlSubj = Dr["email_subject"].ToString();
                    mTemp.emlTyp = Dr["email_type"].ToString();
                    mylist.Add(mTemp);

                }

            }


            return JsonConvert.SerializeObject(mylist, Newtonsoft.Json.Formatting.Indented);
        }
}

Response in JSON:

<pre lang="objc">

[
{
"appId": "0ba9a0e7-25d4-4174-967a-956b47d04d4c",
"bDomainId": "9dd3f102-273c-40a2-a122-69628712680a",
"Bline": "EUR",
"lnG": null,
"emlTyp": "userName",
"emlFrom": "support@xxx.com",
"emlSubj": null,
"emlBody": "[DEV] www.accessxxx.com"
}
]