int _count = SqlCommand.ExecuteNonQuery() //执行SQL语句或者存储过程,返回影响的行数 string _string = SqlCommand.ExecuteScalar() //执行SQL语句或者存储过程,返回结果集的第一行第一列。
我的存储过程:ALTER proc tea_jiaoshi_limit_onstu_id (@stu_id char(10)='Q0152100',@tea_id char(10)='t03',@returnmessage nvarchar(20) output ) as if @tea_id in ( select b.tea_id from stu_info a inner join mancou_info b on a.stu_class=b.stu_class where a.stu_id=@stu_id)begin set @returnmessage='aaaa' end else begin set @returnmessage='bbbb' end我的程序: string strConnection="server=(local);database=temp;uid=sa;password=;"; SqlConnection objConnection = new SqlConnection(strConnection); SqlCommand objCommand = new SqlCommand("tea_jiaoshi_limit_onstu_id",objConnection); objCommand.CommandType=CommandType.StoredProcedure;
SqlConnection objConnection = new SqlConnection(strConnection);
// string strSQL = "Select * from Categories";
// SqlCommand objCommand = new SqlCommand(strSQL,objConnection);
SqlCommand objCommand = new SqlCommand("sp_CustomersByState",objConnection);
objCommand.CommandType=CommandType.StoredProcedure;
//参数对象
SqlParameter objParameter=new SqlParameter("@Region",SqlDbType.NVarChar,20);
objCommand.Parameters.Add(objParameter);
objParameter.Direction=ParameterDirection.Input;
objParameter.Value=txtRegion.Text;
objConnection.Open();
dgOutput.DataSource = objCommand.ExecuteReader();
dgOutput.DataBind();
objConnection.Close();
ExecuteScalar();
objCommand.Parameters.Add(objParameter);
objParameter.Direction=ParameterDirection.ReturnValue;
objParameter.Value=txtRegion.Text;
objCommand.Parameters.Add(objParameter);
objParameter.Direction=ParameterDirection.ReturnValue; //这里定义
存储过程这样写
CREATE PROCEDURE sp_CustomersByState
(
@returnmessage varchar(50) output
)
AS
set @returnmessage='aaaaaaaa'
GO
其实就是后面多了个 output
/// <summary>
/// 获得该学生某学年的学费、教材费、培训费、录取费缴纳情况
/// </summary>
public void GetChargeStat(int SchoolYear)
{
// @Schooling money OUTPUT, --输出应收学费
// @BookFee money OUTPUT, --输出应收教材费
// @TrainFee money OUTPUT, --输出应收培训费
// @MatriculateFee money OUTPUT, --输出应收录取费
// @AlreadySchooling money OUTPUT, --输出已收学费
// @AlreadyBookFee money OUTPUT, --输出已收教材费
// @AlreadyTrainFee money OUTPUT, --输出已收培训费
// @AlreadyMatriculateFee money OUTPUT --输出已收录取费
SqlParameter[] sqlPrm = new SqlParameter[]
{
new SqlParameter("@StudID",this._StudID),
new SqlParameter("@SchoolYear",SchoolYear),
new SqlParameter(),
new SqlParameter(),
new SqlParameter(),
new SqlParameter(),
new SqlParameter(),
new SqlParameter(),
new SqlParameter(),
new SqlParameter()
};
sqlPrm[2].ParameterName = "@Schooling";
sqlPrm[2].SqlDbType = SqlDbType.Money;
sqlPrm[2].Direction = ParameterDirection.Output;
sqlPrm[3].SqlDbType = SqlDbType.Money;
sqlPrm[3].ParameterName = "@BookFee";
sqlPrm[3].Direction = ParameterDirection.Output;
sqlPrm[4].ParameterName = "@TrainFee";
sqlPrm[4].SqlDbType = SqlDbType.Money;
sqlPrm[4].Direction = ParameterDirection.Output;
sqlPrm[5].ParameterName = "@MatriculateFee";
sqlPrm[5].SqlDbType = SqlDbType.Money;
sqlPrm[5].Direction = ParameterDirection.Output;
sqlPrm[6].ParameterName = "@AlreadySchooling";
sqlPrm[6].SqlDbType = SqlDbType.Money;
sqlPrm[6].Direction = ParameterDirection.Output;
sqlPrm[7].ParameterName = "@AlreadyBookFee";
sqlPrm[7].SqlDbType = SqlDbType.Money;
sqlPrm[7].Direction = ParameterDirection.Output;
sqlPrm[8].ParameterName = "@AlreadyTrainFee";
sqlPrm[8].SqlDbType = SqlDbType.Money;
sqlPrm[8].Direction = ParameterDirection.Output;
sqlPrm[9].ParameterName = "@AlreadyMatriculateFee";
sqlPrm[9].SqlDbType = SqlDbType.Money;
sqlPrm[9].Direction = ParameterDirection.Output;
DataOperate dp3 = new DataOperate("CnStr");
dp3.NewStoredProcedure("sp_StudChargeStatus",sqlPrm).ExecuteNonQuery();
this._Schooling = Decimal.Parse(sqlPrm[2].Value.ToString());
this._BookFee = Decimal.Parse(sqlPrm[3].Value.ToString());
this._TrainFee = Decimal.Parse(sqlPrm[4].Value.ToString());
this._MatriculateFee = Decimal.Parse(sqlPrm[5].Value.ToString());
this._AlreadySchooling = Decimal.Parse(sqlPrm[6].Value.ToString());
this._AlreadyBookFee = Decimal.Parse(sqlPrm[7].Value.ToString());
this._AlreadyTrainFee = Decimal.Parse(sqlPrm[8].Value.ToString());
this._AlreadyMatriculateFee = Decimal.Parse(sqlPrm[9].Value.ToString());
this._OweSchooling = this._Schooling - this._AlreadySchooling;
this._OweBookFee = this._BookFee - this._AlreadyBookFee;
this._OweTrainFee = this._TrainFee - this._AlreadyTrainFee;
this._OweMatriculateFee = this._MatriculateFee - this._AlreadyMatriculateFee;
if( this._OweSchooling > 0 || this._OweBookFee > 0 || this._OweTrainFee > 0 || this._OweMatriculateFee > 0)
{
this._OweStatus = true;
}
dp3.ColseLink();
}
#endregion
接下来是DataOperate(实例dp)中的NewStoredProcedure方法,你参考:
#region 存储过程
/// <summary>
/// 存储过程
/// </summary>
/// <param name="spname">存储过程名</param>
/// <param name="sqlParams">参数数组</param>
/// <returns></returns>
public SqlCommand NewStoredProcedure(string spname,params SqlParameter[] sqlParams)
{
SqlCommand cm = new SqlCommand(spname,Conn);
cm.CommandType = CommandType.StoredProcedure;
if(sqlParams!=null&&sqlParams.Length>0)
{
foreach(SqlParameter sqlPrm in sqlParams)
{
cm.Parameters.Add(sqlPrm);
}
}
Conn.Open();
return cm;
}
#endregion
2,存储过程里除了使用select 外同时使用 return
string _string = SqlCommand.ExecuteScalar() //执行SQL语句或者存储过程,返回结果集的第一行第一列。
(@stu_id char(10)='Q0152100',@tea_id char(10)='t03',@returnmessage nvarchar(20) output
)
as
if @tea_id in (
select b.tea_id
from stu_info a inner join mancou_info b
on a.stu_class=b.stu_class
where a.stu_id=@stu_id)begin
set @returnmessage='aaaa'
end
else
begin
set @returnmessage='bbbb'
end我的程序:
string strConnection="server=(local);database=temp;uid=sa;password=;";
SqlConnection objConnection = new SqlConnection(strConnection);
SqlCommand objCommand = new SqlCommand("tea_jiaoshi_limit_onstu_id",objConnection);
objCommand.CommandType=CommandType.StoredProcedure;
//参数对象
SqlParameter objParameter=new SqlParameter("@returnmessage",SqlDbType.NVarChar,20);
objCommand.Parameters.Add(objParameter);
objParameter.Direction=ParameterDirection.ReturnValue;
Response.Write(objParameter.Value);为什么写不出objParameter.Value
高手帮忙
构建SqlCommand的参数的时候,用如下的方法
command.Parameters.Add(new SqlParameter ("ReturnValue",SqlDbType.Int,4, ParameterDirection.ReturnValue,false, 0, 0, string.Empty,DataRowVersion.Default,null ));
关于这个SqlParameter类的构造函数的用法,参见MSDN
ms-help://MS.MSDNQTR.2003FEB.2052/cpref/html/frlrfsystemdatasqlclientsqlparameterclassctortopic6.htm运行这个存储过程后,可以通过如下方法取得返回值int result;
result = (int)command.Parameters["ReturnValue"].Value;