该类为本人项目使用中的oracle连接工具类
1.先引入OraOps10.dll
2.将该类加入项目中
dll及源码下载地址
https://pan.baidu.com/s/1aoFHKG5C1iQThC4rpZ6M_w
源码如下
using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.OracleClient;
namespace TImageEdit
{
public class OracleHelperMS
{
/// <summary>
/// 数据库连接字符串
/// </summary>
public static readonly string Constr = "Data Source=xxxx;User Id=xxxx;Password=xxx;Min Pool Size=10;Connection Lifetime=120;";
/// <summary>
/// 定义hashtable表
/// </summary>
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
/// <summary>基于参数的准备命令对象
/// SqlTransaction是c#类里面的事务对象，能保持数据库的数据统一性。如果出错则回滚
/// </summary>
/// <param name="cmd">OracleCommand对象</param>
/// <param name="conn">OracleConnection对象</param>
/// <param name="trans">sqltransaction对象</param>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdText">sql命令或者是存储过程名</param>
/// <param name="cmdParms">执行命令用的sqlparamter数组</param>
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();
}
}
/// <summary>
/// 基于连接字符串的数据库执行一个非查询的SQL命令
/// </summary>可以使用参数集传入命令参数
/// 例子：int result =ExcuteNonQuery(connstring,cmmandType.storedprocedure,"publishOrders",new OracleParameter("@prodid",24));
/// <param name="connectionString">为OracleConnection提供的连接字符串</param>
/// <param name="cmdType">命令类型（commandtype.storedprocedure,commandtype.text....）</param>
/// <param name="cmdText">存储过程名或者是T-SQL命令</param>
/// <param name="commandParameters">执行命令所使用的OracleParameters数组</param>
/// <returns> 返回命令所影响的行数</returns>
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;
}
}
}
/// <summary>
/// 基于连接对象的数据库执行一个非查询的SQL命令
/// 可以使用参数集传入命令参数
/// 例子：int result=ExcuteNonQuery(conn,commandtype.storedprocedure,"publishorders",new OracleParameter("@prodid",24));
/// </summary>
/// <param name="connection">一个存在的连接对象</param>
/// <param name="cmdType">命令类型（commandtype.storedprocedure,commandtype.text....）</param>
/// <param name="cmdText">存储过程名或者是T-SQL命令</param>
/// <param name="commandParameters">执行命令所使用的OracleParameters数组</param>
/// <returns> 返回命令所影响的行数</returns>
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;
}
}
/// <summary>
/// 基于事务对象的数据库执行一个非查询SQL语句
/// 可以使用参数集传入命令参数
/// 例子：int result=ExcuteNonQuery(trans,commandtype.storedprocedure,"publishorders",new OracleParameter("@prodid",24));
/// </summary>
/// </summary>
/// <param name="trans">一个存在的事务对象</param>
/// <param name="cmdType">命令类型（commandtype.storedprocedure,commandtype.text....）</param>
/// <param name="cmdText">存储过程名或者是T-SQL命令</param>
/// <param name="commandParameters">执行命令所使用的OracleParameters数组</param>
/// <returns> 返回命令所影响的行数</returns>
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;
}
}
/// <summary>
/// 基于连接字符串的数据库执行一个查询，返回数据流sqldatareader
/// 可以使用参数集传入命令参数
/// 例子：SqldataReader r=ExecuteReader(connstring,commandtype.storedprocedure,"publishorders",new OracleParameter("@prodid",24));
///
/// </summary>
/// <param name="connectionString">为OracleConnection提供连接的字符串</param>
/// <param name="cmdType">命令类型（commandtype.storedprocedure,commandtype.text,...）</param>
/// <param name="cmdText">T-SQL命令存储过程名或者是</param>
/// <param name="commandParameters">执行命令所用的sqlparamters</param>
/// <returns></returns>
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;
}
}
/// <summary>
/// 基于连接字符串的数据库执行一个查询，返回数据流sqldatareader
/// 可以使用参数集传入命令参数
/// </summary>
/// <returns></returns>
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];
}
}
}
}
/// <summary>
/// 获取一个数据表格
/// </summary>
/// <returns></returns>
public static void DoExcute(string cmdText)
{
OracleHelperMS.ExecuteNonQuery(OracleHelperMS.Constr, CommandType.Text, cmdText, null);
}
/// <summary>
/// 获取一个数据表格
/// </summary>
/// <returns></returns>
public static DataTable getDataTable(string cmdText)
{
return OracleHelperMS.PDataTable(OracleHelperMS.Constr, CommandType.Text, cmdText, null);
}
/// <summary>
/// 获取一行数据
/// </summary>
/// <returns></returns>
public static DataRow getDataRow(string cmdText)
{
return OracleHelperMS.PDataRow(OracleHelperMS.Constr, CommandType.Text, cmdText, null);
}
/// <summary>
/// 基于连接字符串的数据库执行一个查询，返回数据流sqldatareader
/// 可以使用参数集传入命令参数
/// </summary>
/// <returns></returns>
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;
}
}
}
}
}
/// <summary>
/// 基于连接字符串的数据库执一个查询，返回结果集得第一行第一列的数据对象
/// 可以使用参数集传入命令参数
/// 例子：object boj=ExecuteScalar(connstring,commandType.storedprocedure,"publishoreders",new OracleParameter("@prodid",24));
///
/// </summary>
/// <param name="connectionString">为OracleConnection提供的连接字符串</param>
/// <param name="cmdType">命令类型(commandType.storedprocedure,commandtype.text,......)</param>
/// <param name="cmdText">存储过程名或者T-SQL命令</param>
/// <param name="commandParameters">执行命令所使用的sqlparamters数组</param>
/// <returns>返回结果的第一行第一列</returns>
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;
}
}
}
/// <summary>
/// 基于连接对象的数据库执行一个查询，返回结果的第一行第一列的数据对象
/// 可以使用参数集传入命令参数
/// 例子：object obj =executescalar(conn,commandtype.storedprocedure,"publishOreders",new sqlparamters("@prodid",24));
///
/// </summary>
/// <param name="connection">一个存在的连接对象</param>
/// <param name="cmdType">命令类型（commandtype.storedprocedure,commondtype.text,....）</param>
/// <param name="cmdText">存储过程名或者T-SQL命令</param>
/// <param name="commandParameters">执行命令所使用的sqlparamters数组</param>
/// <returns>返回结果的第一行第一列</returns>
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;
}
}
/// <summary>
/// 执行Insert
/// </summary>
/// <param name="ATable"></param>
/// <param name="AFields"></param>
/// <param name="AValues"></param>
/// <returns></returns>
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);
}
/// <summary>
/// 更新数据表
/// </summary>
/// <param name="ATable"></param>
/// <param name="AFields"></param>
/// <param name="AValues"></param>
/// <param name="ACondFields"></param>
/// <param name="ACondValues"></param>
/// <returns></returns>
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);
}
/// <summary>
/// 向缓冲区添加参数
/// </summary>
/// <param name="cacheKey">参数集对象在缓冲区中的key</param>
/// <param name="commandParamters">要缓存的参数集对象</param>
public static void CacheParameters(string cacheKey, params OracleParameter[] commandParamters)
{
parmCache[cacheKey] = commandParamters;
}
/// <summary>
/// 从缓冲区读取参数集对象
/// </summary>
/// <param name="cacheKey">参数集对象在缓冲区的key</param>
/// <returns>被缓存的参数集对象</returns>
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;
}
/// <summary>
/// Oracle执行事务
/// </summary>
/// <param name="sqls"></param>
/// <returns></returns>
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;
}
}
}
/// <summary>
/// 更新datatable
/// </summary>
/// <param name="connectionString"></param>
/// <param name="GV"></param>
/// <param name="selectTab"></param>
/// <param name="cmdUpText"></param>
/// <param name="uprow"></param>
/// <param name="commandParameters"></param>
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哥讨论
网友评论