I have scoured StackOverflow far and wide for an answer to this question, but nothing seemed to help me so I'll ask it here now:
So, I have a rather simple form with a few labels & fields and a button.
Once you click on the button, you perform a query on a DB. The result gets displayed across several fields. When I run the program for myself, at work, using F5 in Visual Studio 2013, the form works flawlessly, using the reference Oracle.ManagedDataAccess.
However, when I run the program on another PC at work (same network and all), the program returns an oracle “ORA-12154: TNS: could not resolve the connect identifier specified”-error.
What you should know: I set up the Oracle.ManagedDataAccess so that it uses the standard TNSNames.ora file located at c:\Oracle\product\10.2.0\client_1\network\ADMIN. This location is the same for every PC on the network, at work. However, the TNSNames.ora file is not always as complete as me, I might have a couple more connections available than others. That's why, as you'll see in my code soon, I basically say: "if SID xxxxx is found in document X at location Y, do nothing, else, add the necessary connection info specified here: ..."
Here's the code I use that concerns all the connections. For privacy reasons I replaced the connection details with fake names, and translated some of the things from Dutch into English, for your better understanding.
As the code is very long, I'm only showing the part about the connection, and I'm leaving everything else out. But as I said before, the complete code works, only... just for me. I'd like to make it work for everyone else at work.
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Windows.Formss
using System.IO;
namespace WindowsFormsApplication4
{
public partial class GetLivingWagesV2 : Form
{
public GetLivingWagesV2()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
if (comboBox1.SelectedIndex == -1)
{
MessageBox.Show("PLEASE SELECT AN ENVIRONMENT FIRST.");
}
var objtextbox = textBox1.Text;
var ConnectionString350 = "DATA SOURCE=DATABASEPREREGRESSION01;PASSWORD=test;USER ID=REGGY";
var ConnectionString300 = "DATA SOURCE=DATABASEREGRESSION01;PASSWORD=test;USER ID=REGGY";
var Connection = new OracleConnection();
if (comboBox1.SelectedIndex == 0)
{
var PEN300 = File.ReadAllText(@"C:\oracle\product\10.2.0\client_1\network\ADMIN\tnsnames.ora").Contains("PENREG.RSVZINASTI.BE") ? 1 : 0;
Connection.ConnectionString = ConnectionString300;
Connection.Open();
var cmd = Connection.CreateCommand();
cmd.CommandText = "SELECT DBMS_LOB.SUBSTR(b.msg_flux_in, 4000, 200) AS MSG_FLUX_IN, DBMS_LOB.SUBSTR(b.msg_flux_out, 4000, 200) AS MSG_FLUX_OUT FROM tb2b_flux a, tb2b_flux_status b where a.flux_svf_id = '" + objtextbox + "' and a.flux_id = b.flux_id order by a.dt_creatie desc";
var Reader = cmd.ExecuteReader();
var dt = new DataTable();
dt.Load(Reader);
dataGridView1.DataSource = dt;
var column = dataGridView1.Columns[0];
column.Width = 380;
var column1 = dataGridView1.Columns[1];
column1.Width = 380;
if (PEN300 == 0)
{
using (var file = new System.IO.StreamWriter(@"C:\oracle\product\10.2.0\client_1\network\ADMIN\tnsnames.ora", true))
{
file.WriteLine("DATABASEREGRESSION.ORGANISATION.BE =");
file.WriteLine(" (DESCRIPTION =");
file.WriteLine(" (ADDRESS_LIST =");
file.WriteLine(" (ADDRESS = (PROTOCOL = TCP)(HOST = unxin600)(PORT = 1522))");
file.WriteLine(" )");
file.WriteLine(" (CONNECT_DATA =");
file.WriteLine(" (SID = DATABASEREGRESSION01)");
file.WriteLine(" )");
file.WriteLine(" )");
}
}
}
if (comboBox1.SelectedIndex == 1)
{
var PEN350 = File.ReadAllText(@"C:\oracle\product\10.2.0\client_1\network\ADMIN\tnsnames.ora").Contains("pendvt01.rsvzinasti.be") ? 1 : 0;
if (PEN350 == 0)
{
using (var file = new System.IO.StreamWriter(@"C:\oracle\product\10.2.0\client_1\network\ADMIN\tnsnames.ora", true))
{
file.WriteLine("DATABASEPREREGRESSION =");
file.WriteLine(" (DESCRIPTION =");
file.WriteLine(" (ADDRESS_LIST =");
file.WriteLine(" (ADDRESS = (PROTOCOL = TCP)(HOST = UNXPR651)(PORT = 1522))");
file.WriteLine(" )");
file.WriteLine(" (CONNECT_DATA =");
file.WriteLine(" (SERVICE_NAME =DATABASEPREREGRESSION01)");
file.WriteLine(" )");
file.WriteLine(" )");
}
}
else
{
Connection.ConnectionString = ConnectionString350;
Connection.Open();
var cmd = Connection.CreateCommand();
cmd.CommandText = "SELECT DBMS_LOB.SUBSTR(b.msg_flux_in, 4000, 200) AS MSG_FLUX_IN, DBMS_LOB.SUBSTR(b.msg_flux_out, 4000, 200) AS MSG_FLUX_OUT FROM tb2b_flux a, tb2b_flux_status b where a.flux_svf_id = '" + objtextbox + "' and a.flux_id = b.flux_id order by a.dt_creatie desc";
var Reader = cmd.ExecuteReader();
var dt = new DataTable();
dt.Load(Reader);
dataGridView1.DataSource = dt;
var column = dataGridView1.Columns[0];
column.Width = 380;
var column1 = dataGridView1.Columns[1];
column1.Width = 380;
}
}
if (Connection != null && Connection.State == ConnectionState.Closed)
{
Connection.Open();
}
else
{
Connection.Close();
}
foreach (DataGridViewRow dr in dataGridView1.Rows)
{
foreach (DataGridViewCell dc in dr.Cells)
{
if (dc.Value == null || dc.Value.ToString().Trim() == string.Empty)
{
if (dataGridView1.Rows.Count < 2)
{
MessageBox.Show("De FluxID werd niet gevonden.");
return;
}
}
else
{
}
}
}
}
}
}
The
tnsnames.orafile is probably not used, because the connection string uses EZ CONNECT (easy connect) format. Try modifying the data source inside the connectionstring:Trying to do without
tnsnames.orawould make sense for a Windows Forms application, because it would make distribution and application support easier. No need to take care of this file, which is just an external element not under your control that needs to be handled, its installation and updates, it probably means also needing to parse the file to make an update of it, it can be changed by accident by another user or by another application. A single line connection string in a configuration file of the application is less trouble.An overview of connection string formats for the ODP.NET provider are here: https://www.connectionstrings.com/oracle-data-provider-for-net-odp-net/