如何使用数据集读取C#中sql存储过程的返回值 [英] how to read the return value of sql store procedure in C# using dataset
本文介绍了如何使用数据集读取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;
这篇关于如何使用数据集读取C#中sql存储过程的返回值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文