oracle连接工具类,c# .net oracle连接工具类

该类为本人项目使用中的oracle连接工具类

1.先引入OraOps10.dll

2.将该类加入项目中

dll及源码下载地址

源码如下

using System;

using System.Collections.Generic;

using System.Text;

using System.Collections;

using System.Data;

using System.Data.OracleClient;

namespace TImageEdit

{

public class OracleHelperMS

{

///

/// 数据库连接字符串

///

public static readonly string Constr = "Data Source=xxxx;User Id=xxxx;Password=xxx;Min Pool Size=10;Connection Lifetime=120;";

///

/// 定义hashtable表

///

private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

/// 基于参数的准备命令对象

/// SqlTransaction是c#类里面的事务对象，能保持数据库的数据统一性。如果出错则回滚

///

/// OracleCommand对象

/// OracleConnection对象

/// sqltransaction对象

/// 命令类型

/// sql命令或者是存储过程名

/// 执行命令用的sqlparamter数组

private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] cmdParms)

{

if (conn.State != ConnectionState.Open)

conn.Open();

cmd.Connection = conn;

cmd.CommandText = cmdText;

//if (trans != null)

//cmd.Transaction = trans;

cmd.CommandType = cmdType;

if (cmdParms != null)

{

foreach (OracleParameter parm in cmdParms)

cmd.Parameters.Add(parm);

OracleConnection cn = new OracleConnection();

}

}

///

/// 基于连接字符串的数据库执行一个非查询的SQL命令

/// 可以使用参数集传入命令参数

/// 例子：int result =ExcuteNonQuery(connstring,cmmandType.storedprocedure,"publishOrders",new OracleParameter("@prodid",24));

/// 为OracleConnection提供的连接字符串

/// 命令类型(commandtype.storedprocedure,commandtype.text....)

/// 存储过程名或者是T-SQL命令

/// 执行命令所使用的OracleParameters数组

/// 返回命令所影响的行数

public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)

{

using (OracleCommand cmd = new OracleCommand())

{

using (OracleConnection coon = new OracleConnection(connectionString))

{

PrepareCommand(cmd, coon, null, cmdType, cmdText, commandParameters);

//OracleTransaction myTrans=coon.BeginTransaction();

int val = cmd.ExecuteNonQuery();

//myTrans.Commit();

cmd.Parameters.Clear();

return val;

}

}

}

///

/// 基于连接对象的数据库执行一个非查询的SQL命令

/// 可以使用参数集传入命令参数

/// 例子：int result=ExcuteNonQuery(conn,commandtype.storedprocedure,"publishorders",new OracleParameter("@prodid",24));

///

/// 一个存在的连接对象

/// 命令类型(commandtype.storedprocedure,commandtype.text....)

/// 存储过程名或者是T-SQL命令

/// 执行命令所使用的OracleParameters数组

/// 返回命令所影响的行数

public static int ExcuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)

{

using (OracleCommand cmd = new OracleCommand())

{

PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);

int val = cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

return val;

}

}

///

/// 基于事务对象的数据库执行一个非查询SQL语句

///  可以使用参数集传入命令参数

/// 例子：int result=ExcuteNonQuery(trans,commandtype.storedprocedure,"publishorders",new OracleParameter("@prodid",24));

///

///

/// 一个存在的事务对象

/// 命令类型(commandtype.storedprocedure,commandtype.text....)

/// 存储过程名或者是T-SQL命令

/// 执行命令所使用的OracleParameters数组

/// 返回命令所影响的行数

public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)

{

using (OracleCommand cmd = new OracleCommand())

{

PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);

int val = cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

return val;

}

}

///

///  基于连接字符串的数据库执行一个查询，返回数据流sqldatareader

///  可以使用参数集传入命令参数

///  例子：SqldataReader r=ExecuteReader(connstring,commandtype.storedprocedure,"publishorders",new OracleParameter("@prodid",24));

///

///

/// 为OracleConnection提供连接的字符串

/// 命令类型(commandtype.storedprocedure,commandtype.text,...)

/// T-SQL命令存储过程名或者是

/// 执行命令所用的sqlparamters

///

public static OracleDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)

{

OracleCommand cmd = new OracleCommand();

OracleConnection conn = new OracleConnection(Constr);

try

{

PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

cmd.Parameters.Clear();

return rdr;

}

catch

{

conn.Close();

throw;

}

}

///

/// 基于连接字符串的数据库执行一个查询，返回数据流sqldatareader

///  可以使用参数集传入命令参数

///

///

public static DataTable PDataTable(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)

{

using (OracleConnection conn = new OracleConnection(Constr))

{

DataSet dataset = new DataSet();

using (OracleCommand cmd = new OracleCommand())

{

PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

using (OracleDataAdapter adapter = new OracleDataAdapter(cmd))

{

adapter.Fill(dataset);

return dataset.Tables[0];

}

}

}

}

///

/// 获取一个数据表格

///

///

public static void DoExcute(string cmdText)

{

OracleHelperMS.ExecuteNonQuery(OracleHelperMS.Constr, CommandType.Text, cmdText, null);

}

///

/// 获取一个数据表格

///

///

public static DataTable getDataTable(string cmdText)

{

return OracleHelperMS.PDataTable(OracleHelperMS.Constr, CommandType.Text, cmdText, null);

}

///

/// 获取一行数据

///

///

public static DataRow getDataRow(string cmdText)

{

return OracleHelperMS.PDataRow(OracleHelperMS.Constr, CommandType.Text, cmdText, null);

}

///

/// 基于连接字符串的数据库执行一个查询，返回数据流sqldatareader

///  可以使用参数集传入命令参数

///

///

public static DataRow PDataRow(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)

{

using (OracleConnection conn = new OracleConnection(Constr))

{

DataSet dataset = new DataSet();

using (OracleCommand cmd = new OracleCommand())

{

PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

using (OracleDataAdapter adapter = new OracleDataAdapter(cmd))

{

adapter.Fill(dataset);

if (dataset.Tables[0] != null && dataset.Tables[0].Rows.Count > 0)

{

return dataset.Tables[0].Rows[0];

}

else

{

return null;

}

}

}

}

}

///

/// 基于连接字符串的数据库执一个查询，返回结果集得第一行第一列的数据对象

/// 可以使用参数集传入命令参数

/// 例子：object boj=ExecuteScalar(connstring,commandType.storedprocedure,"publishoreders",new OracleParameter("@prodid",24));

///

///

/// 为OracleConnection提供的连接字符串

/// 命令类型(commandType.storedprocedure,commandtype.text,......)

/// 存储过程名或者T-SQL命令

/// 执行命令所使用的sqlparamters数组

/// 返回结果的第一行第一列

public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)

{

using (OracleCommand cmd = new OracleCommand())

{

using (OracleConnection connection = new OracleConnection(Constr))

{

PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);

object val = cmd.ExecuteScalar();

cmd.Parameters.Clear();

return val;

}

}

}

///

/// 基于连接对象的数据库执行一个查询，返回结果的第一行第一列的数据对象

/// 可以使用参数集传入命令参数

/// 例子：object obj =executescalar(conn,commandtype.storedprocedure,"publishOreders",new sqlparamters("@prodid",24));

///

///

/// 一个存在的连接对象

/// 命令类型(commandtype.storedprocedure,commondtype.text,....)

/// 存储过程名或者T-SQL命令

/// 执行命令所使用的sqlparamters数组

/// 返回结果的第一行第一列

public static object ExecuteScalar(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)

{

using (OracleCommand cmd = new OracleCommand())

{

PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);

object val = cmd.ExecuteScalar();

cmd.Parameters.Clear();

return val;

}

}

///

/// 执行Insert

///

///

///

///

///

public static int DoInsert(string ATable, string[] AFields, object[] AValues)

{

string SQL = "Insert into " + ATable + "(";

for (int i = 0; i < AFields.Length; i++)

{

SQL += AFields[i] + " ,";

}

SQL = SQL.Substring(0, SQL.Length - 1) + ") values (";

string[] APs = new string[AFields.Length];

for (int i = 0; i < AFields.Length; i++)

{

if (AValues[i].GetType() == typeof(System.DateTime))

{

SQL += "to_date('" + AValues[i] + "','yyyy/mm/dd hh24:mi:ss') ,";

}

else

{

SQL += "'" + AValues[i] + "' ,";

}

}

SQL = SQL.Substring(0, SQL.Length - 1) + ") ";

return ExecuteNonQuery(OracleHelperMS.Constr, CommandType.Text, SQL, null);

}

public static int ExecuteNonQuery(String SQL)

{

return ExecuteNonQuery(OracleHelperMS.Constr, CommandType.Text, SQL, null);

}

///

/// 更新数据表

///

///

///

///

///

///

///

public static int DoUpdate(string ATable, string[] AFields, object[] AValues,

string[] ACondFields, object[] ACondValues

)

{

string SQL = "Update " + ATable + " Set ";

for (int i = 0; i < AFields.Length; i++)

{

SQL += AFields[i] + " ='" + AValues[i] + "' ,";

}

SQL = SQL.Substring(0, SQL.Length - 1);

if (ACondValues != null)

{

SQL += " where (1>0) ";

for (int i = 0; i < ACondFields.Length; i++)

{

SQL += " and " + ACondFields[i] + " ='" + ACondValues[i]+"'";

}

}

return ExecuteNonQuery(OracleHelperMS.Constr, CommandType.Text, SQL, null);

}

///

/// 向缓冲区添加参数

///

/// 参数集对象在缓冲区中的key

/// 要缓存的参数集对象

public static void CacheParameters(string cacheKey, params OracleParameter[] commandParamters)

{

parmCache[cacheKey] = commandParamters;

}

///

/// 从缓冲区读取参数集对象

///

/// 参数集对象在缓冲区的key

/// 被缓存的参数集对象

public static OracleParameter[] GetCachedParameters(string cacheKey)

{

OracleParameter[] cachedParams = (OracleParameter[])parmCache[cacheKey];

if (cachedParams == null)

return null;

OracleParameter[] clonedParms = new OracleParameter[cachedParams.Length];

for (int i = 0, j = cachedParams.Length; i < j; i++)

clonedParms[i] = (OracleParameter)((ICloneable)cachedParams[i]).Clone();

return clonedParms;

}

///

/// Oracle执行事务

///

///

///

public static bool RunTransaction(string connectionString, string[] sqls)

{

using (OracleConnection connection = new OracleConnection(connectionString))

{

connection.Open();

OracleCommand command = connection.CreateCommand();

OracleTransaction transaction;

transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);

//command.Transaction = transaction;

try

{

foreach (string sql in sqls)

{

command.CommandText = sql;

command.ExecuteNonQuery();

}

transaction.Commit();

return true;

}

catch (Exception ex)

{

transaction.Rollback();

return false;

}

}

}

///

/// 更新datatable

///

///

///

///

///

///

///

public static void DataTableToDB(string connectionString, System.Windows.Forms.DataGridView GV, string selectTab, string cmdUpText, string uprow, params OracleParameter[] commandParameters)

{

using (OracleConnection conn = new OracleConnection(connectionString))

{

conn.Open();

using (OracleDataAdapter sd = new OracleDataAdapter())

{

sd.SelectCommand = new OracleCommand("select top 300 * from " + selectTab + "", conn);

DataSet dataset = new DataSet();

sd.Fill(dataset);

//sd.UpdateCommand = new SqlCommand("update tb11 "

//                + " set t3 = @t3,t4 = @t4 where t1 = @t1 and t2=@t2", conn);

sd.UpdateCommand = new OracleCommand(cmdUpText, conn);

if (commandParameters != null)

{

foreach (OracleParameter parm in commandParameters)

sd.UpdateCommand.Parameters.Add(parm);

}

//sd.UpdateCommand.Parameters.Add("@t1", SqlDbType.Int, 9, "t1");

//sd.UpdateCommand.Parameters.Add("@t2", SqlDbType.NVarChar, 4, "t2");

//sd.UpdateCommand.Parameters.Add("@t3", SqlDbType.NVarChar, 20, "t3");

//sd.UpdateCommand.Parameters.Add("@t4", SqlDbType.NVarChar, 20, "t4");

sd.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

sd.UpdateBatchSize = 0;

for (int count = 0; count < GV.RowCount - 1; )

{

int EverySec = 300;

int DataLeft = (GV.RowCount - 1) % 300;

for (int i = 0; i < EverySec; i++, count++)

{

if (count == (DataLeft - 1))

{

EverySec = DataLeft;

}

dataset.Tables[0].Rows[count].BeginEdit();

string[] lieming = uprow.Split(',');

for (int R = 0; R < lieming.Length; R++)

{

string t4 = lieming[R].ToString();

dataset.Tables[0].Rows[count][t4] = GV.Rows[count].Cells[t4].Value;

}

dataset.Tables[0].Rows[count].EndEdit();

}

try

{

sd.Update(dataset.Tables[0]);

}

catch (Exception exp)

{

throw new Exception(exp.Message);

}

}

//sd.Update(dt);

dataset.Tables[0].Clear();

sd.Dispose();

dataset.Dispose();

}

}

}

}

}

这是个静态类，直接类名+方法名调用就可以

如果有疑问可联系bug哥讨论

​