/************************************************** ***********************************
@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;
}
}
}
}
