引用：Oracle.ManagedDataAccess
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
namespace WebService
{
public class DbConn
{
public static OracleConnection con;//连接对象
public DbConn()
{
//con = new OracleConnection(ConfigurationManager.AppSettings["DataSource"]);
con = new OracleConnection(ConfigurationManager.ConnectionStrings["DataSource"].ConnectionString);
}
public DbConn(string constr)
{
con = new OracleConnection(constr);
}
#region 打开数据库连接
/// <summary>
/// 打开数据库连接
/// </summary>
private static void Open()
{
//打开数据库连接
if (con.State == ConnectionState.Closed)
{
try
{
//打开数据库连接
con.Open();
}
catch (Exception e)
{
throw e;
}
}
}
#endregion
#region 关闭数据库连接
/// <summary>
/// 关闭数据库连接
/// </summary>
public static void Close()
{
//判断连接的状态是否已经打开
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
#endregion
#region 执行查询语句，返回OracleDataReader ( 注意：调用该方法后，一定要对OracleDataReader进行Close )
/// <summary>
/// 执行查询语句，返回OracleDataReader ( 注意：调用该方法后，一定要对OracleDataReader进行Close )
/// </summary>
/// <param name="sql">查询语句</param>
/// <returns>OracleDataReader</returns>
public static OracleDataReader ExecuteReader(string sql)
{
try
{
Open();
OracleDataReader myReader;
OracleCommand cmd = new OracleCommand(sql, con);
myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Dispose();
return myReader;
}
catch (OracleException e)
{
throw e;
}
#region
//List<EntitySpecOrder> orders = new List<EntitySpecOrder>();
//string s = " select * from LY_SPEC_ORDER ";
//OracleDataReader oracle = OR.ExecuteReader(s);
//while (oracle.Read())
//{
// EntitySpecOrder order = new EntitySpecOrder();
// order.Barcode = oracle["Barcode"].ToString();
// order.OrderCode = oracle["Order_Code"].ToString();
// order.OrderName = oracle["Order_Name"].ToString();
// order.OrderPrice = Convert.ToInt32(oracle["Order_Price"]);
// orders.Add(order);
//}
//oracle.Close();
//OR.Close();
#endregion
//finally
//{
// Close();
//}
}
#endregion
#region 执行SQL语句返回受影响的行数
/// <summary>
/// 执行SQL语句返回受影响的行数
/// </summary>
/// <param name="sql">SQL语句</param>
public static int ExecuteSql(string sql)
{
try
{
Open();
OracleCommand cmd = new OracleCommand(sql, con);
int reVal = cmd.ExecuteNonQuery();
cmd.Dispose();
return reVal;
}
catch (OracleException e)
{
throw e;
}
finally
{
Close();
}
}
#endregion
#region 执行SQL语句，返回数据到DataSet中
/// <summary>
/// 执行SQL语句，返回数据到DataSet中
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>返回DataSet</returns>
public static DataSet GetDataSet(string sql)
{
try
{
Open();//打开数据连接
DataSet ds = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(sql, con);
adapter.Fill(ds);
adapter.Dispose();
return ds;
}
catch (OracleException e)
{
throw e;
}
finally
{
Close();//关闭数据库连接
}
}
#endregion
#region 执行SQL语句，返回数据到自定义DataSet中
/// <summary>
/// 执行SQL语句，返回数据到DataSet中
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="DataSetName">自定义返回的DataSet表名</param>
/// <returns>返回DataSet</returns>
public static DataSet GetDataSet(string sql, string DataSetName)
{
try
{
Open();
DataSet ds = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(sql, con);
adapter.Fill(ds, DataSetName);
adapter.Dispose();
return ds;
}
catch (OracleException e)
{
throw e;
}
finally
{
Close();
}
}
#endregion
#region 执行Sql语句,返回带分页功能的自定义dataset----数据量不大的情况可以用
/// <summary>
/// 执行Sql语句,返回带分页功能的自定义dataset
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="PageSize">每页显示记录数</param>
/// <param name="CurrPageIndex">当前页</param>
/// <param name="DataSetName">返回dataset表名</param>
/// <returns>返回DataSet</returns>
public static DataSet GetDataSet(string sql, int PageSize, int CurrPageIndex, string DataSetName)
{
try
{
Open();//打开数据连接
DataSet ds = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(sql, con);
adapter.Fill(ds, PageSize * (CurrPageIndex - 1), PageSize, DataSetName);
adapter.Dispose();
return ds;
}
catch (OracleException e)
{
throw e;
}
finally
{
Close();//关闭数据库连接
}
}
#endregion
#region 执行SQL语句，返回记录总数
/// <summary>
/// 执行SQL语句，返回记录总数
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>返回记录总条数</returns>
public static int GetRecordCount(string sql)
{
try
{
Open();//打开数据连接
int recordCount = 0;
OracleCommand command = new OracleCommand(sql, con);
OracleDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
recordCount++;
}
dataReader.Close();
command.Dispose();
return recordCount;
}
catch (OracleException e)
{
throw e;
}
finally
{
Close();//关闭数据库连接
}
}
#endregion
#region 取当前序列,条件为seq.nextval或seq.currval
/// <summary>
/// 取当前序列
/// </summary>
/// <param name="seqstr"></param>
/// <param name="table"></param>
/// <returns></returns>
public static decimal GetSeq(string seqstr)
{
try
{
Open();
decimal seqnum = 0;
string sql = "select " + seqstr + " from dual";
OracleCommand command = new OracleCommand(sql, con);
OracleDataReader dataReader = command.ExecuteReader();
if (dataReader.Read())
{
seqnum = decimal.Parse(dataReader[0].ToString());
}
dataReader.Close();
dataReader.Dispose();
command.Dispose();
return seqnum;
}
catch (OracleException e)
{
throw e;
}
finally
{
Close();
}
}
#endregion
#region 统计某表记录总数
/// <summary>
/// 统计某表记录总数
/// </summary>
/// <param name="KeyField">主键/索引键</param>
/// <param name="TableName">数据库.用户名.表名</param>
/// <param name="Condition">查询条件不带where</param>
/// <returns>返回记录总数</returns>
public static int GetRecordCount(string keyField, string tableName, string condition)
{
try
{
int RecordCount = 0;
string sql = "select count(" + keyField + ") as count from " + tableName + " where " + condition;
DataSet ds = GetDataSet(sql);
if (ds.Tables[0].Rows.Count > 0)
{
RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
}
ds.Clear();
ds.Dispose();
return RecordCount;
}
catch (OracleException e)
{
throw e;
}
}
/// <summary>
/// 统计某表记录总数
/// </summary>
/// <param name="Field">可重复的字段</param>
/// <param name="tableName">数据库.用户名.表名</param>
/// <param name="condition">查询条件</param>
/// <param name="flag">字段是否主键</param>
/// <returns>返回记录总数</returns>
public int GetRecordCount(string Field, string tableName, string condition, bool flag)
{
try
{
int RecordCount = 0;
if (flag)
{
RecordCount = GetRecordCount(Field, tableName, condition);
}
else
{
string sql = "select count(distinct(" + Field + ")) as count from " + tableName + " where " + condition;
DataSet ds = GetDataSet(sql);
if (ds.Tables[0].Rows.Count > 0)
{
RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
}
ds.Clear();
ds.Dispose();
}
return RecordCount;
}
catch (OracleException e)
{
throw e;
}
}
#endregion
#region
// <summary>
/// 获取泛型集合
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="sqlStr">要查询的T-SQL</param>
/// <returns></returns>
public static IList<T> GetList<T>(string sql)
{
try
{
Open();//打开数据连接
DataSet ds = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(sql, con);
adapter.Fill(ds);
adapter.Dispose();
return DataSetToList<T>(ds, 0);
}
catch (OracleException e)
{
throw e;
}
finally
{
Close();
}
}
/// <summary>
/// DataSetToList
/// </summary>
/// <typeparam name="T">转换类型</typeparam>
/// <param name="dataSet">数据源</param>
/// <param name="tableIndex">需要转换表的索引</param>
/// <returns></returns>
public static IList<T> DataSetToList<T>(DataSet dataSet, int tableIndex)
{
//确认参数有效
if (dataSet == null || dataSet.Tables.Count <= 0 || tableIndex < 0)
return null;
DataTable dt = dataSet.Tables[tableIndex];
IList<T> list = new List<T>();
for (int i = 0; i < dt.Rows.Count; i++)
{
//创建泛型对象
T _t = Activator.CreateInstance<T>();
//获取对象所有属性
PropertyInfo[] propertyInfo = _t.GetType().GetProperties();
for (int j = 0; j < dt.Columns.Count; j++)
{
foreach (PropertyInfo info in propertyInfo)
{
//属性名称和列名相同时赋值
if (dt.Columns[j].ColumnName.ToUpper().Equals(info.Name.ToUpper()))
{
if (dt.Rows[i][j] != DBNull.Value)
{
info.SetValue(_t, dt.Rows[i][j], null);
}
else
{
info.SetValue(_t, null, null);
}
break;
}
}
}
list.Add(_t);
}
return list;
}
#endregion
}
}
