public class OracleSqlHelper
{
//public static readonly string ConnectionString_SYS = Connstr
public static bool TestDataBase()
{
try
{
Conn();
string commandText = "select count(*) from all_tab_comments";
OracleConnection conn = new OracleConnection();
conn.ConnectionString = ConnectionString_SYS;
conn.Open();
OracleDataAdapter thisAdapter = new OracleDataAdapter(commandText, conn);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet);
DataTable dt = new DataTable();
thisDataSet.Tables.Add(dt);
dt = thisDataSet.Tables[0];
int count = 0;
int.TryParse(dt.Rows[0][0].ToString(), out count);
if (count > 0)
{
return true;
}
else
{
return false;
}
}
catch
{
return false;
}
}
public static DataTable ExecuteNonQuery(string commandText)
{
Conn();
OracleConnection conn = new OracleConnection();
try
{
conn.ConnectionString = ConnectionString_SYS;
conn.Open();
OracleDataAdapter thisAdapter = new OracleDataAdapter(commandText, conn);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet);
DataTable dt = new DataTable();
thisDataSet.Tables.Add(dt);
dt = thisDataSet.Tables[0];
return dt;
}
catch (Exception ex)
{
return null;
}
finally
{
conn.Close();
conn.Dispose();
}
}
public static string ExecuteNonString(string commandText)
{
Conn();
OracleConnection conn = new OracleConnection(); ;
try
{
conn.ConnectionString = ConnectionString_SYS;
conn.Open();
OracleDataAdapter thisAdapter = new OracleDataAdapter(commandText, conn);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet);
DataTable dt = new DataTable();
thisDataSet.Tables.Add(dt);
dt = thisDataSet.Tables[0];
string str = dt.Rows[0][0].ToString();
return str;
}
catch (Exception ex)
{
return null;
}
finally
{
conn.Close();
conn.Dispose();
}
}
//查询记录数目
public static int ExecuteNonQueryCount(string commandText)
{
Conn();
OracleConnection conn = new OracleConnection();
try
{
conn.ConnectionString = ConnectionString_SYS;
conn.Open();
OracleDataAdapter thisAdapter = new OracleDataAdapter(commandText, conn);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet);
DataTable dt = new DataTable();
thisDataSet.Tables.Add(dt);
dt = thisDataSet.Tables[0];
int count = 0;
int.TryParse(dt.Rows[0][0].ToString(), out count);
return count;
}
catch (Exception ex)
{
return 0;
}
finally
{
conn.Close();
conn.Dispose();
}
}
//插入和更新返回成功或者失败
public static bool ExecuteInsert(string sqler)
{
Conn();
OracleConnection conn = new OracleConnection();
conn.ConnectionString = ConnectionString_SYS;
conn.Open();
OracleCommand cmdfcode = new OracleCommand(sqler, conn);
cmdfcode.CommandType = CommandType.Text;
int ret = (int)cmdfcode.ExecuteNonQuery();
conn.Close();
if (ret > 0)
{
return true;
}
else
{
return false;
}
}
public static DataSet Query(string sqlString)
{
Conn();
OracleConnection conn = new OracleConnection();
conn.ConnectionString = ConnectionString_SYS;
conn.Open();
DataSet thisDataSet = new DataSet();
try
{
OracleDataAdapter thisAdapter = new OracleDataAdapter(sqlString, conn);
thisAdapter.Fill(thisDataSet);
return thisDataSet;
}
catch (System.Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
conn.Close();
}
}
public static OracleDataReader ExDataReaders(string sql)
{
Conn();
OracleConnection conn = new OracleConnection();
OracleDataReader dataReader=null;
try
{
conn.ConnectionString = ConnectionString_SYS;
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
dataReader = cmd.ExecuteReader();
}
catch (System.Exception ex)
{
LogHelper.WriteLog("sql语句执行失败："+sql,ex);
}
return dataReader;
}
}
