using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.Common;
|
using CSFramework.DB.Common;
|
using CSFramework.DB.Models;
|
using Oracle.ManagedDataAccess.Client;
|
|
namespace CSFramework.DB
|
{
|
public class DatabaseOracle : IDatabase
|
{
|
private string _ConnectionString;
|
|
public DatabaseOracle(string connectionString)
|
{
|
_ConnectionString = connectionString;
|
}
|
|
/// <summary>
|
/// 当前使用系统的用户编号
|
/// </summary>
|
public static string CurrentUserID { get; set; }
|
|
public DatabaseType DatabaseType => DatabaseType.Oracle;
|
|
public CommandHelper CreateSqlProc(string spName)
|
{
|
var sp =
|
new CommandHelper(this, spName, CommandType.StoredProcedure);
|
sp.AddParamOracleCursor("pkg_return");
|
return sp;
|
}
|
|
public CommandHelper CreateCommand(string commandText)
|
{
|
return new CommandHelper(this, commandText, CommandType.Text);
|
}
|
|
public int CommandTimeout
|
{
|
get
|
{
|
var defaultTimeOut = 30;
|
return DatabaseFactory.CommandTimeOut > 0
|
? DatabaseFactory.CommandTimeOut
|
: defaultTimeOut;
|
}
|
}
|
|
public int ConnectionTimeout
|
{
|
get
|
{
|
var defaultTimeOut = 15;
|
return DatabaseFactory.ConnectionTimeOut > 0
|
? DatabaseFactory.ConnectionTimeOut
|
: defaultTimeOut;
|
}
|
}
|
|
public string BuildConnectionString(string server, int port,
|
string dbName, string uid, string pwd, int timeout = 15)
|
{
|
//SERVER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=服务器地址)(PORT=端口号)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=数据库名称)));User Id=用户名;Password=密码;Persist Security Info=True;Enlist=true;Max Pool Size=300;Min Pool Size=0;Connection Lifetime=300;
|
//const string DEF_SQL_CONNECTION = "SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));User Id={3};Password={4};";
|
const string DEF_SQL_CONNECTION =
|
"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));User Id={3};Password={4}";
|
var connstr = string.Format(DEF_SQL_CONNECTION, server, port,
|
dbName, uid, pwd);
|
return connstr;
|
}
|
|
public int DefaultPort => 1521;
|
|
|
/// <summary>
|
/// 服务器时间
|
/// </summary>
|
public DateTime GetServerTime()
|
{
|
var o = ExecuteScalar("SELECT sysdate FROM dual;");
|
return Convert.ToDateTime(o);
|
}
|
|
public Type DateTimeType =>
|
typeof(Oracle.ManagedDataAccess.Types.OracleDate);
|
|
/// <summary>
|
/// 参数符号.如: WHERE Code=:Code
|
/// </summary>
|
public string ParamSymboName { get; set; } = ":";
|
|
/// <summary>
|
/// 创建时间戳参数
|
/// </summary>
|
/// <param name="parameterName">参数名</param>
|
/// <param name="fieldName">字段名</param>
|
/// <returns></returns>
|
public DbParameter CreateTimestampParameter(string parameterName,
|
string fieldName)
|
{
|
var p = new OracleParameter();
|
p.ParameterName = parameterName;
|
p.DbType = DbType.DateTime;
|
p.Size = 8;
|
p.SourceColumn = fieldName;
|
return p;
|
}
|
|
public string ConnectionString
|
{
|
get => _ConnectionString;
|
set => _ConnectionString = value;
|
}
|
|
public void Close(DbConnection connection)
|
{
|
if (connection != null)
|
try
|
{
|
if (connection.State != ConnectionState.Closed)
|
connection.Close();
|
connection.Dispose();
|
}
|
catch
|
{
|
}
|
}
|
|
public DbCommand CreateCommand(string commandText,
|
CommandType commandType)
|
{
|
return new OracleCommand(commandText)
|
{
|
CommandText = commandText,
|
CommandType = commandType,
|
CommandTimeout = CommandTimeout
|
};
|
}
|
|
public DbCommandBuilder CreateCommandBuilder()
|
{
|
return new OracleCommandBuilder();
|
}
|
|
public DbDataAdapter CreateDataAdapter()
|
{
|
return new OracleDataAdapter();
|
}
|
|
public DbParameter CreateParameter(string parameterName,
|
object parameterValue)
|
{
|
parameterName = ParseParamName(parameterName);
|
return new OracleParameter
|
{
|
ParameterName = parameterName, Value = parameterValue,
|
IsNullable = true
|
};
|
}
|
|
public DbParameter CreateParameter(string parameterName,
|
DbType parameterType, int size, string sourceColumn,
|
object parameterValue)
|
{
|
parameterName = ParseParamName(parameterName);
|
return new OracleParameter
|
{
|
ParameterName = parameterName, DbType = parameterType,
|
Size = size, SourceColumn = sourceColumn,
|
Value = parameterValue, IsNullable = true
|
};
|
}
|
|
public DbConnection CreateConnection()
|
{
|
return CreateConnection(_ConnectionString);
|
}
|
|
public DbConnection CreateConnection(string connectionString)
|
{
|
DbConnection DBConn = new OracleConnection(connectionString);
|
DBConn.Open(); //打开连接
|
return DBConn;
|
}
|
|
public DbTransaction TransBegin()
|
{
|
return CreateConnection().BeginTransaction();
|
}
|
|
public void TransCommit(DbTransaction trans,
|
bool closeConnection = false)
|
{
|
trans.Commit();
|
if (closeConnection) Close(trans.Connection);
|
}
|
|
public void TransRollback(DbTransaction trans,
|
bool closeConnection = false)
|
{
|
trans.Rollback();
|
if (closeConnection) Close(trans.Connection);
|
}
|
|
public int ExecuteCommand(DbCommand cmd)
|
{
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null && cmd.Transaction == null)
|
{
|
cmd.Connection =
|
connection = CreateConnection(_ConnectionString);
|
SetContext(cmd.Connection as OracleConnection);
|
}
|
|
return cmd.ExecuteNonQuery();
|
}
|
finally
|
{
|
if (cmd.Transaction == null) Close(connection);
|
}
|
}
|
|
public DbDataReader ExecuteReader(string SQL)
|
{
|
DbCommand cmd = null;
|
DbDataReader o = null;
|
DbConnection conn = null;
|
try
|
{
|
conn = CreateConnection(_ConnectionString);
|
cmd = new OracleCommand();
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
cmd.CommandType = CommandType.Text;
|
cmd.Connection = conn;
|
SetContext(cmd.Connection as OracleConnection);
|
o = cmd.ExecuteReader();
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (cmd != null) cmd.Dispose();
|
Close(conn);
|
}
|
|
return o;
|
}
|
|
|
public DbDataReader ExecuteReader(DbCommand cmd)
|
{
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
{
|
cmd.Connection = connection = CreateConnection();
|
SetContext(cmd.Connection as OracleConnection);
|
}
|
|
return cmd.ExecuteReader();
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
public T ExecuteReader<T>(DbCommand cmd) where T : new()
|
{
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
{
|
cmd.Connection = connection = CreateConnection();
|
SetContext(cmd.Connection as OracleConnection);
|
}
|
|
var obj = default(T);
|
using (var dataReader = cmd.ExecuteReader())
|
{
|
if (dataReader.Read())
|
obj = DbTools.Convert2Object<T>(dataReader);
|
}
|
|
return obj;
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
public T ExecuteReader<T>(string SQL) where T : new()
|
{
|
using (var connection = CreateConnection())
|
{
|
using (var command = CreateCommand(SQL, CommandType.Text))
|
{
|
command.Connection = connection;
|
SetContext(command.Connection as OracleConnection);
|
var obj = default(T);
|
using (var dataReader = command.ExecuteReader())
|
{
|
if (dataReader.Read())
|
obj = DbTools.Convert2Object<T>(dataReader);
|
}
|
|
return obj;
|
}
|
}
|
}
|
|
public List<T> ExecuteReader<T>(string SQL,
|
Func<DbDataReader, T> action) where T : new()
|
{
|
using (var connection = CreateConnection())
|
{
|
using (var command = CreateCommand(SQL, CommandType.Text))
|
{
|
command.Connection = connection;
|
SetContext(command.Connection as OracleConnection);
|
var list = new List<T>();
|
using (var dataReader = command.ExecuteReader())
|
{
|
while (dataReader.Read())
|
list.Add(action.Invoke(dataReader));
|
}
|
|
return list;
|
}
|
}
|
}
|
|
public List<T> ExecuteReader<T>(DbCommand cmd,
|
Func<DbDataReader, T> action) where T : new()
|
{
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
{
|
cmd.Connection = connection = CreateConnection();
|
SetContext(cmd.Connection as OracleConnection);
|
}
|
|
var list = new List<T>();
|
using (var dataReader = cmd.ExecuteReader())
|
{
|
while (dataReader.Read())
|
list.Add(action.Invoke(dataReader));
|
}
|
|
return list;
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
|
public List<T> ExecuteReaderList<T>(string SQL) where T : new()
|
{
|
DbConnection connection = null;
|
DbCommand command = null;
|
DbDataReader dataReader = null;
|
|
try
|
{
|
using (connection = CreateConnection())
|
{
|
using (command = CreateCommand(SQL, CommandType.Text))
|
{
|
command.Connection = connection;
|
var list = new List<T>();
|
using (dataReader = command.ExecuteReader())
|
{
|
while (dataReader.Read())
|
list.Add(DbTools.Convert2Object<T>(dataReader));
|
}
|
|
return list;
|
}
|
}
|
}
|
finally
|
{
|
if (connection != null) connection.Dispose();
|
if (command != null) command.Dispose();
|
if (dataReader != null) dataReader.Dispose();
|
}
|
}
|
|
public List<string> GetStringList(string SQL)
|
{
|
DbConnection connection = null;
|
DbCommand command = null;
|
DbDataReader dataReader = null;
|
|
try
|
{
|
using (connection = CreateConnection())
|
{
|
using (command = CreateCommand(SQL, CommandType.Text))
|
{
|
command.Connection = connection;
|
var list = new List<string>();
|
using (dataReader = command.ExecuteReader())
|
{
|
while (dataReader.Read())
|
list.Add(dataReader.GetValue(0)
|
.ToString()); //取第1列
|
}
|
|
return list;
|
}
|
}
|
}
|
finally
|
{
|
if (connection != null) connection.Dispose();
|
if (command != null) command.Dispose();
|
if (dataReader != null) dataReader.Dispose();
|
}
|
}
|
|
public List<string> GetStringList(DbCommand cmd)
|
{
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
{
|
connection = CreateConnection();
|
cmd.Connection = connection;
|
}
|
|
var list = new List<string>();
|
using (var dataReader = cmd.ExecuteReader())
|
{
|
while (dataReader.Read())
|
list.Add(dataReader.GetValue(0).ToString()); //取第1列
|
}
|
|
return list;
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
public List<T> ExecuteReaderList<T>(DbCommand cmd) where T : new()
|
{
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
cmd.Connection = connection = CreateConnection();
|
|
var list = new List<T>();
|
using (var dataReader = cmd.ExecuteReader())
|
{
|
while (dataReader.Read())
|
list.Add(DbTools.Convert2Object<T>(dataReader));
|
}
|
|
return list;
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
public object ExecuteScalar(string SQL)
|
{
|
DbCommand cmd = new OracleCommand();
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
cmd.CommandType = CommandType.Text;
|
return ExecuteScalar(cmd);
|
}
|
|
public object ExecuteScalar(DbCommand cmd)
|
{
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
{
|
cmd.Connection = connection = CreateConnection();
|
SetContext(cmd.Connection as OracleConnection);
|
}
|
|
var o = cmd.ExecuteScalar();
|
return o;
|
}
|
catch (Exception ex)
|
{
|
return null;
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
public int ExecuteSQL(string SQL)
|
{
|
using (var connection = CreateConnection())
|
{
|
return ExecuteSQL(SQL, connection);
|
}
|
}
|
|
/// <summary>
|
/// 在事务内提交数据
|
/// </summary>
|
/// <param name="trans">事务</param>
|
/// <param name="SQL">SQL语句</param>
|
/// <returns></returns>
|
public int ExecuteTrans(DbTransaction trans, string SQL)
|
{
|
DbCommand cmd = null;
|
var i = -1;
|
try
|
{
|
cmd = new OracleCommand();
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
cmd.CommandType = CommandType.Text;
|
cmd.Connection = trans.Connection;
|
cmd.Transaction = trans;
|
|
i = cmd.ExecuteNonQuery();
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (cmd != null) cmd.Dispose();
|
}
|
|
return i;
|
}
|
|
|
public int ExecuteTrans(DbTransaction trans, DbCommand cmd)
|
{
|
var i = -1;
|
try
|
{
|
cmd.Connection = trans.Connection;
|
cmd.Transaction = trans;
|
i = cmd.ExecuteNonQuery();
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (cmd != null) cmd.Dispose();
|
}
|
|
return i;
|
}
|
|
public DataSet GetDataSet(DbCommand cmd)
|
{
|
DbDataAdapter adapter = null;
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
{
|
connection = CreateConnection(_ConnectionString);
|
cmd.Connection = connection;
|
SetContext(cmd.Connection as OracleConnection);
|
}
|
|
var ds = new DataSet();
|
adapter = new OracleDataAdapter();
|
adapter.SelectCommand = cmd;
|
adapter.Fill(ds);
|
|
return ds;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (adapter != null) adapter.Dispose();
|
if (cmd != null) cmd.Dispose();
|
Close(connection);
|
}
|
}
|
|
public DataSet GetDataSet(string SQL)
|
{
|
DbConnection connection = null;
|
try
|
{
|
connection = CreateConnection(_ConnectionString);
|
SetContext(connection as OracleConnection);
|
return GetDataSet(SQL, connection);
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
/// <summary>
|
/// 执行带参数的 Sql 语句或存储过程，并返回 DataSet 对象。
|
/// </summary>
|
/// <param name="SQL">要执行的 Sql 语句或存储过程名等。</param>
|
/// <param name="type">CommandType 参数类型，即该命令是 sql 语句，还是存储过程名等。</param>
|
/// <param name="paramlist">参数集合。</param>
|
/// <returns>DataSet 对象。</returns>
|
public virtual DataSet GetDataSet(string SQL, CommandType type,
|
IDataParameter[] paramlist)
|
{
|
DbCommand cmd = null;
|
DbDataAdapter adapter = null;
|
DbConnection connection = null;
|
try
|
{
|
connection = CreateConnection();
|
|
cmd = CreateCommand(SQL, type);
|
cmd.Connection = connection;
|
SetContext(cmd.Connection as OracleConnection);
|
|
if (paramlist != null)
|
cmd.Parameters.AddRange(paramlist);
|
|
var ds = new DataSet();
|
adapter = CreateDataAdapter();
|
adapter.SelectCommand = cmd;
|
adapter.Fill(ds);
|
|
return ds;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (adapter != null) adapter.Dispose();
|
if (cmd != null) cmd.Dispose();
|
Close(connection);
|
}
|
}
|
|
public DataTable GetTable(string SQL, string tableName = "")
|
{
|
var dt = GetDataSet(SQL).Tables[0];
|
if (tableName != "") dt.TableName = tableName;
|
return dt;
|
}
|
|
public DataTable GetTop(int top, string tableName, string fields = "",
|
List<DbParameter> where = null, string orderBy = "")
|
{
|
var cmd = CreateCommand("");
|
|
fields = string.IsNullOrWhiteSpace(fields) ? "*" : fields;
|
orderBy = string.IsNullOrEmpty(orderBy)
|
? ""
|
: " ORDER BY " + orderBy;
|
|
var whereSQL = "";
|
if (where != null && where.Count > 0)
|
{
|
string pName;
|
foreach (var p in where)
|
{
|
pName = p.ParameterName;
|
if (pName.IndexOf(ParamSymboName) >= 0) //参数名称带有符号,如：@Code
|
whereSQL = whereSQL +
|
$" AND {pName.Replace(ParamSymboName, "")}={p.ParameterName}";
|
else
|
whereSQL = whereSQL +
|
$" AND {p.ParameterName}={ParamSymboName + p.ParameterName}";
|
|
cmd.Command.Parameters.Add(p);
|
}
|
}
|
|
whereSQL = string.IsNullOrEmpty(whereSQL)
|
? ""
|
: " WHERE ROWNUM<= " + top.ToString() + whereSQL;
|
|
var SQL = $"SELECT {fields} FROM {tableName} {whereSQL} {orderBy}";
|
|
cmd.Command.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
return GetTable(cmd.Command, tableName);
|
}
|
|
public DataTable GetTable(DbCommand cmd, string tableName = "")
|
{
|
var dt = GetDataSet(cmd).Tables[0];
|
if (tableName != "") dt.TableName = tableName;
|
return dt;
|
}
|
|
public DataRow GetDataRow(string SQL)
|
{
|
var dt = GetDataSet(SQL).Tables[0];
|
return dt.Rows.Count > 0 ? dt.Rows[0] : null;
|
}
|
|
/// <summary>
|
/// ORACLE数据类型转DbType
|
/// </summary>
|
/// <param name="sourceType">ORACLE数据类型</param>
|
/// <returns></returns>
|
public DbType ToDbType(string sourceType)
|
{
|
return DBDataTypes.GetDbTypeByOracleType(sourceType);
|
}
|
|
public Type ToNetType(string sourceType)
|
{
|
return DBDataTypes.GetNetTypeByOracle(sourceType);
|
}
|
|
public DataTable meta_GetTableNames(string dbName = "")
|
{
|
throw new NotImplementedException("方法未实现！");
|
}
|
|
public DataTable meta_GetTableStru(string tableName)
|
{
|
throw new NotImplementedException("方法未实现！");
|
}
|
|
public DataTable meta_GetDatabase()
|
{
|
throw new NotImplementedException("方法未实现！");
|
}
|
|
public List<MetaDBNames> meta_GetDatabaseList()
|
{
|
throw new NotImplementedException("方法未实现！");
|
}
|
|
public List<MetaTableNames> meta_GetTableNamesList(string dbName = "")
|
{
|
throw new NotImplementedException("方法未实现！");
|
}
|
|
public List<MetaTableStructure> meta_GetTableStruList(string tableName)
|
{
|
throw new NotImplementedException("方法未实现！");
|
}
|
|
/// <summary>
|
/// 返回包含参数符号的参数名称,比如：@Code, :Code, ?p_Code
|
/// </summary>
|
/// <param name="paramName"></param>
|
/// <returns></returns>
|
public string ParseParamName(string paramName)
|
{
|
if (string.IsNullOrEmpty(ParamSymboName) ||
|
string.IsNullOrEmpty(paramName))
|
{
|
return paramName;
|
}
|
else
|
{
|
if (paramName.IndexOf(ParamSymboName) == 0) //首字母包含
|
return paramName;
|
else
|
return ParamSymboName + paramName; //添加参数符号
|
}
|
}
|
|
public List<MetaStoreProcedure> meta_GetStoreProcList(
|
string dbName = "")
|
{
|
var sql =
|
$"select object_name as name,type from all_procedures WHERE type IN ('PROCEDURE','FUNCTION');";
|
|
CodeSafeHelper.IsCheckSQM = false;
|
var result = new List<MetaStoreProcedure>();
|
var sp = CreateCommand(sql);
|
var dt = GetTable(sp.Command);
|
foreach (DataRow R in dt.Rows)
|
{
|
if (R["type"].ToString().Trim().ToUpper() == "PROCEDURE")
|
result.Add(new MetaStoreProcedure
|
{
|
Type = MetaStoreProcedureType.StoreProcedure,
|
Name = R["name"].ToString()
|
});
|
if (R["type"].ToString().Trim().ToUpper() == "FUNCTION")
|
result.Add(new MetaStoreProcedure
|
{
|
Type = MetaStoreProcedureType.Function,
|
Name = R["name"].ToString()
|
});
|
}
|
|
return result;
|
}
|
|
/// <summary>
|
/// 获取当前表最新(最大)的自增字段值
|
/// </summary>
|
/// <param name="tableName">表名</param>
|
/// <returns></returns>
|
public int GetMaxID(string tableName)
|
{
|
////mssql写法
|
//string sql = $"select ident_current('{tableName}');";
|
//var o = ExecuteScalar(sql);
|
//return o == DBNull.Value || o == null ? 0 : Convert.ToInt32(o);
|
throw new Exception("方法未实现！");
|
}
|
|
public bool UpdateDataSet(DataTable ds, string tableName, string KEY)
|
{
|
//先删除原表数据
|
//var sql = "";
|
//var ErrorMsg = "";
|
//foreach (DataRow dr in ds.Rows)
|
//{
|
// sql = string.Format(@"delete from {0} where {1} in ('{2}')", tableName, KEY, dr[KEY]);
|
// break;
|
//}
|
|
//var a = ExecuteSQL(sql);
|
//var colMapping = new SqlBulkCopyColumnMapping[ds.Columns.Count];
|
//for (var i = 0; i < ds.Columns.Count; i++)
|
//{
|
// colMapping[i] = new SqlBulkCopyColumnMapping(ds.Columns[i].ColumnName, ds.Columns[i].ColumnName);
|
//}
|
|
//DataTableToSQLServer(ds, _ConnectionString, tableName, colMapping, ref ErrorMsg);
|
return true;
|
}
|
|
|
public bool InstDataSet(DataTable ds, string tableName)
|
{
|
//先删除原表数据
|
//var ErrorMsg = "";
|
//var colMapping = new SqlBulkCopyColumnMapping[ds.Columns.Count];
|
//for (var i = 0; i < ds.Columns.Count; i++)
|
//{
|
// colMapping[i] = new SqlBulkCopyColumnMapping(ds.Columns[i].ColumnName, ds.Columns[i].ColumnName);
|
//}
|
|
//DataTableToSQLServer(ds, _ConnectionString, tableName, colMapping, ref ErrorMsg);
|
return true;
|
}
|
|
public int ExecuteSQL(string SQL, DbConnection conn)
|
{
|
DbCommand cmd = null;
|
var i = -1;
|
try
|
{
|
cmd = new OracleCommand();
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
cmd.CommandType = CommandType.Text;
|
cmd.Connection = conn;
|
i = cmd.ExecuteNonQuery();
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (cmd != null) cmd.Dispose();
|
}
|
|
return i;
|
}
|
|
public DataSet GetDataSet(string SQL, DbConnection conn)
|
{
|
DbCommand cmd = null;
|
DbDataAdapter adapter = null;
|
try
|
{
|
cmd = new OracleCommand();
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
cmd.CommandType = CommandType.Text;
|
cmd.Connection = conn;
|
|
var ds = new DataSet();
|
adapter = new OracleDataAdapter();
|
adapter.SelectCommand = cmd;
|
adapter.Fill(ds);
|
return ds;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (adapter != null) adapter.Dispose();
|
if (cmd != null) cmd.Dispose();
|
}
|
}
|
|
/// <summary>
|
/// Oracle内部权限机制控制用户数据查询
|
/// </summary>
|
/// <param name="connection"></param>
|
/// <returns></returns>
|
public int SetContext(OracleConnection connection)
|
{
|
try
|
{
|
var cmd = new OracleCommand("sp_SetContext");
|
cmd.Connection = connection;
|
cmd.CommandType = CommandType.StoredProcedure;
|
cmd.Parameters.Add("In_Userid", CurrentUserID);
|
var iValue = cmd.ExecuteNonQuery();
|
return iValue;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
#region
|
|
protected AdapterRowUpdatingEvent _MyRowUpdatingEvent = null;
|
|
public DbDataAdapter CreateDataAdapter(
|
AdapterRowUpdatingEvent eventHandler)
|
{
|
_MyRowUpdatingEvent = eventHandler;
|
|
var adp = new OracleDataAdapter();
|
adp.RowUpdating += Adp_RowUpdating;
|
|
return adp;
|
}
|
|
private void Adp_RowUpdating(object sender,
|
OracleRowUpdatingEventArgs e)
|
{
|
if (_MyRowUpdatingEvent != null) _MyRowUpdatingEvent(sender, e);
|
}
|
|
#endregion
|
}
|
}
|
