Connect to Oracle APEX database in C#

Question

I have a database called "sistema" using application apex express, I am trying to connect with this code:

private void button1_Click(object sender, EventArgs e)
{
    string constr = "Data Source=sistema;User Id=admin;Password=123;";
    string ProviderName = "Oracle.ManagedDataAccess.Client";

    using (OracleConnection conn = new OracleConnection(constr))
    {
        try
        {
            conn.ConnectionString = constr;
            conn.Open();

            //Get all the schema collections and write to an XML file. 
            //The XML file name is Oracle.ManagedDataAccess.Client_Schema.xml
            DataTable dtSchema = conn.GetSchema();
            dtSchema.WriteXml(ProviderName + "_Schema.xml");

            MessageBox.Show("YEAH");

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            MessageBox.Show(ex.StackTrace);
        }
    } 
}

This code shows me this error:

ORA-12154: TNS could not resolve the specified connection identified

With this new code:

    private void button1_Click(object sender, EventArgs e)
    {
        string constr = @"Data Source=(DESCRIPTION=
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost )(PORT=1521)))
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SISTEMA)));
User Id=ADMIN ;Password=123";
        string ProviderName = "Oracle.ManagedDataAccess.Client";

        using (OracleConnection conn = new OracleConnection(constr))
        {
            try
            {
                conn.ConnectionString = constr;
                conn.Open();

                //Get all the schema collections and write to an XML file. 
                //The XML file name is Oracle.ManagedDataAccess.Client_Schema.xml
                DataTable dtSchema = conn.GetSchema();
                dtSchema.WriteXml(ProviderName + "_Schema.xml");

                MessageBox.Show("YEAH");

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                MessageBox.Show(ex.StackTrace);
            }
        } 
    }

It returns me

User id 'is an invalid connection string attribute

When I try to connect to the database from VS I get this error:

The listener does not currently know the requested service

The database works correctly on Oracle Apex http://localhost:8080/apex and has tables and records created Also try with uppercase and lowercase names and nothing changes

I do not understand how to connect to my Oracle Apex database in application express, it is confusing, I do not know what to change with respect to normal databases in oracle.

How can I connect to my apex database in c# ?

I am not sure but try User ID instead of User Id and remove any space character from connection string. — Wernfried Domscheit

yonas yonas · Accepted Answer · 2016-11-24T14:41:44

Problem is your not putting the tns identifier at the right place. If you had added the oracle manage data access package from nuget there will be an added configuration line on your app config/web config.

<oracle.manageddataaccess.client>
    <version number="*">
      <dataSources>
        <dataSource alias="SampleDataSource" 
                    descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
                    (CONNECT_DATA=(SERVICE_NAME=ORCL))) " />
      </dataSources>
    </version>
  </oracle.manageddataaccess.client>

Edi the alias to "sistema" and put your tns connection identifier on the descriptor. like this

<dataSource alias="sistema" 
            descriptor="(DESCRIPTION=
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost )(PORT=1521)))
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SISTEMA))) " />
          </dataSources>

and use your fist attempt connection string. That will resolve TNS identification error.

Connect to Oracle APEX database in C#

2 Answers