C# Oracle database connection

Question

I am trying to connect to an oracle database using C#.

Here is my code:

      OracleConnection conn = new OracleConnection(); // C#
           conn.ConnectionString = oradb;
        conn.Open();
        string sql = " select department_name from departments where department_id = 10"; // C#
        OracleCommand cmd = new OracleCommand();
        cmd.CommandText = sql;
        cmd.Connection = conn;
        cmd.CommandType = CommandType.Text; ///this is the line that gives the error

What is the proper way to set the command type? Thank you.

How about executing command without specify CommandType? By the way can you reveal more code here? — Hassan Nisar
– Hassan Nisar, Commented Aug 25, 2014 at 14:38
Hassan Nisar do you mean where is the cmd.ExecuteDataReader command..? — MethodMan
– MethodMan, Commented Aug 25, 2014 at 14:41
here is a good SO example to follow I suggest you get familiar with using statement and how to wrap your code for disposing automatically as well stackoverflow.com/questions/3101786/… also learn how to use Parameterized querys I doubt that you will be wanting to hard code your Where Filter — MethodMan
– MethodMan, Commented Aug 25, 2014 at 14:42
@DJKRAZE. I expect OP has not posted cmd.ExecuteReader() intentionally. — Hassan Nisar
– Hassan Nisar, Commented Aug 25, 2014 at 14:43

Hassan Nisar · Accepted Answer · 2014-08-25 15:10:23Z

2

Using Store Procedure:

using (OracleConnection conn = new OracleConnection( oradb ))
{
      conn.Open();
      OracleCommand cmd = new OracleCommand("StoreProcedureName", con);
      cmd.CommandType = CommandType.StoredProcedure;   

      //specify command parameters 
      //and Direction

      using(OracleDataReader reader = cmd.ExecuteReader())
      {
          while (reader.Read())
          {
             //string s = reader.GetInt32(0) + ", " + reader.GetInt32(1);
          }
      }                   
}

CommandType.Text: (not mandatory to specify CommandType).

using (OracleConnection conn = new OracleConnection( oradb ))
{
      string sql = @"SELECT department_name FROM departments 
                     WHERE department_id = @department_id";
      conn.Open();
      OracleCommand cmd = new OracleCommand(sql, conn);

      //specify command parameters
      cmd.Parameters.Add(new OracleParameter("@department_id", 10));

      using(OracleDataReader reader = cmd.ExecuteReader())
      {
          while (reader.Read())
          {
             //string s = reader.GetString(0);
          }
      }                   
}

edited Aug 25, 2014 at 15:10

answered Aug 25, 2014 at 15:01

Hassan Nisar

5,4502 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hassan Nisar Over a year ago

Yes @Brett. I intentionally left it out to improve readability.

Locke · Accepted Answer · 2014-08-25 14:44:56Z

1

Make sure you toss each of these pieces in a using() statement, i.e.

using( OracleConnection conn = new OracleConnection( oradb ) )
{
    conn.Open();

    using( OracleCommand cmd = new OracleCommand( "sql here", conn ) )
    {
        //cmd.Execute(); cmd.ExecuteNonQuery();
    }
}

answered Aug 25, 2014 at 14:44

Locke

1,1751 gold badge11 silver badges33 bronze badges

4 Comments

user3022875 Over a year ago

how do I set the cmd to be Text vs Stored procedure?

Brett Over a year ago

This is a good answer -- the usings are critical here as you will certainly cause a memory leak otherwise (can testify from experience). The default command type is Text, so you should be all set. See: docs.oracle.com/cd/B19306_01/win.102/b14307/… I only recall changing the CommandType when I wanted to execute a stored procedure.

user3022875 Over a year ago

@Brett when you want to do a stored procedure do you do it like Hassan's answer or some other way?

Brett Over a year ago

Hassan's answer is more complete, and, yes, that's what I would do.

Collectives™ on Stack Overflow

C# Oracle database connection

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related