/*************************************************************************************
@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.
* The XmlCommandType property denotes XML operations on an
OracleCommand.
* The ExecuteXmlReader method of OracleCommand returns a .NET
framework
* XmlDocument object which is returned as a string.
****************************************************************************/
public string generateEmpXml()
{
try
{
// Represents SQL
command for execution, to return results
OracleCommand empCmd = new OracleCommand("", ConnectionMgr.conn);
empCmd.CommandText = "SELECT empno,
ename, sal, job, deptno FROM emp " +
" ORDER BY empno
DESC" ;
// Represents XML
operations on OracleCommand. Setting
//
OracleXmlCommandType.Query returns result as XML document
empCmd.XmlCommandType =
OracleXmlCommandType.Query;
// 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 str = empDoc.OuterXml;
empCmd.Dispose();
return str;
}
catch (Exception
ex)
{
MessageBox.Show("Error " +
ex.Message);
return
ex.Message;
}
}
/*****************************************************************************
* The method is called when the user selects a record in the
datagrid. Its
* purpose is to return selected record in XML format.
****************************************************************************/
public string getSelectedEmpRecord(string
empno)
{
try
{
// Represents SQL
command for execution, to return results
OracleCommand empCmd = new OracleCommand("", ConnectionMgr.conn);
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" ;
}
// Represents XML
operations on OracleCommand. By setting
//
OracleXmlCommandType.Query returns result as XML document
empCmd.XmlCommandType =
OracleXmlCommandType.Query;
// XmlReader
provides read-only fast access to XML data,
//
OracleCommand.ExecuteXMLReader returns XmlDocument 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 str = empDoc.OuterXml;
empCmd.Dispose();
return str;
}
catch (Exception
ex)
{
MessageBox.Show("Error " +
ex.Message);
return
ex.Message;
}
}
/****************************************************************************
* This method is called when the 'Connect' button is clicked.
After
* Oracle connection is established successfully, it fetches data
from 'Emp'
* table and returns a DataSet that is bound to the DataGrid.
****************************************************************************/
public DataSet
populateEmpDataGrid()
{
try{
OracleDataAdapter empAdapter = new OracleDataAdapter();
empAdapter.SelectCommand = new
OracleCommand("SELECT empno, ename, job, " +
"sal, deptno FROM emp " +
"ORDER BY
empno DESC",
ConnectionMgr.conn);
DataSet empDataSet = new
DataSet("empDataSet");
empAdapter.Fill(empDataSet,"emp");
return empDataSet;
}
catch(Exception ex)
{
MessageBox.Show("Error " +
ex.Message);
return null;
}
}
/*****************************************************************************
* This method is called when 'Create New Record' button is
clicked.
* It generates a sample XML record for the 'Emp' table and
populates the
* withXsltTextBox with it. The empno is generated automatically.
****************************************************************************/
public string createNewRecord(string
stylesheet)
{
try
{
// Generate unique
Empno based on the MAX(empno)+1
OracleCommand empnoCmd = new OracleCommand("SELECT
NVL((MAX(empno)+1),8000)" +
"FROM emp
",ConnectionMgr.conn);
OracleDataReader empnoReader =
empnoCmd.ExecuteReader();
empnoReader.Read();
OracleDecimal i = empnoReader.GetDecimal(0);
string str =
"";
// Create a sample XML document based on selected
stylesheet
if
(stylesheet == s.xmlToXmlStylesheet)
{
str = "<?xml
version=\"1.0\"?>\n" +
"<EMPLOYEES>\n"
+
" <EMPLOYEE EMPID=\""+ i.ToString() +"\"
>\n" +
"
<FULL_NAME>RAM</FULL_NAME>\n" +
" <SALARY>3000</SALARY>\n" +
" <DESIGNATION>MANAGER</DESIGNATION>\n" +
" <DEPARTMENT>10</DEPARTMENT>\n" +
" </EMPLOYEE>\n" +
"</EMPLOYEES>\n";
}
else if (stylesheet == s.xmlToHtmlStylesheet)
{
str = "<HTML>\n" +
" <HEAD>\n" +
" <TITLE>OTN: XSLT Sample</TITLE>\n"+
" </HEAD>\n" +
" <BODY>\n" +
" <TABLE border=\"1\">\n" +
" <TR>\n" +
" <TH>Emp ID</TH>\n" +
" <TH>Full Name</TH>\n" +
" <TH>Salary</TH>\n" +
" <TH>Designation</TH>\n" +
" <TH>Department</TH>\n" +
" </TR>\n" +
" <TR>\n" +
" <TD>"+ i.ToString() +"</TD>\n" +
" <TD>RAM</TD>\n" +
" <TD>3000</TD>\n" +
" <TD>MANAGER</TD>\n" +
" <TD>10</TD>\n" +
" </TR>\n" +
" </TABLE>\n" +
" </BODY>\n" +
"</HTML>";
}
empnoCmd.Dispose();
return str;
}
catch (Exception
ex)
{
MessageBox.Show("Error " +
ex.Message);
return
"";
}
}
/***********************************************************************
* This method is called when the 'Connect' button is clicked.
After
* Oracle connection is established successfully, it fetches
department
* numbers from 'Dept' table and returns as DataSet to fill the
list of
* valid department numbers required by user to view while
inserting/updating
* XML data.
**********************************************************************/
public DataSet
populateDeptno()
{
try
{
OracleDataAdapter deptAdapter = new OracleDataAdapter();
deptAdapter.SelectCommand = new OracleCommand("SELECT deptno FROM
dept",
ConnectionMgr.conn);
DataSet deptDataSet = new DataSet("deptDataSet");
deptAdapter.Fill(deptDataSet,"dept");
deptAdapter.Dispose();
return
deptDataSet;
}
catch(Exception ex)
{
MessageBox.Show("Error " +
ex.Message);
return null;
}
}
}
}
Bookmark
