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)
{
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.