如何使用数据集读取C#中sql存储过程的返回值

如何使用数据集读取C#中sql存储过程的返回值

问题描述:

大家好,

我想使用数据集读取C#中sql存储过程的返回值.我该如何实现?

以下是我当前的信息:

存放程序:

hi folks,

I want to read the return value of sql store procedure in C# using dataset. How can i achieve this?

Below is my current:

store procedure:

create procedure abc
(
    @pcode varchar(max),
    @RETURNVALUE INT =0 OUT
)

as

DECLARE @COUNT INT

    BEGIN
        SELECT @COUNT=COUNT(*) FROM product where pcode=@pcode
            IF(@COUNT >0)
                BEGIN
                    SET @RETURNVALUE = 1;
                    RETURN @RETURNVALUE
                END
            ELSE
                BEGIN
                SELECT @COUNT=COUNT(*) FROM ProductColor where pcode=@pcode
                    IF(@COUNT >0)
                        BEGIN
                            SET @RETURNVALUE = 1;
                            RETURN @RETURNVALUE
                        END
                    ELSE
                        BEGIN
                            SELECT @COUNT=COUNT(*) FROM ProductColorSize where pcode=@pcode
                                IF(@COUNT >0)
                                    BEGIN
                                        SET @RETURNVALUE = 1;
                                        RETURN @RETURNVALUE
                                    END
                                ELSE
                                    BEGIN
                                        SET @RETURNVALUE = 0;
                                        RETURN @RETURNVALUE
                                    END

    END
    END
END
select RETURNVALUE=@RETURNVALUE;




---------------------------

C#应用程序




---------------------------

C# application

ds1 = new DataSet();


                                    try
                                    {
                                        paraPM = new SqlParameter[1];
                                        paraPM[0] = new SqlParameter("@pcode", Code.ToString().Replace("'", "'"));

                                        ds1 = Dataacess.GetDataSet("verifyProductCodeExcel2", CommandType.StoredProcedure, paraPM);




                                        if (ds1.Tables[0].Rows.Count > 0)
                                        {

                                            eok = false;


                                        }
                                        else
                                        {
                                            eok = true;

                                        }
                                    }
                                    catch (Exception ex)
                                    {


                                        throw ex;

                                    }
                                    finally
                                    {
                                        ds1.Dispose();

                                        paraPM = null;
                                    }


------------------------------------


------------------------------------

DataSet ds=null;
        SqlCommand cmd=null;
        SqlDataAdapter da=null;
        SqlConnection con=null;
        try
        {

            string conString = siteconfiguration.DbConnectionstring();
            using (con = new SqlConnection(conString))
            {
                con.Open();
                using (cmd = new SqlCommand(cmdText, con))
                {
                    cmd.CommandType = cmdType;
                    if (parameters != null)
                    {
                        foreach (SqlParameter parameter in parameters)
                        {
                            if (null != parameter) cmd.Parameters.Add(parameter);
                        }
                    }
                    using (da = new SqlDataAdapter(cmd))
                    {
                        ds = new DataSet();
                        da.Fill(ds);

                        return ds;
                    }
                }
            }
        }
        catch (Exception ex)
        {
           //HttpContext.Current.Response.Write("===" + ex.Message + ex.StackTrace);
           throw ex;

        }
        finally
            {
                if (ds != null)
                {
                    ds.Dispose();
                }
                if (da != null)
                {
                    da.Dispose();
                }
                if (cmd != null)
                {
                    cmd.Parameters.Clear();
                    cmd.Dispose();
                }
                if (con != null)
                {
                    con.Dispose();
                }

            }




===============================

来自Mustafa:
请正确设置代码格式和发布内容.




================================

From Mustafa:
Please format your code and postings properly.

看,这都是错误的.我不会通过数据集检索标量值.尝试使用DataReader或其派生类之一,例如SqlDataReader.

您将执行以下操作:

See, this is all wrong. I wouldn''t go about retrieving scalar values through a DataSet. Try the DataReader or one of its derivative classes like SqlDataReader.

you would do something like this:

//Setup SPROC
Command = new SqlCommand();
Command.Connection = Connection;

Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "abc";

Command.Parameters.Add(new SqlParameter("@pcode", SqlDbType.VarChar,5000));
Command.Parameters.Add(new SqlParameter("@RETURNVALUE", SqlDbType.Int));

Command.Parameters["@pcode"].Value = abcValue.pcode;

Command.Parameters["@RETURNVALUE"].Direction = ParameterDirection.Output;

if (Command.Connection.State != ConnectionState.Open)
    Command.Connection.Open();

Command.ExecuteNonQuery();

return (int)Command.Parameters["@RETURNVALUE"].Value;