访问oracle的类
不是原创，参照好多人的写法，在他人的基础上修改增加的。
oracleclients类，framework 4 开始，虽然也可以使用，但已经有过期的提示了。微软不再提供了。比较狠的一招。
试用了一下 devart的 dotnet for oracle。的oracledatatable 。挺好用，可惜只能试用1个月。
留念一下
using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Collections.Specialized;
using System.Runtime.Remoting.Messaging;
using System.Data;
using System.Configuration;
using System.Data.OracleClient;
using System.Security.Permissions;
using System.Windows.Forms;
namespace APP.NET
{
/// <summary>
/// ADO.NET数据库操作基础类。
/// </summary>
public abstract class OraCommand
{
//数据库连接字符串
// private static string connectionString ;
protected static string connectionString = "Data Source=oradb;User Id=oraapp;Password=password;";
public OraCommand()
{
}
public static string conn
{
get { return connectionString; }
set { connectionString = value; }
}
#region 执行SQL语句，返回影响的记录数
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString"></param>
/// <returns></returns>
public static int ExecuteSql(string SQLString)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand(SQLString, connection))
{
connection.Open();
OracleTransaction tx = connection.BeginTransaction();
cmd.Transaction = tx;
try
{
cmd.CommandText = SQLString;
int rows = cmd.ExecuteNonQuery();
tx.Commit();
return rows;
}
catch (OracleException ex)
{
tx.Rollback();
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
}
#endregion
/*
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString"></param>
/// <returns></returns>
public static int ExecuteSql(string SQLString)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (OracleException ex)
{
throw new Exception(ex.Message);
}
}
}
}
*/
/// <summary>
/// 执行两条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLString1"></param>
/// <param name="SQLString2"></param>
public static void ExecuteSqlTran(string SQLString1, string SQLString2)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
connection.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;
OracleTransaction tx = connection.BeginTransaction();
cmd.Transaction = tx;
try
{
cmd.CommandText = SQLString1;
cmd.ExecuteNonQuery();
cmd.CommandText = SQLString2;
cmd.ExecuteNonQuery();
tx.Commit();
}
catch (OracleException ex)
{
tx.Rollback();
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务，每条语句以“;”分割。
/// </summary>
/// <param name="SQLStringList"></param>
public static void ExecuteSqlTran(string SQLStringList)
{
using (OracleConnection conn = new OracleConnection(connectionString))
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
string[] split = SQLStringList.Split(new Char[] { ';' });
foreach (string strsql in split)
{
if (strsql.Trim() != "")
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (OracleException ex)
{
tx.Rollback();
throw new Exception(ex.Message);
}
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString"></param>
/// <param name="content"></param>
/// <returns></returns>
public static int ExecuteSql(string SQLString, string content)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand(SQLString, connection);
OracleParameter myParameter = new OracleParameter("@content", SqlDbType.NText);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (OracleException ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 向数据库里插入图像格式的字段
/// </summary>
/// <param name="strSQL"></param>
/// <param name="fs"></param>
/// <returns></returns>
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand(strSQL, connection);
OracleParameter myParameter = new OracleParameter("@fs", SqlDbType.Image);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (OracleException ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（整数）。
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static int GetCount(string strSQL)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand(strSQL, connection);
try
{
connection.Open();
OracleDataReader result = cmd.ExecuteReader();
int i = 0;
while (result.Read())
{
i = result.GetInt32(0);
}
result.Close();
return i;
}
catch (OracleException ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString"></param>
/// <returns></returns>
public static object GetSingle(string SQLString)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand(SQLString, connection);
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (OracleException ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行查询语句，返回OracleDataReader
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static OracleDataReader ExecuteReader(string strSQL)
{
OracleConnection connection = new OracleConnection(connectionString);
//using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand(strSQL, connection);
OracleDataReader myReader;
try
{
connection.Open();
myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return myReader;
}
catch (OracleException ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose();
//connection.Close();
}
}
}
#region GetSchema(string strSQL) -- 执行查询语句，返回Datatable(表信息Schema).
public static DataTable GetSchema(string strSQL)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand(strSQL, connection);
OracleDataReader myReader;
try
{
connection.Open();
myReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable dt = myReader.GetSchemaTable();
return dt;
}
catch (OracleException ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
#endregion
#region 方法 Query(string SQLString) 执行查询语句，返回DataSet
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString"></param>
/// <returns></returns>
public static DataSet Query(string SQLString)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
OracleDataAdapter command = new OracleDataAdapter(SQLString, connection);
command.Fill(ds);
}
catch (OracleException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
#endregion
#region 方法： GetTable(SQLString) -- 执行查询语句，返回DataTable
/// <summary>
/// 执行查询语句，返回DataTable
/// zhang
/// 最后修改日期 2006-12-10
/// </summary>
/// <param name="SQLString"></param>
/// <returns></returns>
public static DataTable GetTable(string tablename)
{
string sqlQuery;
//MessageBox.Show(tablename.ToLower().IndexOf("select").ToString());
if (tablename.ToLower().IndexOf("select") >= 0)
{
sqlQuery = tablename;
}
else
{
sqlQuery = "select * from " + tablename;
}
//MessageBox.Show(sqlQuery);
using (OracleConnection connection = new OracleConnection(connectionString))
{
DataTable dt = new DataTable(tablename);
try
{
connection.Open();
OracleDataAdapter DataAdapter = new OracleDataAdapter(sqlQuery, connection);
DataAdapter.Fill(dt);
}
catch (OracleException ex)
{
throw new Exception(ex.Message);
}
return dt;
}
}
#endregion
#region 方法： GetTable(tablename,SQLString) -- 执行查询语句，返回DataTable
/// <summary>
/// 执行查询语句，返回DataTable
/// zhang
/// 最后修改日期 2006-12-10
/// </summary>
/// <param name="SQLString"></param>
/// <returns></returns>
public static DataTable GetTable(string tablename, string strFilter)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
DataTable dt = new DataTable(tablename);
try
{
connection.Open();
OracleDataAdapter DataAdapter = new OracleDataAdapter("select * from " + tablename + " " + strFilter, connection);
DataAdapter.Fill(dt);
}
catch (OracleException ex)
{
throw new Exception(ex.Message);
}
return dt;
}
}
#endregion
#region 方法： GetTable(viewname,tablename,strFilter) -- 执行查询语句，返回DataTable
/// <summary>
/// 执行查询语句，返回DataTable
/// zhang
/// 最后修改日期 2006-12-10
/// </summary>
/// <param name="SQLString"></param>
/// <returns></returns>
public static DataTable GetTable(string viewname, string tablename, string strFilter)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
DataTable dt = new DataTable(tablename);
try
{
connection.Open();
OracleDataAdapter DataAdapter = new OracleDataAdapter("select * from " + viewname + " " + strFilter, connection);
DataAdapter.Fill(dt);
}
catch (OracleException ex)
{
throw new Exception(ex.Message);
}
return dt;
}
}
#endregion
#region 存储过程操作
/// <summary>
/// 运行存储过程
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static OracleDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleDataReader returnReader;
connection.Open();
OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader();
//Connection.Close();
return returnReader;
}
}
private static OracleCommand BuildQueryCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
{
OracleCommand command = new OracleCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach ( OracleParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
return command;
}
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
OracleDataAdapter sqlDA = new OracleDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
}
public static DataTable RunProcedure(string storedProcName)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
connection.Open();
OracleCommand command = new OracleCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("dt", OracleType.Cursor).Direction = ParameterDirection.Output;
OracleDataAdapter DataAdapter = new OracleDataAdapter(command);
DataTable dt = new DataTable();
DataAdapter.Fill(dt);
connection.Close();
return dt;
}
}
#endregion
#region 新建
public static void New__(string package,DataTable table)
{
DataTable dtNew = table.GetChanges(DataRowState.Added);
string attr_ = "";
foreach (DataRow row in dtNew.Rows)
{
foreach (DataColumn column in dtNew.Columns)
{
attr_ += column.ColumnName + Convert.ToChar(31) + row[column].ToString() + Convert.ToChar(30);
}
}
using (OracleConnection connection = new OracleConnection(connectionString))
{
try
{
connection.Open();
OracleCommand Command = new OracleCommand("member_api.New__", connection);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Add("info_",OracleType.VarChar,100);
Command.Parameters["info_"].Direction = ParameterDirection.Output;
Command.Parameters.Add("objid_", OracleType.VarChar,100);
Command.Parameters["objid_"].Direction = ParameterDirection.Output;
Command.Parameters.Add("objversion_", OracleType.VarChar,100);
Command.Parameters["objversion_"].Direction = ParameterDirection.Output;
Command.Parameters.AddWithValue("attr_", attr_ );
Command.Parameters.AddWithValue("action_", "DO");
Command.ExecuteNonQuery();
}
catch (OracleException ex)
{
throw new Exception(ex.Message);
}
}
} //end New__
#endregion
}
}
