using System.Data;
|
using Oracle.ManagedDataAccess.Client;
|
|
namespace MES.Service.DB;
|
|
/// <summary>
|
/// 用于Oracle数据库访问的帮助类
|
/// </summary>
|
public class OracleSQLHelper
|
{
|
public const string ConnectionString =
|
"Data Source = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.107)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = ORCL))); Persist Security Info=True;User ID = xm_dev; Password=xmdev";
|
|
private readonly string _connectionString;
|
|
//public static string connectionString = "Data Source = 192.168.1.19; Persist Security Info=True;User ID = abtdev; Password=abt_dev";
|
public string connectionString =
|
"Data Source = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.107)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = ORCL))); Persist Security Info=True;User ID = xm_dev; Password=xmdev"; // 这里替换成你的实际连接字符串
|
|
public OracleSQLHelper()
|
{
|
_connectionString = connectionString;
|
}
|
|
public OracleSQLHelper(string connectionString)
|
{
|
_connectionString = connectionString;
|
}
|
|
public DataTable ExecuteQuery(string query)
|
{
|
using (var connection = new OracleConnection(_connectionString))
|
{
|
using (var command = new OracleCommand(query, connection))
|
{
|
connection.Open();
|
var dataTable = new DataTable();
|
using (var reader = command.ExecuteReader())
|
{
|
dataTable.Load(reader);
|
}
|
|
return dataTable;
|
}
|
}
|
}
|
|
// 执行非查询操作 对数据库进行 增、删、改 操作(1）
|
public int ExecuteNonQuery(string query)
|
{
|
using (var connection = new OracleConnection(_connectionString))
|
{
|
using (var command = new OracleCommand(query, connection))
|
{
|
connection.Open();
|
return command.ExecuteNonQuery();
|
}
|
}
|
}
|
|
public int ExecuteNonQueryWithTransaction(OracleConnection connection,
|
OracleTransaction transaction, string query)
|
{
|
using (var command = connection.CreateCommand())
|
{
|
command.Transaction = transaction;
|
command.CommandText = query;
|
return command.ExecuteNonQuery();
|
}
|
}
|
|
public int ExecuteTransaction(string query1)
|
{
|
using (var connection = new OracleConnection(_connectionString))
|
{
|
connection.Open();
|
|
var transaction = connection.BeginTransaction();
|
var totalRowsAffected = 0;
|
|
try
|
{
|
totalRowsAffected =
|
ExecuteNonQueryWithTransaction(connection, transaction,
|
query1);
|
|
transaction.Commit();
|
}
|
catch (Exception ex)
|
{
|
transaction.Rollback();
|
throw new Exception(ex.Message);
|
}
|
|
return totalRowsAffected;
|
}
|
}
|
|
|
// 执行查询并返回单个值
|
public object ExecuteScalar(string query)
|
{
|
using (var connection = new OracleConnection(_connectionString))
|
{
|
using (var command = new OracleCommand(query, connection))
|
{
|
connection.Open();
|
return command.ExecuteScalar();
|
}
|
}
|
}
|
|
|
// 执行存储过程
|
//OracleParameter[] parameters = new OracleParameter[]
|
//{
|
// new OracleParameter("param1", value1),
|
// new OracleParameter("param2", value2)
|
//};
|
// sqlHelper.ExecuteStoredProcedure("存储过程名称", parameters);
|
public void ExecuteStoredProcedure(string procedureName,
|
OracleParameter[] parameters)
|
{
|
using (var connection = new OracleConnection(_connectionString))
|
{
|
using (var command =
|
new OracleCommand(procedureName, connection))
|
{
|
command.CommandType = CommandType.StoredProcedure;
|
command.Parameters.AddRange(parameters);
|
connection.Open();
|
command.ExecuteNonQuery();
|
}
|
}
|
}
|
|
public DataTable ExecuteDataTable(string sql)
|
{
|
return ExecuteDataTable(sql, CommandType.Text, null);
|
}
|
|
public DataTable ExecuteDataTable(string sql, CommandType commandType)
|
{
|
return ExecuteDataTable(sql, commandType, null);
|
}
|
|
public DataTable ExecuteDataTable(string sql, CommandType commandType,
|
OracleParameter[] parameters)
|
{
|
var data = new DataTable();
|
using (var connection = new OracleConnection(_connectionString))
|
{
|
using (var command = new OracleCommand(sql, connection))
|
{
|
command.CommandType = commandType;
|
if (parameters != null)
|
command.Parameters.AddRange(parameters);
|
|
using (var adapter = new OracleDataAdapter(command))
|
{
|
adapter.Fill(data);
|
}
|
}
|
}
|
|
return data;
|
}
|
|
|
// public int ExecuteStoredProcedure(string procedureName, params OracleParameter[] parameters)
|
// {
|
// int result = 0; // 存储过程返回值
|
//
|
// // 创建数据库连接对象并指定连接字符串
|
// using (OracleConnection connection =
|
// new OracleConnection(connectionString))
|
// {
|
// // 创建命令对象并指定要执行的存储过程名称
|
// using (OracleCommand command =
|
// new OracleCommand(procedureName, connection))
|
// {
|
// command.CommandType = CommandType.StoredProcedure;
|
//
|
// // 添加存储过程参数
|
// foreach (OracleParameter parameter in parameters)
|
// {
|
// command.Parameters.Add(parameter);
|
// }
|
//
|
// // 添加输出参数
|
// OracleParameter outputParameter =
|
// new OracleParameter("C_Result", OracleDbType.Int32);
|
// outputParameter.Direction = ParameterDirection.Output;
|
// command.Parameters.Add(outputParameter);
|
//
|
// OracleParameter messageParameter =
|
// new OracleParameter("C_Msg", OracleDbType.Varchar2,
|
// 1000);
|
// messageParameter.Direction = ParameterDirection.Output;
|
// command.Parameters.Add(messageParameter);
|
//
|
// try
|
// {
|
// // 打开数据库连接并执行存储过程
|
// connection.Open();
|
// command.ExecuteNonQuery();
|
//
|
// // 获取输出参数的值
|
// OracleDecimal oracleDecimalValue =
|
// (OracleDecimal)outputParameter.Value;
|
// decimal decimalValue = 0;
|
//
|
// if (oracleDecimalValue != OracleDecimal.Null)
|
// {
|
// decimalValue = oracleDecimalValue.Value;
|
// }
|
//
|
// result = Convert.ToInt32(decimalValue);
|
// string message = messageParameter.Value.ToString();
|
// if (result == 1)
|
// {
|
// throw new Exception(message);
|
// }
|
// }
|
// catch (Exception ex)
|
// {
|
// // 处理异常
|
// throw new Exception(ex.Message);
|
// }
|
// }
|
// }
|
// return result;
|
// }
|
}
|
