.net C# SqlHelper for Oracle
2024-10-17 15:57:04
适用于Oracle的sqlhelper
需要使用ODP.Net，引用Oracle.DataAccess.dll 推荐安装ODAC
代码如下：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Reflection;
using System.Data;
using System.Configuration;
using Oracle.DataAccess.Client; namespace DAL
{
public static class SqlHelper
{
#region 样本
//标准连接-SSPI
private static readonly string defaultConnectString = "Data Source=ORCL;Integrated Security=SSPI;";
//标准连接
//private static readonly string defaultConnectString = "Data Source=ORCL;User Id=UPDM;Password=1234;";
//标准链接
//private static readonly string defaultConnectString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)));User Id=system;Password=1234;";
#endregion
#region 变量 private static OracleConnection _con = null;
public static string _constr = ConfigurationManager.ConnectionStrings["orcl"].ToString(); #endregion #region 属性 public static string constr
{
get
{
if (_constr == null||_constr.Equals(String.Empty))
{
_constr = defaultConnectString;
}
return _constr;
}
set
{
_constr = value;
}
} /// <summary>
/// 获取或设置数据库连接对象
/// </summary>
public static OracleConnection Con
{
get
{ if (SqlHelper._con == null)
{
SqlHelper._con = new OracleConnection();
}
if (SqlHelper._con.ConnectionString == null || SqlHelper._con.ConnectionString.Equals(string.Empty))
{
SqlHelper._con.ConnectionString = SqlHelper.constr;
}
return SqlHelper._con;
}
set
{
SqlHelper._con = value;
}
}
#endregion #region 方法 /// <summary>
/// 执行并返回第一行第一列的数据库操作
/// </summary>
/// <param name="commandText">Sql语句或存储过程名</param>
/// <param name="commandType">Sql命令类型</param>
/// <param name="param">Oracle命令参数数组</param>
/// <returns>第一行第一列的记录</returns>
public static int ExecuteScalar(string commandText, CommandType commandType, params OracleParameter[] param)
{
int result = ;
try{
using (OracleCommand cmd = new OracleCommand(commandText, SqlHelper.Con))
{
try
{
cmd.CommandType = commandType;
if (param!=null)
{
cmd.Parameters.AddRange(param);
}
SqlHelper.Con.Open();
string x = cmd.CommandText;
result = Convert.ToInt32(cmd.ExecuteScalar());
}
catch
{
result = -;
}
}
}
finally
{
if (SqlHelper.Con.State != ConnectionState.Closed)
{
SqlHelper.Con.Close();
}
}
return result;
} /// <summary>
/// 执行不查询的数据库操作
/// </summary>
/// <param name="commandText">Oracle语句或存储过程名</param>
/// <param name="commandType">Oracle命令类型</param>
/// <param name="param">Oracle命令参数数组</param>
/// <returns>受影响的行数</returns>
public static int ExecuteNonQuery(string commandText, CommandType commandType, params OracleParameter[] param)
{
int result = ;
try
{
using (OracleCommand cmd = new OracleCommand(commandText, SqlHelper.Con))
{
try
{
cmd.CommandType = commandType;
if (param!=null)
{
cmd.Parameters.AddRange(param);
}
SqlHelper.Con.Open();
result = cmd.ExecuteNonQuery();
}
catch
{
result = -;
}
}
}
finally
{
if (SqlHelper.Con.State != ConnectionState.Closed)
{
SqlHelper.Con.Close();
}
}
return result;
} /// <summary>
/// 获取数据表
/// </summary>
/// <param name="commandText">select命令</param>
/// <param name="param">参数表</param>
/// <returns></returns>
public static DataTable GetDataTable(string commandText,params OracleParameter[] param)
{
DataTable result = new DataTable();
try
{
using (OracleCommand cmd = new OracleCommand(commandText, SqlHelper.Con))
{
cmd.Parameters.AddRange(param);
try {
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
adapter.Fill(result);
}
catch
{
result = null;
}
}
}
finally
{
if (SqlHelper.Con.State != ConnectionState.Closed)
{
SqlHelper.Con.Close();
}
}
return result;
} public static int GetNextValueInSequence(string sequenceName)
{
if (ExecuteScalar("select count(*) from user_objects where OBJECT_NAME=:seqName", CommandType.Text, new OracleParameter(":seqName",sequenceName)) > )
{
return ExecuteScalar("select " + sequenceName + ".nextval from dual", CommandType.Text);
}
else
{
return -;
} } /// <summary>
/// 事务模式执行多行非查询语句
/// </summary>
/// <param name="commandText">sql语句</param>
/// <param name="param">参数</param>
/// <returns>受影响行数</returns>
public static int ExecuteNonQueryTransaction(string commandText, List<OracleParameter[]> param)
{
int result = ;
try
{
using (OracleCommand cmd = new OracleCommand(commandText, SqlHelper.Con))
{
SqlHelper.Con.Open();
cmd.Transaction = cmd.Connection.BeginTransaction();
try
{
foreach (OracleParameter[] par in param)
{
cmd.Parameters.Clear();
cmd.Parameters.AddRange(par);
result += cmd.ExecuteNonQuery();
}
cmd.Transaction.Commit();
}
catch
{
result = -;
try
{
cmd.Transaction.Rollback();
}
catch
{
result = -;
}
}
}
}
finally
{
if (SqlHelper.Con.State != ConnectionState.Closed)
{
SqlHelper.Con.Close();
}
}
return result;
} /// <summary>
/// 执行返回一条记录的泛型对象
/// </summary>
/// <typeparam name="T">泛型类型</typeparam>
/// <param name="reader">只进只读对象</param>
/// <returns>泛型对象</returns>
private static T ExecuteDataReader<T>(IDataReader reader)
{
T obj = default(T);
try
{
Type type = typeof(T);
obj = (T)Activator.CreateInstance(type);//从当前程序集里面通过反射的方式创建指定类型的对象
//obj = (T)Assembly.Load(SqlHelper._assemblyName).CreateInstance(SqlHelper._assemblyName + "." + type.Name);//从另一个程序集里面通过反射的方式创建指定类型的对象
PropertyInfo[] propertyInfos = type.GetProperties();//获取指定类型里面的所有属性
foreach (PropertyInfo propertyInfo in propertyInfos)
{
for (int i = ; i < reader.FieldCount; i++)
{
string fieldName = reader.GetName(i);
if (fieldName.ToLower() == propertyInfo.Name.ToLower())
{
object val = reader[propertyInfo.Name];//读取表中某一条记录里面的某一列
if (val != null && val != DBNull.Value)
{
Type valType = val.GetType(); if (valType == typeof(float) || valType == typeof(double) || valType== typeof(decimal))
{
propertyInfo.SetValue(obj, Convert.ToDouble(val), null);
}
else if (valType == typeof(int))
{
propertyInfo.SetValue(obj, Convert.ToInt32(val), null);
}
else if (valType == typeof(DateTime))
{
propertyInfo.SetValue(obj, Convert.ToDateTime(val), null);
}
else if (valType == typeof(string))
{
propertyInfo.SetValue(obj, Convert.ToString(val), null);
}
}
break;
}
}
}
}
catch
{
throw;
}
return obj;
} /// <summary>
/// 执行返回一条记录的泛型对象
/// </summary>
/// <typeparam name="T">泛型类型</typeparam>
/// <param name="commandText">Oracle语句或存储过程名</param>
/// <param name="commandType">Oracle命令类型</param>
/// <param name="param">Oracle命令参数数组</param>
/// <returns>实体对象</returns>
public static T ExecuteEntity<T>(string commandText, CommandType commandType, params OracleParameter[] param)
{
T obj = default(T);
try
{
using (OracleCommand cmd = new OracleCommand(commandText, SqlHelper.Con))
{
cmd.CommandType = commandType;
cmd.Parameters.AddRange(param);
SqlHelper.Con.Open();
OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
obj = SqlHelper.ExecuteDataReader<T>(reader);
}
}
}
finally
{
if (SqlHelper.Con.State != ConnectionState.Closed)
{
SqlHelper.Con.Close();
}
}
return obj;
} /// <summary>
/// 执行返回多条记录的泛型集合对象
/// </summary>
/// <typeparam name="T">泛型类型</typeparam>
/// <param name="commandText">Oracle语句或存储过程名</param>
/// <param name="commandType">Oracle命令类型</param>
/// <param name="param">Oracle命令参数数组</param>
/// <returns>泛型集合对象</returns>
public static List<T> ExecuteList<T>(string commandText, CommandType commandType, params OracleParameter[] param)
{
List<T> list = new List<T>();
try
{
using (OracleCommand cmd = new OracleCommand(commandText, SqlHelper.Con))
{
try
{
cmd.CommandType = commandType; if (param != null)
{
cmd.Parameters.AddRange(param);
}
SqlHelper.Con.Open(); OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read())
{
T obj = SqlHelper.ExecuteDataReader<T>(reader);
list.Add(obj);
}
}
catch (Exception ex)
{
list = null;
}
}
}
finally
{
if (SqlHelper.Con.State != ConnectionState.Closed)
{
SqlHelper.Con.Close();
}
}
return list;
} #endregion
}
}
最新文章
- Java环境变量
- javascript判断回文数
- 解决织梦标签artlist不能调用副栏目的问题？
- css布局详解（一）——盒模型
- html5的自定义data-*属性与jquery的data()方法的使用
- vue.js实例对象+组件树
- JQuery插件开发标准写法
- 前端div层级控制
- .NET 机器学习生态调查
- post数据时报错：远程服务器返回错误: (400) 错误的请求。
- C++中数组作为形参进行传递（转）
- TrustManagerService.java
- [源码]Python简易http服务器(内网渗透大文件传输含下载命令)
- Django之组件--cookie与session
- python,类和对象练习
- css之标签选择器
- centos7下/etc/rc.local文件里配置的开机启动项不执行的解决办法
- Linux基础学习（4）--Linux常用命令
- 安装旧版本的Firefox
- ubuntu上安装boost库
热门文章
- 为什么WebSphere好好的,他就不干活了?
- 在非SQL客户端使用命令行方式定期连接SQL Server 服务器并模拟用户查询操作，同时输出信息内容
- 1Z0-053 争议题目解析134
- Android中Application的应用
- deployment与Web应用程序部署
- ASP.NET 5 已死 - 隆重介绍 ASP.NET Core 1.0 和 .NET Core 1.0
- Java 代码完成删除文件、文件夹操作
- Mybatis中javaType和jdbcType对应关系
- python tornado websocket 实时日志展示
- Linux Cmd Tool 系列之—script & scriptreplay