Use a. NET application to connect to the Oracle database. netoracle

Use a. NET application to connect to the Oracle database. netoracle

Recently, I was asked by my friends, "Why is there always an error in connecting the. NET application to the Oracle database ?", I think this is because we usually use Microsoft products, but lack research on other platform products. After talking so much nonsense, let's talk about how to correctly configure Oracle to adapt to. NET application development.

1. Download required components

• Oracle 11.2g (64-bit)
  : Http://www.oracle.com/technetwork/cn/database/enterprise-edition/downloads/index.html
  Note: This article uses 64-bit. If your host does not support 64-bit, download the 32-bit package.
• Oracle Data Access Components (ODAC) (64-bit)
  This component serves as a bridge between. NET Applications and Oracle, And. NET applications access the Oracle database through this component.
  64-bit: http://www.oracle.com/technetwork/cn/database/windows/downloads/index-098472-zhs.html
  32-bit: http://www.oracle.com/technetwork/cn/database/windows/downloads/index-101312-zhs.html
  Note: This article uses 64-bit. If your host does not support 64-bit, download the 32-bit package.
• Oracle SQL Developer
  This tool is a visual tool developed by PL/SQL. If you think it is better to use SQLPlus, you do not need to download this tool.
  : Http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
  Note: This article uses 64-bit. If your host does not support 64-bit, download the 32-bit package.

2. Start to install required components

1. Oracle installation is relatively simple. Click "Next" to complete the installation.

2. Configure the listener. ora file of Oracle. My file is in the "C: \ oracle \ product \ 11.2.0 \ dbhome_1 \ NETWORK \ ADMIN" directory. If you want to install Oracle elsewhere, go to the relevant directory. Find the file and open it in Notepad. Then we need to add a "SID_DESC" node. The content of this node is as follows:

(SID_DESC =
    (GLOBAL_DBNAME = ORCL)
    (ORACLE_HOME = C:\oracle\product\11.2.0\dbhome_1)   
    (SID_NAME = ORCL)
)

Then we need to add a parameter configuration item "DEFAULT_SERVICE_LISTENER" and set its value to "ORCL ". If this item is not configured, the following error will always be reported "ORA-12504: TNS: listener was not given the SERVICE_NAME in CONNECT_DATA" when calling the ODAC component, remember! Remember! This is what it takes me a day to design this parameter!

DEFAULT_SERVICE_LISTENER = ORCL

Therefore, the final content of the listener. ora file should be as follows. Pay attention to the yellow part:

# listener.ora Network Configuration File: C:\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
 
      (GLOBAL_DBNAME = ORCL)
 
      (ORACLE_HOME = C:\oracle\product\11.2.0\dbhome_1)
 
      (SID_NAME = ORCL)
 
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oracle\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
 
DEFAULT_SERVICE_LISTENER = ORCL
 
ADR_BASE_LISTENER = C:\oracle

Then, modify the tnsnames. ora file as follows:

# tnsnames.ora Network Configuration File: C:\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
 
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

3. Restart the "OracleOraDb11g_home1TNSListener" service and the "OracleServiceORCL" service.

4. Run the sqlplus command line tool, enter "/as sysdba" to log on to Oracle as an administrator, and then enter "startup" to start the database. The normal situation is shown in:

However, what should I do if it is shown?

Here we recommend a solution I found online at http://blog.csdn.net/lpftobetheone/article/details/11099785. You can fix the problem by referring to this Article. Note that the descriptions in some parts of this article are inconsistent. Please try your best to be accurate. If you do not understand it, leave a message below.

5. Oracle has been installed and configured. Now we need to create a table and insert several data records. I personally do not like to write SQL statements in sqlplus. If you like it, skip step 1 directly.

1) decompress the downloaded “sql0000-4.0.3.16.84-x64.zip20.compressed package. Then, double-click “sql0000.exe to run the package. Then, click the "Create connection" button shown in the following figure:

2) input as shown in, and then click Connect:

3) enter the following SQL statement:

-- Create a suzy user with the password orcl. The default tablespace is users.
Create user suzy identified by orcl default tablespace users;
 
-- Authorize suzy users not to restrict the use of their tablespace
Grant unlimited tablespace to suzy;
 
-- Authorize the connection permission and resource access permission of the suzy user.
Grant CONNECT, RESOURCE to suzy;

4) then run the SQL statement:

5) Repeat Step 2nd to log on as "suzy:

6) then write the SQL statement for table creation and execute:

7) insert two records:

6. Install the ODAC component. decompress the downloaded ODAC component to a directory, for example, C: \ ODAC112021Xcopy_x64.

7. Open the command line in the decompressed path in the previous step, enter the command "install. bat", and press Enter. We can see some command prompts, you can choose according to your needs, here I choose to install all the ODAC components, and install it to "C: \ odp.net ", enter the following command and wait until it is completed:

install.bat all C:\odp.net myhome

8. Open "C: \ odp.net" to see that many files have been created. Then, open the command window in this directory and enter the "configure. bat ", we will still see the command prompt information, then enter the following command according to the prompt information, and wait until it is completed:

configure.bat all myhome

9. Use VS to create a console project, and then add reference "C: \ Windows \ Microsoft. NET \ assembly \ GAC_64 \ Oracle. dataAccess \ v4.0 _ 4.112.2.0 _ 89b483f429c47342 \ oracle. dataaccess. dll ". Of course, if your vs gac list contains "Oracle. dataAccess ", you do not need to go to the GAC directory of drive C to find it. I ran the GAC directory of drive C because it was not found in the GAC list of my.

10. Write the test code as follows:

static void Main(string[] args)
{
    string connectionString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
    using (OracleConnection conn = new OracleConnection(connectionString))
    {
        string sql = "select * from testorcl";
        using (OracleCommand cmd = new OracleCommand(sql, conn))
        {
            conn.Open();
            OracleDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                string id = (reader.GetValue(0) ?? String.Empty).ToString();
                string name = (reader.GetValue(1) ?? String.Empty).ToString();
                Console.WriteLine(String.Format("ID={0}, Name={1}", id, name));
            }
        }
    }
    Console.ReadKey();
}

The connection string is configured in the "App. config" file as follows:

<connectionStrings>
  <add name="connstr" connectionString="Data Source=127.0.0.1;User ID=suzy;Password=orcl;" providerName="Oracle.DataAccess.Client"/>
</connectionStrings>

Set the target platform of the project to "x64". Otherwise, the operation fails. Of course, if your host is 32-bit, you need to select the "x86" platform.

11. The running result is as follows:

This is the general procedure for connecting to Oracle using a. NET application. Of course, Oracle also provides related class libraries that support EntityFramework. If you are interested, you can search for them on the Oracle official website.

References

• Http://ora-12xyz.com/error/ORA-12504
• Http://gerardnico.com/wiki/database/oracle/listener.ora
• Http://blog.csdn.net/lpftobetheone/article/details/11099785