using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;
using System.Configuration;
using System.Data.Common;
namespace etong.DAL
{
public interface IOracleDb
{
//string GetDbType();
/// <summary>
/// 开始 Transaction
/// </summary>
void BeginTransaction();
/// <summary>
/// 回滚
/// </summary>
void RollbackTransaction();
/// <summary>
/// 提交 Transaction
/// </summary>
void CommitTransaction();
/// <summary>
/// 结束 Transaction
/// </summary>
void EndTransaction();
/// <summary>
/// 打开连接
/// </summary>
void CreateConnection();
/// <summary>
/// 对连接执行 Transact-SQL 语句并返回受影响的行数。
/// </summary>
/// <param name="psSQL">Insert,Update,Delete等语句</param>
/// <returns>对连接执行 Transact-SQL 语句并返回受影响的行数。</returns>
int ExeNonQuery(string psSQL);
/// <summary>
/// 获得DataSet
/// </summary>
/// <param name="psSQL"></param>
/// <returns></returns>
DataSet GetDataSet(string psSQL);
/// <summary>
/// 返回 DataTable 对象
/// </summary>
/// <param name="psSQL"></param>
/// <returns></returns>
DataTable GetDataTable(string psSQL);
/// <summary>
/// 从数据库中读取信息
/// </summary>
/// <param name="psDataFielde">字段名称数组</param>
/// <param name="psSQL">查询语句</param>
/// <returns>对应字段的值数组</returns>
string[] ReadData(string[] psDataFielde, string psSQL);
/// <summary>
/// 主要用于Min,Max,Count,Sum,Avg等函数,返回数值,错误返回-1
/// </summary>
/// <param name="psSQL">查询语句</param>
/// <returns>数值</returns>
int ExeScalar(string psSQL);
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="psProcedureName">存储过程名</param>
/// <param name="psParameters">参数列表</param>
int ExecuteNonQuery(string psProcedureName, OracleParameter[] psParameters);
/// <summary>
/// 执行存储过程并返回数据集
/// </summary>
/// <param name="psProcedureName">存储过程名</param>
/// <param name="psParameters">参数列表</param>
/// <returns></returns>
DataSet GetProcDataSet(string psProcedureName, OracleParameter[] psParameters);
/// <summary>
/// 关闭连接
/// </summary>
void CloseConnection();
/// <summary>
/// 获取最大字段值
/// </summary>
/// <param name="FieldName">字段名</param>
/// <param name="TableName">表名</param>
/// <returns></returns>
int GetMaxID(string FieldName, string TableName);
/// <summary>
/// 执行存储过程返回int字符
/// </summary>
/// <param name="psProcedureName">存储过程名</param>
/// <param name="psParameters">参数列表</param>
/// <returns></returns>
string GetProcInt(string psProcedureName, OracleParameter[] psParameters);
}
public class ORACLEDB : IOracleDb
{
#region property
private OracleTransaction m_Transaction;
private OracleCommand m_Command;
public OracleCommand DBCommand
{
get { return m_Command; }
}
private OracleConnection m_Connection;
public OracleConnection DBConnection
{
get { return m_Connection; }
}
private string m_ConnectString;
public string DBConnectString
{
get { return m_ConnectString; }
}
public Boolean IsInTransaction
{
get
{
if (m_Transaction == null)
{
return false;
}
else
{
return true;
}
}
}
#endregion
public ORACLEDB()
{
}
#region 公用方法
public int GetMaxID(string FieldName, string TableName)
{
string strsql = "select max(" + FieldName + ")+1 from " + TableName;
object obj = GetSingle(strsql);
if (obj == null)
{
return 1;
}
else
{
return int.Parse(obj.ToString());
}
}
#endregion
#region Public method
//public string GetDbType()
//{
// return HprGlobal.DB_ORACLE;
//}
public void BeginTransaction()
{
if (!this.IsInTransaction)
{
// 开始一个新的Transaction
m_Transaction = this.DBConnection.BeginTransaction();
}
else
{
// 已经在Transaction中, 不需要做任何动作。
}
m_Command.Transaction = m_Transaction;//2008-8-4
}
public void RollbackTransaction()
{
if (this.IsInTransaction)
{
m_Transaction.Rollback();
}
else
{
}
}
public void CommitTransaction()
{
if (this.IsInTransaction)
{
m_Transaction.Commit();
}
else
{
}
}
public void EndTransaction()
{
if (this.IsInTransaction)
{
m_Transaction = null;
}
else
{
}
}
private void Init()
{
//1. 判断数据库类型, 然后指定使用何种Connection
m_Connection = new OracleConnection();
m_Connection.ConnectionString = m_ConnectString;
//2. 建立Command 对象
m_Command = m_Connection.CreateCommand();
}
public ORACLEDB(string psDBConnectString)
{
if (string.IsNullOrEmpty(psDBConnectString))
{
m_ConnectString = System.Configuration.ConfigurationManager.ConnectionStrings["OracleConnString"].ConnectionString;
}
else
{
m_ConnectString = psDBConnectString;
}
Init();
}
/// <summary>
/// 执行一条计算查询结果语句,返回查询结果(object)。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果(object)</returns>
public object GetSingle(string SQLString)
{
m_ConnectString = System.Configuration.ConfigurationManager.ConnectionStrings["OracleConnString"].ConnectionString;
using (OracleConnection connection = new OracleConnection(m_ConnectString))
{
using (OracleCommand cmd = new OracleCommand(SQLString, connection))
{
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.OracleClient.OracleException e)
{
connection.Close();
throw new Exception(e.Message);
}
}
}
}
public void CreateConnection()
{
if (this.m_Connection.State != ConnectionState.Open)
this.m_Connection.Open();
}
public string[] ReadData(string[] psDataField, string psSQL)
{
string[] sValue = new string[psDataField.Length];
try
{
CreateConnection();
m_Command.CommandText = psSQL;
OracleDataReader objDataReader = m_Command.ExecuteReader();
while (objDataReader.Read())
{
for (int i = 0; i < psDataField.Length; i++)
sValue[i] = objDataReader[psDataField[i]].ToString();
}
objDataReader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (!this.IsInTransaction) CloseConnection();
}
return sValue;
}
public int ExeScalar(string psSQL)
{
CreateConnection();
int iValue = -1;
try
{
m_Command.CommandText = psSQL;
m_Command.CommandType = CommandType.Text;
int iNum = Convert.ToInt32(m_Command.ExecuteScalar());
iValue = iNum;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (!this.IsInTransaction) CloseConnection();
}
return iValue;
}
public int ExeNonQuery(string psSQL)
{
int iRowCount = 0;
try
{
this.CreateConnection();
m_Command.CommandText = psSQL;
if (this.IsInTransaction)
{
m_Command.Transaction = m_Transaction;
iRowCount = m_Command.ExecuteNonQuery();
}
else
{
iRowCount = m_Command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
iRowCount = -1;
throw ex;
}
finally
{
m_Command = new OracleCommand();
m_Command = m_Connection.CreateCommand();
if (!this.IsInTransaction) CloseConnection();
}
return iRowCount;
}
public DataSet GetDataSet(string psSQL)
{
DataSet objDataSet = new DataSet();
try
{
CreateConnection();
m_Command.CommandText = psSQL;
m_Command.CommandType = CommandType.Text;
OracleDataAdapter objDataAdapter = new OracleDataAdapter(m_Command);
objDataAdapter.Fill(objDataSet);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (!this.IsInTransaction) CloseConnection();
}
return objDataSet;
}
public DataTable GetDataTable(string psSQL)
{
DataSet objDataSet = GetDataSet(psSQL);
return objDataSet.Tables[0];
}
public int ExecuteNonQuery(string psProcedureName, OracleParameter[] psParameters)
{
int result = -1;
try
{
this.CreateConnection();
m_Command.CommandText = psProcedureName;//声明存储过程名
m_Command.CommandType = CommandType.StoredProcedure;
foreach (OracleParameter objParam in psParameters)
{
m_Command.Parameters.Add(objParam);
}
if (this.IsInTransaction)
{
m_Command.Transaction = this.m_Transaction;
result = m_Command.ExecuteNonQuery();//执行存储过程
}
else
{
result = m_Command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
result = -1;
throw ex;
}
finally
{
m_Command = new OracleCommand();
m_Command = m_Connection.CreateCommand();
if (!this.IsInTransaction) CloseConnection();
}
return result;
}
public DataSet GetProcDataSet(string psProcedureName, OracleParameter[] psParameters)
{
DataSet objDataSet = new DataSet();
try
{
int paramLength = psParameters.Length;
OracleParameter _oParamCursor = new OracleParameter();
_oParamCursor.ParameterName = "v_cursor";
_oParamCursor.OracleType = OracleType.Cursor;
_oParamCursor.Direction = ParameterDirection.Output;
this.CreateConnection();
m_Command.CommandText = psProcedureName;//声明存储过程名
m_Command.CommandType = CommandType.StoredProcedure;
foreach (OracleParameter objParam in psParameters)
{
m_Command.Parameters.Add(objParam);
}
m_Command.Parameters.Add(_oParamCursor);
OracleDataAdapter objDataAdapter = new OracleDataAdapter(m_Command);
objDataAdapter.Fill(objDataSet);
}
catch (Exception ex)
{
throw ex;
}
finally
{
m_Command = new OracleCommand();
m_Command = m_Connection.CreateCommand();
if (!this.IsInTransaction) CloseConnection();
}
return objDataSet;
}
public DataSet GetProcDataSetTwo(string psProcedureName, OracleParameter[] psParameters)
{
DataSet objDataSet = new DataSet();
try
{
int paramLength = psParameters.Length;
OracleParameter _oParamCursor = new OracleParameter();
_oParamCursor.ParameterName = "v_cursor";
_oParamCursor.OracleType = OracleType.Cursor;
_oParamCursor.Direction = ParameterDirection.Output;
OracleParameter _oParamCursorAll = new OracleParameter();
_oParamCursorAll.ParameterName = "v_cursorall";
_oParamCursorAll.OracleType = OracleType.Cursor;
_oParamCursorAll.Direction = ParameterDirection.Output;
this.CreateConnection();
m_Command.CommandText = psProcedureName;//声明存储过程名
m_Command.CommandType = CommandType.StoredProcedure;
foreach (OracleParameter objParam in psParameters)
{
m_Command.Parameters.Add(objParam);
}
m_Command.Parameters.Add(_oParamCursor);
m_Command.Parameters.Add(_oParamCursorAll);
OracleDataAdapter objDataAdapter = new OracleDataAdapter(m_Command);
objDataAdapter.Fill(objDataSet);
}
catch (Exception ex)
{
throw ex;
}
finally
{
m_Command = new OracleCommand();
m_Command = m_Connection.CreateCommand();
if (!this.IsInTransaction) CloseConnection();
}
return objDataSet;
}
public string GetProcInt(string psProcedureName, OracleParameter[] psParameters)
{
string objResult = "";
try
{
int paramLength = psParameters.Length;
OracleParameter _oParamCursor = new OracleParameter();
_oParamCursor.ParameterName = "v_number";
_oParamCursor.OracleType = OracleType.NVarChar;
_oParamCursor.Size = 100;
_oParamCursor.Direction = ParameterDirection.Output;
this.CreateConnection();
m_Command.CommandText = psProcedureName;//声明存储过程名
m_Command.CommandType = CommandType.StoredProcedure;
foreach (OracleParameter objParam in psParameters)
{
m_Command.Parameters.Add(objParam);
}
m_Command.Parameters.Add(_oParamCursor);
m_Command.ExecuteNonQuery();
objResult = _oParamCursor.Value.ToString();
}
catch (Exception ex)
{
throw ex;
}
finally
{
m_Command = new OracleCommand();
m_Command = m_Connection.CreateCommand();
if (!this.IsInTransaction) CloseConnection();
}
return objResult;
}
public OracleParameter MakeInParam(string ParamName, OracleType DbType, int Size, object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
}
public OracleParameter MakeOutParam(string ParamName, OracleType DbType, int Size)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
}
public OracleParameter MakeParam(string ParamName, OracleType DbType, int Size, ParameterDirection Direction, object Value)
{
OracleParameter parameter = new OracleParameter(ParamName, Value);
parameter.Direction = Direction;
if ((Direction != ParameterDirection.Output) || (Value != null))
{
parameter.Value = Value;
}
return parameter;
}
public void CloseConnection()
{
if (this.IsInTransaction)
{
this.m_Transaction = null;
}
if (this.m_Connection.State == ConnectionState.Open)
{
this.m_Connection.Close();
}
}
#endregion
}
}