(原) 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();
}
}
}
* 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();
}
}
}
posted on 2007-12-29 15:17 mjgforever 阅读(390) 评论(0) 编辑 收藏 举报