/// <summary>
/// Get XMLType data as Oracle Type
/// </summary>
/// <param name="dr"></param>
public static void GetOraType(OracleDataReader dr)
{
try
{
// Get the XMLType column as an OracleXmlType
OracleXmlType xml = dr.GetOracleXmlType(0);
// Print out the xml data in the OracleXmlType object
Console.WriteLine("Get XMLType column using GetOracleXmlType(i)");
Console.WriteLine("Value: " + xml.Value);
Console.WriteLine(String.Empty);
xml.Dispose();
// Get the XMLType column as an OracleString
OracleString ostr = dr.GetOracleString(0);
// Print out the xml data in the OracleString object
Console.WriteLine("Get XMLType column using GetOracleString(i)");
Console.WriteLine(ostr);
Console.WriteLine(String.Empty);
// Get the XMLType column as an Oracle type
Object obj = dr.GetOracleValue(0);
// Print the type name of the Oracle type object
Console.WriteLine("Get XMLType column using GetOracleValue(i)");
Console.WriteLine("TypeName: " + obj.GetType().AssemblyQualifiedName);
Console.WriteLine(String.Empty);
}
catch (Exception e)
{
Console.WriteLine("GetOraType Error: {0}", e.Message);
}
}
/*********************************************************************************
* This method is called when the 'Update' button is clicked. The purpose of this
* method is to update the 'Emp_View' record based on the 'Empno' key column.
* An OracleXmlType object based on the selected record from the dataGrid is
* created. The OracleXmlType is updated using the 'OracleXmlType.Update' method.
* This updated OracleXmlType object is bound as a parameter to an OracleCommand
* for updating the XmlType view.
********************************************************************************/
public bool updateRecord(string rec, string keycolumn)
{
try
{
// Creating an OracleXMLType object
OracleXmlType ox = new OracleXmlType(ConnectionMgr.conn, rec);
// OracleCommand object for updating the 'Emp_View' in the database
OracleCommand cmd = new OracleCommand("", ConnectionMgr.conn);
// Declare the SQL statement to update the XML record in the view
// Use XPATH to identify the 'empno' primary key
cmd.CommandText = "UPDATE emp_view t SET value(t)= :1 " +
" WHERE extractvalue(value(t), '/EMP/EMPNO')=" + keycolumn;
cmd.CommandType = CommandType.Text;
// Bind the updated OracleXmlType object to the OracleCommand
cmd.Parameters.Add(":1", OracleDbType.XmlType, ox, ParameterDirection.Input);
// Execute the update command
cmd.ExecuteNonQuery();
MessageBox.Show("Record updated successfully !");
cmd.Dispose();
return(true);
}
catch (Exception ex)
{
MessageBox.Show("error :" + ex.Message + ex.StackTrace);
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);
// Create a new queue object
OracleAQQueue _queueObj = new OracleAQQueue("EDZEHOO.JobsXML", _connObj);
// Set the payload type to XML
_queueObj.MessageType = OracleAQMessageType.Xml;
_connObj.Open();
OracleTransaction _txn = _connObj.BeginTransaction();
// Dequeue the message.
_queueObj.DequeueOptions.Visibility = OracleAQVisibilityMode.OnCommit;
_queueObj.DequeueOptions.Wait = 10;
_queueObj.DequeueOptions.ProviderSpecificType = true;
OracleAQMessage _deqMsg = _queueObj.Dequeue();
OracleXmlType _jobXML = (OracleXmlType)_deqMsg.Payload;
MessageBox.Show("Dequeued Payload Data: \n" + _jobXML.Value);
_txn.Commit();
_queueObj.Dispose();
_connObj.Close();
_connObj.Dispose();
_connObj = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void button1_Click(object sender, EventArgs e)
{
string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;";
try
{
OracleConnection _connObj = new OracleConnection(_connstring);
// Create a new queue object
OracleAQQueue _queueObj = new OracleAQQueue("EDZEHOO.JobsXML", _connObj);
_connObj.Open();
OracleTransaction _txn = _connObj.BeginTransaction();
// Set payload type to XML
_queueObj.MessageType = OracleAQMessageType.Xml;
// Create a new message object
OracleAQMessage _msg = new OracleAQMessage();
OracleXmlType _jobXML = new OracleXmlType(_connObj, "<JOB><JOBID>J1234</JOBID><JOBNAME>Feed Snuppy</JOBNAME></JOB>");
_msg.Payload = _jobXML;
// Enqueue the message
_queueObj.EnqueueOptions.Visibility = OracleAQVisibilityMode.OnCommit;
_queueObj.Enqueue(_msg);
// Display the payload data that was enqueued
MessageBox.Show("Payload Data : \n" + _jobXML.Value);
_txn.Commit();
_queueObj.Dispose();
_connObj.Close();
_connObj.Dispose();
_connObj = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void button12_Click(object sender, EventArgs e)
{
string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;";
try
{
OracleConnection _connObj = new OracleConnection(_connstring);
OracleCommand _cmdObj = new OracleCommand("", _connObj);
_connObj.Open();
_cmdObj.CommandType = CommandType.Text;
string _sql;
_sql = "SELECT XMLQuery('" +
"for $i in ora:view(\"PRODUCT_EXTRAINFO\") " +
"where $i/ROW/INFO/PRODUCT/CATEGORY = $MyID " +
"return $i' " +
"PASSING :MyID AS \"MyID\" RETURNING CONTENT) " +
"FROM DUAL";
_cmdObj.CommandText = _sql;
//Pass in “Engines” as the category to search for
_cmdObj.Parameters.Add("MyID", OracleDbType.Varchar2, "Engines",
ParameterDirection.Input);
OracleDataReader _rdrObj = _cmdObj.ExecuteReader();
_rdrObj.Read();
OracleXmlType xml = _rdrObj.GetOracleXmlType(0);
MessageBox.Show(xml.Value);
_connObj.Close();
_connObj.Dispose();
_connObj = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void button5_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 _data = "";
_data = "<PRODUCT xmlns=\"PRODUCT.xsd\">" +
" <CATEGORY>Slipspace drives</CATEGORY>" +
" <PERSON_IN_CHARGE>Fujikawa</PERSON_IN_CHARGE>" +
" <REGIONAL_PRICING>" +
" <EASTASIA>5000</EASTASIA>" +
" <AMERICAS>8000</AMERICAS>" +
" </REGIONAL_PRICING> " +
"</PRODUCT>";
OracleXmlType _oracleXmlType = new OracleXmlType(_connObj, _data);
MessageBox.Show("Validation result is : " +
_oracleXmlType.Validate("PRODUCT.xsd"));
_connObj.Close();
_connObj.Dispose();
_connObj = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void button4_Click(object sender, EventArgs e)
{
string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;";
try
{
OracleConnection _connObj = new OracleConnection(_connstring);
_connObj.Open();
OracleCommand _cmdObj = new OracleCommand("proc_InsertProdInfo", _connObj);
_cmdObj.CommandType = CommandType.StoredProcedure;
//Define first input parameter
OracleParameter _ProductIDParam = new OracleParameter("ProductID",
OracleDbType.Varchar2);
_ProductIDParam.Value = "W1";
_cmdObj.Parameters.Add(_ProductIDParam);
//Define the second input parameter
OracleParameter _ProductInfoParam = new OracleParameter("ProductInfo",
OracleDbType.XmlType);
OracleXmlType _ProductInfoXML = new OracleXmlType(_connObj, "<PRODUCT><CATEGORY>Accessories</CATEGORY><PERSON_IN_CHARGE>Mary Sabbath</PERSON_IN_CHARGE><REGIONAL_PRICING><EASTASIA>3.00</EASTASIA><AMERICAS>8.00</AMERICAS></REGIONAL_PRICING></PRODUCT>");
_ProductInfoParam.Value = _ProductInfoXML;
_cmdObj.Parameters.Add(_ProductInfoParam);
_cmdObj.ExecuteNonQuery();
_connObj.Close();
_connObj.Dispose();
_connObj = null;
MessageBox.Show("Product inserted");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void button3_Click(object sender, EventArgs e)
{
string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;";
try
{
OracleConnection _connObj = new OracleConnection(_connstring);
_connObj.Open();
OracleCommand _cmdObj = new OracleCommand("proc_GetProdInfo", _connObj);
_cmdObj.CommandType = CommandType.StoredProcedure;
//Define the first parameter – we want to retrieve the XML info for the
//product with the ID "E1"
OracleParameter _ProductIDParam = new OracleParameter("ProductID",
OracleDbType.Varchar2);
_ProductIDParam.Value = "E1";
_cmdObj.Parameters.Add(_ProductIDParam);
//Define the output parameter that receives the XMLType data
OracleParameter _ProductInfoParam = new OracleParameter("ProductInfo",
OracleDbType.XmlType);
_ProductInfoParam.Direction = ParameterDirection.Output;
_cmdObj.Parameters.Add(_ProductInfoParam);
_cmdObj.ExecuteNonQuery();
OracleXmlType _returnValue = (OracleXmlType)_ProductInfoParam.Value;
MessageBox.Show(_returnValue.Value);
_connObj.Close();
_connObj.Dispose();
_connObj = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
/*********************************************************************************
* 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);
}
}
public static TransportMessage DeserializeFromXml(OracleAQMessage message)
{
if (message == null)
{
return(null);
}
XmlDocument bodyDoc;
using (OracleXmlType type = (OracleXmlType)message.Payload)
{
bodyDoc = type.GetXmlDocument();
}
var bodySection = bodyDoc.DocumentElement.SelectSingleNode("Body").FirstChild as XmlCDataSection;
var headerDictionary = new SerializableDictionary <string, string>();
var headerSection = bodyDoc.DocumentElement.SelectSingleNode("Headers");
if (headerSection != null)
{
headerDictionary.SetXml(headerSection.InnerXml);
}
Address replyToAddress = null;
var replyToAddressSection = bodyDoc.DocumentElement.SelectSingleNode("ReplyToAddress");
if (replyToAddressSection != null && !string.IsNullOrWhiteSpace(replyToAddressSection.InnerText))
{
replyToAddress = Address.Parse(replyToAddressSection.InnerText.Trim());
}
MessageIntentEnum messageIntent = default(MessageIntentEnum);
var messageIntentSection = bodyDoc.DocumentElement.SelectSingleNode("MessageIntent");
if (messageIntentSection != null)
{
messageIntent = (MessageIntentEnum)Enum.Parse(typeof(MessageIntentEnum), messageIntentSection.InnerText);
}
var transportMessage = new TransportMessage(new Guid(message.MessageId).ToString(), headerDictionary)
{
Body = bodySection != null?Encoding.UTF8.GetBytes(bodySection.Data) : new byte[0],
ReplyToAddress = replyToAddress,
MessageIntent = messageIntent,
};
return(transportMessage);
}
public string XmlToString(object dbXmlData)
{
if (dbXmlData == null)
{
return("");
}
OracleXmlType oxt = dbXmlData as OracleXmlType;
if (oxt == null)
{
return("");
}
return(oxt.Value);
}
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());
}
}
private void OracleXML(string sName, string sID = "")
{
try
{
string sFilePath = "";
//判读是否有StripID
if (string.IsNullOrEmpty(sName))
{
return;
}
else
{
sFilePath = "D:/Map/" + sName + ".xml";
}
//判断文件是否存在
if (System.IO.File.Exists(sFilePath))
{
}
else
{
return;
}
XmlDocument xe = new XmlDocument();
xe.Load(sFilePath);//加载XML文件
OracleConnection conn = new OracleConnection("Data Source = KS_QAS_AY; User Id = FA; Password = FA");
conn.Open();
OracleXmlType cxml = new OracleXmlType(conn, xe);
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO MARKOUT_STRIPMAP VALUES ('" + sID + "','" + sName + "',:pb)";
cmd.Parameters.Add("pb", OracleDbType.XmlType, 1).Value = cxml;
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception e)
{
e.Message.ToString();
}
}
/// <summary>
/// The main entry point for the application.
/// </summary>
static void Main(string[] args)
{
// Connect
string constr = "User Id=scott;Password=tiger;Data Source=oracle";
OracleConnection con = Connect(constr);
// Create an OracleXmlType from String
StringBuilder blr = new StringBuilder();
blr.Append("<?xml version=\"1.0\"?> <PO pono=\"1\"> ");
blr.Append("<PNAME>Po_1</PNAME> <CUSTNAME>John</CUSTNAME> ");
blr.Append("<SHIPADDR> <STREET>1033, Main Street</STREET> ");
blr.Append("<CITY>Sunnyvale</CITY> <STATE>CA</STATE> </SHIPADDR> ");
blr.Append("</PO>");
OracleXmlType xml = new OracleXmlType(con, blr.ToString());
//Demonstrate various properties on OracleXmlType
PropDemo(xml);
con.Close();
con.Dispose();
}
public void TestOracleTypes(string context)
{
using (var conn = new DataConnection(context))
{
var arr = new byte[] { 0x30, 0x39 };
Assert.That(conn.Execute <OracleBinary> ("SELECT to_blob('3039') FROM sys.dual").Value, Is.EqualTo(arr));
Assert.That(conn.Execute <OracleBlob> ("SELECT to_blob('3039') FROM sys.dual").Value, Is.EqualTo(arr));
Assert.That(conn.Execute <OracleDecimal> ("SELECT Cast(1 as decimal) FROM sys.dual").Value, Is.EqualTo(1));
Assert.That(conn.Execute <OracleString> ("SELECT Cast('12345' as char(6)) FROM sys.dual").Value, Is.EqualTo("12345 "));
Assert.That(conn.Execute <OracleClob> ("SELECT ntextDataType FROM AllTypes WHERE ID = 2").Value, Is.EqualTo("111"));
Assert.That(conn.Execute <OracleDate> ("SELECT datetimeDataType FROM AllTypes WHERE ID = 2").Value, Is.EqualTo(new DateTime(2012, 12, 12, 12, 12, 12)));
Assert.That(conn.Execute <OracleTimeStamp>("SELECT datetime2DataType FROM AllTypes WHERE ID = 2").Value, Is.EqualTo(new DateTime(2012, 12, 12, 12, 12, 12, 12)));
#if !MANAGED_ORACLE
Assert.That(conn.Execute <OracleXmlType> ("SELECT XMLTYPE('<xml/>') FROM sys.dual").Value, Is.EqualTo("<xml/>\n"));
var xmlType = new OracleXmlType((OracleConnection)conn.Connection, "<xml/>");
Assert.That(conn.Execute <OracleXmlType>(PathThroughSql, new DataParameter("p", xmlType)).Value, Is.EqualTo("<xml/>\n"));
Assert.That(conn.Execute <OracleXmlType>(PathThroughSql, new DataParameter("p", xmlType, DataType.Xml)).Value, Is.EqualTo("<xml/>\n"));
#endif
}
}
public virtual System.Array CreateArray(int length)
{
OracleXmlType[] collElem = new OracleXmlType[length];
return(collElem);
}
private void button6_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";
OracleCommand _cmdObj = new OracleCommand(_sql, _connObj);
OracleDataReader _rdrObj = _cmdObj.ExecuteReader();
if (_rdrObj.HasRows)
{
while (_rdrObj.Read())
{
OracleXmlType _oracleXmlType =
_rdrObj.GetOracleXmlType(_rdrObj.GetOrdinal("INFO"));
string _xsl;
_xsl = "<?xml version=\"1.0\"?>" +
"<xsl:stylesheet version=\"1.0\" " +
" xmlns:xsl=\"http://www.w3.org/1999/XSL/Transform\">" +
" <xsl:template match=\"/\">" +
" <ProductExtraInfo>" +
" <xsl:apply-templates select=\"PRODUCT\"/>" +
" </ProductExtraInfo>" +
" </xsl:template>" +
" <xsl:template match=\"PRODUCT\">" +
" <xsl:apply-templates select=\"CATEGORY\"/>" +
" <xsl:apply-templates select=\"REGIONAL_PRICING\"/>" +
" </xsl:template>" +
" <xsl:template match=\"CATEGORY\">" +
" <CategoryName>" +
" <xsl:value-of select=\".\"/>" +
" </CategoryName>" +
" </xsl:template>" +
" <xsl:template match=\"REGIONAL_PRICING\">" +
" <xsl:apply-templates select=\"EASTASIA\"/>" +
" <xsl:apply-templates select=\"AMERICAS\"/>" +
" </xsl:template>" +
" <xsl:template match=\"EASTASIA\">" +
" <EastAsianPrice>" +
" <xsl:value-of select=\".\"/>" +
" </EastAsianPrice>" +
" </xsl:template>" +
" <xsl:template match=\"AMERICAS\">" +
" <AmericanPrice>" +
" <xsl:value-of select=\".\"/>" +
" </AmericanPrice>" +
" </xsl:template>" +
"</xsl:stylesheet>";
OracleXmlType _transformedXML = _oracleXmlType.Transform(_xsl, "");
MessageBox.Show(_transformedXML.Value);
}
}
_rdrObj.Close();
_connObj.Close();
_connObj.Dispose();
_connObj = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
static void Main(string[] args)
{
string xml = "<Employees><Employee number='1' firstname='john' lastname='smith'></Employee><Employee number='2' firstname='john1' lastname='smith1'></Employee><Employee number='3' firstname='john2' lastname='smith2'></Employee></Employees>";
OracleConnection oc = new OracleConnection("Data Source=dev1;User ID=system;Password=oracle;");
oc.Open();
OracleCommand cmd = oc.CreateCommand();
string sql =
@"
SELECT
xt.*
FROM
XMLTABLE
(
'/Employees/Employee' PASSING :l_xml
COLUMNS
empno VARCHAR2(4) PATH '@number',
firstname VARCHAR2(10) PATH '@firstname',
lastname VARCHAR2(9) PATH '@lastname'
) xt
join employees e on xt.empno = e.empno";
cmd.CommandText = sql;
OracleParameter p = cmd.CreateParameter();
p.ParameterName = "l_xml";
OracleXmlType t = new OracleXmlType(oc, xml);
p.Value = t;
/*p.OracleDbType = OracleDbType.Clob;
* OracleClob c = new OracleClob(oc);
* c.Read(xml.ToCharArray(), 0, xml.Length);
* p.Value = c;*/
cmd.Parameters.Add(p);
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (reader.HasRows)
{
object[] values = new object[reader.FieldCount];
reader.GetValues(values);
Console.WriteLine
(
string.Format
(
"empno='{0}' lastname={1} firstname='{2}'",
reader.GetDecimal(reader.GetOrdinal("empno")),
reader.GetString(reader.GetOrdinal("lastname")),
reader.GetString(reader.GetOrdinal("firstname"))
)
);
}
}
//c.Close();
reader.Close();
oc.Close();
}
/// <summary>
/// The main entry point for the application.
/// </summary>
static void Main(string[] args)
{
// Create the connection.
string constr = "User Id=scott;Password=tiger;Data Source=oracle";
OracleConnection con = new OracleConnection(constr);
con.Open();
// Create an OracleXmlType from String
StringBuilder blr = new StringBuilder();
blr.Append("<?xml version=\"1.0\"?> <PO pono=\"1\"> ");
blr.Append("<PNAME>Po_1</PNAME> <CUSTNAME>John</CUSTNAME> ");
blr.Append("<SHIPADDR> <STREET>1033, Main Street</STREET> ");
blr.Append("<CITY>Sunnyvale</CITY> <STATE>CA</STATE> </SHIPADDR> ");
blr.Append("</PO>");
// Create a OracleXmlStream from OracleXmlType
OracleXmlType xml = new OracleXmlType(con, blr.ToString());
OracleXmlStream strm = new OracleXmlStream(xml);
// Print the length of xml data in the stream
Console.WriteLine("OracleXmlStream Length: " + strm.Length);
Console.WriteLine("");
// Print the xml data in the stream
Console.WriteLine("OracleXmlStream Value: " + strm.Value);
Console.WriteLine("");
// Check CanRead property on the stream
Console.WriteLine("OracleXmlStream CanRead: " + strm.CanRead);
Console.WriteLine("");
// Check CanWrite property on the stream
Console.WriteLine("OracleXmlStream CanWrite: " + strm.CanWrite);
Console.WriteLine("");
// Print current position in stream
Console.WriteLine("OracleXmlStream Position: " + strm.Position);
Console.WriteLine("");
// Read 10 bytes at a time from the stream
int rb = 0;
int curpos = 0;
byte[] bytebuf = new byte[500];
while ((rb = strm.Read(bytebuf, curpos, 10)) > 0)
{
curpos += rb;
}
// Print the contents of the byte array
System.Text.Encoding encoding = System.Text.Encoding.Unicode;
Console.WriteLine("OracleXmlStream Read byte[]: " +
encoding.GetString(bytebuf));
Console.WriteLine("");
// Print current position in stream
Console.WriteLine("OracleXmlStream Position: " + strm.Position);
Console.WriteLine("");
strm.Dispose();
xml.Dispose();
con.Close();
con.Dispose();
}
private object ExecuteFunctionCore(string sqlText, DbParameter[] sqlParams)
{
try
{
//记录当前的参数调用状态，编译异常时明确问题原因
//DebugPar.WriteBugParWithSql(sqlText, sqlParams);
//调用时必须要自己设置正确的返回值类型，才能正确使用，使用不方便。
//改用select的方式查询函数，可以直接用object类型接收返回值。
string strResult = null;
//确保数据库已经连接
ConnectDB();
//处理参数，执行SQL，返回结果
using (OracleCommand cmd = new OracleCommand(sqlText, _conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.BindByName = true;
cmd.AddToStatementCache = true;
//增加参数
if (sqlParams != null)
{
cmd.Parameters.AddRange(sqlParams);
}
//增加返回值
//增加标准的返回值
OracleParameter pOut = null;
pOut = new OracleParameter("retVAL", OracleDbType.Double);
pOut.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(pOut);
//执行函数
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//调用数据库重连
if (_conn.State != ConnectionState.Open)
{
ReconnectDB();
cmd.ExecuteNonQuery();
}
else
{
throw ex;
}
}
//提取函数执行返回值
foreach (OracleParameter var in cmd.Parameters)
{
if (var.Direction == ParameterDirection.ReturnValue)
{
if (var.Value == null || (var.Value is INullable && (var.Value as INullable).IsNull))
{
strResult = null;
}
else if (var.Value is OracleXmlType)
{
OracleXmlType valXMLType = (var.Value as OracleXmlType);
if (!valXMLType.IsEmpty)
{
strResult = valXMLType.Value.ToString();
}
}
else
{
strResult = var.Value.ToString();
}
break;
}
}
}
return(strResult);
}
catch (Exception ex)
{
throw new DBException("语句执行错误：" + sqlText + "\r\n\r\n 参数信息：" + GetExecuteParameterInfo(sqlParams) + "\r\n\r\n 错误信息：" + ex.Message, ex);
}
}
public OracleXmlValue(OracleXmlType xmlType, int previewLength = DefaultPreviewLength)
{
_xmlType = xmlType;
PreviewLength = previewLength;
}
/// <summary>
/// 获取Oracle参数的值
/// </summary>
/// <param name="oraParam"></param>
/// <returns></returns>
protected virtual object GetOraParamVal(OracleParameter oraParam)
{
if (oraParam.Value == null || (oraParam.Value is INullable && (oraParam.Value as INullable).IsNull))
{
return(DBNull.Value);
}
object val = DBNull.Value;
if (oraParam.Value is OracleXmlType)
{
OracleXmlType xmltype = (OracleXmlType)oraParam.Value;
if (!xmltype.IsEmpty)
{
val = xmltype.Value;
}
}
else if (oraParam.Value is OracleBlob)
{
OracleBlob blobVal = (OracleBlob)oraParam.Value;
if (!blobVal.IsNull)
{
val = (oraParam.Value as OracleBlob).Value;
}
}
else if (oraParam.Value is OracleClob)
{
OracleClob clobVal = (OracleClob)oraParam.Value;
if (!clobVal.IsNull)
{
val = clobVal.Value;
}
}
else if (oraParam.Value is OracleDecimal)
{
OracleDecimal decimalVal = (OracleDecimal)oraParam.Value;
if (!decimalVal.IsNull)
{
val = decimalVal.Value;
}
}
else if (oraParam.Value is OracleDate)
{
OracleDate dateVal = (OracleDate)oraParam.Value;
if (!dateVal.IsNull)
{
val = dateVal.Value;
}
}
else if (oraParam.Value is OracleString)
{
OracleString stringVal = (OracleString)oraParam.Value;
if (!stringVal.IsNull)
{
val = stringVal.Value;
}
}
else if (oraParam.Value is OracleBFile)
{
OracleBFile fileVal = oraParam.Value as OracleBFile;
if (!fileVal.IsNull)
{
val = fileVal.Value;
}
}
else if (oraParam.Value is OracleBinary)
{
OracleBinary binaryVal = (OracleBinary)oraParam.Value;
if (!binaryVal.IsNull)
{
val = binaryVal.Value;
}
}
else if (oraParam.Value is OracleTimeStamp)
{
OracleTimeStamp timeStampVal = (OracleTimeStamp)oraParam.Value;
if (!timeStampVal.IsNull)
{
val = timeStampVal.Value;
}
}
else if (oraParam.Value is OracleRefCursor)
{
using (OracleRefCursor timeStampVal = (OracleRefCursor)oraParam.Value)
{
if (timeStampVal.IsNull)
{
return(null);
}
OracleDataReader dataReader = timeStampVal.GetDataReader();
DataTable datatable = new DataTable();
datatable.Load(dataReader);
return(datatable);
}
}
else
{
val = oraParam.Value;
}
return(val);
}
/// <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);
}
}
/// <summary>
/// The main entry point for the application.
/// </summary>
static void Main(string[] args)
{
// Create the connection.
string constr = "User Id=scott;Password=tiger;Data Source=oracle";
OracleConnection con = new OracleConnection(constr);
con.Open();
// Register the XML schema in the database
RegisterXmlSchema(con);
// Construct the schema based XML data
StringBuilder blr = new StringBuilder();
blr.Append("<?xml version=\"1.0\"?> ");
blr.Append("<PO xmlns=\"po.xsd\" ");
blr.Append("xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" ");
blr.Append("xsi:schemaLocation=\"po.xsd po.xsd\" PONO=\"1\"> ");
blr.Append("<PNAME>Po_1</PNAME> <CUSTNAME>John</CUSTNAME> ");
blr.Append("<SHIPADDR> <STREET>1033, Main Street</STREET> ");
blr.Append("<CITY>Sunnyvale</CITY> <STATE>CA</STATE> </SHIPADDR> ");
blr.Append("</PO>");
// Create a OracleXmlType from the schema based XML data
OracleXmlType xml = new OracleXmlType(con, blr.ToString());
// Print the XML data in the OracleXmlType
Console.WriteLine("OracleXmlType Value: " + xml.Value);
Console.WriteLine("");
// Validate the XML data in the OracleXmlType against the schema registered
// in the oracle database
if (xml.Validate("po.xsd"))
{
Console.WriteLine("OracleXmlType Schema Validation: Success\n");
}
else
{
Console.WriteLine("OracleXmlType Schema Validation: Failed\n");
}
// Print the RootElement of the XML data in the oraclexmltype
Console.WriteLine("OracleXmlType RootElement: " + xml.RootElement);
Console.WriteLine("");
// Print the SchemaUrl of the XML data in the oraclexmltype
Console.WriteLine("OracleXmlType SchemaUrl: " + xml.SchemaUrl);
Console.WriteLine("");
// Print the XML Schema of the XML data in the oraclexmltype
Console.WriteLine("OracleXmlType Schema: " + xml.Schema.Value);
Console.WriteLine("");
// Dispose the OracleXmlType
xml.Dispose();
// Unregister the XML schema from the database
DeleteXmlSchema(con);
// Close the connection
con.Close();
con.Dispose();
}
