PDA

View Full Version : یک کلاس کامل برای کار با پایگاه داده اراکل



habedijoo
چهارشنبه 17 اسفند 1384, 16:18 عصر
/************************************************** ***********************************

@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;

}

}

}

}