C#学习--Oracle数据库基本操作(连接、增、删、改、查)封装
写在前面:
sqlserver的c#封装:
类似于上篇有关sqlserver的c#封装,小编对oracle数据库进行了相应的封装,方便后期开发使用,主要包括oracle数据库的连接、增、删、改、查,如有什么问题还请各位大佬指教。后续也将对其他几个常用的数据库进行相应的整理。话不多说,直接开始码代码。
先声明一个sqlconnection便于后续使用。
private oracleconnection oracle_con;;//声明一个oracleconnection方便使用
oracle打开:
/// <summary>
/// oracle open
/// </summary>
/// <param name="link">link statement</param>
/// <returns>success:success; fail:reason</returns>
public string oracle_open(string link)
{
try
{
oracle_con = new oracleconnection(link);
oracle_con.open();
return "success";
}
catch (exception ex)
{
return ex.message;
}
}
oracle关闭:
/// <summary>
/// oracle close
/// </summary>
/// <returns>success:success fail:reason</returns>
public string oracle_close()
{
try
{
if (oracle_con == null)
{
return "no database connection";
}
if (oracle_con.state == connectionstate.open)
{
oracle_con.close();
oracle_con.dispose();
}
else
{
if (oracle_con.state == connectionstate.closed)
{
return "success";
}
if (oracle_con.state == connectionstate.broken)
{
return "connectionstate:broken";
}
}
return "success";
}
catch (exception ex)
{
return ex.message;
}
}
oracle的增删改:
/// <summary>
/// oracle insert,delete,update
/// </summary>
/// <param name="sql">insert,delete,update statement</param>
/// <returns>success:success + number of affected rows; fail:reason</returns>
public string oracle_insdelupd(string sql)
{
try
{
int num = 0;
if (oracle_con == null)
{
return "please open the database connection first";
}
if (oracle_con.state == connectionstate.open)
{
oraclecommand oraclecommand = new oraclecommand(sql, oracle_con);
num = oraclecommand.executenonquery();
}
else
{
if (oracle_con.state == connectionstate.closed)
{
return "database connection closed";
}
if (oracle_con.state == connectionstate.broken)
{
return "database connection is destroyed";
}
}
return "success" + num;
}
catch (exception ex)
{
return ex.message.tostring();
}
}
oracle的查:
/// <summary>
/// oracle select
/// </summary>
/// <param name="sql">select statement</param>
/// <param name="record">success:success; fail:reason</param>
/// <returns>select result</returns>
public dataset oracle_select(string sql, out string record)
{
try
{
dataset dataset = new dataset();
if (oracle_con != null)
{
if (oracle_con.state == connectionstate.open)
{
oracledataadapter oracledataadapter = new oracledataadapter(sql, oracle_con);
oracledataadapter.fill(dataset, "sample");
oracledataadapter.dispose();
record = "ok";
return dataset;
}
if (oracle_con.state == connectionstate.closed)
{
record = "database connection closed";
}
else if (oracle_con.state == connectionstate.broken)
{
record = "database connection is destroyed";
}
}
else
{
record = "please open the database connection first";
}
record = "error";
return dataset;
}
catch (exception ex)
{
dataset dataset = new dataset();
record = ex.message.tostring();
return dataset;
}
}
小编发现以上这种封装方式还是很麻烦,每次对oracle进行增删改查的时候还得先打开数据库,最后还要关闭,实际运用起来比较麻烦。因此对上面两个增删改查的方法进行了重载,在每次进行操作时都先打开数据库,然后关闭数据库。
/// <summary>
/// oracle insert,delete,update
/// </summary>
/// <param name="sql">insert,delete,update statement</param>
/// <param name="link">link statement</param>
/// <returns>success:success + number of affected rows; fail:reason</returns>
public string oracle_insdelupd(string sql, string link)
{
try
{
int num = 0;
using (oracleconnection oracleconnection = new oracleconnection(link))
{
dataset dataset = new dataset();
oracleconnection.open();
oraclecommand oraclecommand = new oraclecommand(sql, oracleconnection);
num = oraclecommand.executenonquery();
oracleconnection.close();
return "success" + num;
}
}
catch (exception ex)
{
return ex.message.tostring();
}
}
/// <summary>
/// oracle select
/// </summary>
/// <param name="sql">select statement</param>
/// <param name="link">link statement</param>
/// <param name="record">success:success; fail:reason</param>
/// <returns>select result</returns>
public dataset oracle_select(string sql, string link, out string record)
{
try
{
using (oracleconnection oracleconnection = new oracleconnection(link))
{
dataset dataset = new dataset();
oracleconnection.open();
oracledataadapter oracledataadapter = new oracledataadapter(sql, oracleconnection);
oracledataadapter.fill(dataset, "sample");
oracledataadapter.dispose();
oracleconnection.close();
record = "success";
return dataset;
}
}
catch (exception ex)
{
dataset dataset = new dataset();
record = ex.message.tostring();
return dataset;
}
}