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 Summary description 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 ("Exception info: 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 ("Exception info: 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 ("Exception info: 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");//Create a node
XmlElement xesub01 = xmldoc.createelement ("rownum");
Xesub01. InnerText = root. ChildNodes.Count.ToString ();
Xe. AppendChild (XESUB01);//Add to Node
XmlElement xesub02 = xmldoc.createelement ("message");
Xesub02. innertext = Vmessage;
Xe. AppendChild (XESUB02);//Add to Node
XmlElement xesub03 = xmldoc.createelement ("Insertime");
Xesub03. innertext = Getdatetimenow ();
Xe. AppendChild (XESUB03);//Add to Node
Root. AppendChild (XE);//Add to Node
Xmldoc.save (Vxmlpath);
root = null;
xmldoc = null;
}
catch (Exception ex)
{
Writelog (ex. message);
}
}
}
