Oracle的一个处理类
using System;
using System.Data;
using System.Data.OracleClient;
namespace WebQuery1
{
/// <summary>
/// ManageData 的摘要说明。
/// </summary>
public class ManageData
{
private OracleConnection m_conn = null;
private string m_ConnStr = null; //连接字符串
private string m_DataSource = null; //数据源
private string m_uid = null; //用户名
private string m_Password = null; //密码
private string m_error = ""; //错误返回
public ManageData (string DataSource, string uid, string Password)
{
m_DataSource = DataSource;
m_uid = uid;
m_Password = Password;
m_ConnStr = "user id = " + uid + ";data source=" + DataSource + ";password = " + Password + ";";
m_conn = new OracleConnection(m_ConnStr);
}
public ManageData(string ConnStr)
{
m_ConnStr = ConnStr;
m_conn = new OracleConnection (ConnStr);
}
public ManageData()
{
m_conn = new OracleConnection ();
}
#region Public Properties
/// <summary>
/// 连接字符串
/// </summary>
public string ConnectionString
{
get
{
return m_ConnStr;
}
set
{
m_ConnStr = value;
m_conn.ConnectionString = m_ConnStr;
}
}
/// <summary>
/// 用户名
/// </summary>
public string UID
{
get
{
return m_uid;
}
set
{
m_uid = value;
m_ConnStr = "user id = " + m_uid + ";data source=" + m_DataSource + ";password = " + m_Password + ";";
m_conn.ConnectionString = m_ConnStr;
}
}
/// <summary>
/// 密码
/// </summary>
public string Password
{
get
{
return m_Password;
}
set
{
m_Password = value;
m_ConnStr = "user id = " + m_uid + ";data source=" + m_DataSource + ";password = " + m_Password + ";";
m_conn.ConnectionString = m_ConnStr;
}
}
/// <summary>
/// 数据源
/// </summary>
public string DataSource
{
get
{
return m_DataSource;
}
set
{
m_DataSource = value;
m_ConnStr = "user id = " + m_uid + ";data source=" + m_DataSource + ";password = " + m_Password + ";";
m_conn.ConnectionString = m_ConnStr;
}
}
/// <summary>
/// 数据源
/// </summary>
public string ErrorInfo
{
get
{
return m_error;
}
set
{
m_error = value;
}
}
#endregion
#region Public Methods
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close ()
{
if (m_conn != null && (m_conn.State == ConnectionState.Open ))
{
m_conn.Close(); // 关闭数据库
}
}
public OracleDataReader ExecReader (string sText)
{
OracleDataReader dr = null;
m_error = "";
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
OracleCommand cmd = new OracleCommand (sText, m_conn);
cmd.CommandType = CommandType.Text;
dr = cmd.ExecuteReader ();
}
catch (Exception ex)
{
m_error =ex.Message;
}
return dr;
}
public OracleCommand ExecOracleCommand(string sText)
{
if(m_conn.State==ConnectionState.Closed)
m_conn.Open();
OracleCommand sqlcom=new OracleCommand(sText,m_conn);
return sqlcom;
}
/// <summary>
/// 执行查询,并返回查询所返回的结果集中的第一行的第一列.忽略额外的行或列.
/// </summary>
/// <param name="sText">查询语句</param>
/// <returns>返回查询所返回的结果集中的第一行的第一列</returns>
public object ExecuteScalar(string sText)
{
object obj = null;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open();
}
OracleCommand cmd = new OracleCommand(sText,m_conn);
cmd.CommandType = CommandType.Text;
obj = cmd.ExecuteScalar();
}
catch (Exception ex)
{
string s = ex.Message;
}
finally
{
m_conn.Close ();
}
return obj;
}
/// <summary>
/// 通过SQL查询得到数据集
/// </summary>
/// <param name="sText">sql查询字符串</param>
/// <returns></returns>
public DataSet ExecDS (string sText)
{
m_error = "";
DataSet ds = null;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
OracleDataAdapter da = new OracleDataAdapter (sText, m_conn);
ds = new DataSet ();
da.Fill (ds);
}
catch (Exception ex)
{
m_error =ex.Message;
}
finally
{
m_conn.Close ();
}
return ds;
}
/// <summary>
/// 通过SQL查询得到数据适配器
/// </summary>
/// <param name="sText"></param>
/// <returns></returns>
public OracleDataAdapter ExecDA(string sText)
{
OracleDataAdapter da = null;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
da = new OracleDataAdapter (sText, m_conn);
}
catch (Exception ex)
{
string s = ex.Message;
}
finally
{
m_conn.Close ();
}
return da;
}
/// <summary>
/// 通过SQL查询得到数据表
/// </summary>
/// <param name="sText"></param>
/// <returns></returns>
public DataTable ExecDT (string sText)
{
DataTable dt = null;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
OracleDataAdapter da = new OracleDataAdapter (sText, m_conn);
dt = new DataTable ();
da.Fill (dt);
}
catch (Exception ex)
{
string s = ex.Message;
throw (new Exception(s));
}
finally
{
m_conn.Close ();
}
return dt;
}
public int ExecNonQuery (string sText)
{
int iRowsAffected = -1;
m_error = "";
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
OracleCommand cmd = new OracleCommand (sText, m_conn);
cmd.CommandType = CommandType.Text;
iRowsAffected = cmd.ExecuteNonQuery ();
}
catch (Exception ex)
{
m_error=ex.Message;
if(ex!=null)
{
return 0;
}
}
finally
{
m_conn.Close ();
}
return iRowsAffected;
}
public bool ExecSqlQuery (string sql)
{
int iRowsAffected =-1;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
OracleCommand cmd = new OracleCommand (sql, m_conn);
cmd.CommandType = CommandType.Text;
iRowsAffected = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
string s = ex.Message;
if(ex!=null)
{
return false;
}
}
finally
{
m_conn.Close ();
}
if(iRowsAffected!=-1)
{
return true;
}
else
{
return false;
}
}
public bool ExecQuery(string sText)
{
OracleDataReader dr;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
OracleCommand cmd = new OracleCommand (sText, m_conn);
cmd.CommandType = CommandType.Text;
dr = cmd.ExecuteReader();
if (dr.HasRows==true)
return true;
else
return false;
}
catch (Exception ex)
{
string s = ex.Message;
if(ex!=null)
{
return false;
}
}
finally
{
m_conn.Close ();
}
return true;
}
public void ExecTran (string sText)
{
OracleTransaction trans = null;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
OracleCommand cmd = new OracleCommand (sText, m_conn);
cmd.CommandType = CommandType.Text;
trans = m_conn.BeginTransaction ();
cmd.Transaction = trans;//++
cmd.ExecuteNonQuery ();
trans.Commit();//++
//trans.Rollback ();
}
catch (Exception ex)
{
trans.Rollback ();
throw (new Exception (ex.Message));
}
finally
{
m_conn.Close ();
}
}
#endregion
}
}
using System;
using System.Data;
using System.Data.OracleClient;
namespace WebQuery1
{
/// <summary>
/// ManageData 的摘要说明。
/// </summary>
public class ManageData
{
private OracleConnection m_conn = null;
private string m_ConnStr = null; //连接字符串
private string m_DataSource = null; //数据源
private string m_uid = null; //用户名
private string m_Password = null; //密码
private string m_error = ""; //错误返回
public ManageData (string DataSource, string uid, string Password)
{
m_DataSource = DataSource;
m_uid = uid;
m_Password = Password;
m_ConnStr = "user id = " + uid + ";data source=" + DataSource + ";password = " + Password + ";";
m_conn = new OracleConnection(m_ConnStr);
}
public ManageData(string ConnStr)
{
m_ConnStr = ConnStr;
m_conn = new OracleConnection (ConnStr);
}
public ManageData()
{
m_conn = new OracleConnection ();
}
#region Public Properties
/// <summary>
/// 连接字符串
/// </summary>
public string ConnectionString
{
get
{
return m_ConnStr;
}
set
{
m_ConnStr = value;
m_conn.ConnectionString = m_ConnStr;
}
}
/// <summary>
/// 用户名
/// </summary>
public string UID
{
get
{
return m_uid;
}
set
{
m_uid = value;
m_ConnStr = "user id = " + m_uid + ";data source=" + m_DataSource + ";password = " + m_Password + ";";
m_conn.ConnectionString = m_ConnStr;
}
}
/// <summary>
/// 密码
/// </summary>
public string Password
{
get
{
return m_Password;
}
set
{
m_Password = value;
m_ConnStr = "user id = " + m_uid + ";data source=" + m_DataSource + ";password = " + m_Password + ";";
m_conn.ConnectionString = m_ConnStr;
}
}
/// <summary>
/// 数据源
/// </summary>
public string DataSource
{
get
{
return m_DataSource;
}
set
{
m_DataSource = value;
m_ConnStr = "user id = " + m_uid + ";data source=" + m_DataSource + ";password = " + m_Password + ";";
m_conn.ConnectionString = m_ConnStr;
}
}
/// <summary>
/// 数据源
/// </summary>
public string ErrorInfo
{
get
{
return m_error;
}
set
{
m_error = value;
}
}
#endregion
#region Public Methods
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close ()
{
if (m_conn != null && (m_conn.State == ConnectionState.Open ))
{
m_conn.Close(); // 关闭数据库
}
}
public OracleDataReader ExecReader (string sText)
{
OracleDataReader dr = null;
m_error = "";
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
OracleCommand cmd = new OracleCommand (sText, m_conn);
cmd.CommandType = CommandType.Text;
dr = cmd.ExecuteReader ();
}
catch (Exception ex)
{
m_error =ex.Message;
}
return dr;
}
public OracleCommand ExecOracleCommand(string sText)
{
if(m_conn.State==ConnectionState.Closed)
m_conn.Open();
OracleCommand sqlcom=new OracleCommand(sText,m_conn);
return sqlcom;
}
/// <summary>
/// 执行查询,并返回查询所返回的结果集中的第一行的第一列.忽略额外的行或列.
/// </summary>
/// <param name="sText">查询语句</param>
/// <returns>返回查询所返回的结果集中的第一行的第一列</returns>
public object ExecuteScalar(string sText)
{
object obj = null;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open();
}
OracleCommand cmd = new OracleCommand(sText,m_conn);
cmd.CommandType = CommandType.Text;
obj = cmd.ExecuteScalar();
}
catch (Exception ex)
{
string s = ex.Message;
}
finally
{
m_conn.Close ();
}
return obj;
}
/// <summary>
/// 通过SQL查询得到数据集
/// </summary>
/// <param name="sText">sql查询字符串</param>
/// <returns></returns>
public DataSet ExecDS (string sText)
{
m_error = "";
DataSet ds = null;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
OracleDataAdapter da = new OracleDataAdapter (sText, m_conn);
ds = new DataSet ();
da.Fill (ds);
}
catch (Exception ex)
{
m_error =ex.Message;
}
finally
{
m_conn.Close ();
}
return ds;
}
/// <summary>
/// 通过SQL查询得到数据适配器
/// </summary>
/// <param name="sText"></param>
/// <returns></returns>
public OracleDataAdapter ExecDA(string sText)
{
OracleDataAdapter da = null;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
da = new OracleDataAdapter (sText, m_conn);
}
catch (Exception ex)
{
string s = ex.Message;
}
finally
{
m_conn.Close ();
}
return da;
}
/// <summary>
/// 通过SQL查询得到数据表
/// </summary>
/// <param name="sText"></param>
/// <returns></returns>
public DataTable ExecDT (string sText)
{
DataTable dt = null;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
OracleDataAdapter da = new OracleDataAdapter (sText, m_conn);
dt = new DataTable ();
da.Fill (dt);
}
catch (Exception ex)
{
string s = ex.Message;
throw (new Exception(s));
}
finally
{
m_conn.Close ();
}
return dt;
}
public int ExecNonQuery (string sText)
{
int iRowsAffected = -1;
m_error = "";
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
OracleCommand cmd = new OracleCommand (sText, m_conn);
cmd.CommandType = CommandType.Text;
iRowsAffected = cmd.ExecuteNonQuery ();
}
catch (Exception ex)
{
m_error=ex.Message;
if(ex!=null)
{
return 0;
}
}
finally
{
m_conn.Close ();
}
return iRowsAffected;
}
public bool ExecSqlQuery (string sql)
{
int iRowsAffected =-1;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
OracleCommand cmd = new OracleCommand (sql, m_conn);
cmd.CommandType = CommandType.Text;
iRowsAffected = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
string s = ex.Message;
if(ex!=null)
{
return false;
}
}
finally
{
m_conn.Close ();
}
if(iRowsAffected!=-1)
{
return true;
}
else
{
return false;
}
}
public bool ExecQuery(string sText)
{
OracleDataReader dr;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
OracleCommand cmd = new OracleCommand (sText, m_conn);
cmd.CommandType = CommandType.Text;
dr = cmd.ExecuteReader();
if (dr.HasRows==true)
return true;
else
return false;
}
catch (Exception ex)
{
string s = ex.Message;
if(ex!=null)
{
return false;
}
}
finally
{
m_conn.Close ();
}
return true;
}
public void ExecTran (string sText)
{
OracleTransaction trans = null;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
OracleCommand cmd = new OracleCommand (sText, m_conn);
cmd.CommandType = CommandType.Text;
trans = m_conn.BeginTransaction ();
cmd.Transaction = trans;//++
cmd.ExecuteNonQuery ();
trans.Commit();//++
//trans.Rollback ();
}
catch (Exception ex)
{
trans.Rollback ();
throw (new Exception (ex.Message));
}
finally
{
m_conn.Close ();
}
}
#endregion
}
}