Oracle数据库驱动类DBHelper-11075795
using System;using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using Oracle.DataAccess.Client;
namespace DAL
{
public> {
//public static string ConnectionString = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;//方式一，数据库链接串写在配置文件中
public static string ConnectionString = "Data Source=ORCL;User> #region 对于所有表 ExecuteNonQuery() 直接对数据进行更新操作
public static int ExecuteNonQuery(string sql)
{
return ExecuteNonQuery(sql, null, CommandType.Text);
}
public static int ExecuteNonQuery(string sql, OracleParameter[] para)
{
return ExecuteNonQuery(sql, para, CommandType.Text);
}
public static int ExecuteNonQuery(string sql, CommandType cmdType)
{
return ExecuteNonQuery(sql, null, cmdType);
}
public static int ExecuteNonQuery(string sql, OracleParameter[] para, CommandType cmdType)
{
int rows = -1;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
OracleCommand cmd = new OracleCommand(sql, conn);
if (para != null)
{
cmd.Parameters.AddRange(para);
}
cmd.CommandType = cmdType;
conn.Open();
rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
conn.Close();
}
return rows;
}
#endregion
#region 对于所有表 ExecuteReader 返回结果集
public static OracleDataReader ExecuteReader(string sql)
{
return ExecuteReader(sql, null, CommandType.Text);
}
public static OracleDataReader ExecuteReader(string sql, OracleParameter[] para)
{
return ExecuteReader(sql, para, CommandType.Text);
}
public static OracleDataReader ExecuteReader(string sql, CommandType cmdType)
{
return ExecuteReader(sql, null, cmdType);
}
public static OracleDataReader ExecuteReader(string sql, OracleParameter[] para, CommandType cmdType)
{
OracleCommand cmd = null;
OracleConnection conn = new OracleConnection(ConnectionString); ;
cmd = new OracleCommand(sql, conn);
if (para != null)
{
cmd.Parameters.AddRange(para);
}
cmd.CommandType = cmdType;
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
#endregion
#region 对于所有表 ExecuteDataTable 返回数据表
public static DataTable ExecuteDataTable(string sql)
{
return ExecuteDataTable(sql, null, CommandType.Text);
}
public static DataTable ExecuteDataTable(string sql, OracleParameter[] para)
{
return ExecuteDataTable(sql, para, CommandType.Text);
}
public static DataTable ExecuteDataTable(string sql, CommandType cmdType)
{
return ExecuteDataTable(sql, null, cmdType);
}
public static DataTable ExecuteDataTable(string sql, OracleParameter[] para, CommandType cmdType)
{
try
{
DataTable myTable = new DataTable();
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
OracleCommand cmd = new OracleCommand(sql, conn);
if (para != null)
{
cmd.Parameters.Add(para);
}
cmd.CommandType = cmdType;
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(myTable);
}
return myTable;
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region 对于所有表 ExecuteScalar 返回数据表
public static object ExecuteScalar(string sql)
{
return ExecuteScalar(sql, null, CommandType.Text);
}
public static object ExecuteScalar(string sql, OracleParameter[] para)
{
return ExecuteScalar(sql, para, CommandType.Text);
}
public static object ExecuteScalar(string sql, CommandType cmdType)
{
return ExecuteScalar(sql, null, cmdType);
}
public static object ExecuteScalar(string sql, OracleParameter[] para, CommandType cmdType)
{
object res = null;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
OracleCommand cmd = new OracleCommand(sql, conn);
if (para != null)
{
cmd.Parameters.AddRange(para);
}
cmd.CommandType = cmdType;
conn.Open();
res = cmd.ExecuteScalar();
cmd.Parameters.Clear();
conn.Close();
}
return res;
}
#endregion
#region 存储过程操作
///
/// 执行存储过程
///
/// 存储过程名
/// 存储过程参数
/// OracleDataReader返回数据集
public static OracleDataReader RunProcedure(string storedProcName,> {
OracleConnection connection = new OracleConnection(ConnectionString);
OracleDataReader returnReader;
connection.Open();
OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader();
return returnReader;
}
///
/// 执行存储过程
///
/// 存储过程名
/// 存储过程参数
/// DataSet结果中的表名
/// DataSet
public static DataSet RunProcedure(string storedProcName,> {
using (OracleConnection connection = new OracleConnection(ConnectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
OracleDataAdapter sqlDA = new OracleDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
}
///
/// 构建 OracleCommand 对象(用来返回一个结果集，而不是一个整数值)
///
/// 数据库连接
/// 存储过程名
/// 存储过程参数
/// OracleCommand
private static OracleCommand BuildQueryCommand(OracleConnection connection, string storedProcName,> {
OracleCommand command = new OracleCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (OracleParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
return command;
}
///
/// 创建 OracleCommand 对象实例(存储过程返回输出参数)
///
/// 存储过程名
/// 存储过程参数
/// OracleCommand 对象实例
/// 调用此方法的例子
/* OracleParameter[] parameter = {
new OracleParameter("starttime",OracleDbType.Varchar2,8),
new OracleParameter("endtime",OracleDbType.Varchar2,8),
new OracleParameter("days",OracleDbType.Decimal,3)
};
parameter.Direction = ParameterDirection.Input;
parameter.Direction = ParameterDirection.Input;
parameter.Direction = ParameterDirection.Output;
//new OracleParameter("t_sql", OracleType.Cursor)};
parameter.Value = starttime;
parameter.Value = endtime;
// parameter.Direction = System.Data.ParameterDirection.Output;
command = DBHelper.RunProcedureCommand("pro_comovertimedays", parameter);
//接收函数返回值
over_days = Convert.ToString(parameter.Value);//res
*/
public static OracleCommand RunProcedureCommand(string storedProcName,> {
using (OracleConnection connection = new OracleConnection(ConnectionString))
{
OracleCommand command = new OracleCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (OracleParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
connection.Open();
command.ExecuteNonQuery();
connection.Close();
return command;
}
}
#endregion
}
}
页:
[1]