Common Operations for Asp. Net Oracle Data

In a project, may use two types of data, such as ORACLE, MS_SQLSERVER at the same time to use, MS-SQL data generic class in many tutorials there are ready-made examples and code, but ORACLE is rare, however, this project is very useful and shared with everyone.
Here we use OraDbHelper. cs for four common data operations.
An ExecuteDataTable is returned to the memory data table.
2. SqlDataReader reads data directly
3. ExecuteScalar
Iv. ExecuteNonQuery: add, delete, and modify a database
Environment: the Windows XP Client is installed with the ORACLE 8.17 client.
Database Server: Red Hat Enterprise Linux AS is installed with Oracle Database 10 GB
Copy codeThe Code is as follows:
SQL * Plus: Release 8.1.7.0.0-Production on Saturday December 19 18:10:19 2009
(C) Copyright 2000 Oracle Corporation. All rights reserved.
Connect:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options

First, OraDbHelper. cs
Copy codeThe Code is as follows:
Using System;
Using System. Data;
Using System. Configuration;
Using System. Web;
Using System. Web. Security;
Using System. Web. UI;
Using System. Web. UI. WebControls;
Using System. Web. UI. WebControls. WebParts;
Using System. Web. UI. HtmlControls;
Using System. Data. SqlClient;
Using System. Data. OracleClient;
Namespace xjy. DAL
{
/// <Summary>
/// General class for ORACLE database operations by OraDbHelper
/// Author; Xue Jiayue
/// Date: 2009-12-18
/// </Summary>
Public class OraDbHelper
{
Private string connectionString;
/// <Summary>
/// Set the connection of the database string
/// </Summary>
Public string ConnectionString
{
Set {connectionString = value ;}
}
Public OraDbHelper (string connectionString)
{
This. connectionString = connectionString;
}
# Region ExecuteDataTable
/// <Summary>
/// Execute a query and return the result set
/// </Summary>
/// <Param name = "commandText"> SQL text query command to be executed </param>
/// <Returns> return the query result set </returns>
Public DataTable ExecuteDataTable (string commandText)
{
Return ExecuteDataTable (commandText, CommandType. Text, null );
}
/// <Summary>
/// Execute a query and return the query result
/// </Summary>
/// <Param name = "commandText"> SQL statement to be executed </param>
/// <Param name = "commandType"> type of the query statement to be executed, such as a stored procedure or SQL text command </param>
/// <Returns> return the query result set </returns>
Public DataTable ExecuteDataTable (string commandText, CommandType commandType)
{
Return ExecuteDataTable (commandText, commandType, null );
}
/// <Summary>
/// Execute a query and return the query result
/// </Summary>
/// <Param name = "commandText"> SQL statement to be executed </param>
/// <Param name = "commandType"> type of the query statement to be executed, such as a stored procedure or SQL text command </param>
/// <Param name = "parameters"> an array of parameters for a PL-SQL statement or stored procedure </param>
/// <Returns> </returns>
Public DataTable ExecuteDataTable (string commandText, CommandType commandType, params OracleParameter [] parameters)
{
DataTable data = new DataTable (); // instantiate the DataTable, used to load the query result set
Using (OracleConnection connection = new OracleConnection (connectionString ))
{
Using (OracleCommand command = new OracleCommand (commandText, connection ))
{
Command. CommandType = commandType; // set the CommandType of the command to the specified CommandType.
// If both parameters are input, add these parameters
If (parameters! = Null)
{
Foreach (OracleParameter parameter in parameters)
{
Command. Parameters. Add (parameter );
}
}
// Instantiate the SqlDataAdapter by using the SqlCommand instance that contains the query SQL
OracleDataAdapter adapter = new OracleDataAdapter (command );
Adapter. Fill (data); // Fill in the DataTable
}
}
Return data;
}
# Endregion ExecuteDataTable
# Region ExecuteReader
/// <Summary>
/// Send CommandText to Connection and generate an OracleDataReader.
/// </Summary>
/// <Param name = "commandText"> SQL text query command to be executed </param>
/// <Returns> </returns>
Public OracleDataReader ExecuteReader (string commandText)
{
Return ExecuteReader (commandText, CommandType. Text, null );
}
/// <Summary>
/// Send CommandText to Connection and generate an OracleDataReader.
/// </Summary>
/// <Param name = "commandText"> SQL statement to be executed </param>
/// <Param name = "commandType"> type of the query statement to be executed, such as a stored procedure or SQL text command </param>
/// <Returns> </returns>
Public OracleDataReader ExecuteReader (string commandText, CommandType commandType)
{
Return ExecuteReader (commandText, commandType, null );
}
/// <Summary>
/// Send CommandText to Connection and generate an OracleDataReader.
/// </Summary>
/// <Param name = "commandText"> SQL statement to be executed </param>
/// <Param name = "commandType"> type of the query statement to be executed, such as a stored procedure or SQL text command </param>
/// <Param name = "parameters"> parameter array of A Transact-SQL statement or stored procedure </param>
/// <Returns> </returns>
Public OracleDataReader ExecuteReader (string commandText, CommandType commandType, OracleCommand [] parameters)
{
OracleConnection connection = new OracleConnection (connectionString );
OracleCommand command = new OracleCommand (commandText, connection );
// If both parameters are input, add these parameters
If (parameters! = Null)
{
Foreach (OracleCommand parameter in parameters)
{
Command. Parameters. Add (parameter );
}
}
Connection. Open ();
// CommandBehavior. CloseConnection indicates that the Connection object associated with the Reader object is closed when the Reader object is closed.
Return command. ExecuteReader (CommandBehavior. CloseConnection );
}
# Endregion ExecuteReader
# Region ExecuteScalar
/// <Summary>
/// Retrieve a single value (for example, an aggregate value) from the database ).
/// </Summary>
/// <Param name = "commandText"> the query PL-SQL text command to be executed </param>
/// <Returns> </returns>
Public Object ExecuteScalar (string commandText)
{
Return ExecuteScalar (commandText, CommandType. Text, null );
}
/// <Summary>
/// Retrieve a single value (for example, an aggregate value) from the database ).
/// </Summary>
/// <Param name = "commandText"> SQL statement to be executed </param>
/// <Param name = "commandType"> type of the query statement to be executed, such as a stored procedure or a PL-SQL text command </param>
/// <Returns> </returns>
Public Object ExecuteScalar (string commandText, CommandType commandType)
{
Return ExecuteScalar (commandText, commandType, null );
}
/// <Summary>
/// Retrieve a single value (for example, an aggregate value) from the database ).
/// </Summary>
/// <Param name = "commandText"> SQL statement to be executed </param>
/// <Param name = "commandType"> type of the query statement to be executed, such as a stored procedure or SQL text command </param>
/// <Param name = "parameters"> an array of parameters for a PL-SQL statement or stored procedure </param>
/// <Returns> </returns>
Public Object ExecuteScalar (string commandText, CommandType commandType, OracleParameter [] parameters)
{
Object result = null;
Using (OracleConnection connection = new OracleConnection (connectionString ))
{
Using (OracleCommand command = new OracleCommand (commandText, connection ))
{
Command. CommandType = commandType; // set the CommandType of the command to the specified CommandType.
// If both parameters are input, add these parameters
If (parameters! = Null)
{
Foreach (OracleParameter parameter in parameters)
{
Command. Parameters. Add (parameter );
}
}
Connection. Open (); // Open the database connection
Result = command. ExecuteScalar ();
}
}
Return result; // return the first column of the first row of the query result. ignore other rows and columns.
}
# Endregion ExecuteScalar
# Region ExecuteNonQuery
/// <Summary>
/// Add, delete, and modify a database
/// </Summary>
/// <Param name = "commandText"> query the pl-SQL text command to be executed </param>
/// <Returns> </returns>
Public int ExecuteNonQuery (string commandText)
{
Return ExecuteNonQuery (commandText, CommandType. Text, null );
}
/// <Summary>
/// Add, delete, and modify a database
/// </Summary>
/// <Param name = "commandText"> pl-SQL statement to be executed </param>
/// <Param name = "commandType"> type of the query statement to be executed, such as the stored procedure or pl-SQL text command </param>
/// <Returns> </returns>
Public int ExecuteNonQuery (string commandText, CommandType commandType)
{
Return ExecuteNonQuery (commandText, commandType, null );
}
/// <Summary>
/// Add, delete, and modify a database
/// </Summary>
/// <Param name = "commandText"> pl-SQL statement to be executed </param>
/// <Param name = "commandType"> type of the query statement to be executed, such as the stored procedure or pl-SQL text command </param>
/// <Param name = "parameters"> pl-SQL statement or parameter array of Stored Procedure </param>
/// <Returns> return the number of affected rows of the execution operation </returns>
Public int ExecuteNonQuery (string commandText, CommandType commandType, OracleParameter [] parameters)
{
Int count = 0;
Using (OracleConnection connection = new OracleConnection (connectionString ))
{
Using (OracleCommand command = new OracleCommand (commandText, connection ))
{
Command. CommandType = commandType; // set the CommandType of the command to the specified CommandType.
// If both parameters are input, add these parameters
If (parameters! = Null)
{
Foreach (OracleParameter parameter in parameters)
{
Command. Parameters. Add (parameter );
}
}
Connection. Open (); // Open the database connection
Count = command. ExecuteNonQuery ();
}
}
Return count; // return the number of affected rows in the database after the add, delete, and modify operation is performed.
}
# Endregion ExecuteNonQuery
}
}

One test datatable
Front-end code
Copy codeThe Code is as follows:
<% @ Page Language = "C #" AutoEventWireup = "true" CodeFile = "Default. aspx. cs" Inherits = "_ Default" %>
<! DOCTYPE html PUBLIC "-// W3C // dtd xhtml 1.0 Transitional // EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<Html xmlns = "http://www.w3.org/1999/xhtml">
<Head runat = "server">
<Title> No title page </title>
</Head>
<Body>
<Form id = "form1" runat = "server">
<Div>
<Asp: GridView ID = "GridView1" runat = "server">
</Asp: GridView>
</Div>
</Form>
</Body>
</Html>

Background code
Copy codeThe Code is as follows:
Using System;
Using System. Data;
Using System. Configuration;
Using System. Collections;
Using System. Web;
Using System. Web. Security;
Using System. Web. UI;
Using System. Web. UI. WebControls;
Using System. Web. UI. WebControls. WebParts;
Using System. Web. UI. HtmlControls;
Using xjy. DAL;
Using System. Data. SqlClient;
Public partial class _ Default: System. Web. UI. Page
{
Protected void Page_Load (object sender, EventArgs e)
{
ConnectionStringSettings setting = System. Configuration. ConfigurationManager. ConnectionStrings ["ConnectionString1"];
String myStr = setting. ConnectionString;
OraDbHelper myora = new OraDbHelper (myStr );
DataTable t1 = new DataTable ();
T1 = myora. ExecuteDataTable ("select art_no, descr from article where art_no <100 ");
GridView1.DataSource = t1;
GridView1.DataBind ();
}
}

Result

Test SqlDataReader
Front-end code unchanged
The background code is as follows:
Copy codeThe Code is as follows:
Using System;
Using System. Data;
Using System. Configuration;
Using System. Collections;
Using System. Web;
Using System. Web. Security;
Using System. Web. UI;
Using System. Web. UI. WebControls;
Using System. Web. UI. WebControls. WebParts;
Using System. Web. UI. HtmlControls;
Using xjy. DAL;
Using System. Data. SqlClient;
Using System. Data. OracleClient;
Public partial class _ Default: System. Web. UI. Page
{
Protected void Page_Load (object sender, EventArgs e)
{
ConnectionStringSettings setting = System. Configuration. ConfigurationManager. ConnectionStrings ["ConnectionString1"];
String myStr = setting. ConnectionString;
OraDbHelper myora = new OraDbHelper (myStr );
OracleDataReader myDr = myora. ExecuteReader ("select art_no, descr from article where art_no <100 ");
While (myDr. Read ())
{
Response. Write ("|" + myDr [0] + "|" + myDr [1] +"
");
Response. Write ("____________________________________");
}
MyDr. Close ();
MyDr. Dispose ();
}
}

Result

Iii. Test ExecuteScalar
Front-end code
Copy codeThe Code is as follows:
<% @ Page Language = "C #" AutoEventWireup = "true" CodeFile = "Default. aspx. cs" Inherits = "_ Default" %>
<! DOCTYPE html PUBLIC "-// W3C // dtd xhtml 1.0 Transitional // EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<Html xmlns = "http://www.w3.org/1999/xhtml">
<Head runat = "server">
<Title> No title page </title>
</Head>
<Body>
<Form id = "form1" runat = "server">
<Div>
<Asp: Label ID = "Label1" runat = "server" Text = "Label"> </asp: Label>
</Div>
</Form>
</Body>
</Html>

Background code
Copy codeThe Code is as follows:
Using System;
Using System. Data;
Using System. Configuration;
Using System. Collections;
Using System. Web;
Using System. Web. Security;
Using System. Web. UI;
Using System. Web. UI. WebControls;
Using System. Web. UI. WebControls. WebParts;
Using System. Web. UI. HtmlControls;
Using xjy. DAL;
Using System. Data. SqlClient;
Using System. Data. OracleClient;
Public partial class _ Default: System. Web. UI. Page
{
Protected void Page_Load (object sender, EventArgs e)
{
ConnectionStringSettings setting = System. Configuration. ConfigurationManager. ConnectionStrings ["ConnectionString1"];
String myStr = setting. ConnectionString;
OraDbHelper myora = new OraDbHelper (myStr );
String shuliang;
Shuliang = myora. ExecuteScalar ("select count (art_no) from article where art_no <100"). ToString ();
Label1.Text = shuliang;
}
}

Result

Iv. Test ExecuteNonQuery

Front-end code:
Copy codeThe Code is as follows:
<% @ Page Language = "C #" AutoEventWireup = "true" CodeFile = "Default. aspx. cs" Inherits = "_ Default" %>
<! DOCTYPE html PUBLIC "-// W3C // dtd xhtml 1.0 Transitional // EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<Html xmlns = "http://www.w3.org/1999/xhtml">
<Head runat = "server">
<Title> No title page </title>
</Head>
<Body>
<Form id = "form1" runat = "server">
<Div> <asp: Label ID = "Label1" runat = "server" Text = "Label"> </asp: Label>
<Asp: GridView ID = "GridView1" runat = "server">
</Asp: GridView>
<Asp: Label ID = "Label2" runat = "server" Text = "Label"> </asp: Label>
<Asp: GridView ID = "GridView2" runat = "server">
</Asp: GridView>
<Asp: Button ID = "Button1" runat = "server" OnClick = "button#click" Text = "Restore value"/>
</Div>
</Form>
</Body>
</Html>

Background code
Copy codeThe Code is as follows:
Using System;
Using System. Data;
Using System. Configuration;
Using System. Collections;
Using System. Web;
Using System. Web. Security;
Using System. Web. UI;
Using System. Web. UI. WebControls;
Using System. Web. UI. WebControls. WebParts;
Using System. Web. UI. HtmlControls;
Using xjy. DAL;
Using System. Data. SqlClient;
Using System. Data. OracleClient;
Public partial class _ Default: System. Web. UI. Page
{
Protected void Page_Load (object sender, EventArgs e)
{
ConnectionStringSettings setting = System. Configuration. ConfigurationManager. ConnectionStrings ["ConnectionString1"];
String myStr = setting. ConnectionString;
OraDbHelper myora = new OraDbHelper (myStr );
DataTable t1 = new DataTable ();
Label1.Text = "Values Before update ";
T1 = myora. ExecuteDataTable ("select art_no, descr from article where art_no = 8 ");
GridView1.DataSource = t1;
GridView1.DataBind ();
If (myora. ExecuteNonQuery ("update article set descr = 'Update description test value 'where art_no = 8")> 0)
{
Label2.Text = "updated value ";
T1 = myora. ExecuteDataTable ("select art_no, descr from article where art_no = 8 ");
GridView2.DataSource = t1;
GridView2.DataBind ();
}
}
Protected void button#click (object sender, EventArgs e)
{
ConnectionStringSettings setting = System. Configuration. ConfigurationManager. ConnectionStrings ["ConnectionString1"];
String myStr = setting. ConnectionString;
OraDbHelper myora = new OraDbHelper (myStr );
DataTable t1 = new DataTable ();
If (myora. ExecuteNonQuery ("update article set descr = 'poor 60 ML/bottle 'where art_no = 8")> 0)
{
Label2.Text = "updated value ";
T1 = myora. ExecuteDataTable ("select art_no, descr from article where art_no = 8 ");
GridView2.DataSource = t1;
GridView2.DataBind ();
}
}
}

Result: