XSLTSample - VB.NET Source Code

'*************************************************************************************

'@author Jagriti

'@version 1.0

'Development Environment : MS Visual Studio .NET

'Name of the File : ManageEmp.vb

'Creation/Modification History :

' 24-Nov-2003 Created

'Overview:

'This VB.NET 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.vb 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

'**************************************************************************************

Imports System

Imports System.Windows.Forms

Imports System.Xml

Imports System.Data

Imports Oracle.DataAccess.Client

Imports Oracle.DataAccess.Types

Imports System.IO

Namespace XSLTSample

'<summary>

'Summary description for ManageEmp.

'</summary>

Public Class ManageEmp

Public s As Stylesheet = New Stylesheet()

'***********************************************************************************

' 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 Function applyStylesheet(ByVal empno As String, ByVal stylesheet As String) As String

Try

' Create the command

Dim empCmd As OracleCommand = New OracleCommand("", ConnectionMgr.conn)

' Set the XML command type to query

empCmd.XmlCommandType = OracleXmlCommandType.Query

If (empno = "") Then

' 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"

End If

'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

Dim empReader As XmlReader = empCmd.ExecuteXmlReader()

' .NET framework class representing XmlDocument

Dim empDoc As XmlDocument = 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

Dim str1 As String = empDoc.OuterXml

empCmd.Dispose()

Return str1

Catch ex As Exception

MessageBox.Show("Error " + ex.Message)

Return ""

End Try

End Function

'*****************************************************************************

' 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.vb class.

' To apply transformation it uses Command.XmlSaveProperties.Xslt property.

'*****************************************************************************

Public Function insertEmployee(ByVal insertEmployeeTxt As String, ByVal dbStylesheet As String) As Boolean

Dim UpdateColumnsList As String() = Nothing

ReDim UpdateColumnsList(5)

Try

Dim insCmd As OracleCommand = New OracleCommand("", ConnectionMgr.conn)

' Set the XML document as command text

insCmd.CommandText = insertEmployeeTxt

' Denotes that the inserts are to be made using an XML document

insCmd.XmlCommandType = OracleXmlCommandType.Insert

' List of columns for update

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) Then

insCmd.XmlSaveProperties.RowTag = "ROW"

ElseIf (dbStylesheet = s.dbFromHtmlStylesheet) Then

insCmd.XmlSaveProperties.RowTag = "ROWSET"

End If

' Set the selected stylesheet

insCmd.XmlSaveProperties.Xslt = dbStylesheet

' Execute the insert operation

insCmd.ExecuteNonQuery()

MessageBox.Show("Data inserted successfully!")

insCmd.Dispose()

Return True

Catch ex As Exception

MessageBox.Show("Error " + ex.Message)

Return False

End Try

End Function

'***************************************************************************

' 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.vb class.

' To apply transformation it uses Command.XmlSaveProperties.Xslt property.

'*************************************************************************

Public Function updateEmployee(ByVal updateEmployeeTxt As String, ByVal dbStylesheet As String) As Boolean

' Set the key columns to locate existing row(s) for update

Dim KeyColumnsList As String()

ReDim KeyColumnsList(1)

KeyColumnsList(0) = "EMPNO"

Dim UpdateColumnsList As String() = Nothing

ReDim UpdateColumnsList(4)

Try

Dim updcmd As OracleCommand = New OracleCommand("", ConnectionMgr.conn)

' Set the XML document as command text

updcmd.CommandText = updateEmployeeTxt

' Denotes that the inserts are to be made using an XML document

updcmd.XmlCommandType = OracleXmlCommandType.Update

' List of columns for update

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) Then

updcmd.XmlSaveProperties.RowTag = "ROW"

ElseIf (dbStylesheet = s.dbFromHtmlStylesheet) Then

updcmd.XmlSaveProperties.RowTag = "ROWSET"

End If

' Set the selected stylesheet

updcmd.XmlSaveProperties.Xslt = dbStylesheet

' Execute the insert operation

updcmd.ExecuteNonQuery()

MessageBox.Show("Data updated successfully!")

updcmd.Dispose()

Return True

Catch ex As Exception

MessageBox.Show("Error " + ex.Message)

Return False

End Try

End Function

'***************************************************************************

' 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.vb class.

' To apply transformation it uses Command.XmlSaveProperties.Xslt property.

'**************************************************************************

Public Function deleteEmployee(ByVal deleteEmployeeTxt As String, ByVal dbStylesheet As String) As Boolean

'Set the key columns to locate existing row(s) for update

Dim KeyColumnsList As String()

ReDim KeyColumnsList(1)

KeyColumnsList(0) = "EMPNO"

Try

Dim delCmd As OracleCommand = New OracleCommand("", ConnectionMgr.conn)

' Set the XML document as command text

delCmd.CommandText = deleteEmployeeTxt

' 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) Then

delCmd.XmlSaveProperties.RowTag = "ROW"

ElseIf (dbStylesheet = s.dbFromHtmlStylesheet) Then

delCmd.XmlSaveProperties.RowTag = "ROWSET"

End If

' Set the selected stylesheet

delCmd.XmlSaveProperties.Xslt = dbStylesheet

' Execute the insert operation

delCmd.ExecuteNonQuery()

MessageBox.Show("Data deleted successfully!")

delCmd.Dispose()

Return True

Catch ex As Exception

MessageBox.Show("Error " + ex.Message)

Return False

End Try

End Function

'****************************************************************************

' 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 Function generateEmpXml() As String

Try

' Represents SQL command for execution, to return results

Dim empcmd As OracleCommand = 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,