1.Oracle 封装类
using System; using System.Collections.Generic; using System.Linq; using System.Text; using Oracle.ManagedDataAccess.Client; using System.Data; using System.Data.SqlClient; namespace OracleHelper { public static class OracleSQLHelper { //从配置文件中获取连接字符串 readonly修饰的变量只能在初始化或构造函数中赋值;其他地方只能读取 //private static readonly string constr = ConfigurationManager.ConnectionStrings["OperationHistory"].ConnectionString; //private static readonly string constr = "Data Source=DELLYS;Initial Catalog=ceshi;User=sa;PassWord=123"; private static string constr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=130.147.246.144)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ECMS)));Persist Security Info=True;User ID=system;Password=Service01;"; /// <summary> /// 增、删、改的方法[ExecuteNonQuery] 返回所影响的行数,执行其他 /// </summary> /// <param name="sql">Sql命令</param> /// <param name="cmdtype">SQL语句(CommandType.Text)或者存储过程(CommandType.StoredProcedure)</param> /// <param name="pms"></param> /// <returns></returns> public static int ExecuteNonQuery(string sql, CommandType cmdtype, params OracleParameter[] pms) { int i = -1; try { using (OracleConnection con = new OracleConnection(constr)) { using (OracleCommand cmd = new OracleCommand(sql, con)) { //存储过程或者Sql语句 cmd.CommandType = cmdtype; if (pms != null) { cmd.Parameters.AddRange(pms); } con.Open(); i = cmd.ExecuteNonQuery(); } } } catch (Exception) { throw; } return i; } /// <summary> /// 执行查询、查询多少条数据;返回第一行,第一列方法[ExecuteScalar] 返回-1执行失败 /// </summary> /// <param name="sql">Sql命令</param> /// <param name="cmdtype">SQL语句(CommandType.Text)或者存储过程(CommandType.StoredProcedure)</param> /// <param name="pms"></param> /// <returns></returns> public static object ExecuteScalar(string sql, CommandType cmdtype, params OracleParameter[] pms) { try { using (OracleConnection con = new OracleConnection(constr)) { using (OracleCommand cmd = new OracleCommand(sql, con)) { //存储过程或者Sql语句 cmd.CommandType = cmdtype; if (pms != null) { cmd.Parameters.AddRange(pms); } con.Open(); return cmd.ExecuteScalar(); } } } catch (Exception) { throw; } } /// <summary> /// 执行查询,返回多行、多列的方法ExecuteReader() /// </summary> /// <param name="sql">Sql命令</param> /// <param name="cmdtype">SQL语句(CommandType.Text)或者存储过程(CommandType.StoredProcedure)</param> /// <param name="pms"></param> /// <returns></returns> public static OracleDataReader ExecuteReader(string sql, CommandType cmdtype, params OracleParameter[] pms) { using (OracleConnection con = new OracleConnection(constr)) { using (OracleCommand cmd = new OracleCommand(sql, con)) { //存储过程或者Sql语句 cmd.CommandType = cmdtype; if (pms != null) { cmd.Parameters.AddRange(pms); } try { con.Open(); return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); } catch { con.Close(); con.Dispose(); throw; } } } } /// <summary> /// 执行返回多个查询时使用,返回DataTable类型 /// </summary> /// <param name="sql">Sql命令</param> /// <param name="cmdtype">SQL语句(CommandType.Text)或者存储过程(CommandType.StoredProcedure)</param> /// <param name="pms">参数</param> /// <returns></returns> public static DataTable ExecuteDataTable(string sql, CommandType cmdtype, params OracleParameter[] pms) { DataTable dt = new DataTable(); try { //通过adapter读取数据。 using (OracleDataAdapter adapter = new OracleDataAdapter(sql, constr)) { adapter.SelectCommand.CommandType = cmdtype; if (pms != null) { adapter.SelectCommand.Parameters.AddRange(pms); } adapter.Fill(dt); return dt; } } catch (Exception) { throw; } } /// <summary> /// 需要获得多个结果集的时候用该方法,返回DataSet对象。 /// </summary> /// <param name="sql语句"></param> /// <returns></returns> public static DataSet ExecuteDataSet(string sql, params OracleParameter[] pms) { using (OracleConnection con = new OracleConnection(constr)) { //数据适配器 OracleDataAdapter sqlda = new OracleDataAdapter(sql, con); sqlda.SelectCommand.Parameters.AddRange(pms); DataSet ds = new DataSet(); sqlda.Fill(ds); return ds; //不需要打开和关闭链接. } } } }