In a project, you can use 2 kinds of data, such as Oracle, Ms_sqlserver also use, ms-sql data generic class in many tutorials have ready-made examples and code, but Oracle is relatively rare, but this project is just used, posted and shared with everyone.
This is where you use OraDbHelper.cs to do four of the usual data operations.
Four ExecuteNonQuery to the database to perform additions and deletions to modify the operation
The database server is: Red Hat Enterprise Linux as installed Oracle database 10g
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>
Oradbhelper Common classes for Oracle database operations
Shija of the author;
Date: 2009-12-18
</summary>
public class Oradbhelper
{
private string connectionString;
<summary>
To set a connection to a database string
</summary>
public string ConnectionString
{
set {connectionString = value;}
}
Public Oradbhelper (String connectionString)
{
this.connectionstring = connectionString;
}
#region executedatatable
<summary>
Executes a query and returns the result set
</summary>
<param name= "CommandText" > Query SQL Text command to execute </param>
<returns> return query result set </returns>
Public DataTable executedatatable (string commandtext)
{
Return executedatatable (CommandText, CommandType.Text, NULL);
}
<summary>
Executes a query and returns the results of the query
</summary>
<param name= "CommandText" > SQL statement to execute </param>
<param name= "CommandType" > type of query statement to execute, such as stored procedures or SQL text commands </param>
<returns> return query result set </returns>
Public DataTable executedatatable (string commandtext, CommandType commandtype)
{
Return Executedatatable (CommandText, CommandType, NULL);
}
<summary>
Executes a query and returns the results of the query
</summary>
<param name= "CommandText" > SQL statement to execute </param>
<param name= "CommandType" > type of query statement to execute, such as stored procedures or SQL text commands </param>
<param name= "parameters" >pl-sql statement or stored procedure parameter array </param>
<returns></returns>
Public DataTable executedatatable (string commandtext, CommandType commandtype, params oracleparameter[] parameters)
{
DataTable data = new DataTable ();//materialized DataTable for loading query result set
using (oracleconnection connection = new OracleConnection (connectionString))
{
using (OracleCommand command = new OracleCommand (commandtext, connection))
{
Command.commandtype = commandtype;//Set command commandtype to specified CommandType
If parameters are passed in at the same time, these parameters are added
if (Parameters!= null)
{
foreach (oracleparameter parameter in parameters)
{
Command. Parameters.Add (parameter);
}
}
Instantiate SqlDataAdapter by containing a SqlCommand instance of query sql
OracleDataAdapter adapter = new OracleDataAdapter (command);
Adapter. Fill DataTable with Fill (data);/
}
}
return data;
}
#endregion executedatatable
#region ExecuteReader
<summary>
Sends the CommandText to the Connection and generates a OracleDataReader.
</summary>
<param name= "CommandText" > Query SQL Text command to execute </param>
<returns></returns>
Public OracleDataReader ExecuteReader (string commandtext)
{
Return ExecuteReader (CommandText, CommandType.Text, NULL);
}
<summary>
Sends the CommandText to the Connection and generates a OracleDataReader.
</summary>
<param name= "CommandText" > SQL statement to execute </param>
<param name= "CommandType" > type of query statement to execute, such as stored procedures or SQL text commands </param>
<returns></returns>
Public OracleDataReader ExecuteReader (string commandtext, CommandType commandtype)
{
Return ExecuteReader (CommandText, CommandType, NULL);
}
<summary>
Sends the CommandText to the Connection and generates a OracleDataReader.
</summary>
<param name= "CommandText" > SQL statement to execute </param>
<param name= "CommandType" > type of query statement to execute, such as stored procedures or SQL text commands </param>
<param name= "parameters" >transact-sql statement or stored procedure parameter array </param>
<returns></returns>
Public OracleDataReader ExecuteReader (string commandtext, CommandType commandtype, oraclecommand[] parameters)
{
OracleConnection connection = new OracleConnection (connectionString);
OracleCommand command = new OracleCommand (commandtext, connection);
If parameters are passed in at the same time, these parameters are added
if (Parameters!= null)
{
foreach (oraclecommand parameter in parameters)
{
Command. Parameters.Add (parameter);
}
}
Connection. Open ();
The CommandBehavior.CloseConnection parameter indicates that the connection object associated with the reader object is closed when it is closed
return command. ExecuteReader (commandbehavior.closeconnection);
}
#endregion ExecuteReader
#region ExecuteScalar
<summary>
Retrieves a single value, such as an aggregate value, from the database.
</summary>
<param name= "CommandText" > Query pl-sql text command to execute </param>
<returns></returns>
Public Object ExecuteScalar (string commandtext)
{
Return ExecuteScalar (CommandText, CommandType.Text, NULL);
}
<summary>
Retrieves a single value, such as an aggregate value, from the database.
</summary>
<param name= "CommandText" > SQL statement to execute </param>
<param name= "CommandType" > type of query statement to execute, such as stored procedures or pl-sql text commands </param>
<returns></returns>
Public Object ExecuteScalar (string commandtext, CommandType commandtype)
{
Return ExecuteScalar (CommandText, CommandType, NULL);
}
<summary>
Retrieves a single value, such as an aggregate value, from the database.
</summary>
<param name= "CommandText" > SQL statement to execute </param>
<param name= "CommandType" > type of query statement to execute, such as stored procedures or SQL text commands </param>
<param name= "parameters" >pl-sql statement or stored procedure parameter array </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 command commandtype to specified CommandType
If parameters are passed in at the same time, these parameters are added
if (Parameters!= null)
{
foreach (oracleparameter parameter in parameters)
{
Command. Parameters.Add (parameter);
}
}
Connection. Open ();//Opening database connection
result = command. ExecuteScalar ();
}
}
Return result;//returns the first row of the query result, ignoring other rows and columns
}
#endregion ExecuteScalar
#region ExecuteNonQuery
<summary>
Perform additions and deletions to the database
</summary>
<param name= "CommandText" > Query pl-sql text command to execute </param>
<returns></returns>
public int ExecuteNonQuery (string commandtext)
{
Return ExecuteNonQuery (CommandText, CommandType.Text, NULL);
}
<summary>
Perform additions and deletions to the database
</summary>
<param name= "CommandText" > Pl-sql statement to be executed </param>
<param name= "CommandType" > type of query statement to execute, such as stored procedures or pl-sql text commands </param>
<returns></returns>
public int ExecuteNonQuery (string commandtext, CommandType commandtype)
{
Return ExecuteNonQuery (CommandText, CommandType, NULL);
}
<summary>
Perform additions and deletions to the database
</summary>
<param name= "CommandText" > Pl-sql statement to be executed </param>
<param name= "CommandType" > type of query statement to execute, such as stored procedures or pl-sql text commands </param>
<param name= "parameters" >pl-sql statement or stored procedure parameter array </param>
<returns> returns the number of rows affected by the execution of the 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 command commandtype to specified CommandType
If parameters are passed in at the same time, these parameters are added
if (Parameters!= null)
{
foreach (oracleparameter parameter in parameters)
{
Command. Parameters.Add (parameter);
}
}
Connection. Open ();//Opening database connection
Count = command. ExecuteNonQuery ();
}
}
Return count;//returns the number of rows affected in the database after the execution of the delete or modify operation
}
#endregion ExecuteNonQuery
}
}
