c#连接oracle数据库底层方法

2019-01-08 08:24:23来源：博客园阅读 ()

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;
}