Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET SQL Server
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# 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();
                }
 
            }
 

================================
 
From Mustafa:
Please format your code and postings properly.
Posted 9-Apr-10 0:50am
Edited 9-Apr-10 2:09am
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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;
 

  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 7,215
1 DamithSL 5,114
2 Maciej Los 4,866
3 Sergey Alexandrovich Kryukov 4,747
4 Kornfeld Eliyahu Peter 4,514


Advertise | Privacy | Mobile
Web03 | 2.8.141223.1 | Last Updated 9 Apr 2010
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100