problem with oracle connection in c#

Question

I have a Windows XP machine used to create .Net applications with VS 2008.

I want to connect to a remote network server (using Windows xp) that is running an Oracle 10g database.

I am using the code below (with the first connection string) to connect directly to a version of 10g that is running on the same machine with no problems, however when I try to connect to the network machine, it actually crashes my application.

I have tried several variations of connection strings as I feel that I must be making a syntax error somewhere.

What concerns me is that I have dual try/catch statements in the application and I do not understand why it simply does not refuse the connection and report the error.

I suppose the real question is 'what is the correct syntax for the connection string'....or WHATEVER the hell I am doing wrong.

Any help or suggestions are greatly appreciated. Thank you in advance.

//Class Variables
string CONNSTR = "Server=192.168.0.1:1521;User ID=zahid;Password=abc123;";


public Oracle()
{
  InitializeComponent();
}

//Methods
private void TestMyOracleConnection()
{
  OracleConnection Conn = new OracleConnection(CONNSTR);
  try
  {
    Conn.Open();
    MessageBox.Show("Oracle Connection Established", "Success");
  }
  catch (OracleException ex)
  {
    MessageBox.Show(ex.Message, "Oracle Connection Failed!");
  }
  catch (Exception ex)
  {
    MessageBox.Show(ex.Message, "Oracle Connection Failed!");
  }
  finally
  {
    Conn.Close();
    MessageBox.Show("Oracle Connection Closed", "Success");
  }
}

private void buttonTestConnection_Click(object sender, EventArgs e)
{
  TestMyOracleConnection();
}

What does 'I have dual try/catch statements' mean? There's only one try block here, with several lines not covered by it. When it crashes your application, does it output a stack trace? What does it say? — forsvarir
– forsvarir, Commented May 17, 2011 at 12:22
It's possible that it crashes in native code, try..catch might not be able to catch it. Have you tried other Oracle Client versions? — Vladislav Zorov
– Vladislav Zorov, Commented May 17, 2011 at 12:28

Mutation Person · Accepted Answer · 2011-05-17 12:28:04Z

1

You might want to try something like:

connection string:

Data Source=DBNAME;User ID=zahid;Password= abc123;Persist Security Info=True;Unicode=True;

Now look in the tnsname.ora file on your PC. It will be somewhere under your local Oracle Client installation. You're looking for something like:

DBNAME =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = OracleServerName)(PORT = 1510))
      (ADDRESS = (PROTOCOL = TCP)(HOST = OracleServerName)(PORT = 1514))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DBNAME)
    )
  )

You're looking to replace DBNAME and OracleServerName with your own. Don't forget, DBNAME is the name of the main database, not one of the schemas (the entity with all the tables and procedures, etc) under it.

answered May 17, 2011 at 12:28

Mutation Person

30.6k18 gold badges102 silver badges166 bronze badges

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

Comments

dcp · Accepted Answer · 2011-05-17 12:32:22Z

1

Assuming you are using ODP.Net, you can try a connection string such as the following (taken from a web.config):

<add name="OdpConnection" connectionString="Data Source=xe;User Id=some_user;Password=some_password; Promotable Transaction=local"
     providerName="Oracle.DataAccess.Client" />

You can read this connection string from the web.config in your code using something like this:

string connectionString = ConfigurationManager.ConnectionStrings["OdpConnection"].ConnectionString;

It's a good practice to avoid putting connection strings directly in code, since to change them requires a re-compile. By putting them in a config file, such as web.config or app.config, you can change them without having to re-compile your library or exe.

edited May 17, 2011 at 12:32

answered May 17, 2011 at 12:27

dcp

55.7k24 gold badges149 silver badges169 bronze badges

Comments

VikciaR · Accepted Answer · 2011-05-17 12:34:55Z

try
  {
    Conn.Open();
    MessageBox.Show("Oracle Connection Established", "Success");
  }
  catch (OracleException ex)
  {
    MessageBox.Show(ex.Message, "Oracle Connection Failed!");
  }
  catch (Exception ex)
  {
    MessageBox.Show(ex.Message, "Oracle Connection Failed!");
  }
  finally
  {
    Conn.Close();
    MessageBox.Show("Oracle Connection Closed", "Success");
  }

You have pottential problem here: if conn.Open() fails (so connection is not opened) in finally you call conn.close(), but connection isn't opened - so it also fails, but it is now outside try catch. You need check for

if (Conn!=null && Conn.IsOpen())
  Conn.Close();

Connection string for oracle: Data Source=TORCL;User Id=myUsername;Password=myPassword;

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;

More here

Collectives™ on Stack Overflow

problem with oracle connection in c#

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related