(原) ODP.NET 演示如何利用 XML 文档保存更改

 

/**//* 演示如何利用 XML 文档保存更改
 * 1. 必须cmd.XmlCommandType = OracleXmlCommandType.*;
 * 2. 对于插入，必须使用 CommandText 为XML形式，设置 XmlSaveProperties 类的 KeyColumnsList UpdateColumnsList 等
 * 3. 对于查询，可以采用 stream 接收返回的流，sr.ReadToEnd()打印出来
 * 4. 对于更新，必须使用 CommandText 为XML形式，设置 XmlSaveProperties 类的 KeyColumnsList UpdateColumnsList 等
 *    这时，要定位记录以及要更新的列
 * 5. 对于删除，和更新差不多，主要在定位 KeyColumnsList[0] = "EMPNO";
*/

namespace XmlSave11
{
    using System;
    using System.Data;
    using System.Text;
    using System.IO;
    using Oracle.DataAccess.Client;
    using Oracle.DataAccess.Types;

    // 演示如何利用 XML 文档保存更改
    class Program
    {
        static void Main(string[] args)
        {
            int rows = 0;
            string[] KeyColumnsList = null;
            string[] UpdateColumnsList = null;
            Stream stream = null;
            StreamReader sr = null;

            // Define the XSL document for doing the transform.
            string xslstr = "<?xml version='1.0'?>\n" +
                            "<xsl:stylesheet version=\"1.0\" xmlns:xsl=\"http://www.w3.org/1999/XSL/Transform\">\n" +
                            "  <xsl:output encoding=\"utf-8\"/>\n" +
                            "  <xsl:param name=\"param1\">default</xsl:param>\n" +
                            "  <xsl:param name=\"param2\">default</xsl:param>\n" +
                            "  <xsl:template match=\"/\">\n" +
                            "    <ROWSET>\n" +
                            "      <xsl:apply-templates select=\"EMPLOYEES\"/>\n" +
                            "    </ROWSET>\n" +
                            "  </xsl:template>\n" +
                            "  <xsl:template match=\"EMPLOYEES\">\n" +
                            "      <xsl:apply-templates select=\"EMPLOYEE\"/>\n" +
                            "  </xsl:template>\n" +
                            "  <xsl:template match=\"EMPLOYEE\">\n" +
                            "    <ROW>\n" +
                            "    <EMPNO>\n" +
                            "      <xsl:apply-templates select=\"EMPLOYEE_ID\"/>\n" +
                            "    </EMPNO>\n" +
                            "    <ENAME>\n" +
                            "      <xsl:apply-templates select=\"EMPLOYEE_NAME\"/>\n" +
                            "    </ENAME>\n" +
                            "    <HIREDATE>\n" +
                            "        <xsl:value-of select=\"$param1\"/>\n" +
                            "    </HIREDATE>\n" +
                            "    <JOB>\n" +
                            "        <xsl:value-of select=\"$param2\"/>\n" +
                            "    </JOB>\n" +
                            "    </ROW>\n" +
                            "  </xsl:template>\n" +
                            "</xsl:stylesheet>\n";

            // Create the connection.
            string constr = "User Id=scott;Password=tiger;Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.24)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = bjoracle.oracle10g.mynet)))";
            OracleConnection con = new OracleConnection(constr);
            con.Open();

            // Set the date, and timestamp formats for Oracle 9i Release 2, or later.
            // This is just needed for queries.
            if (!con.ServerVersion.StartsWith("9.0") && !con.ServerVersion.StartsWith("8.1"))
            {
                OracleGlobalization sessionParams = con.GetSessionInfo();
                sessionParams.DateFormat = "YYYY-MM-DD\"T\"HH24:MI:SS";
                sessionParams.TimeStampFormat = "YYYY-MM-DD\"T\"HH24:MI:SS.FF3";
                sessionParams.TimeStampTZFormat = "YYYY-MM-DD\"T\"HH24:MI:SS.FF3";
                con.SetSessionInfo(sessionParams);
            }

            // Create the command.
            OracleCommand cmd = new OracleCommand("", con);

            // Set the XML command type to insert.
            cmd.XmlCommandType = OracleXmlCommandType.Insert;  // 设置 XML 命令类型

            // Set the XML document.
            cmd.CommandText = "<?xml version=\"1.0\"?>\n" +
                              "<EMPLOYEES>\n" +
                              "  <EMPLOYEE>\n" +
                              "    <EMPLOYEE_ID>1234</EMPLOYEE_ID>\n" +
                              "    <EMPLOYEE_NAME>Smith</EMPLOYEE_NAME>\n" +
                              "  </EMPLOYEE>\n" +
                              "</EMPLOYEES>\n";

            // Set the XML save properties.
            // 说明更新或插入的行 
            UpdateColumnsList = new string[4];
            UpdateColumnsList[0] = "EMPNO";
            UpdateColumnsList[1] = "ENAME";
            UpdateColumnsList[2] = "HIREDATE";
            UpdateColumnsList[3] = "JOB";

            cmd.XmlSaveProperties.Table = "emp";  // 表名
            cmd.XmlSaveProperties.RowTag = "ROW";  // XML element 值
            cmd.XmlSaveProperties.KeyColumnsList = null;
            cmd.XmlSaveProperties.UpdateColumnsList = UpdateColumnsList;
            cmd.XmlSaveProperties.Xslt = xslstr;
            cmd.XmlSaveProperties.XsltParams = "param1=\"2003-1-1T0:0:0.000\";param2=\"CLERK\"";

            // Do the inserts.
            rows = cmd.ExecuteNonQuery();
            Console.WriteLine("Rows inserted: " + rows);

            // Do a query for the inserted employee.
            Console.WriteLine("Do a query for the inserted employee.");
            cmd.XmlCommandType = OracleXmlCommandType.Query;
            cmd.CommandText = "select * from emp e where e.empno = 1234";
            stream = cmd.ExecuteStream();
            sr = new StreamReader(stream, Encoding.Unicode);
            Console.WriteLine(sr.ReadToEnd());

            // Set the XML command type to update.
            cmd.XmlCommandType = OracleXmlCommandType.Update;

            // Set the XML document.
            cmd.CommandText = "<?xml version=\"1.0\"?>\n" +
                              "<EMPLOYEES>\n" +
                              "  <EMPLOYEE>\n" +
                              "    <EMPLOYEE_ID>1234</EMPLOYEE_ID>\n" +
                              "    <EMPLOYEE_NAME>Adams</EMPLOYEE_NAME>\n" +
                              "  </EMPLOYEE>\n" +
                              "</EMPLOYEES>\n";

            // Set the XML save properties.
            // 定位记录
            KeyColumnsList = new string[1];
            KeyColumnsList[0] = "EMPNO";

            UpdateColumnsList = new string[1];
            UpdateColumnsList[0] = "ENAME";

            cmd.XmlSaveProperties.KeyColumnsList = KeyColumnsList;
            cmd.XmlSaveProperties.UpdateColumnsList = UpdateColumnsList;

            // Do the updates.
            rows = cmd.ExecuteNonQuery();
            Console.WriteLine("Rows updated: " + rows);

            // Do a query for the updated employee.
            Console.WriteLine("Do a query for the updated employee.");
            cmd.XmlCommandType = OracleXmlCommandType.Query;
            cmd.CommandText = "select * from emp e where e.empno = 1234";
            stream = cmd.ExecuteStream();
            sr = new StreamReader(stream, Encoding.Unicode);
            Console.WriteLine(sr.ReadToEnd());

            // Set the XML command type to delete.
            cmd.XmlCommandType = OracleXmlCommandType.Delete;

            // Set the XML document.
            cmd.CommandText = "<?xml version=\"1.0\"?>\n" +
                              "<EMPLOYEES>\n" +
                              "  <EMPLOYEE>\n" +
                              "    <EMPLOYEE_ID>1234</EMPLOYEE_ID>\n" +
                              "  </EMPLOYEE>\n" +
                              "</EMPLOYEES>\n";

            // Set the XML save properties.
            KeyColumnsList = new string[1];
            KeyColumnsList[0] = "EMPNO";

            cmd.XmlSaveProperties.KeyColumnsList = KeyColumnsList;
            cmd.XmlSaveProperties.UpdateColumnsList = null;

            // Do the deletes.
            rows = cmd.ExecuteNonQuery();
            Console.WriteLine("Rows deleted: " + rows);

            // Do a query for the deleted employee.
            Console.WriteLine("Do a query for the deleted employee.");
            cmd.XmlCommandType = OracleXmlCommandType.Query;
            cmd.CommandText = "select * from emp e where e.empno = 1234";
            stream = cmd.ExecuteStream();
            sr = new StreamReader(stream, Encoding.Unicode);
            Console.WriteLine(sr.ReadToEnd());

            // Clean up.
            cmd.Dispose();
            con.Close();
            con.Dispose();
            

            // Waiting
            Console.ReadLine();

        }
    }
}