//**********************************************************
//2005-06-21 17:37:11
//程序 吴建明 wujm_xa@sina.com
//功能
//**********************************************************
using System;
using System.Data.OracleClient;
using System.Data;
using System.Windows.Forms;
using System.Collections;
using System.IO;
using UtilityLibrary.WinControls;
namespace DoReport
{
/// <summary>
/// ClsDB 的摘要说明。
/// </summary>
public class ClsDB
{
/// <summary>
/// ＝＝＝吴建明 数据库访问类2005-04-18＝＝＝
/// </summary>
public OracleConnection cn;
private OracleCommand cmd;
private OracleDataAdapter ada;
private OracleCommandBuilder bldr;
private DataSet ds;
public DataRow dr;
public OracleTransaction tran;
public String ConnStr;
public Hashtable myHT;
public ClsDB()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public ClsDB(string SConnStr)
{
ConnStr=SConnStr;
}
public void Dispose(OracleConnection Conn)
{
if(cn!=null)
{
cn.Close();
cn.Dispose();
}
GC.Collect();
}
//以下2个方法是执行sql脚本的方法 0raclre没通过，sql server可以
/// <summary>
/// 执行Sql文件
/// </summary>
/// <param name="varFileName"></param>
/// <returns></returns>
public bool ExecuteSqlFile(string varFileName,ProgressBarEx myPrgBar)
{
if(!File.Exists(varFileName))
{
return false;
}
StreamReader sr = File.OpenText(varFileName);
ArrayList alSql = new ArrayList();
string commandText = "";
string varLine = "";
myPrgBar.Value =0;
myPrgBar.Maximum=500;
while(sr.Peek() > -1)
{
varLine = sr.ReadLine();
myPrgBar.Value ++;
if(varLine == "")
{
continue;
}
if(varLine.ToLower() != "go")
{
commandText += varLine;
commandText += "\r\n";
}
else
{
alSql.Add(commandText);
commandText = "";
}
}
sr.Close();
try
{
ExecuteCommand(alSql, myPrgBar);
}
catch
{
return false;
}
return true;
}
public void ExecuteCommand(ArrayList varSqlList,ProgressBarEx myPrgBar)
{
Open();
OracleTransaction varTrans = cn.BeginTransaction();
OracleCommand command = new OracleCommand();
command.Connection =cn;
command.Transaction = varTrans;
try
{
myPrgBar.Value =0;
myPrgBar.Maximum =varSqlList.Count;
// myLabel.Text="开始执行Sql脚本";
foreach(string varcommandText in varSqlList)
{
myPrgBar.Value ++;
// myLabel.Text="执行Sql脚本"+varcommandText;
command.CommandText = varcommandText;
command.ExecuteNonQuery();
}
varTrans.Commit();
// myLabel.Text="执行Sql脚本完毕";
}
catch(Exception ex)
{
varTrans.Rollback();
MessageBox.Show(ex.Message);
throw ex;
}
finally
{
// myLabel.Text="执行Sql脚本失败";
cn.Close();
}
}
//以上是执行sql脚本文件
//**********************************>> 基本数据库操作篇 <<**********************************//
/// <summary>
/// 打开数据库
/// </summary>
public void Open()
{
try
{
if(cn == null)
{
cn = new OracleConnection(ConnStr);
}
if (cn.State== ConnectionState.Closed)
{
cn.Open();
}
}
catch (Exception ex)
{
if(MessageBox.Show("数据库连接失败！\n请检查数据库连接属性！","错误", MessageBoxButtons.RetryCancel,MessageBoxIcon.Error)==DialogResult.Retry )
{
Open();
}
else
{
throw ex;
}
}
finally
{
}
}
/// <summary>
/// 打开数据库,不返回提示
/// </summary>
public void Open(int No)
{
try
{
if(cn == null)
{
cn = new OracleConnection(ConnStr);
}
if (cn.State== ConnectionState.Closed)
{
cn.Open();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
/// <summary>
/// 关闭数据库
/// </summary>
public void Close()
{
if (cn != null)
{
cn.Close();
}
}
/// <summary>
/// 返回一个SqlParameter实例
/// </summary>
/// <param name="ParamName"></param>
/// <param name="Value"></param>
/// <returns></returns>
public OracleParameter MakeParam(string ParamName,object Value)
{
return new OracleParameter(ParamName, Value);
}
/// <summary>
/// 调用存储过程创建一个SqlCommand对象
/// </summary>
/// <param name="procName">存储过程</param>
/// <returns></returns>
private OracleCommand CreateCommand(string procName)
{
Open();
OracleCommand cmd = new OracleCommand(procName, cn);
cmd.CommandType = CommandType.StoredProcedure;
return cmd;
}
/// <summary>
/// 调用存储过程创建一个SqlCommand对象
/// </summary>
/// <param name="procName">存储过程</param>
/// <param name="prams">给存储过程传递传输SqlParameter对象</param>
/// <returns></returns>
private OracleCommand CreateCommand(string procName, OracleParameter[] prams)
{
Open();
OracleCommand cmd = new OracleCommand(procName, cn);
cmd.CommandType = CommandType.StoredProcedure;
if (prams != null)
{
foreach (OracleParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
//这一段sql 需要，oracle则需要屏蔽
// cmd.Parameters.Add(
// new OracleParameter("ReturnValue",OracleType.Int32, 4,
// ParameterDirection.ReturnValue, false, 0, 0,
// string.Empty, DataRowVersion.Default, null));
return cmd;
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <returns></returns>
public void RunProc(string procName)
{
OracleCommand cmd = CreateCommand(procName);
cmd.ExecuteNonQuery();
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="prams">给存储过程传递传输SqlParameter对象</param>
/// <returns></returns>
public void RunProc(string procName, OracleParameter[] prams)
{
OracleCommand cmd = CreateCommand(procName, prams);
cmd.ExecuteNonQuery();
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="prams">给存储过程传递传输SqlParameter对象</param>
/// <param name="dataReader">输出一个DataReader对象</param>
public void RunProc(string procName, OracleParameter[] prams, out OracleDataReader dataReader)
{
OracleCommand cmd = CreateCommand(procName, prams);
dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
/// <summary>
/// 获得DataSet对象
/// </summary>
/// <param name="str_Sql">SQL语句</param>
/// <returns></returns>
public DataSet GetDs(string str_Sql)
{
Open();
ada = new OracleDataAdapter(str_Sql,cn);
DataSet ds = new DataSet();
bldr = new OracleCommandBuilder(ada);
ada.Fill(ds);
cn.Close();
return ds;
}
/// <summary>
/// 获得DataSet对象
/// </summary>
/// <param name="tablename">内存表ID</param>
/// <param name="str_Sql">SQL语句</param>
/// <returns></returns>
public DataSet GetDs(string tablename,string str_Sql)
{
Open();
OracleDataAdapter Ada = new OracleDataAdapter(str_Sql,cn);
DataSet ds = new DataSet();
bldr = new OracleCommandBuilder(Ada);
Ada.Fill(ds,tablename);
cn.Close();
return ds;
}
/// <summary>
/// 获得DataTable对象
/// </summary>
/// <param name="str_Sql">SQL语句</param>
/// <returns></returns>
public DataTable GetTable(string str_Sql)
{
return GetDs(str_Sql).Tables[0];
}
/// <summary>
/// 获得DataTable对象
/// </summary>
/// <param name="tablename">内存表ID</param>
/// <param name="str_Sql">SQL语句</param>
/// <returns></returns>
public DataTable GetTable(string tablename,string str_Sql)
{
return GetDs(str_Sql).Tables[tablename];
}
public OracleDataReader GetDataReader(string str_Sql)
{
try
{
Open();
OracleCommand cmd = new OracleCommand(str_Sql,cn);
OracleDataReader dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return dataReader;
}
catch(Exception ex)
{
throw ex;
}
}
/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="str_Sql">Sql语句</param>
public void RunSql(string str_Sql)
{
Open();
OracleCommand cmd = new OracleCommand(str_Sql,cn);
cmd.ExecuteNonQuery();
cn.Close();
}
//**********************************>> 数据库操作篇(包括添加Add,修改Edit,删除Del,详细Detail) <<**********************************//
/// <summary>
/// 获得表记录数
/// </summary>
/// <param name="table_name">表名或者表名+条件,GetRsCount("t_user where id="+Request["id"])</param>
/// <returns></returns>
public int GetRsCount(string table_name)
{
string strSql;
int intCount;
Open();
strSql="select count(*) from "+table_name;
OracleCommand cmd=new OracleCommand(strSql,cn);
intCount=(int)cmd.ExecuteScalar();
cn.Close();
return intCount;
}
/// <summary>
/// 获得单个int类型字段总和
/// </summary>
/// <param name="field">字段</param>
/// <param name="table_name">表名或者表名+条件,GetFiledSum("id","t_user where id="+Request["id"])</param>
/// <returns></returns>
public string GetFiledSum(string field,string table_name)
{
string SumValue;
Open();
OracleCommand cmd=new OracleCommand("select sum("+field+") as s from "+table_name,cn);
SumValue=cmd.ExecuteScalar().ToString();
cn.Close();
return SumValue;
}
public string GetFiledSum(string field)
{
string SumValue;
Open();
OracleCommand cmd=new OracleCommand(field,cn);
SumValue=cmd.ExecuteScalar().ToString();
cn.Close();
return SumValue;
}
/// <summary>
/// 获得单个字段值
/// </summary>
/// <param name="str_Sql">Sql语句</param>
/// <returns></returns>
public string GetFiledValue(string str_Sql)
{
string str;
Open();
OracleCommand cmd=new OracleCommand(str_Sql,cn);
str=cmd.ExecuteScalar().ToString();
cn.Close();
return str;
}
/// <summary>
/// 获得表记录数
/// </summary>
/// <param name="table_name">表名或者表名+条件,GetRsCount("t_user where id="+Request["id"])</param>
/// <returns></returns>
public int GetMaxId(string filed,string table_name)
{
string strSql;
int intCount;
Open();
strSql="select max("+filed+") from "+table_name;
OracleCommand cmd=new OracleCommand(strSql,cn);
object obj=cmd.ExecuteScalar();
if (obj==System.DBNull.Value)
{
intCount=1;
}
else
{
intCount = Convert.ToInt32(cmd.ExecuteScalar())+1;
}
cn.Close();
return intCount;
}
/// <summary>
/// 通过SqlCommandBuilder对象增加数据库记录
/// </summary>
/// <param name="sql">Select-SQL语句</param>
public void Builder(string str_Sql)
{
Open();
ada=new OracleDataAdapter(str_Sql,cn);
OracleCommandBuilder myCommandBuilder=new OracleCommandBuilder(ada);
ds=new DataSet();
ada.Fill(ds);
dr=ds.Tables[0].NewRow();
}
/// <summary>
/// 关闭SqlCommandBuilder对象
/// </summary>
public void BuilderClose()
{
ds.Tables[0].Rows.Add(dr);
ada.Update(ds); // 更新数据库
cn.Close(); // 关闭数据库
ds.Clear(); // 清空DataSet对象
}
/// <summary>
/// 通过SqlCommandBuilder对象修改数据库记录
/// </summary>
/// <param name="sql">Select-SQL语句</param>
public void BuilderEdit(string str_Sql)
{
Open();
ada=new OracleDataAdapter(str_Sql,cn);
OracleCommandBuilder myCommandBuilder=new OracleCommandBuilder(ada);
ds=new DataSet();
ada.Fill(ds);
dr=ds.Tables[0].Rows[0];
}
/// <summary>
/// 关闭SqlCommandBuilder对象
/// </summary>
public void BuilderEditClose()
{
ada.Update(ds); // 更新数据库
cn.Close(); // 关闭数据库
ds.Clear(); // 清空DataSet对象
}
//**********************************>> 事务处理篇 <<**********************************//
/// <summary>
/// 开始事务
/// </summary>
public void TranBegin()
{
Open();
tran=cn.BeginTransaction();
}
/// <summary>
/// 结束事务
/// </summary>
public void TranEnd()
{
tran.Commit(); // 结束事务
if (cn!=null)
{
cn.Dispose(); // 关闭数据库
cn.Close(); // 关系数据库
}
if (ds!=null)
{
ds.Clear();
}
}
public OracleDataReader TranGetDataReader(string str_Sql)
{
Open();
OracleCommand cmd=new OracleCommand(str_Sql,cn);
OracleDataReader dr=cmd.ExecuteReader();
return dr;
}
public DataSet TranGetDs(string str_Sql)
{
Open();
OracleDataAdapter Ada = new OracleDataAdapter(str_Sql,cn);
Ada.SelectCommand.Transaction=tran;
DataSet ds = new DataSet();
Ada.Fill(ds);
//cn.Close();
return ds;
}
public DataTable TranGetTable(string str_Sql)
{
return TranGetDs(str_Sql).Tables[0];
}
/// <summary>
/// 获得表记录数
/// </summary>
/// <param name="table_name">表名或者表名+条件,GetRsCount("t_user where id="+Request["id"])</param>
/// <returns></returns>
public int TranGetRsCount(string table_name)
{
string strSql;
int intCount;
strSql="select count(*) from "+table_name;
OracleCommand cmd=new OracleCommand(strSql,cn);
cmd.Transaction=tran;
intCount=(int)cmd.ExecuteScalar();
return intCount;
}
/// <summary>
/// 获得单个int类型字段总和
/// </summary>
/// <param name="field">字段</param>
/// <param name="table_name">表名或者表名+条件,GetFiledSum("id","t_user where id="+Request["id"])</param>
/// <returns></returns>
public string TranGetFiledSum(string field,string table_name)
{
string SumValue;
OracleCommand cmd=new OracleCommand("select sum("+field+") as s from "+table_name,cn);
cmd.Transaction=tran;
SumValue=cmd.ExecuteScalar().ToString();
return SumValue;
}
/// <summary>
/// 获得单个字段值
/// </summary>
/// <param name="str_Sql">Sql语句</param>
/// <returns></returns>
public string TranGetFiledValue(string str_Sql)
{
string str;
OracleCommand cmd=new OracleCommand(str_Sql,cn);
cmd.Transaction=tran;
str=cmd.ExecuteScalar().ToString();
return str;
}
/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="str_Sql">Sql语句</param>
public void TranRunSql(string str_Sql)
{
OracleCommand cmd = new OracleCommand(str_Sql,cn);
cmd.Transaction=tran;
cmd.ExecuteNonQuery();
}
/// <summary>
/// 通过SqlCommandBuilder对象增加数据库记录
/// </summary>
/// <param name="sql">Select-SQL语句</param>
public void TranBuilder(string str_Sql)
{
ada=new OracleDataAdapter(str_Sql,cn);
ada.SelectCommand.Transaction=tran;
OracleCommandBuilder myCommandBuilder=new OracleCommandBuilder(ada);
ds=new DataSet();
ada.Fill(ds);
dr=ds.Tables[0].NewRow();
}
/// <summary>
/// 关闭SqlCommandBuilder对象
/// </summary>
public void TranBuilderClose()
{
ds.Tables[0].Rows.Add(dr);
ada.Update(ds); // 更新数据库
}
/// <summary>
/// 通过SqlCommandBuilder对象修改数据库记录
/// </summary>
/// <param name="sql">Select-SQL语句</param>
public void TranBuilderEdit(string str_Sql)
{
ada=new OracleDataAdapter(str_Sql,cn);
ada.SelectCommand.Transaction=tran;
OracleCommandBuilder myCommandBuilder=new OracleCommandBuilder(ada);
ds=new DataSet();
ada.Fill(ds);
dr=ds.Tables[0].Rows[0];
}
/// <summary>
/// 关闭SqlCommandBuilder对象
/// </summary>
public void TranBuilderEditClose()
{
ada.Update(ds); // 更新数据库
}
/// <summary>
/// 事务回滚
/// </summary>
public void TranRollback()
{
tran.Rollback(); // 数据库回滚
cn.Dispose(); // 关闭数据库
cn.Close(); // 关系数据库
}
}
}