using Oracle.ManagedDataAccess.Client;
using System;using System.Collections;using System.Collections.Generic;using System.Data;using System.Data.OleDb;using System.Linq;using System.Text;using System.Web; /// <summary>/// OracleConnection 的摘要说明/// </summary>public class OracleConn{ public OracleConn() { // // TODO: 在此处添加构造函数逻辑 // }static string connString = DBConnection.connString;
public int GetConn()
{ OracleConnection conn = new OracleConnection(connString); int result = 0; try { conn.Open(); result = 1; } catch (Exception ex) { throw (ex); } finally { conn.Close(); } return result; }/// <summary>
/// 查询数据 /// </summary> /// <returns></returns> public DataTable GetTableData() { DataTable dt = new DataTable(); //创建数据库表 using (OracleConnection con = new OracleConnection(connString)) { con.Open(); //打开数据库链接 OracleCommand sqlCom = new OracleCommand(); //声明并创建数据库命令集 StringBuilder sqlStr = new StringBuilder(); //声明sql语句 sqlStr.Append("select * from testTable ORDER BY id "); //获取sql语句sqlCom.CommandText = sqlStr.ToString(); //为sqlcommand对象指定sql语句
sqlCom.Connection = con; //为sqlcommand对象指定链接对象
OracleDataAdapter sqlDa = new OracleDataAdapter(sqlCom); //声明数据库适配器 OracleCommandBuilder sqlBuilder = new OracleCommandBuilder(sqlDa); sqlDa.Fill(dt); //填充表 }return dt;
}/// <summary>
/// 查询数据 /// </summary> /// <returns></returns> public OracleCommand GetData() { //使用using语句进行数据库连接 using (OracleConnection sqlCon = new OracleConnection(connString)) { sqlCon.Open(); //打开数据库连接OracleCommand sqlcom = new OracleCommand(); //创建数据库命令对象
sqlcom.CommandText = "select * from testTable"; //为命令对象指定执行语句 sqlcom.Connection = sqlCon; //为命令对象指定连接对象 OracleDataReader reader = sqlcom.ExecuteReader(); while (reader.Read()) { reader.GetInt32(0); } sqlCon.Close(); return sqlcom; } }/// <summary>
/// 删除数据 /// </summary> /// <param name="intId"></param> public void DeleteData(int intId) { using (OracleConnection con = new OracleConnection(connString)) { con.Open(); //打开数据库连接OracleCommand sqlcmd = new OracleCommand(); //创建数据库命令对象
sqlcmd.CommandText = "delete from testTable where id=@id"; //为命令对象指定执行语句 sqlcmd.Connection = con; //为命令对象指定连接对象//创建参数集合,并向sqlcom中添加参数集合
OracleParameter sqlParam = new OracleParameter("@id", intId); sqlcmd.Parameters.Add(sqlParam);sqlcmd.ExecuteNonQuery(); //指定更新语句
} }/// <summary>
/// 修改数据 /// </summary> /// <param name="intId"></param> /// <param name="strText"></param> public void UpdateData(int intId, string strText) { using (OracleConnection con = new OracleConnection(connString)) { con.Open(); //打开数据库连接OracleCommand sqlcmd = new OracleCommand(); //创建数据库命令对象
sqlcmd.CommandText = "update testTable set name=:str where id=:id"; //为命令对象指定执行语句 sqlcmd.Connection = con; //为命令对象指定连接对象//创建参数集合,并向sqlcom中添加参数集合
OracleParameter[] sqlParam = { new OracleParameter(":str", strText), new OracleParameter(":id", intId) }; sqlcmd.Parameters.AddRange(sqlParam);sqlcmd.ExecuteNonQuery(); //指定更新语句
con.Close(); } }/// <summary>
/// 添加操作方法 /// </summary> /// <param name="name"></param> /// <param name="address"></param> /// <param name="tel"></param> /// <param name="remark"></param> /// <returns></returns> public int AddData(string name, string address, string tel, string remark) { OracleConnection conn = new OracleConnection(connString); try { conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; string sql = "insert into Testtable (id,name,Address,Tel,Remark)values (Testtable_Id_Seq.NEXTVAL,'" + name + "','" + address + "','" + tel + "','" + remark + "')"; cmd.CommandText = sql; int retval = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return retval; } catch (Exception ex) { throw ex; } finally { conn.Close(); } }/// <summary>
/// 添加操作方法 /// </summary> /// <param name="ht"></param> public void InsertData(Hashtable ht) { //int id = Convert.ToInt32(ht[":ID"].ToString()); OracleConnection conn = new OracleConnection(connString); conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; string sql = "insert into Testtable (id,name,Address,Tel,Remark)values (Testtable_Id_Seq.NEXTVAL,:Name,:Address,:Tel,:Remark)"; cmd.CommandText = sql; OracleParameter[] param = { //new OracleParameter(":Id",OracleDbType.Int32), new OracleParameter(":Name",OracleDbType.Varchar2), new OracleParameter(":Address",OracleDbType.Varchar2), new OracleParameter(":Tel",OracleDbType.Varchar2), new OracleParameter(":Remark",OracleDbType.Varchar2) }; //param[0].Value = id; param[0].Value = ht[":Name"].ToString(); param[1].Value = ht[":Address"].ToString(); param[2].Value = ht[":Tel"].ToString(); param[3].Value = ht[":Remark"].ToString(); foreach (OracleParameter p in param) { if (p != null) { if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && (p.Value == null)) { p.Value = DBNull.Value; } cmd.Parameters.Add(p); } } int retval = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); conn.Close(); }/// <summary>
/// 查询表数据（单表） /// </summary> /// <param name="sql">sql语句</param> /// <returns></returns> public static DataSet SelectData(string sql) { DataSet ds = new DataSet(); try { using (OracleConnection conn = new OracleConnection(connString)) { //conn.Open(); OracleCommand cmd = new OracleCommand(sql, conn); OracleDataAdapter adapter = new OracleDataAdapter(cmd); adapter.Fill(ds); return ds; } } catch (Exception e) { Console.Write(e); } return null; }//增改删
/// <summary> /// 执行sql 语句 /// </summary> /// <param name="sql"></param> /// <returns>返回影响的行数</returns> public static Boolean AddUpDelData(string sql) { try { using (OracleConnection conn = new OracleConnection(connString)) { conn.Open(); OracleCommand cmd = new OracleCommand(sql, conn); int row = cmd.ExecuteNonQuery(); conn.Close(); if (row > 0) { return true; } } } catch (Exception e) { Console.Write(e); } return false; }public static Boolean ExeTransaction(List<string> sqlText)
{using (OracleConnection conn = new OracleConnection(connString))
{ conn.Open(); OracleTransaction tran = conn.BeginTransaction(); try {OracleCommand cmd = new OracleCommand();
cmd.Transaction = tran;
cmd.Connection = conn;
foreach (string item in sqlText)
{ cmd.CommandText = item;cmd.ExecuteNonQuery();
} tran.Commit();return true;
}
catch (Exception et)
{ tran.Rollback(); return false; }finally
{ conn.Close(); }}
}
}