＋设为首页 ＋添加到收藏夹
c#中调用存储过程怎么获取返回值？
public string GetPriceLst(string StorageID,string MaterialID,string FactoryID,string inprice,string verif)
{
try
{
firstCommand = new OleDbCommand("M_GetPrice");
firstCommand.CommandType = CommandType.StoredProcedure;
firstCommand.Parameters.Clear();
OleDbParameter param = firstCommand.Parameters.Add("@StorageID", OleDbType.VarChar);
param.Value = StorageID;
param = firstCommand.Parameters.Add("@MaterialID", OleDbType.VarChar);
param.Value = MaterialID;
param = firstCommand.Parameters.Add("@FactoryID", OleDbType.VarChar);
param.Value = FactoryID;
param = firstCommand.Parameters.Add("@inprice", OleDbType.VarChar);
param.Value = inprice;
param = firstCommand.Parameters.Add("@Verif", OleDbType.VarChar);
param.Value = verif;
param = firstCommand.Parameters.Add("@Strprice", OleDbType.VarChar,100);
param.Value = "";
param.Direction = ParameterDirection.Output;
OleDbConnection Conn =new OleDbConnection(ClsDbConnect.ConnectionString );
Conn.Open();
firstCommand.Connection = Conn;
firstCommand.ExecuteNonQuery();
return firstCommand.Parameters["@Strprice"].Value.ToString();
}
catch (System.Exception eFillDataSet)
{
return eFillDataSet.Message;
}
}
同意 Lovest(大法师)
up
这是我曾写过的一个存储过程：
CREATE PROCEDURE vw_News_InsertNews
@CategoryID int,
@Title varchar(250),
@Body text,
@ReleaseDate datetime,
@ExpireDate datetime,
@NewsID int OUTPUT
AS
INSERT INTO News_News(CategoryID, Title, Body, ReleaseDate, ExpireDate)
VALUES (@CategoryID, @Title, @Body, @ReleaseDate, @ExpireDate)
SET @NewsID = @@IDENTITY
IF @@ERROR > 0
BEGIN
RAISERROR (Insert of News failed, 16, 1)
RETURN 99
END
ELSE
BEGIN
RETURN 1
END
返回：99 or 1
下面代码方法可调用该存储过程，当然之前myConnection和parameters必须构造好
/// <summary>
/// 这一方法有利于执行Insert,Update和Delete查询，返回一个数值条件代码，
/// 来说明查询是成功还是失败。
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">命令对象的参数</param>
/// <param name="rowsAffected">执行存储过程所影响的行数</param>
/// <returns>返回参数中ReturnValue的值</returns>
protected int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected )
{
int result;
myConnection.Open();
SqlCommand command = BuildCommand( storedProcName, parameters );//这个函数是我自定义的，根据parameters构造一个命令对象并返回
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
//result即为返回值
myConnection.Close();
return result;
}
// SQL参数输入参数
public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size,
object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
}
// SQL参数输出参数
public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
}
在存储过程中定义与这样:
Create Proc ProcName(
@aa varchar(20),
@bb varchar(30) output
)
此时你就用
MakeInParam( @aa, SqlDbTypes.Varchar, 20 );
MakeOutParam( @bb, SqlDbTypes.Varchar, 30 );
...
