form1.cs

oracle xmltype类型字段的数据插入和检索

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;

using System.Configuration;
using System.Xml;
using System.IO;

//using System.Data.OracleClient;
using Oracle.DataAccess.Types;
using Oracle.DataAccess.Client;



namespace oracleXmlTest
{
    

    public partial class Form1 : Form
    {
        //public OracleLob m_TemporaryCLOB = null;
        //private OracleDBOprator oraOper = new OracleDBOprator(conn);
       
        
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
           //m_TemporaryCLOB.g
           // OracleDataReader reader 
            string XmlFileName = "E:\\my.net\\oracleXmlTest\\mcc.xml";
            XmlTextReader aXmlTextReader = new XmlTextReader(XmlFileName);
            System.IO.FileInfo fi = new System.IO.FileInfo(XmlFileName);
			int iSize = (int)fi.Length;
            byte []img = new byte[iSize];
			aXmlTextReader.ReadBinHex(img,0,iSize);
            StreamReader sr = new StreamReader("E:\\my.net\\oracleXmlTest\\mcc.xml");

            XmlDocument xDocument = new XmlDocument();
            xDocument.Load("E:\\my.net\\oracleXmlTest\\mcc.xml");
            string line;
            string outstring = ""; 
                // Read and display lines from the file until the end of 
                // the file is reached.
             while ((line = sr.ReadLine()) != null)
             {
                 outstring += line;
             }
            // OracleConnection conn = new OracleConnection("Data Source=GISORA;User Id=meng;Password=meng");
             OracleConnection conn = new OracleConnection("User Id=meng;Password=meng;Data Source=GISORA");
             OracleConnection conn2 = new OracleConnection();
             conn.Open();
            OracleCommand cmd = new OracleCommand();
             cmd.Connection = conn;
            // OracleXmlType cxml = new OracleXmlType(conn, outstring);
             OracleXmlType cxml = new OracleXmlType(conn, xDocument);


           //   cmd.CommandText = "INSERT INTO resumes4 VALUES (122,XMLType(:pb))";
               // cmd.CommandText = "INSERT INTO resumes3 VALUES (128,XMLType('" + outstring + "'))";
          //   cmd.CommandText = "INSERT INTO resumes2 VALUES (300,:pb)";
          // cmd.CommandText = "INSERT INTO resumes3 VALUES (109,XMLType('<abc>se</abc>'))";
          //   cmd.CommandText = "INSERT INTO resumes3 VALUES ('800',:pb)";
             cmd.CommandText = "INSERT INTO resumes4 VALUES (455,:pb)";
             cmd.Parameters.Add("pb", OracleDbType.XmlType, iSize).Value = cxml;
          
           //   OracleParameter op = new OracleParameter("pb", OracleType.Clob);
           //  op.Value = outstring;
           //  cmd.Parameters.Add(op);

            
             //  Oracle.DataAccess.Types.OracleClob clob = new OracleClob(conn);

              
            //  cmd.Parameters.Add("pb", OracleType.Clob, iSize).Value = outstring;  
            
             //  cmd.Parameters.Add("pb", OracleDbType.XmlType, iSize).Value = outstring;
             cmd.ExecuteNonQuery();            
             conn.Close();

          //string strSQL = "Select employee_id, resume from resumes3 where employee_id = 300";

          //   OracleTransaction oTrans = conn.BeginTransaction(IsolationLevel.Serializable);
          //   OracleCommand oCmd = new OracleCommand(strSQL, conn);
          //   OracleDataReader oDrdr = oCmd.ExecuteReader(CommandBehavior.SequentialAccess);
          //   oDrdr.Read();
          //   OracleClob oclob = new OracleClob(conn);
            
          //   cxml = oDrdr.GetOracleXmlType(1);

             //cxml.GetStream(
             ////Start writing the chunks into the clob 

             //cxml.Write(img, 0, iSize);
            
             ////Needs to be called since we called BeginChunkWrite()
             //cxml.EndChunkWrite();
             ////Commit the Transaction
             //oTrans.Commit();

             //cxml.Close();

            MessageBox.Show("success!");
            this.Close();

            
         
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //   OracleParameter op = new OracleParameter("pb", OracleType.Clob);
            //  op.Value = outstring;
            //  cmd.Parameters.Add(op);


            //  Oracle.DataAccess.Types.OracleClob clob = new OracleClob(conn);


            //  cmd.Parameters.Add("pb", OracleType.Clob, iSize).Value = outstring;  
            string XmlFileName = "E:\\my.net\\oracleXmlTest\\mcc.xml";
            System.IO.FileInfo fi = new System.IO.FileInfo(XmlFileName);
            int iSize = (int)fi.Length;
            XmlDocument xDocument = new XmlDocument();
            xDocument.Load(XmlFileName);
            
            OracleConnection conn = new OracleConnection("User Id=meng;Password=meng;Data Source=GISORA");
            conn.Open();
            OracleXmlType cxml = new OracleXmlType(conn, xDocument);
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "INSERT INTO metanodes VALUES ('mcc2',:pb)";
            cmd.Parameters.Add("pb", OracleDbType.XmlType, iSize).Value = cxml;

            cmd.ExecuteNonQuery();
            conn.Close();

            MessageBox.Show("success!");
            this.Close();




        }

        private void button3_Click(object sender, EventArgs e)
        {
            OracleConnection conn = new OracleConnection("User Id=meng;Password=meng;Data Source=GISORA");
            conn.Open();              

            string cmdString = " SELECT x.metadata.getCloBVal() FROM metanodes x WHERE metaId='aa' ";
            OracleCommand cmd = new OracleCommand(cmdString,conn);
            OracleDataReader myReader = cmd.ExecuteReader();           
            myReader.Read();
           string xmlfile = myReader.GetString(0);         
          
            XmlDocument myDocument = new XmlDocument();
            myDocument.Load(new StringReader(xmlfile));
            myDocument.Save("c:\\aa.xml");

            conn.Close();

            MessageBox.Show("success!");
            this.Close();
            //   OracleParameter op = new OracleParameter("pb", OracleType.Clob);
            //  op.Value = outstring;
            //  cmd.Parameters.Add(op);


            //  Oracle.DataAccess.Types.OracleClob clob = new OracleClob(conn);


            //  cmd.Parameters.Add("pb", OracleType.Clob, iSize).Value = outstring;  

        }
    }
}