如何使用多个输出参数创建Web服务？ [英] How I make web service with multiple output parameters?

问题描述

我试过但它返回null。



我尝试过的事情：



我的存储过程是

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);
}

推荐答案

你应该从SP返回数据而不是使用put参数。

如下所示



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

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

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

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

in .NET

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);
        }
}

JSON回复：

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：支持@ xxx.com，

emlSubj：null，

emlBody：[DEV] www.accessxxx.com

}

]

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

这篇关于如何使用多个输出参数创建Web服务？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！