//******************************************** //Name: Oralce 操作通用类 //Desc: //Date: 2009-2-20 //Create: Yan Xiao Ming //Modify: //******************************************** using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.OracleClient; using System.Data.SqlClient; using System.Collections; /// <summary> /// DB_Common 的摘要说明 /// </summary> public class DB_Common { #region 类中的全局变量 private OracleConnection sqlcon; //申明一个SqlConnection对象 private OracleCommand sqlcom; //申明一个SqlCommand对象 private OracleDataAdapter sqldata; //申明一个SqlDataAdapter对象 private string ConnStr; #endregion #region 构造函数 /// <summary> /// 构造函数,初始化时连接数据库 /// </summary> public DB_Common() { string connStr; connStr = System.Configuration.ConfigurationSettings.AppSettings[0].ToString(); sqlcon = new OracleConnection(connStr); } #endregion #region 打开数据库 /// <summary> /// 打开数据库 /// </summary> public void OpenConn() { try { if (this.sqlcon.State != ConnectionState.Open) this.sqlcon.Open(); } catch (OracleException ex) { throw new Exception(ex.Message); } } #endregion #region 关闭数据库联接 /// <summary> /// 关闭数据库联接 /// </summary> public void CloseConn() { try { if (sqlcon.State == ConnectionState.Open) sqlcon.Close(); } catch (OracleException ex) { throw new Exception(ex.Message); } } #endregion #region 绑定用户页面中的GridView控件 /// <summary> /// 此方法实现数据绑定到GridView中 /// </summary> /// <param name="dl">要绑定的控件</param> /// <param name="SqlCom">要执行的SQL语句</param> /// <returns></returns> public bool BindData(GridView dl, string SqlCom) { dl.DataSource = this.ExceDS(SqlCom); try { dl.DataBind(); return true; } catch { return false; } finally { sqlcon.Close(); } } #endregion #region 执行SQL语句 /// <summary> /// 此方法用来执行SQL语句 /// </summary> /// <param name="SqlCom">要执行的SQL语句</param> /// <returns></returns> public bool ExceSQL(string strSqlCom) { sqlcom = new OracleCommand(strSqlCom, sqlcon); try { OpenConn(); sqlcom.ExecuteNonQuery(); return true; } catch (OracleException ex) { throw new Exception(ex.Message); return false; } finally { CloseConn(); //sqlcon.Close(); } } #endregion #region 执行SQL语句,返回记录总数 /// <summary> /// 执行SQL语句,返回记录总数 /// </summary> /// <param name="sql">sql语句</param> /// <returns>返回记录总条数</returns> /// public int Execute_Record(string sql) { int recordCount = 0; OpenConn();//打开数据连接 OracleCommand command = new OracleCommand(sql, sqlcon); OracleDataReader dataReader = command.ExecuteReader(); while (dataReader.Read()) { recordCount++; } dataReader.Close(); CloseConn();//关闭数据库连接 return recordCount; } #endregion #region 执行SQ DML语句,返回所影响的行数 /// <summary> /// 执行SQL语句,返回所影响的行数 /// </summary> /// <param name="sql"></param> /// <returns></returns> public int ExecuteDML(string strSqlCom) { int Cmd = 0; OpenConn(); OracleCommand command = new OracleCommand(strSqlCom, sqlcon); OracleTransaction OraTrans = sqlcon.BeginTransaction(); command.Transaction = OraTrans; try { Cmd = command.ExecuteNonQuery(); OraTrans.Commit(); } catch (OracleException ex) { throw new Exception(ex.Message); OraTrans.Rollback(); } finally { CloseConn(); } return Cmd; } #endregion #region 返回DataSet类型数据 /// <summary> /// 此方法返回一个DataSet类型 /// </summary> /// <param name="SqlCom">要执行的SQL语句</param> /// <returns></returns> public DataSet ExceDS(string SqlCom) { try { OpenConn(); sqlcom = new OracleCommand(SqlCom, sqlcon); sqldata = new OracleDataAdapter(); sqldata.SelectCommand = sqlcom; DataSet ds = new DataSet(); sqldata.Fill(ds); return ds; } catch (OracleException ex) { throw new Exception(ex.Message); } finally { // sqlcon.Close(); CloseConn(); } } #endregion #region 执行查询语句,返回OracleDataReader ( 注意:调用该方法后,一定要对OracleDataReader进行Close ) /// <summary> /// 执行查询语句,返回OracleDataReader ( 注意:调用该方法后,一定要对OracleDataReader进行Close ) /// </summary> /// <param name="sql">查询语句</param> /// <returns>OracleDataReader</returns> public OracleDataReader ExecuteReader(string SqlCom) { OracleDataReader myReader; OpenConn(); OracleCommand cmd = new OracleCommand(SqlCom, sqlcon); myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return myReader; } #endregion #region public int ExecuteDML_Mutil(ArrayList SQLStringList) { int count = 0; OpenConn(); OracleCommand command = new OracleCommand(); command.Connection = sqlcon; OracleTransaction OraTrans = sqlcon.BeginTransaction(); command.Transaction = OraTrans; try { for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n].ToString(); if (strsql.Trim().Length > 1) { command.CommandText = strsql; count = command.ExecuteNonQuery(); } } OraTrans.Commit(); } catch (OracleException ex) { OraTrans.Rollback(); throw new Exception(ex.Message); } finally { // sqlcon.Close(); CloseConn(); } return count; } //public int ExecuteDML_Mutil(string SQLStringList) //{ // int count = 0; // OpenConn(); // OracleCommand command = new OracleCommand(SQLStringList,sqlcon); // OracleTransaction OraTrans = sqlcon.BeginTransaction(); // command.Transaction = OraTrans; // try // { // //for (int n = 0; n < SQLStringList.Count; n++) // //{ // // string strsql = SQLStringList[n].ToString(); // // if (strsql.Trim().Length > 1) // // { // // command.CommandText = strsql; // // count = command.ExecuteNonQuery(); // // } // //} // //command.CommandText = SQLStringList; // count = command.ExecuteNonQuery(); // OraTrans.Commit(); // } // catch (System.Data.SqlClient.SqlException ex) // { // OraTrans.Rollback(); // throw new Exception(ex.Message); // } // finally // { // // sqlcon.Close(); // CloseConn(); // } // return count; //} #endregion }