OracleSqlHelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Oracle.DataAccess.Client;
using System.Configuration;

namespace ItcastCater.DAL
{
public class SqlHelper
{
/// <summary>
/// l连接数据库
/// </summary>
/// <returns></returns>
public static OracleConnection getConnection()
{
string conStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
OracleConnection con = new OracleConnection(conStr);
return con;
}

//获取受影响的行数,
public static int ExecuteNonQuery(String sql,params OracleParameter[] param)
{
OracleConnection con = getConnection();
using(OracleCommand cmd=new OracleCommand(sql,con))
{

if (param!= null && param.Length > 0)
{
foreach (OracleParameter item in param)
{
cmd.Parameters.Add(item);
}
}

try
{
con.Open();
return cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
Console.WriteLine("ExecuteNonQuery处异常信息："+ex.Message);
con.Close();
return -1;
}
}
}

//
public static object ExecuteScalar(string sql, params OracleParameter[] param)
{
OracleConnection con = getConnection();
using (OracleCommand cmd = new OracleCommand(sql, con))
{

if (param != null && param.Length > 0)
{
foreach (OracleParameter item in param)
{
cmd.Parameters.Add(item);
}
}

try
{
con.Open();
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
con.Close();
Console.WriteLine("异常："+ex.Message);
return -1;
}
}
}

//用DataTable返回结果集
public static DataTable ExecuteTable(string sql,params OracleParameter[] param)
{
DataTable dt = new DataTable();
using(OracleConnection con=getConnection())
{
using(OracleCommand cmd=con.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
con.Open();
if (param != null && param.Length >0)
{
foreach (OracleParameter item in param)
{
cmd.Parameters.Add(item);
}
}
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
adapter.Fill(dt);
}
}
return dt;
}

public static OracleDataReader ExecuteReader(string sql, params OracleParameter[] param)
{

OracleConnection con = getConnection();
//using (OracleCommand cmd = new OracleCommand(sql, con))
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = con;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
if (param != null && param.Length > 0)
{
foreach (OracleParameter item in param)
{
cmd.Parameters.Add(item);
}
}
try
{
con.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
con.Close();
con.Dispose();
throw ex;
}
}
}

//

 

}
}