c# oracle的使用

来源：互联网发布：linux启动mysql244 编辑：程序博客网时间：2024/05/16 15:59
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 updatepublic 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 rollbackedor commited, pleaseprovidean opentransaction.", "transaction");            // Create acommandandprepareit for execution            OracleCommand cmd = new OracleCommand();            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);            // Execute the command & returntheresults            object retval = cmd.ExecuteScalar();            // Detach the SqlParametersfrom the command object, sothey can beused 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;            }        }}
0 0