/// <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(); }