c# oracle的使用

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