using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
namespace Utils
{
public static class OracleHelper
{
public static readonly string connString =ConfigurationManager.ConnectionStrings["oracleConnstring"].ConnectionString;
/// <summary>
/// ExecuteScalar
/// 第一行第一列
/// </summary>
/// <param name="strSql"></param>
/// <param name="parms"></param>
/// <returns></returns>
public static object ExecuteDataScalar(string strSql, params OracleParameter[] parms)
{
using (OracleConnection conn = new OracleConnection(connString))
{
OracleCommand cmd = new OracleCommand(strSql, conn);
//cmd.CommandType = CommandType.StoredProcedure; //存储过程
cmd.CommandType = CommandType.Text; //Sql语句
cmd.Parameters.Clear();
cmd.Parameters.AddRange(parms);
conn.Open();
var result = cmd.ExecuteScalar();
return result;
}
}
/// <summary>
/// SqlDataReader
/// </summary>
/// <param name="strSql"></param>
/// <param name="parms"></param>
/// <returns></returns>
public static OracleDataReader ExecuteDataReader(string strSql, params OracleParameter[] parms)
{
using (OracleConnection conn = new OracleConnection(connString))
{
OracleCommand cmd = new OracleCommand(strSql, conn);
//cmd.CommandType = CommandType.StoredProcedure; //存储过程
cmd.CommandType = CommandType.Text; //Sql语句
cmd.Parameters.Clear();
cmd.Parameters.AddRange(parms);
OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
}
/// <summary>
/// DataTable
/// </summary>
/// <param name="strSql"></param>
/// <param name="parms"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string strSql, params SqlParameter[] parms)
{
using (OracleConnection conn = new OracleConnection(connString))
{
OracleCommand cmd = new OracleCommand(strSql, conn);
//cmd.CommandType = CommandType.StoredProcedure; //存储过程
cmd.CommandType = CommandType.Text; //Sql语句
cmd.Parameters.Clear();
cmd.Parameters.AddRange(parms);
conn.Open();
DataTable dt = new DataTable();
OracleDataAdapter adapter = new OracleDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dt);
return dt;
}
}
/// <summary>
/// 增删改
/// </summary>
/// <param name="strSql"></param>
/// <param name="parms"></param>
/// <returns></returns>
public static int ExecuteDataNonQuery(string strSql, params SqlParameter[] parms)
{
using (OracleConnection conn = new OracleConnection(connString))
{
OracleCommand cmd = new OracleCommand(strSql, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
cmd.Parameters.AddRange(parms);
conn.Open();
int k = cmd.ExecuteNonQuery();
return k;
}
}
/// <summary>
/// 事务执行
/// </summary>
/// <param name="comList"></param>
/// <returns></returns>
public static bool ExecuteDataTrans(List<CommandInfo> comList)
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = trans;
try
{
int count = 0;
for (int i = 0; i < comList.Count; i++)
{
cmd.CommandText = comList[i].CommandText;
//存储过程 or Sql语句
if (comList[i].IsProc)
cmd.CommandType = CommandType.StoredProcedure;
else
cmd.CommandType = CommandType.Text;
if (comList[i].Parameters.Length > 0)
{
cmd.Parameters.Clear();
cmd.Parameters.AddRange(comList[i].Parameters);
}
count += cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
trans.Commit();
return true;
}
catch (Exception ex)
{
trans.Rollback();
throw new Exception("执行事务出现异常", ex);
}
}
}
}
}
C#中OracleHelper工具类的实现
于 2023-04-21 13:59:08 首次发布