存储过程的输出参数 [英] Output parameter from stored procedure

问题描述

我正在用c#编码设计一个asp.net项目.

我已经在3层架构中设计了我的项目.

数据链接层:

I am designing a asp.net project with c# coding.

I have design my project in 3 tier architecture.

Data Link Layer:

public int ExecuteNonQuerySP(Parameters[] Parameters, string SpName)
        {
            SqlCommand cmd;
            int i_AffectedRecords = 0;
            try
            {
                c.Open();
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = c;
                cmd.CommandText = SpName;
                BuildParameters(ref cmd, Parameters);
                i_AffectedRecords = Convert.ToInt32(cmd.ExecuteNonQuery());
                c.Close();

            }
            catch (SqlException SqlExc)
            {
                throw SqlExc;

            }
            return (i_AffectedRecords);
        }


private void BuildParameters(ref SqlCommand cmd, Parameters[] InputParameters)
        {
            foreach (Parameters param in InputParameters)
            {
                SqlParameter tempParam = new SqlParameter(param.ParamName, param.SqlDataType);
                tempParam.Value = param.ParamValue;
                cmd.Parameters.Add(tempParam);
            }

        }

业务层:

Business layer :

public int SelectUser(String lon_id_c, String pwd_c)
        {
            try
            {
                //DataSet ds = new DataSet();
                int result;
                Parameters[] Param = new Parameters[3];

                Param[0] = new Parameters();
                Param[0].ParamName = "@lon_id_c";
                Param[0].ParamValue = Convert.ToString(lon_id_c);
                Param[0].SqlDataType = SqlDbType.VarChar;

                Param[1] = new Parameters();
                Param[1].ParamName = "@pwd_c";
                Param[1].ParamValue = Convert.ToString(pwd_c);
                Param[1].SqlDataType = SqlDbType.VarChar;

                Param[2] = new Parameters();
                Param[2].ParamName = "@error_msg";
                Param[2].ParamValue = "";
                Param[2].SqlDataType = SqlDbType.VarChar;
                Param[2].ParamDirection = "Output";

                result = connect.ExecuteNonQuerySP(Param, "chk_login");
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {

            }

        }

表示层:

Presentation Layer:

public void CheckUser()
    {
        try
        {
            int res = Log.SelectUser(txt_username.Text,txt_password.Text);
            //if (dsEmp != null)
            //{
            //    if (dsEmp.Tables[0].Rows.Count > 0)
            //    {
            //        Response.Redirect("Home.aspx");
            //    }

            //}
        }
        catch (Exception ex)
        {
            throw ex;

        }
    }

存储过程:

Stored procedure:

alter procedure  chk_login                      
   @lon_id_c                       varchar(25),                                  
   @pwd_c   varchar(25),                                   
   @error_msg    varchar(100) output                                  
                                  
as                                  
begin                           
              
              
if not exists (select lon_id_c from user_list where lon_id_c = @lon_id_c)          
begin          
select @error_msg = 'Invalid User Name'      
RETURN                      
end          
                            
             
if not exists (select pwd_c from user_list where pwd_c = @pwd_c and lon_id_c = @lon_id_c)          
begin          
select @error_msg = 'Invalid Password'                    
RETURN                      
end          
          
                               

 SELECT @error_msg = 'SUCCESS'                                              
 Return                                
                                  
  End

请帮助我访问存储过程的输出参数，因为我仅通过存储过程验证了所有数据.

我无法通过客户端脚本或表示层来验证数据.

Please help me to access output parameter from the stored procedure, since I have validated all my data through only stored procedure.

I can''t validate the data through client script or in the presentation layer.

推荐答案

在数据访问层和业务层方法中使用输出参数，并且相同它会在表示层中调用.

例如，
数据访问层

Use the output parameter in the data access layer and business layer methods, and same it call in presentation layer.

For example,
Data access layer

public string fun(string a,string b, string out c)
{
    //connection string
    //comand object

    cmd.Parameters.Add("@c",SqlDbType.DataType);
    cmd.Parameters["@c"].Direction = ParameterDirection.Output
    command object return statement
    c = (cmd.Parameters["@c"].Value).ToString(),
}

在业务层中，调用数据访问层的方法.

In the business layer call the method of data access layer.

我已经尝试过了.但我的输出参数中没有值.

如何调试.

I have tried it. but there is no value in my output parameter.

how to debug this .

请回复任何一个.我在我的项目中也遇到了同样的问题.尽快回复任何一个

问候，

Sathis

Please reply any one. Im also facing same issue in my project.Reply any one ASAP

Regards,

Sathis

这篇关于存储过程的输出参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！