/*********************************************************************************
* This method is called when the 'Delete' button is clicked. The purpose of this
* method is to delete a record from the 'Emp_View' that is selected from the datagrid.
* Deletion happens based on the 'Empno' key column of the selected record.
* ********************************************************************************/
public bool deleteRecord(string xmlRec)
{
try
{
// OracleCommand object for delete operation
OracleCommand delCmd = new OracleCommand("", ConnectionMgr.conn);
// Use OracleXmlType to extract the 'empno' primary key to identify the record
// to be deleted
OracleXmlType oraxml = new OracleXmlType(ConnectionMgr.conn, xmlRec);
String empno = oraxml.Extract("/EMP/EMPNO/text()", "").Value;
// Declare the SQL statement to delete the XML record from the view
delCmd.CommandText = "DELETE FROM emp_view t WHERE EXTRACTVALUE(VALUE(t), " +
"'/EMP/EMPNO')=" + empno;
delCmd.CommandType = CommandType.Text;
// Execute the delete command
delCmd.ExecuteNonQuery();
MessageBox.Show("Record deleted successfully");
oraxml = null;
return(true);
}
catch (Exception ex)
{
MessageBox.Show("error: " + ex.Message);
return(false);
}
}
private void button2_Click(object sender, EventArgs e)
{
string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;";
try
{
OracleConnection _connObj = new OracleConnection(_connstring);
_connObj.Open();
string _sql = "SELECT INFO FROM PRODUCT_EXTRAINFO";
string _message = "";
OracleCommand _cmdObj = new OracleCommand(_sql, _connObj);
OracleDataReader _rdrObj = _cmdObj.ExecuteReader();
if (_rdrObj.HasRows)
{
while (_rdrObj.Read())
{
OracleXmlType _oracleXmlType = _rdrObj.GetOracleXmlType(_rdrObj.GetOrdinal("INFO"));
if (!_oracleXmlType.IsNull)
{
string _xPath = "/PRODUCT/CATEGORY";
string _nsMap = null;
if (_oracleXmlType.IsExists(_xPath, _nsMap))
{
OracleXmlType _oracleXmlTypeNode = _oracleXmlType.Extract(_xPath, _nsMap);
if (!_oracleXmlTypeNode.IsEmpty)
{
_message = "Category tag:\t" +
_oracleXmlTypeNode.Value;
}
}
_message += "Raw XML:\n" + _oracleXmlType.Value;
MessageBox.Show(_message);
}
}
}
_rdrObj.Close();
_connObj.Close();
_connObj.Dispose();
_connObj = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
/// <summary>
/// Demonstrate the properties and methods on OracleXmlType
/// </summary>
/// <param name="connectStr"></param>
/// <returns></returns>
public static void PropDemo(OracleXmlType xml)
{
try
{
// Value property
Console.WriteLine("Value property: ");
Console.WriteLine(xml.Value);
Console.WriteLine("");
// Get an Oracle XML Stream
OracleXmlStream strm = xml.GetStream();
Console.WriteLine("GetStream() method: ");
Console.WriteLine(strm.Value);
Console.WriteLine("");
strm.Dispose();
// Get a .NET XmlReader
XmlReader xmlRdr = xml.GetXmlReader();
XmlDocument xmlDocFromRdr = new XmlDocument();
xmlDocFromRdr.Load(xmlRdr);
Console.WriteLine("GetXmlReader() method: ");
Console.WriteLine(xmlDocFromRdr.OuterXml);
Console.WriteLine("");
xmlDocFromRdr = null;
xmlRdr = null;
// Get a .NET XmlDocument
XmlDocument xmlDoc = xml.GetXmlDocument();
Console.WriteLine("GetXmlDocument() method: ");
Console.WriteLine(xmlDoc.OuterXml);
Console.WriteLine("");
xmlDoc = null;
// IsExists method
string xpathexpr = "/PO/SHIPADDR";
string nsmap = null;
if (xml.IsExists(xpathexpr, nsmap))
{
// Extract method
OracleXmlType xmle = xml.Extract(xpathexpr, nsmap);
// IsEmpty property
if (xmle.IsEmpty)
{
Console.WriteLine("Extract() method returns empty xml data");
}
else
{
Console.WriteLine("Extracted XML data: ");
Console.WriteLine(xmle.Value);
}
Console.WriteLine("");
xmle.Dispose();
}
// Use XSLT on the OracleXmlType
StringBuilder blr = new StringBuilder();
blr.Append("<?xml version=\"1.0\"?> ");
blr.Append("<xsl:stylesheet version=\"1.0\" ");
blr.Append("xmlns:xsl=\"http://www.w3.org/1999/XSL/Transform\"> ");
blr.Append(" <xsl:template match=\"/\"> ");
blr.Append(" <NEWPO> ");
blr.Append(" <xsl:apply-templates select=\"PO\"/> ");
blr.Append(" </NEWPO> ");
blr.Append(" </xsl:template> ");
blr.Append(" <xsl:template match=\"PO\"> ");
blr.Append(" <xsl:apply-templates select=\"CUSTNAME\"/> ");
blr.Append(" </xsl:template> ");
blr.Append(" <xsl:template match=\"CUSTNAME\"> ");
blr.Append(" <CNAME> <xsl:value-of select=\".\"/> </CNAME> ");
blr.Append(" </xsl:template> </xsl:stylesheet> ");
string pmap = null;
OracleXmlType xmlt = xml.Transform(blr.ToString(), pmap);
//Print the transformed xml data
Console.WriteLine("XML Data after Transform(): ");
Console.WriteLine(xmlt.Value);
// Update the CNAME in the transformed xml data
xpathexpr = "/NEWPO/CNAME/text()";
xmlt.Update(xpathexpr, nsmap, "NewName");
// See the updated xml data
Console.WriteLine("XML Data after Update(): ");
Console.WriteLine(xmlt.Value);
Console.WriteLine("");
xmlt.Dispose();
}
catch (Exception e)
{
Console.WriteLine("Error: {0}", e.Message);
}
}