/*************************************************************************************
@author Jagriti
@version 1.0
Development
Environment : MS Visual Studio .NET
Name of the
File : ManageEmp.cs
Creation/Modification
History :
22-Oct-2003 Created
Overview:
This C# source
file contains the database operation methods that are used for the
insert, update,
delete, query of records with XSL transformations.
These methods
are called from the ManageEmp class. It uses the stylesheets from the
Stylesheet.cs
class.
Method Name Brief Description
----------- -----------------
applyStylesheet Does transformation based on the stylesheet
selected
insertEmployee Applies selected XSL and inserts
employee into 'Emp' table
updateEmployee Applies selected XSL and updates
employee record(s) into 'Emp' table
deleteEmployee Applies selected XSL and deletes
employee record(s) into 'Emp' table
generateEmpXml Returns relational data in the
XML form, from the 'Emp' table
getSelectedEmpRecord Returns the indiviual XML record
without XSL transformation
populateEmpDataGrid Populates the EmpDataGrid with records
from 'Emp' table
createNewRecord Returns a sample XML record with
XSL transformation used while insertion
populateDeptno Populates the valid Deptno's
listbox
**************************************************************************************/
using System;
using System.Windows.Forms;
using System.Xml;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.IO;
namespace XSLTSample
{
/// <summary>
///
Summary description for ManageEmp.
/// </summary>
public class
ManageEmp
{
public Stylesheet s = new Stylesheet();
public ManageEmp()
{
}
/***********************************************************************************
* The purpose of this method is to transform
the XML data. This method is valid for
* the transformation of all the records and valid for a single
record selected
* from the datagrid. It does the transformation based on the
selected radio button.
* For the XSL transformation it utilises Command.XmlQueryProperties.
XSLT to set the
* required XSL.
**********************************************************************************/
public string applyStylesheet(string
empno, string stylesheet)
{
try
{
// Create the
command
OracleCommand empCmd = new OracleCommand("", ConnectionMgr.conn);
// Set the XML
command type to query
empCmd.XmlCommandType = OracleXmlCommandType.Query;
if
(empno=="")
{
// Set the
SQL query for all records
empCmd.CommandText = " SELECT
empno, ename, sal, job, deptno FROM emp " +
" ORDER BY empno DESC" ;
}
else
{
// Set the
SQL query for particular employee
empCmd.CommandText = " SELECT
empno, ename, sal, job, deptno FROM emp " +
" WHERE empno=" + empno + " ORDER
BY empno DESC" ;
}
// Set the XML
query properties.
empCmd.XmlQueryProperties.MaxRows = -1;
empCmd.XmlQueryProperties.RootTag = "ROWSET";
empCmd.XmlQueryProperties.RowTag = "ROW";
empCmd.XmlQueryProperties.Xslt = stylesheet;
// XmlReader
provides read-only fast access to XML data,
//
OracleCommand.ExecuteXMLReader returns an XML document as result
XmlReader empReader =
empCmd.ExecuteXmlReader();
// .NET framework
class representing XmlDocument
XmlDocument empDoc = new
XmlDocument();
// Handles white
spaces during XmlDocument load process
empDoc.PreserveWhitespace = true;
// Loads data from
the specified XmlReader
empDoc.Load(empReader);
// Gets markup
representing root node and all its children
String str1 = empDoc.OuterXml;
empCmd.Dispose();
return str1;
}
catch(Exception ex)
{
MessageBox.Show("Error " +
ex.Message);
return "";
}
}
/*****************************************************************************
* This method is called when the 'Insert' button is clicked. It
transforms
* the text available in the 'Employee Data with XSLT' text box to
form that
* conforms to the acceptable structure by the 'Emp' database
table.
* It uses the stylesheets available in the Stylesheet.cs class.
* To apply transformation it uses Command.XmlSaveProperties.Xslt
property.
****************************************************************************/
public bool insertEmployee(string
insertEmployee, string dbStylesheet)
{
string[]
UpdateColumnsList = null;
try
{
OracleCommand insCmd = new OracleCommand("",ConnectionMgr.conn);
// Set the XML
document as command text
insCmd.CommandText = insertEmployee;
// Denotes that the
inserts are to be made using an XML document
insCmd.XmlCommandType =
OracleXmlCommandType.Insert;
// List of columns
for update
UpdateColumnsList = new string[5];
UpdateColumnsList[0] = "EMPNO";
UpdateColumnsList[1] = "ENAME";
UpdateColumnsList[2] = "SAL";
UpdateColumnsList[3] = "JOB";
UpdateColumnsList[4] = "DEPTNO";
// Set the XML save
properties
// Specifies list
of the columns for insertion
insCmd.XmlSaveProperties.UpdateColumnsList =
UpdateColumnsList;
// Specifies the
name of the table to which changes are saved
insCmd.XmlSaveProperties.Table =
"emp";
// Specifies the
value for the XML element that identifies
// row of data in
the XML document
if
(dbStylesheet == s.dbFromXmlStylesheet)
{
insCmd.XmlSaveProperties.RowTag =
"ROW";
}
else if (dbStylesheet == s.dbFromHtmlStylesheet)
{
insCmd.XmlSaveProperties.RowTag =
"ROWSET";
}
// Set the selected
stylesheet
insCmd.XmlSaveProperties.Xslt = dbStylesheet;
// Execute the
insert operation
insCmd.ExecuteNonQuery();
MessageBox.Show("Data inserted
successfully!");
insCmd.Dispose();
return true;
}
catch(Exception ex)
{
MessageBox.Show("Error " +
ex.Message);
return false;
}
}
/***************************************************************************
* This method is called when the 'Update' button is clicked. It
transforms
* the text available in the 'Employee Data with XSLT' text box to
form that
* conforms to the acceptable structure by the 'Emp' database
table.
* It uses the stylesheets available in the Stylesheet.cs class.
* To apply transformation it uses Command.XmlSaveProperties.Xslt
property.
**************************************************************************/
public bool updateEmployee(string
updateEmployee, string dbStylesheet)
{
// Set the key columns to
locate existing row(s) for update
string[]
KeyColumnsList = new string[1];
KeyColumnsList[0] = "EMPNO";
string[]
UpdateColumnsList = null;
try
{
OracleCommand updCmd = new OracleCommand("",ConnectionMgr.conn);
// Set the XML
document as command text
updCmd.CommandText = updateEmployee;
// Denotes that the
inserts are to be made using an XML document
updCmd.XmlCommandType =
OracleXmlCommandType.Update;
// List of columns
for update
UpdateColumnsList = new string[4];
UpdateColumnsList[0] = "ENAME";
UpdateColumnsList[1] = "SAL";
UpdateColumnsList[2] = "JOB";
UpdateColumnsList[3]
= "DEPTNO";
// Set the XML save
properties
// Specifies
columns that are used to locate row for update
updCmd.XmlSaveProperties.KeyColumnsList =
KeyColumnsList;
// Specifies list
of the columns for insertion
updCmd.XmlSaveProperties.UpdateColumnsList =
UpdateColumnsList;
// Specifies the
name of the table to which changes are saved
updCmd.XmlSaveProperties.Table =
"emp";
// Specifies the
value for the XML element that identifies
// row of data in
the XML document
if
(dbStylesheet == s.dbFromXmlStylesheet)
{
updCmd.XmlSaveProperties.RowTag =
"ROW";
}
else if (dbStylesheet == s.dbFromHtmlStylesheet)
{
updCmd.XmlSaveProperties.RowTag =
"ROWSET";
}
// Set the selected
stylesheet
updCmd.XmlSaveProperties.Xslt = dbStylesheet;
// Execute the
insert operation
updCmd.ExecuteNonQuery();
MessageBox.Show("Data updated
successfully!");
updCmd.Dispose();
return true;
}
catch(Exception ex)
{
MessageBox.Show("Error " +
ex.Message);
return false;
}
}
/***************************************************************************
* This method is called when the 'Delete' button is clicked. It
transforms
* the text available in the 'Employee Data with XSLT' text box to
the form
* that conforms to the acceptable structure by the 'Emp' database
table.
* It uses the stylesheets available in the Stylesheet.cs class.
* To apply transformation it uses Command.XmlSaveProperties.Xslt
property.
**************************************************************************/
public bool deleteEmployee(string
deleteEmployee, string dbStylesheet)
{
// Set the key columns to
locate existing row(s) for update
string[]
KeyColumnsList = new string[1];
KeyColumnsList[0] = "EMPNO";
try
{
OracleCommand delCmd = new OracleCommand("",ConnectionMgr.conn);
// Set the XML
document as command text
delCmd.CommandText = deleteEmployee;
// Denotes that the
deletes are to be made using an XML document
delCmd.XmlCommandType =
OracleXmlCommandType.Delete;
// Set the XML save
properties
// Specifies
columns that are used to locate row for update
delCmd.XmlSaveProperties.KeyColumnsList =
KeyColumnsList;
// Specifies the
name of the table to which changes are saved
delCmd.XmlSaveProperties.Table =
"emp";
// Specifies the
value for the XML element that identifies
// row of data in
the XML document
if
(dbStylesheet == s.dbFromXmlStylesheet)
{
delCmd.XmlSaveProperties.RowTag =
"ROW";
}
else if (dbStylesheet == s.dbFromHtmlStylesheet)
{
delCmd.XmlSaveProperties.RowTag =
"ROWSET";
}
// Set the selected
stylesheet
delCmd.XmlSaveProperties.Xslt
= dbStylesheet;
// Execute the
insert operation
delCmd.ExecuteNonQuery();
MessageBox.Show("Data deleted
successfully!");
delCmd.Dispose();
return true;
}
catch(Exception ex)
{
MessageBox.Show("Error " +
ex.Message);
return false;
}
}
/****************************************************************************
* This method is called when the 'Connect' button is clicked.
After the
* Oracle connection is successfully established. It accesses
relational
* data from an Oracle database and displays it as XML.
Bookmark