Oracle 数据库操作类

using system;
using system.data;
using system.configuration;
using system.data.oracleclient;
using system.text;
using system.windows.forms;
using system.xml;
using transactions;
/// <summary>
/// db 的摘要说明 written by luos.luo ,the creator of saleplayer.com
/// </summary>
public class myoradb
{
public myoradb()
{
}
public int excutesqlwithnoquery(string vsql)
{
int vi = 0;
oracleconnection voracleconn = openoracledbconn();
try
{
if (voracleconn.state != connectionstate.open)
{
voracleconn.open();
}
oraclecommand voraclecmd = new oraclecommand();
voraclecmd.connection = voracleconn;
voraclecmd.commandtext = vsql;
voraclecmd.commandtype = commandtype.text;
vi = voraclecmd.executenonquery();
}
catch (exception ex)
{
mylog vmylog = new mylog();
vmylog.writelog("myoradb", vsql, ex);
}
finally
{
closeoracledbconn(voracleconn);
}
return vi;
}
public int excutesqlwithsinglenum(string vsql)
{
int vi = 0;
oracleconnection voracleconn = openoracledbconn();
try
{
if (voracleconn.state != connectionstate.open)
{
voracleconn.open();
}
oracledatareader voracledatareader = createoracledatareader(vsql);
while (voracledatareader.read())
{
vi = voracledatareader.getint32(0);
}
voracledatareader.close();
}
catch (exception ex)
{
mylog vmylog = new mylog();
vmylog.writelog("myoradb", vsql, ex);
}
finally
{
closeoracledbconn(voracleconn);
}
return vi;
}
public string excutesqlwithsinglestring(string vsql)
{
stringbuilder vtempstrbld = new stringbuilder();
oracleconnection voracleconn = openoracledbconn();
try
{
if (voracleconn.state != connectionstate.open)
{
voracleconn.open();
}
oracledatareader voracledatareader = createoracledatareader(vsql);
while (voracledatareader.read())
{
vtempstrbld.append(voracledatareader.getstring(0));
}
voracledatareader.close();
}
catch (exception ex)
{
mylog vmylog = new mylog();
vmylog.writelog("myoradb", vsql, ex);
}
finally
{
closeoracledbconn(voracleconn);
}
return vtempstrbld.tostring();
}
public datatable excutesqlwithdatatable(string vsql)
{
datatable vdatatable = new datatable();
oracleconnection voracleconn = openoracledbconn();
try
{
if (voracleconn.state != connectionstate.open)
{
voracleconn.open();
}
oracledataadapter voracledataadapter = new oracledataadapter(vsql, voracleconn);
voracledataadapter.fill(vdatatable);
}
catch (exception ex)
{
mylog vmylog = new mylog();
vmylog.writelog("myoradb", vsql, ex);
}
finally
{
closeoracledbconn(voracleconn);
}
return vdatatable;
}
public dataset excutesqlwithdataset(string vsql)
{
dataset vdataset = new dataset();
oracleconnection voracleconn = openoracledbconn();
try
{
if (voracleconn.state != connectionstate.open)
{
voracleconn.open();
}
oracledataadapter voracledataadapter = new oracledataadapter(vsql, voracleconn);
voracledataadapter.fill(vdataset);
}
catch (exception ex)
{
mylog vmylog = new mylog();
vmylog.writelog("myoradb", vsql, ex);
}
finally
{
closeoracledbconn(voracleconn);
}
return vdataset;
}
public string excutesqltransactionwithstring(string[] vsqlarray)
{
int vi = vsqlarray.length;
string vsql = string.empty;
oracleconnection voracleconn = openoracledbconn();
if (voracleconn.state != connectionstate.open)
{
voracleconn.open();
}
oracletransaction voracletrans = voracleconn.begintransaction(isolationlevel.readcommitted);
oraclecommand voraclecmd = new oraclecommand();
voraclecmd.connection = voracleconn;
voraclecmd.transaction = voracletrans;
try
{
for (int i = 0; i < vi; i++)
{
if (string.isnullorempty(vsqlarray[i]) == false)
{
vsql = vsqlarray[i];
voraclecmd.commandtext = vsql;
voraclecmd.executenonquery();
}
}
voracletrans.commit();
}
catch (exception ex)
{
voracletrans.rollback();
closeoracledbconn(voracleconn);
mylog vmylog = new mylog();
vmylog.writelog("", vsql, ex);
return ex.message;
}
closeoracledbconn(voracleconn);
return "success";
}
public void excuteprocedurewithnonquery(string vprocedurename, oracleparameter[] parameters)
{
oracleconnection voracleconn = openoracledbconn();
try
{
if (voracleconn.state != connectionstate.open)
{
voracleconn.open();
}
oraclecommand voraclecmd = new oraclecommand();
voraclecmd.connection = voracleconn;
voraclecmd.commandtext = vprocedurename;
voraclecmd.commandtype = commandtype.storedprocedure;
foreach (oracleparameter parameter in parameters)
{
voraclecmd.parameters.add(parameter);
}
voraclecmd.executenonquery();
}
catch (exception ex)
{
mylog vmylog = new mylog();
writelog("异常信息:excuteprocedurewithnonquery:" + ex.message);
}
finally
{
closeoracledbconn(voracleconn);
}
}
public string excuteprocedurewithsinglestring(string vprocedurename, oracleparameter[] parameters)
{
string vtempstr = string.empty;
oracleparameter voutmessage;
oracleconnection voracleconn = openoracledbconn();
try
{
if (voracleconn.state != connectionstate.open)
{
voracleconn.open();
}
oraclecommand voraclecmd = new oraclecommand();
voraclecmd.connection = voracleconn;
voraclecmd.commandtext = vprocedurename;
voraclecmd.commandtype = commandtype.storedprocedure;
voutmessage = new oracleparameter("o_flag", oracletype.varchar);
voutmessage.direction = parameterdirection.output;
voutmessage.size = 100;
voraclecmd.parameters.add(voutmessage);
foreach (oracleparameter parameter in parameters)
{
voraclecmd.parameters.add(parameter);
}
voraclecmd.executenonquery();
voraclecmd.dispose();
voraclecmd = null;
vtempstr = voutmessage.value.tostring();
}
catch (exception ex)
{
mylog vmylog = new mylog();
writelog("异常信息:excuteprocedurewithnonquery:" + ex.message);
}
finally
{
closeoracledbconn(voracleconn);
}
return vtempstr;
}
public int excuteprocedurewithsinglenum(string vprocedurename, oracleparameter[] parameters)
{
int vi = 0;
oracleparameter voutmessage;
oracleconnection voracleconn = openoracledbconn();
try
{
if (voracleconn.state != connectionstate.open)
{
voracleconn.open();
}
oraclecommand voraclecmd = new oraclecommand();
voraclecmd.connection = voracleconn;
voraclecmd.commandtext = vprocedurename;
voraclecmd.commandtype = commandtype.storedprocedure;
voutmessage = new oracleparameter("o_flag", oracletype.int32);
voutmessage.direction = parameterdirection.output;
voutmessage.size = 100;
voraclecmd.parameters.add(voutmessage);
foreach (oracleparameter parameter in parameters)
{
voraclecmd.parameters.add(parameter);
}
voraclecmd.executenonquery();
voraclecmd.dispose();
voraclecmd = null;
vi = system.convert.toint32(voutmessage.value);
}
catch (exception ex)
{
mylog vmylog = new mylog();
writelog("异常信息:excuteprocedurewithnonquery:" + ex.message);
}
finally
{
closeoracledbconn(voracleconn);
}
return vi;
}
/// <summary>
/// creates the parameter.
/// </summary>
/// <param name="name">the name.</param>
/// <param name="dbtype">type of the db.</param>
/// <param name="size">the value size</param>
/// <param name="direction">the direction.</param>
/// <param name="paramvalue">the param value.</param>
/// <returns></returns>
public oracleparameter createparameter(string vprocedurename, oracletype voracletype, int vsize, parameterdirection vdirection, object vparamvalue)
{
oracleparameter voracleparameter = new oracleparameter();
voracleparameter.parametername = vprocedurename;
voracleparameter.oracletype = voracletype;
voracleparameter.size = vsize;
voracleparameter.direction = vdirection;
if (!(voracleparameter.direction == parameterdirection.output))
{
voracleparameter.value = vparamvalue;
}
return voracleparameter;
}
private oracleconnection openoracledbconn()
{
string vconnstr = string.empty;
string voradbname = system.configuration.configurationmanager.appsettings["oradbname"];
switch (voradbname)
{
case "mesdb_03":
vconnstr = "data source=szmesdb;persist security info=true;user id=mesdb_03;password=mesdb;unicode=true;";
break;
case "mesdbtest_03":
vconnstr = "data source=szmesdb;persist security info=true;user id=mesdb_03;password=mesdb;unicode=true;";
break;
default:
vconnstr = "data source=szmesdb;persist security info=true;user id=mesdbtest_03;password=mesdb;unicode=true;";
break;
}
oracleconnection voracleconnection = new oracleconnection(vconnstr);
if (voracleconnection.state != connectionstate.open)
{
voracleconnection.open();
}
return voracleconnection;
}
private void closeoracledbconn(oracleconnection voracleconnection)
{
if (voracleconnection.state == connectionstate.open)
{
voracleconnection.close();
}
}
private oracledatareader createoracledatareader(string vsql)
{
oracleconnection voracleconn = openoracledbconn();
oraclecommand voraclecommand = new oraclecommand(vsql, voracleconn);
oracledatareader voracledatareader = voraclecommand.executereader();
return voracledatareader;
}
private oracledataadapter createoledbdataadapter(string vsql)
{
oracleconnection voracleconn = openoracledbconn();
oracledataadapter voracledataadapter = new oracledataadapter(vsql, voracleconn);
closeoracledbconn(voracleconn);
return voracledataadapter;
}
public string getdatetimenow()
{
return system.datetime.now.tostring("u").replace("z", "").replace("z", "");
}
private void writelog(string vmessage)
{
try
{
string vtempvalue = string.empty;
string vfilepath = application.startuppath;
string vxmlpath = system.configuration.configurationmanager.appsettings["logaddress"];
vxmlpath = vfilepath + vxmlpath;
xmldocument xmldoc = new xmldocument();
xmldoc.load(vxmlpath);
xmlnode root = xmldoc.selectsinglenode("//root");
xmlelement xe = xmldoc.createelement("node");//创建一个节点
xmlelement xesub01 = xmldoc.createelement("rownum");
xesub01.innertext = root.childnodes.count.tostring();
xe.appendchild(xesub01);//添加到节点中
xmlelement xesub02 = xmldoc.createelement("message");
xesub02.innertext = vmessage;
xe.appendchild(xesub02);//添加到节点中
xmlelement xesub03 = xmldoc.createelement("insertime");
xesub03.innertext = getdatetimenow();
xe.appendchild(xesub03);//添加到节点中
root.appendchild(xe);//添加到节点中
xmldoc.save(vxmlpath);
root = null;
xmldoc = null;
}
catch (exception ex)
{
writelog(ex.message);
}
}
}