/// <summary>
/// 执行存储过程,返回bool值,默认存储过程全部返回1或者0
/// </summary>
/// <param name="pName"></param>
/// <param name="opc"></param>
/// <param name="outPutParameterName"></param>
/// <returns></returns>
public bool ExecuteProcedure(string pName,ArrayList al,string outPutParameterName)
{
OracleConnection olc = new OracleConnection(this.conStr);
OracleCommand oleCommand = new OracleCommand(pName,olc);
oleCommand.CommandType = CommandType.StoredProcedure;
foreach(OracleParameter parameter in al)
{
oleCommand.Parameters.Add(parameter);
}
try
{
olc.Open();
oleCommand.ExecuteNonQuery();
}
catch(Exception ex)
{
throw ex;
}
finally
{
olc.Close();
}
bool result = true;
if(oleCommand.Parameters[outPutParameterName].Value.ToString() == "0")
{
result = false;
}
olc.Dispose();
oleCommand.Dispose();
return result;
} /// <summary>
/// 检测用户
/// </summary>
/// <returns></returns>
public bool checkUser()
{
ArrayList al = new ArrayList();
OracleParameter parameter = new OracleParameter("UserResult",OracleType.Number);
parameter.Direction = ParameterDirection.Output;
al.Add(parameter);
parameter = new OracleParameter("UserId",this.userId);
al.Add(parameter);
parameter = new OracleParameter("UserPwd",this.userPwd);
al.Add(parameter);
DBAccess db = new DBAccess();
bool result = true;
try
{
result = db.ExecuteProcedure("JASON_PKG_MESSAGEBOOK.JASON_LOGIN",al,"UserResult");
}
catch(Exception ex)
{
throw ex;
}
return result;
}
/// 执行存储过程,返回bool值,默认存储过程全部返回1或者0
/// </summary>
/// <param name="pName"></param>
/// <param name="opc"></param>
/// <param name="outPutParameterName"></param>
/// <returns></returns>
public bool ExecuteProcedure(string pName,ArrayList al,string outPutParameterName)
{
OracleConnection olc = new OracleConnection(this.conStr);
OracleCommand oleCommand = new OracleCommand(pName,olc);
oleCommand.CommandType = CommandType.StoredProcedure;
foreach(OracleParameter parameter in al)
{
oleCommand.Parameters.Add(parameter);
}
try
{
olc.Open();
oleCommand.ExecuteNonQuery();
}
catch(Exception ex)
{
throw ex;
}
finally
{
olc.Close();
}
bool result = true;
if(oleCommand.Parameters[outPutParameterName].Value.ToString() == "0")
{
result = false;
}
olc.Dispose();
oleCommand.Dispose();
return result;
} /// <summary>
/// 检测用户
/// </summary>
/// <returns></returns>
public bool checkUser()
{
ArrayList al = new ArrayList();
OracleParameter parameter = new OracleParameter("UserResult",OracleType.Number);
parameter.Direction = ParameterDirection.Output;
al.Add(parameter);
parameter = new OracleParameter("UserId",this.userId);
al.Add(parameter);
parameter = new OracleParameter("UserPwd",this.userPwd);
al.Add(parameter);
DBAccess db = new DBAccess();
bool result = true;
try
{
result = db.ExecuteProcedure("JASON_PKG_MESSAGEBOOK.JASON_LOGIN",al,"UserResult");
}
catch(Exception ex)
{
throw ex;
}
return result;
}
begin
select count(*) into UserResult from jason_user
where user_id = UserId and user_pwd = UserPwd;
end JASON_LOGIN;
学习
Dim sampleCMD As SqlCommand = New SqlCommand("SampleProc", nwindConn)
sampleCMD.CommandType = CommandType.StoredProcedureDim sampParm As SqlParameter = sampleCMD.Parameters.Add("RETURN_VALUE", SqlDbType.Int)
sampParm.Direction = ParameterDirection.ReturnValuesampParm = sampleCMD.Parameters.Add("@InputParm", SqlDbType.NVarChar, 12)
sampParm.Value = "Sample Value"sampParm = sampleCMD.Parameters.Add("@OutputParm", SqlDbType.NVarChar, 28)
sampParm.Direction = ParameterDirection.OutputnwindConn.Open()Dim sampReader As SqlDataReader = sampleCMD.ExecuteReader()Console.WriteLine("{0}, {1}", sampReader.GetName(0), sampReader.GetName(1))Do While sampReader.Read()
Console.WriteLine("{0}, {1}", sampReader.GetInt32(0), sampReader.GetString(1))
LoopsampReader.Close()
nwindConn.Close()
CREATE PROCEDURE [dbo].[dz_test]
@x int,
@y int,
@z int
AS
select (@x + @y + @z)
--返回值
GO程序中:
string strConn = "" ;//自己的数据库连接字符串
OleDbConnection sqlConn = new OleDbConnection(strConn) ;
sqlConn.Open();
string sql = "dz_test 1,2,3";
OleDbCommand cmd = new OleDbCommand(sql, sqlConn);
OleDbDataReader dr = cmd.ExecuteReader();
if (dr.Read())
int intSum = dr.GetInt32(0);
dr.Close();
sqlConn.Close();试试吧。^_^
@x int,
@y varchar(10),
@z datetime
as
select sum(total) from xx where (price > @x) and (buyDate =@z) and (name = @y)
go
-----------------------------
string strConn = "xxxxx" ;
SqlConnection sqlConn = new SqlConnection(strConn) ;
sqlConn.Open();
SqlCommand cmd = new SqlCommand("sp_test", sqlConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Paramaters.Add("@x",int);
cmd.Paramaters.Add("@y",varchar,10);
cmd.Paramaters.Add("@z",DateTime);
cmd.Paramaters["@x"].Value = xx;
cmd.Paramaters["@y"].Value = yy;
cmd.Paramaters["@z"].Value = zz;decimal sumMoney = decimal.Pares(cmd.ExecuteScalar().ToString());sqlConn.Close();