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);

}

}

}