using System;
using System.Data;
using System.Data.OracleClient;
namespace SUEM.WebModule.Data
{
public class OracleDB: IDisposable
{
private OracleConnection con=null;
private OracleTransaction trans=null;
public OracleDB() { }
private OracleCommand CreateCommand(string procName,OracleParameter[] paras)
{
if(con==null)
{
Open();
}
OracleCommand cmd=new OracleCommand(procName,con);
cmd.Transaction=trans;
cmd.CommandType=CommandType.StoredProcedure;
if(paras!=null)
{
foreach(OracleParameter parameter in paras) cmd.Parameters.Add(parameter);
}
cmd.Parameters.Add(new OracleParameter("ReturnValue",OracleType.Int32,4,ParameterDirection.ReturnValue,false,0,0,string.Empty,DataRowVersion.Default,null));
return cmd;
}
private OracleCommand CreateOracleCommand(string Sql,string[] paras)
{
if(con==null)
{
Open();
}
int index,position;
index=-1; position=0;
String newsql=Sql;
index=Sql.IndexOf(":?");
while(index!=1 && position<=paras.Length)
{
string temp=newsql;
newsql=newsql.Substring(0,index);
string para=paras[position].ToString();
newsql=newsql+para+temp.Substring(index+2);
index=newsql.IndexOf(":?");
position++;
}
OracleCommand cmd=new OracleCommand(newsql,con);
cmd.Transaction=trans;
cmd.CommandType=CommandType.Text;
return cmd;
}
private OracleDataAdapter CreateOracleDataAdapter(string Sql,string[] paras)
{
if(con==null)
{
Open();
}
int index,position;
index=-1; position=0;
string newsql=Sql;
index=Sql.IndexOf(":?");
while(index!=-1 && position<=paras.Length)
{
string temp=newsql;
newsql=newsql.Substring(0,index);
string para=paras[position].ToString();
newsql=newsql+para+temp.Substring(index+2);
index=newsql.IndexOf(":?");
position++;
}
OracleDataAdapter cmd=new OracleDataAdapter(newsql,con);
cmd.SelectCommand.CommandType=CommandType.Text;
return cmd;
}
private OracleDataAdapter CreateDataAdapter(string procName,OracleParameter[] paras)
{
if(con==null)
{
Open();
}
OracleDataAdapter cmd=new OracleDataAdapter(procName,con);
cmd.SelectCommand.CommandType=CommandType.StoredProcedure;
if(paras!=null)
{
foreach(OracleParameter parameter in paras) cmd.SelectCommand.Parameters.Add(parameter);
}
cmd.SelectCommand.Parameters.Add(new OracleParameter("ReturnValue",OracleType.Int32,4,ParameterDirection.ReturnValue,false,0,0,string.Empty,DataRowVersion.Default,null));
return cmd;
}
public int RunSql(string Sql)
{
OracleCommand cmd=CreateOracleCommand(Sql,null);
try
{
cmd.ExecuteNonQuery();
return 1;
}
catch
{
return 0;
}
finally
{
Close();
}
}
public int RunSql(string Sql,string[] paras)
{
OracleCommand cmd=CreateOracleCommand(Sql,paras);
try
{
cmd.ExecuteNonQuery();
return 1;
}
catch
{
return 0;
}
finally
{
Close();
}
}
public void RunSql(string Sql,out OracleDataReader dataReader)
{
OracleCommand cmd=CreateOracleCommand(Sql,null);
OracleDataReader dr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
dataReader=dr;
}
public void RunSql(string Sql,String[] paras,out OracleDataReader dataReader)
{
OracleCommand cmd=CreateOracleCommand(Sql,paras);
dataReader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
public void RunSql(string Sql,string tableName,out DataSet dsReturn)
{
DataSet ds=new DataSet();
OracleDataAdapter cmd=CreateOracleDataAdapter(Sql,null);
if(tableName!=null && tableName.Trim()!="")
{
cmd.Fill(ds,tableName);
}
else
{
cmd.Fill(ds);
}
dsReturn=ds;
Close();
}
public void RunSql(string Sql,string[] paras,string tableName,out DataSet dsReturn)
{
DataSet ds=new DataSet();
OracleDataAdapter cmd=CreateOracleDataAdapter(Sql,paras);
if(tableName!=null && tableName.Trim()!="")
{
cmd.Fill(ds,tableName);
}
else
{
cmd.Fill(ds);
}
dsReturn=ds;
Close();
}
public int RunXml(string KeyValue)
{
string sql=System.Configuration.ConfigurationSettings.AppSettings[KeyValue];
return RunSql(sql);
}
public int RunXml(string KeyValue,String[] paras)
{
string sql=System.Configuration.ConfigurationSettings.AppSettings[KeyValue];
return RunSql(sql,paras);
}
public void RunXml(string KeyValue,out OracleDataReader dataReader)
{
string sql=System.Configuration.ConfigurationSettings.AppSettings[KeyValue];
OracleDataReader dr;
RunSql(sql,out dr);
dataReader=dr;
}
public void RunXml(string KeyValue,string[] paras,out OracleDataReader dataReader)
{
string sql=System.Configuration.ConfigurationSettings.AppSettings[KeyValue];
OracleDataReader dr;
RunSql(sql,paras,out dr);
dataReader=dr;
}
public void RunXml(string KeyValue,string[] paras,string tableName,out DataSet dsReturn)
{
string sql=System.Configuration.ConfigurationSettings.AppSettings[KeyValue];
DataSet ds=new DataSet();
RunSql(sql,paras,tableName,out ds);
dsReturn=ds;
}
public int RunProc(string procName)
{
OracleCommand cmd=CreateCommand(procName,null);
cmd.ExecuteNonQuery();
Close();
return (int)cmd.Parameters["ReturnValue"].Value;
}
public int RunProc(string procName,OracleParameter[] paras)
{
OracleCommand cmd=CreateCommand(procName,paras);
cmd.ExecuteNonQuery();
int i=(int)cmd.Parameters["ReturnValue"].Value;
cmd.Parameters.Clear();
Close();
return i;
}
public int RunProcTrans(string procName,OracleParameter[] paras)
{
OracleCommand cmd=CreateCommand(procName,paras);
cmd.ExecuteNonQuery();
int i=(int)cmd.Parameters["ReturnValue"].Value;
cmd.Parameters.Clear();
return i;
}
public void RunProc(string procName,out OracleDataReader dataReader)
{
OracleCommand cmd=CreateCommand(procName,null);
dataReader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
}
public void RunProc(string procName,OracleParameter[] paras,out OracleDataReader dataReader)
{
OracleCommand cmd=CreateCommand(procName,paras);
dataReader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
}
public int RunProc(string procName,OracleParameter[] paras,string tableName,out DataSet dsReturn)
{
DataSet ds=new DataSet();
OracleDataAdapter cmd=CreateDataAdapter(procName,paras);
if(tableName!=null && tableName.Trim()!="")
{
cmd.Fill(ds,tableName);
}
else
{
cmd.Fill(ds);
}
dsReturn=ds;
int Ret=(int)cmd.SelectCommand.Parameters["ReturnValue"].Value;
cmd.SelectCommand.Parameters.Clear();
Close();
return Ret;
}
public OracleConnection GetConnection()
{
return con;
}
public OracleParameter MakeParam(string paramName,OracleType dbType,Int32 size,ParameterDirection direction,object val)
{
OracleParameter param;
if(size>0)
{
param=new OracleParameter(paramName,dbType,size);
}
else
{
param=new OracleParameter(paramName,dbType);
}
param.Direction=direction;
if(!(direction==ParameterDirection.Output && val==null))
{
param.Value=val;
}
return param;
}
public OracleParameter MakeInParam(string paramName,OracleType dbType,int size,object val)
{
return MakeParam(paramName,dbType,size,ParameterDirection.Input,val);
}
public OracleParameter MakeOutParam(string paramName,OracleType dbType,int size)
{
return MakeParam(paramName,dbType,size,ParameterDirection.Output,null);
}
public void Open()
{
if(con==null)
{
con=new OracleConnection(System.Configuration.ConfigurationSettings.AppSettings["ConStr"]);
con.Open();
}
}
public void Open(string ConStr)
{
if(con==null)
{
con=new OracleConnection(ConStr);
con.Open();
}
}
public void Close()
{
if(con!=null)
{
con.Close();
con=null;
}
}
public void Dispose()
{
if(con!=null)
{
con.Dispose();
con=null;
}
}
public void BeginTrans()
{
if(con==null)
{
Open();
}
trans=con.BeginTransaction();
}
public void CommitTrans()
{
if(trans!=null)
{
trans.Commit();
con.Close();
}
}
public void RollBack()
{
if(trans!=null)
{
trans.Rollback();
con.Close();
}
}
}
}
