C# how connect to oracle!

Because of a new project, I started to use the oracle database since last week. It's a little hard for me to do so now because of some private things. So I need to record some basic methods here in order to show to my co-works how to use it!  All is from MSDN and have been modified a little!

 

此 C# 示例创建一个 Oracle 表并在其中加载数据。运行后面的示例之前必须先运行此示例，后面的示例会阐释如何使用 OracleDataReader 访问使用 OracleType 结构的数据。
using System.Data.OracleClient;
public void Setup(string connectionString)
{
   OracleConnection connection = new OracleConnection(connectionString);
   try
   {
      connection.Open();
      OracleCommand command = connection.CreateCommand();
      command.CommandText ="CREATE TABLE OracleTypesTable (MyVarchar2 varchar2(3000),MyNumber number(28,4) PRIMARY KEY,MyDate date, MyRaw raw(255))";
      command.ExecuteNonQuery();
      command.CommandText ="INSERT INTO OracleTypesTable VALUES ('test', 2, to_date('2000-01-11 12:54:01','yyyy-mm-dd hh24:mi:ss'), '0001020304')";
      command.ExecuteNonQuery();
      command.CommandText="SELECT * FROM OracleTypesTable";
   }
   catch(Exception)
   {
   }
   finally
   {
      connection.Close();
   }
}

此 C# 示例使用 OracleDataReader 访问数据，并使用几个 OracleType 结构显示数据。

public void ReadOracleTypesExample(string connectionString)
{
   OracleConnection connection = new OracleConnection(connectionString);
   connection.Open();
   OracleCommand command = connection.CreateCommand();
   try
   {
      command.CommandText = "SELECT * FROM OracleTypesTable";
      OracleDataReader reader = command.ExecuteReader();
      reader.Read();
      //Using the Oracle specific getters for each type is faster than
      //using GetOracleValue.
      //First column, MyVarchar2, is a VARCHAR2 data type in Oracle Server
      //and maps to OracleString.
      OracleString oraclestring1 = reader.GetOracleString(0);
      Console.WriteLine("OracleString " + oraclestring1.ToString());

      //Second column, MyNumber, is a NUMBER data type in Oracle Server
      //and maps to OracleNumber.
      OracleNumber oraclenumber1 = reader.GetOracleNumber(1);
      Console.WriteLine("OracleNumber " + oraclenumber1.ToString());

      //Third column, MyDate, is a DATA data type in Oracle Server
      //and maps to OracleDateTime.
      OracleDateTime oracledatetime1 = reader.GetOracleDateTime(2);
      Console.WriteLine("OracleDateTime " + oracledatetime1.ToString());

      //Fourth column, MyRaw, is a RAW data type in Oracle Server and
      //maps to OracleBinary.
      OracleBinary oraclebinary1 = reader.GetOracleBinary(3);

      //Calling value on a null OracleBinary throws
      //OracleNullValueException; therefore, check for a null value.
      if (oraclebinary1.IsNull==false)
      {
         foreach(byte b in oraclebinary1.Value)
         {
            Console.WriteLine("byte " + b.ToString());
         }
      }
      reader.Close();
   }
   catch(Exception e)
   {
      Console.WriteLine(e.ToString());
   }
   finally
   {
      connection.Close();
   }
}

下面的示例创建一个 OracleConnection、一个 OracleCommand 和一个 OracleDataReader。该示例读取全部数据，并将这些数据写到控制台。最后，该示例先关闭 OracleDataReader，然后关闭 OracleConnection。
public void ReadData(string connectionString)
{
    string queryString = "SELECT EmpNo, EName FROM Emp";
    using (OracleConnection connection = new OracleConnection(connectionString))
    {
        OracleCommand command = new OracleCommand(queryString, connection);
        connection.Open();
        OracleDataReader reader;
        reader = command.ExecuteReader();
        // Always call Read before accessing data.
        while (reader.Read())
        {
            Console.WriteLine(reader.GetInt32(0) + ", " + reader.GetString(1));
        }
        // always call Close when done reading.
        reader.Close();
    }
}

Be aware that, the following is our project code!

       string ConnectionString = "Data Source=;Persist Security Info=True;User ID=;PASSWORD=";
        OracleConnection conn = new OracleConnection(ConnectionString);
        //OracleConnection conn = new OracleConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
        try
        {
            conn.Open();
            OracleCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select * from T_XTGL_CZRY where LOGIN = '" + UserName + "'"; //在这儿写sql语句
            OracleDataReader odr = cmd.ExecuteReader(); //创建一个OracleDateReader对象
            while (odr.Read()) //读取数据，如果odr.Read()返回为false的话，就说明到记录集的尾部了               
            {
                i++;
            }
            odr.Close();
        }
        catch (Exception ee)
        {
            Response.Write(ee.Message); //如果有错误，输出错误信息
        }
        finally
        {
            conn.Close(); //关闭连接
        }