using Oracle.ManagedDataAccess.Client;using System;using System.Collections;using System.Collections.Generic;using System.Data;using System.Data.OleDb ...
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();
}
}
}
}
