public void updateTable(string user, string id)
{
string strSQL = "update table1 set name where user=:user and id=:id";
OracleParameter[] parms = new OracleParameter[2];
parms[0] = new OracleParameter(":user", OracleType.VarChar, 256);
parms[0].Value = user;
parms[1] = new OracleParameter(":id", OracleType.Number);
parms[1].Value = id;
OracleUtils.ExecuteNonQuery(OracleUtils.ConnectionStringLocalTransaction, CommandType.Text, strSQL, parms);
}
public string queryTable(string ID)
{
string return_value = "";
OracleParameter[] parms = new OracleParameter[2];
Int64 OriUserID = 0;
Int64 NewUserID = 0;
string sql = "SELECT id,user FROM table1 WHERE id = :ID";
parms[0] = new OracleParameter("ID", OracleType.VarChar);
parms[0].Value = ID;
using (OracleDataReader rdr = OracleUtils.ExecuteReader(OracleUtils.ConnectionStringLocalTransaction, CommandType.Text, sql, parms[0]))
{
while (rdr.Read())
{
if (!rdr.IsDBNull(rdr.GetOrdinal("ID")))
NewUserID = rdr.GetInt64(rdr.GetOrdinal("ID"));
if (!rdr.IsDBNull(rdr.GetOrdinal("USER")))
OriUserID = rdr.GetInt64(rdr.GetOrdinal("USER"));
}
}
}
public string updateTransaction(string ID)
{
string return_value = "";
OracleParameter[] parms = new OracleParameter[2];
Int64 OriUserID = 0;
Int64 NewUserID = 0;
OracleTransaction trans1 = OracleHelper.BeginTransaction();
string sql1 = "update table1 set name = 'Jason' where id = :ID";
try
{
parms[0] = new OracleParameter("ID", OracleType.VarChar);
parms[0].Value = ID;
OracleHelper.ExecuteScalar(trans1, CommandType.Text, sql1, parms[0]);
trans1.Commit();
OracleHelper.EndTransaction(trans1);
}
catch
{
trans1.Rollback();
OracleHelper.EndTransaction(trans1);
return_value = " message error";
return return_value;
}
}
public void queryToDataTable()
{
#region move quo to memory
OracleParameter[] sql_parms1 = { new OracleParameter("id", OracleType.Number, 1) };
sql_parms1[0].Value = quo_id;
string sqlString = "select query";
QuoDs = new DataSet();
DataTable info1 = new DataTable("info_table1");
info1 = OracleHelper.ExecuteAdapter_DT(pi_trans, CommandType.Text, sqlString, sql_parms1);
QuoDs.Tables.Add(info1);
OracleParameter[] sql_parms2 = { new OracleParameter("id", OracleType.Number, 15) };
sql_parms2[0].Value = quo_id;
sqlString = "query";
DataTable info2 = new DataTable("info_table2");
info2 = OracleHelper.ExecuteAdapter_DataTable(pi_trans, CommandType.Text, sqlString, sql_parms2);
QuoDs.Tables.Add(info2);
#endregion
}
class OracleUtils{
// Read the connection strings from the configuration file
public static readonly string ConnectionStringLocalTransaction = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
// for update
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
// Create a new Oracle command
OracleCommand cmd = new OracleCommand();
//Create a connection
using (OracleConnection connection = new OracleConnection(connectionString))
{
//Prepare the command
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
//Execute the command
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
public static OracleDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
//Create the command and connection
OracleCommand cmd = new OracleCommand();
OracleConnection conn = new OracleConnection(connectionString);
try
{
//Prepare the command to execute
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
//Execute the query, stating that the connection should close when the resulting datareader has been read
OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
if (transaction == null)
throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null)
throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// Create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
// Execute the command & return the results
object retval = cmd.ExecuteScalar();
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
return retval;
}
private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
{
//Open the connection if required
if (conn.State != ConnectionState.Open)
conn.Open();
//Set up the command
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
//Bind it to the transaction if it exists
if (trans != null)
cmd.Transaction = trans;
// Bind the parameters passed in
if (commandParameters != null)
{
foreach (OracleParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
}
public static void EndTransaction(OracleTransaction tran)
{
OracleConnection conn = tran.Connection;
if (conn != null && conn.State != ConnectionState.Closed)
conn.Close();
}
public static DataSet ExecuteAdapter_DataSet(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
//Create the command and connection
OracleCommand cmd = new OracleCommand();
OracleConnection conn = new OracleConnection(connectionString);
DataSet DS = new DataSet();
try
{
//Prepare the command to execute
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
OracleDataAdapter ODA = new OracleDataAdapter(cmd);
ODA.Fill(DS);
return DS;
}
catch
{
//If an error occurs close the connection as the reader will not be used and we expect it to close the connection
conn.Close();
throw;
}
}
public static DataTable ExecuteAdapter_DataTable(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
//Create the command and connection
OracleCommand cmd = new OracleCommand();
DataTable DT = new DataTable();
try
{
//Prepare the command to execute
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
OracleDataAdapter ODA = new OracleDataAdapter(cmd);
ODA.Fill(DT);
cmd.Parameters.Clear();
return DT;
}
catch
{
//If an error occurs close the connection as the reader will not be used and we expect it to close the connection
trans.Connection.Close();
cmd.Parameters.Clear();
throw;
}
}
}
c# oracle的使用
最新推荐文章于 2021-09-22 13:28:14 发布